Sql components
Download
1 / 12

SQL components - PowerPoint PPT Presentation


  • 100 Views
  • Uploaded on

SQL components. In Oracle. SQL in Oracle. SQL is made up of 4 components: DDL Data Definition Language CREATE, ALTER, DROP, TRUNCATE. Creates / Alters / Drops tables, views, sequences, indexes and synonyms. DML Data Manipulation Language SELECT, INSERT, UPDATE, DELETE. DCL

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' SQL components' - brendan-lyne


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Sql components

SQL components

In Oracle


Sql in oracle
SQL in Oracle

  • SQL is made up of 4 components:

    • DDL

      • Data Definition Language

      • CREATE, ALTER, DROP, TRUNCATE.

      • Creates / Alters / Drops tables, views, sequences, indexes and synonyms.

    • DML

      • Data Manipulation Language

      • SELECT, INSERT, UPDATE, DELETE.

    • DCL

      • Data Control Language

      • GRANT, REVOKE,

    • Transaction control statements

      • SET TRANSACTION, COMMIT, ROLLBACK


Create table statement
CREATE TABLE statement

  • Creates tables in your schema.

  • The basic Create statement:

    CREATE TABLE [schema_name].table_name

    (

    {column_name data_type}

    )

  • [] optional, {} one or more, lower case – user supplied names, upper case reserved words, [|] either or, () are part of the syntax.

  • See www.ss64.com/orasyntax/datatypes.html for a comprehensive list and description of data types.


Adding constraints
Adding constraints

  • To make one the attributes a primary key, put a CONSTRAINT on the table, by adding:

  • PRIMARY KEY (column_name).


Constraints
Constraints

  • Constraints can be imposed when the table is being created, in the CREATE statement.

  • Constraints can be on the column or the table.

  • Column constraints can be listed after the column data type; e.g. NOT NULL, UNIQUE, PRIMARY KEY, CHECK….

  • Other constraints can be added after all columns have been declared, by ALTERing the table.

  • These can include

    • primary keys, including compound primary keys, or

    • foreign keys.


Exercise
Exercise

  • Set up two tables.

    • Product with columns ProductCode 4 digits, ProductName 15 characters, ProductPrice Decimal – 4 digits with two after the decimal place. The product code is the primary key.

    • Supplier with columns SupplierId 4 digits, SName 15 characters. SupplierId is the primary key.

  • Find the tables in the catalog.

    • To find tables in the catalog, you manipulate the SQLPlus buffer.

    • Select * from cat


Data manipulation language
Data Manipulation Language

  • Data Manipulation language (DML) allows you to manipulate the contents of the tables in you schema, rather than the definition.

  • DML includes

    • Insert

    • Delete

    • Update

    • Select


Insert statement
Insert statement

  • This adds data to the table.

  • Either a full row can be added, or values can be put in individual columns.

  • NOTE: If only some of the columns are given values, the remainder of the columns have an undefined status of NULL.

  • If a column is constrained by the ‘NOT NULL’ constraint, every insert to the owning table must provide a value for that column, unless a DEFAULT value is provided for the table.



Exercise1
Exercise

  • Insert four rows into the supplier table, ensuring that the data types and constraints are respected.

  • Insert two rows into the product table, as above.

  • Check the contents of both tables by

    • Select * from tablename;

    • Note that the semicolon ends the statement.


Altering tables
Altering tables

  • Alter is part of the data definition language.

  • Alter allows a column to be added or dropped from the table.

    • ALTER TABLE customer ADD server NUMBER(7) DEFAULT 1234567;

  • Alter allows a constraint to be added or removed from a table.

    • ALTER TABLE customer ADD CONSTRAINT servedby FOREIGN KEY server REFERENCES staff(staffId);


Exercise2
Exercise

  • Use the Alter command to:

    • Add a new column called ‘supplier’ of type 4 digits, to the product table, with a default value of 1234.

    • Add a constraint to the product table, making the new ‘supplier’ field a foreign key, referencing the SupplierId in the Supplier table.

  • Lessons?

    • If this doesn’t work, why not?

    • What can you do to make it work?


ad