sql components
Download
Skip this Video
Download Presentation
SQL components

Loading in 2 Seconds...

play fullscreen
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 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