1 / 26

Chapter 5

Chapter 5. Structured Query Language (SQL2) Revision. In this lecture, you will learn:. About more advanced queries SQL features such as updatable views, stored procedures, and triggers. Four Question to Create A Query. What output do you want to see?

alina
Download Presentation

Chapter 5

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 5 Structured Query Language (SQL2) Revision

  2. In this lecture, you will learn: • About more advanced queries • SQL features such as updatable views, stored procedures, and triggers

  3. Four Question to Create A Query • What output do you want to see? • What do you already know (or what constraints are given)? • What tables are involved? • How are the tables joined together?

  4. Queries SELECT <column(s)>FROM <table name>WHERE <conditions>; • Creating partial listings of table contents Table 5.4 Mathematical Operators

  5. Examples SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <> 21344; • Mathematical operators on numeric/ integer • Mathematical operators on character attributes • Mathematical operators on dates SELECT P_CODE,P_DESCRIPT,P_ONHAND,P_MIN,P_PRICEFROM PRODUCTWHERE P_CODE < ‘1558-QWI’; SELECT P_DESCRIPT,P_ONHAND,P_MIN,P_PRICE,PINDATEFROM PRODUCTWHERE P_INDATE >= ‘01/20/2002’;

  6. Computed Columns • New columns can be created through valid computations or formulas • Formulas may contain mathematical operators • May contain attributes of any tables specified in FROM clause • Alias is alternate name given to table or column in SQL statement SELECT P_DESCRIPT,P_ONHAND,P_PRICE,P_ONHAND*P_PRICE AS TOTVALUE FROM PRODUCT;

  7. Operators SELECT * FROM PRODUCTWHERE V_CODE = 21344 OR V_CODE = 24288; • Logical: AND, OR, NOT • Rules of precedence • Conditions within parenthesis executed first • Boolean algebra • Special • BETWEEN - defines limits • IS NULL - checks for nulls • LIKE - checks for similar string • IN - checks for value in a set • EXISTS - opposite of IS NULL

  8. Advanced Data ManagementCommands • ALTER - changes table structure • ADD - adds column • MODIFY - changes column characteristics • Entering data into new column ALTER TABLE <table name>ADD (<column name> <new column characteristics>); ALTER TABLE <table name>MODIFY (<column name> <new column characteristics>); UPDATE PRODUCTSET P_SALECODE = ‘2’WHERE P_CODE = ‘1546-QQ2’;

  9. Advanced Data Management Commands (con’t.) • Dropping a column • Arithmetic operators and rules of precedence ALTER TABLE VENDORDROP COLUMN V_ORDER; Table 5.5

  10. Advanced Data Management Commands (con’t.) • Deleting a table from database • Primary and foreign key designation DROP TABLE PART; ALTER TABLE LINE ADD PRIMARY KEY (INV_NUMBER, LINE_NUMBER) ADD FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE ADD FOREIGN KEY (PROD_CODE) REFERENCES PRODUCT;

  11. Example Aggregate Function Operations SELECT COUNT(DISTINCT V_CODE)FROM PRODUCT; SELECT COUNT(DISTINCT V_CODE)FROM PRODUCTWHERE P_PRICE <= 10.00; • COUNT • MAX and MIN SELECT MIN(P_PRICE)FROM PRODUCT; SELECT P_CODE, P_DESCRIPT, P_PRICEFROM PRODUCTWHERE P_PRICE = MAX(P_PRICE);

  12. Example Aggregate Function Operations (con’t.) • SUM • AVG SELECT SUM(P_ONHAND * P_PRICE)FROM PRODUCT; SELECT P_DESCRIPT, P_ONHAND, P_PRICE, V_CODEFROM PRODUCTWHERE P_PRICE > (SELECT AVG(P_PRICE) FROM PRODUCT)ORDER BY P_PRICE DESC;

  13. More Complex Queries and SQL Functions • Ordering a listing • Results ascending by default • Descending order uses DESC • Cascading order sequence ORDER BY <attributes> ORDER BY <attributes> DESC ORDER BY <attribute 1, attribute 2, ...>

  14. More Complex Queries and SQL Functions (con’t.) • Listing unique values • DISTINCT clause produces list of different values • Aggregate functions • Mathematical summaries SELECT DISTINCT V_CODE FROM PRODUCT; Table 5.6

  15. More Complex Queries and SQL Functions (con’t.) • Grouping data • Creates frequency distributions • Only valid when used with SQL arithmetic functions • HAVING clause operates like WHERE for grouping output SELECT P_SALECODE, MIN(P_PRICE)FROM PRODUCT_2GROUP BY P_SALECODE; SELECT V_CODE,COUNT(DISTINCT(P_CODE)),AVG(P_PRICE) FROM PRODUCT_2 GROUP BY V_CODE HAVING AVG(P_PRICE) < 10;

  16. More Complex Queries and SQL Functions (con’t.) • Joining database tables • Data are retrieved from more than one table • Recursive queries joins a table to itself • Outer joins can be used when ‘null’ values need to be included in query result SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE, VENDOR.V_NAME, VENDOR.V_CONTACT, VENDOR.V_AREACODE, VENDOR.V_PHONEFROM PRODUCT, VENDORWHERE PRODUCT.V_CODE = VENDOR.V_CODE; SELECT A.EMP_NUM,A.EMP_LNAME,A.EMP_MGR, B.EMP_LNAME FROM EMP A, EMP B WHERE A.EMP_MGR=B.EMP_NUM ORDER BY A.EMP_MGR

  17. SQL JOIN (Two Table Joining) FROM table1 INNER JOIN table2 ON table1.column = table2.column SQL 92 syntax (Access and SQL Server) FROM table1, table2 WHERE table1.column = table2.column SQL 89 syntax (Oracle & DB2) FROM table1, table2 JOIN table1.column = table2.column Informal syntax

  18. Syntax for Three Tables Join SQL ‘92 syntax to join three tables FROM Table1 INNER JOIN (Table2 INNER JOIN Table3 ON Table2.ColA = Table3.ColA) ON Table1.ColB = Table2.ColB SQL ‘89 syntax to join three tables FROM table1, table2, Table3 WHERE table1.column = table2.column AND table2.column = table3.column

  19. More Complex Queries and SQL Functions (con’t.) • Virtual tables: creating a view • CREATE VIEW command • Creates logical table existing only in virtual memory • SQL indexes CREATE VIEW PRODUCT_3 ASSELECT P_DESCRIPT, P_ONHAND, P_PRICEFROM PRODUCTWHERE P_PRICE > 50.00; CREATE INDEX P_CODEXON PRODUCT(P_CODE);

  20. Example of SQL Join Q: List the Last Name and Phone of anyone who bought a registered White cat between 6/1/2001 and 12/31/2001.

  21. Example of SQL Join (con’t) • Identify the tables involved. • Look at the columns you want to see. • LastName, Phone: Customer • Look at the columns used in the constraints. • Registered, Color, Category: Animal • Sale Date: Sale • Find connector tables. • To connect from Customer to Animal, four tables involved • Select the desired columns and test the query. • Enter the constraints. • Set Order By columns. • Add Group By columns. • Add summary computations to the SELECT statement.

  22. Example of SQL Join (con’t) • SELECT LastName, Phone FROM Customer, Animal, SaleAnimal, Sale • WHERE Customer.CustomerID = Sale.CustomerID • AND Sale.SaleID = SaleAnimal.SaleID • AND SaleAnimal.AnimalID = Animal.AnimalID • AND Category="Cat“ • AND Registered Is Not Null • AND Color Like "*White*" • AND SaleDate Between ‘6/1/2001’ And ‘12/31/2001’);

  23. Triggers • Procedural SQL code invoked before or after data row is selected, inserted, or updated • Associated within the table • Table may have multiple triggers • Invoked before or after a data row is selected, inserted, or updated • Can update values, insert records, and call procedures • Add processing power

  24. Triggers (con’t.) • DB2 example CREATE TRIGGER <trigger_name>[BEFORE/AFTER] [DELETE/INSERT/UPDATE OF <column_name] ON <table_name>[FOR EACH ROW MODE DB2SQL]BEGIN PL/SQL instructions;……………END;

  25. Example of Trigger Product table

  26. Example of Trigger (con’t) TRG_PRODUCT_REORDER create trigger TRG_PRODUCT REORDER after update of p_onhand, p_min on product for each row mode db2sql update product set reorder =1 where (p_onhand <= p_min);

More Related