1 / 12

SQL components

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

brendan-lyne
Download Presentation

SQL components

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL components In Oracle

  2. 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

  3. 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.

  4. Adding constraints • To make one the attributes a primary key, put a CONSTRAINT on the table, by adding: • PRIMARY KEY (column_name).

  5. 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.

  6. 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

  7. 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

  8. 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.

  9. Example insert (full version)

  10. 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.

  11. 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);

  12. 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?

More Related