1 / 34

Chapter 1

Chapter 1. Structured Query Language(SQL). Outlines. Data Definition Language (DDL) Creating Tables Altering Tables Dropping Tables Data Manipulation Language (DML) INSERT, UPDATE, DELETE SELECT Statements Union, Intersect, Minus Operations Joins (Simple, Natural)

cade-moon
Download Presentation

Chapter 1

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. Chapter 1 Structured Query Language(SQL)

  2. Outlines • Data Definition Language (DDL) • Creating Tables • Altering Tables • Dropping Tables • Data Manipulation Language (DML) • INSERT, UPDATE, DELETE SELECT Statements • Union, Intersect, Minus Operations • Joins (Simple, Natural) • Outer Joins (Left, Right, Full)

  3. Creating tables • Syntax: • CREATETABLE table-name • ( • column-name-1 data-type-1 [constraint], • column-name-2 data-type-2 [constraint], • … • column-name-n data-type-n [constraint] • ); • constraint will be one of the following: • PRIMARY KEY (PK) • FOREIGN KEY (FK) • NOT NULL (NN) • UNIQUE (U) • CHECK

  4. Example of Table Creation CREATETABLE Sailors (sidNUMBER(4)PRIMARYKEY, snameCHAR(30), rating NUMBER(2), age NUMBER(2)); CREATETABLE Boats (bid NUMBER(2)PRIMARYKEY, bnameCHAR(30), color CHAR(30) CHECK(COLORIN(‘Red‘, ‘Green’, ‘Blue’, ‘Yellow’))); CREATETABLE Reserves (sidNUMBER(4), bid NUMBER(2), day DATEDEFAULTSYSDATE, CONSTRAINTreserves_pkPRIMARYKEY(sid, bid), CONSTRAINTreserves_sid_fkFOREIGNKEY(sid)REFERENCES Sailors, CONSTRAINTreserves_bid_fkFOREIGNKEY(bid)REFERENCES Boats);

  5. Cascading/Restricting tables • SQL allows to create a table with CASCADE and RESTRICT options. • CASCADE option deletes or updates the row from the parent table (containing PRIMARY KEYs), and automatically delete or update the matching rows in the child table (containing FOREIGN KEYs). • RESTRICT option bars the removal (i.e. using delete) or modification (i..e using update) of rows from the parent table. Examples: ON UPDATE CASCADE (or RESTRICT) ON DELETE CASCADE ON UPDATE SET DEFAULT ON UPDATE SET NULL

  6. Renaming a table To rename a table, the SQL ALTER TABLE syntax is: ALTERTABLEtable_nameRENAMETOnew_table_name; Example: ALTERTABLE suppliers RENAMETO vendors;

  7. Adding Constraints Constraints: • NOT NULL • UNIQUE • PRIMARY KEY • FOREIGN KEY • CHECK • DEFAULT Examples: ALTERTABLE Supplier ADDCONSTRAINTSupplier_PKPRIMARYKEY (Supplier_id); ALTERTABLE Product ADDCONSTRAINTProduct_FKFOREIGNKEY (Supplier_id) REFERENCES Supplier(Supplier_id);

  8. Adding column(s) to a table Syntax: ALTERTABLEtable_nameADD( column_1 column-definition, column_2 column-definition, ... column_ncolumn_definition); Example: ALTERTABLE Employees ADDsalary NUMBER(6);

  9. Modifying column(s) in a table Syntax: ALTERTABLEtable_nameMODIFY( column_1 column_type, column_2 column_type, ... column_ncolumn_type); Examples: ALTERTABLE supplier MODIFYsupplier_nameVARCHAR2 (100)NOTNULL; ALTERTABLE supplier MODIFY (supplier_nameVARCHAR2 (100)NOTNULL, city VARCHAR2 (75));

  10. Drop column(s) in a table Syntax: ALTERTABLEtable_nameDROPCOLUMNcolumn_name; Example: ALTERTABLE supplier DROPCOLUMNsupplier_name;

  11. Rename column(s) in a table Syntax: ALTERTABLEtable_nameRENAMECOLUMNold_name to new_name; Example: ALTERTABLE supplier RENAMECOLUMNsupplier_name to sname;

  12. Removing Tables DROPTABLEtable-name [CASCADE | RESTRICT] DROPTABLE statement allows you to remove tables from your schema: DROPTABLEtable_name; DROPTABLEtable_nameCASCADECONSTRAINTS; Cascade constraints Specify CASCADECONSTRAINTS to drop all referential integrity constraints that refer to primary and unique keys in the dropped table.

  13. Creating indexes Speed up random/sequential access to base table data Example: CREATEINDEX NAME_IDX ONCUSTOMER_T(CUSTOMER_NAME) This makes an index for the CUSTOMER_NAME field of the CUSTOMER_T table

  14. Insert Statement • Adds data to a table INSERTINTO CUSTOMER_T VALUES (001, ‘Contemporary Casuals’, ‘1355 S. Himes Blvd.’, ‘Gainesville’, ‘FL’, 32601); • Inserting a record that has some null attributes requires identifying the fields that actually get data INSERTINTO PRODUCT_T (PRODUCT_ID, PRODUCT_DESCRIPTION, PRODUCT_FINISH, STANDARD_PRICE, PRODUCT_ON_HAND) VALUES (1, ‘End Table’, ‘Cherry’, 175, 8); • Inserting from another table • INSERTINTO CA_CUSTOMER_T SELECT*FROM CUSTOMER_T WHERE STATE = ‘CA’;

  15. Delete Statement • Removes rows from a table • Delete certain rows DELETEFROM CUSTOMER_T WHERE STATE = ‘HI’; • Delete all rows DELETEFROM CUSTOMER_T;

  16. Update Statement It modifies data in existing rows. Syntax: UPDATEtable_nameSETcolumn1 = value1, column2 = value2,... WHEREsome_column = some_value; Example: UPDATE PRODUCT_T SET UNIT_PRICE = 775 WHERE PRODUCT_ID = 7; UPDATEPersons SETAddress=‘UAE', City=‘Abu Dhabi‘ WHERELastName=‘Al Ameri' ANDFirstName=‘Jasim‘;

  17. SELECT Statement • Used for queries on single or multiple tables • Clauses of the SELECT statement: • SELECT • List the columns (and expressions) that should be returned from the query • FROM • Indicate the table(s) or view(s) from which data will be obtained • WHERE • Indicate the conditions under which a row will be included in the result • GROUP BY • Indicate categorization of results • HAVING • Indicate the conditions under which a category (group) will be included • ORDER BY • Sorts the result according to specified criteria

  18. SELECT Example Find products with standard price less than $275 SELECTPRODUCT_NAME, STANDARD_PRICE FROMPRODUCT_V WHERESTANDARD_PRICE < 275; Comparison Operators in SQL

  19. SELECT Example Using Alias Alias is an alternative column or table name Example: SELECTCUST.CUSTOMER ASNAME, CUST.CUSTOMER_ADDRESS FROMCUSTOMER_V CUSTWHERENAME = ‘Home Furnishings’;

  20. SELECT Example Using a Function Using the COUNT aggregate function to find totals SELECTCOUNT(*) FROMORDER_LINE_V WHEREORDER_ID = 1004; • Note: with aggregate functions you can’t have single-valued columns included in the SELECT clause

  21. SELECT Example–Boolean Operators AND, OR, and NOT Operators for customizing conditions in WHERE clause SELECT PRODUCT_DESCRIPTION, PRODUCT_FINISH, STANDARD_PRICE FROMPRODUCT_V WHERE(PRODUCT_DESCRIPTION LIKE ‘%Desk’ ORPRODUCT_DESCRIPTION LIKE ‘%Table’) AND UNIT_PRICE > 300; Note: the LIKE operator allows you to compare strings using wildcards. For example, the % wildcard in ‘%Desk’ indicates that all strings that have any number of characters preceding the word “Desk” will be allowed

  22. Sorting Results with the ORDER BY Clause Sort the results first by STATE, and within a state by CUSTOMER_NAME SELECTCUSTOMER_NAME, CITY, STATE FROM CUSTOMER_V WHERESTATE IN (‘FL’, ‘TX’, ‘CA’, ‘HI’) ORDERBY STATE, CUSTOMER_NAME;

  23. Aggregate Functions The five SQL functions include: COUNT: return number of values in specified column SUM: return sum of values in specified column AVG: return average of values in specified column MIN: return smallest value in specified column MAX: return largest value in specified column COUNT, MIN, MAX apply to numeric and non numeric fields SUM and AVG can only be applied to numeric fields Example: SELECTCUSTOMER_STATE, COUNT (CUSTOMER_STATE) FROMCUSTOMER_V GROUPBY CUSTOMER_STATE;

  24. Qualifying Results by Categories Using the HAVING Clause For use with GROUP BY SELECTCUSTOMER_STATE, COUNT(CUSTOMER_STATE) FROM CUSTOMER_V GROUPBYCUSTOMER_STATE HAVINGCOUNT(CUSTOMER_STATE) > 1;

  25. SQL: UNION Query • Given two relations R and S, the union of these two relations, denoted by R ᴜ S, is the set of all tuples that are currently present in R or are currently present in S or present in both relations. • Each SQL SELECT statement within the UNION query must have the same number of fields in the result sets with similar data types. Syntax: SELECT field1, field2, ... fieldnFROM table_name1 UNION SELECT field1, field2, ... fieldnFROM Table_name2; Example: SELECTsupplier_id, supplier_nameFROM suppliers WHEREsupplier_id > 2000 UNION SELECTcompany_id, company_nameFROM companies WHEREcompany_id > 1000 ORDERBY 2;

  26. SQL: INTERSECT Query • Given two relations R and S, the intersection of these two relations, denoted by R ∩ S, is the set of tuples currently present in both relations. • Each SQL statement within the SQL INTERSECT query must have the same number of fields in the result sets with similar data types. Syntax: SELECT field1, field2, ... fieldnFROM table_name1 INTERSECT SELECT field1, field2, ... fieldnFROM Table_name2; Example: SELECTsupplier_id, supplier_nameFROM suppliers WHERE supplier_id > 2000 INTERSECT SELECT company_id, company_nameFROM companies WHERE company_id > 1000 ORDERBY 2;

  27. SQL: MINUS Query • Given two relations R and S, the difference of these two relations, denoted by R - S, is the set of all tuples that are currently present in relation R and are not currently present in relation S. • This operation is NOT commutative: R – S ≠ S – R Syntax: SELECT field1, field2, ... fieldnFROM table_name1 MINUS SELECT field1, field2, ... fieldnFROM Table_name2; Example: SELECT supplier_id, supplier_nameFROM suppliers WHERE supplier_id> 2000 MINUS SELECT company_id, company_nameFROM companies WHERE company_id> 1000 ORDERBYSupplier_id;

  28. Examples C_Programmer Java_Programmer C_Programmer ᴜ Java_Programmer = ? C_Programmer ∩ Java_Programmer = ? C_Programmer – Java_Programmer = ?

  29. SQL Inner Join (simple join) • It is the most common type of SQL join. • SQL inner joins return all rows from multiple tables where the join condition is met. Example: SELECTsuppliers.supplier_id, suppliers.supplier_name, orders.order_dateFROM suppliers, orders WHEREsuppliers.supplier_id = orders.supplier_id; • This SQL inner join example would return all rows from the suppliers and orders tables where there is a matching supplier_id value in both the suppliers and orders tables.

  30. Natural join The SQL NATURAL JOIN is a type of equi-join and is structured in such a way that, columns with same name of associate tables will appear once only. Example: SELECT*FROM Dept NATURALJOINEmp;

  31. Outer Joins LEFT OUTER JOIN: All tuples in the left relation are preserved, even if they have no matching tuples in the right relation. RIGHT OUTER JOIN: All tuples in the right relation are preserved, even if they have no matching tuples in the left relation. FULL OUTER JOIN: This operation keeps all tuples in both the left and the right relations when no matching tuples are found, padding them with null values as needed.

  32. Left Outer Join SELECT * FROM Dept LEFTOUTERJOINEmp ON Dept.ID = Emp.Dept_ID; Or SELECT * FROM Dept D, Emp E WHERE D.ID = E.Dept_ID(+);

  33. Right Outer Join SELECT * FROM Dept RIGHTOUTERJOINEmp ON Dept.ID = Emp.Dept_ID;; Or SELECT* FROM Dept D, Emp E WHERE D.ID (+) = E.Dept_ID;

  34. Full Outer Join SELECT * FROM Dept FULLOUTERJOINEmpON Dept.ID = Emp.Dept_ID; Or SELECT * FROM Dept D, Emp E WHERE D.ID = E.Dept_ID(+) UNION SELECT * FROM Dept D, Emp E WHERE D.ID(+) = E.Dept_ID;

More Related