1 / 27

Structured Query Language (SQL)

Structured Query Language (SQL) . One language for Relational Databases ANSI Standard Oracle: SQL*Plus MS SQL Server: Transact-SQL IBM DB2 MySQL Sybase . Structured Query Language (SQL) . Case insensitive (like VB) Free style (like C++ and Java)

ivy
Download Presentation

Structured Query Language (SQL)

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. Structured Query Language (SQL) • One language for Relational Databases • ANSI Standard • Oracle: SQL*Plus • MS SQL Server: Transact-SQL • IBM DB2 • MySQL • Sybase ...

  2. Structured Query Language (SQL) • Case insensitive (like VB) • Free style (like C++ and Java) • Statement terminator – semicolon (like C++ and Java) • Programming Rule: Style Each clause on a separate line Each field on a separate line Each table constraint on a separate line

  3. Structured Query Language (SQL) • DDL (Data Definition Language) Create Table (user) ... Drop Table (user) ... Alter Table (user) ... • DML (Data Manipulation language) Select * From Branch … Insert into Branch ... Update branch ... Delete from BRANCH ...

  4. Oracle Data Types • Char(size) fixed length string up to 2000 bytes default is 1 byte blanks are padded on right when fewer chars entered • Varchar2(size) variable size string up to 2000 bytes must specify the limit (size) • Varchar(size) same as Varchar2(size) better to use Varchar2

  5. Oracle Data Types • Integer, int, smallint • Float • Date Valid dates from 1-Jan-4712 B.C. to 31-Dec-4712 A.D. Default format: DD-MON-YY 23-Mar-09 Including time

  6. Oracle Data Types • Number(l, d) l: length (total) d: decimal digits number (5, 2): largest value is 999.99 • Decimal(l, d), Numeric(l, d) same as number(l, d) SQL standard • blob: binary large object, up to 4 GB • clob: character large object, up to 4 GB • raw(size): raw binary data, up to 2000 bytes • ...

  7. Create Oracle Tables Create table Staff ( Sno char(4), Bno char(4), FName Varchar2(20), LName Varchar2(20), DOB Date, Salary Number, Primary Key (Sno), Foreign Key (Bno) References Branch); -- This is a comment: Follow the style! -- Must use () for PK and FK

  8. Integrity Rules: Constraints • Column Constraints • Table Constraints

  9. Column Constraints • Primary key Sno Char(4) Primary Key, • Alternate Key SSN Char(9) Unique, • Foreign Key BNo char(4) References Branch, -- when attribute has the same name BNo char(4) References Branch(B_no), -- when attribute has different names BNo char(4) References Branch on Delete Cascade, -- Do NOT use “Foreign Key” in column constaints!

  10. Column Constraints • Domain constraint Salary Number Check (Salary > 10000 and Salary < 200000), PType varchar2(6) Check (PType in ('House', 'Flat', 'Appt')), -- Strings are in single quotes, NOT double quotes Bno Char(4) Default 'B363' References Branch, Rent Float Check (Rent Between 200 and 400), -- between is Inclusive • Required data LName Varchar2(20) Not Null, -- Can be specified only by column constraint

  11. Column Constraints Create table Staff ( SNo char(4) Primary Key, Bno Char(4) Default 'B363' References Branch on Delete Cascade, FName Varchar2(20) Not Null, LName Varchar2(20) Not Null, -- assuming functions DateDiff and Now DOB Date Not Null Check (DateDiff(Year, Now, DOB) >= 16), Salary Number Check (Salary Between 30000 and 100000), SSN Char(9) Unique, Tel_No Char(12)); -- Primary Key, Unique, References should be the last constraint for a column -- Do not use Foreign key for column constraints, only use References

  12. Table Constraints • Integrity Rules • Apply to one or more columns • Cannot use Not Null in table constraints

  13. Table Constraints • Primary Key -- Constraint name is optional -- Oracle will provide a name if not provided Constraint PK_Staff Primary Key (SNo), Primary Key (C1, C2), • Alternate Key Unique (SSN), Unique (C1, C2),

  14. Table Constraints • Foreign Key Foreign Key (BNo) References Branch (BNo) on Delete Cascade, -- Primary key (c1, c2) Foreign Key (c1, c2) References TableA, -- Same order as PK Foreign Key (c2, c1) References TableA(c2, c1), -- Different order from PK • Range (Domain) Constraint Range_of_Salary Check (Salary >= 30000 and Salary <= 200000),

  15. Table Constraints Create table Staff ( SNo char(4), FName Varchar2(20) Not Null, LName Varchar2(20) Not Null, DOB Date, Salary Number, BNo Char(4), Tel_No Char(12), SSN Char(11), Constraint PK_Staff Primary Key (SNo), Constraint Range_of_Salary Check (Salary >= 30000 and Salary <= 200000), Unique (SSN), Foreign Key (BNo) References Branch (BNo) on Delete Cascade);

  16. Login to Your Oracle Account • SQL*Plus • Should be linked to 92 client • UserName • Password • Host String ORED • Case insensitive

  17. Create a Table SQL> Create Table Test1 ( C1 char(5), C2 Varchar2(50), C3 Integer, C4 Date, Primary Key (C1));

  18. Oracle Editor • Last command is stored in a buffer file • Run the command in the buffer SQL> Run SQL> R SQL> / • Edit the last command • May need to change directory SQL> edit -- Notepad to edit the last command -- No ; is needed at the end -- Save and exit editor

  19. Oracle Editor Change directory File Menu Open Go to a folder you have the rights Open any text file It does not run the command.

  20. Oracle Command Describe SQL> Desc Test1 -- Oracle*Plus command may not need ;

  21. Log out SQL> exit

  22. Insert Records Insert into Test1 Values ('SL21', 'John', 101, '5-Dec-60'); -- All fields in the same order as defined -- Each clause on a separate line -- Could be like this: Insert into Test1 Values ('SL20', 'John', 101, '5-Dec-60'); -- Do not go to next line within a string Insert into Test1 Values ('SL23', ‘Long string on Two lines', 101, '5-Dec-60'); Insert into Test1 Values ('SL24', ‘Long string’ || ‘ on Two lines', 101, '5-Dec-60');

  23. Insert Records -- Could use Null if unknown -- Even there is a Check constraint -- Cannot use Null if defined with Not Null Insert into Test1 Values ('SL21', 'John', Null, null);

  24. Insert Records -- Insert values for all fields in the same order -- as table schema Insert into Test1 Values ('SL21', 'John', 101, '5-Dec-60'); -- Insert values for some fields in any order -- All other fields get a Null value Insert into Test1(C1, c3) Values ('SL21', 101);

  25. Retrieve Records Select * From test1; -- Each clause on a separate line Select * From Test1;

  26. Command Commit • DDL commands are sent back to server and executed there • DML commands are executed at client site • Use commit to send results back to server

  27. Schedule • Project – Phase I Due beginning of Wednesday class • Assignment 7 Due Friday, March 25 Beginning of class Individual Assignment • Wednesday Script file (Assignment 7) Lab 206

More Related