Complex Queries, SQL Functions, and Procedural SQL

1. Chapter 3Structured Query Language (SQL) Database Systems: Design, Implementation, and Management 4th Edition Peter Rob & Carlos Coronel

2. More Complex Queries and SQL Functions • Ordering a Listing ORDER BY <attributes> SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICEFROM PRODUCTORDER BY P_PRICE;

3. Selected PRODUCT Table Attributes Ordered by (Ascending) P_PRICE Figure 3.18

4. The Partial Listing of the EMPLOYEE Table Figure 3.19

5. SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONEFROM EMPLOYEEORDER BY EMP_LNAME, EMP_FNAME, EMP_INITIAL; Figure 3.20

6. SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICEFROM PRODUCTWHERE P_INDATE < ‘08/20/1999’AND P_PRICE <= 50.00ORDER BY V_CODE, P_PRICE, DESC; Figure 3.21 A Query Based on Multiple Restrictions

7. More Complex Queries and SQL Functions • Listing Unique Values SELECT DISTINCT V_CODEFROM PRODUCT; Figure 3.22 A Listing of Distinct V_CODE Values in the PRODUCT Table

8. Some Basic SQL Numeric Functions Table 3.6

9. Querying a Query: Nested Process Figure 3.23

10. COUNT Function Output Examples Figure 3.24

11. MAX and MIN Function Output Examples Figure 3.25

12. More Complex Queries and SQL Functions SUM SELECT SUM(P_ONHAND*P_PRICE)FROM PRODUCT; AVG SELECT P_DESCRIPT, P_ONHAND, P_PRICE, V_CODEFROM PRODUCTWHERE P_PRICE > (SELECT AVG(P_PRICE) FROM PRODUCT)ORDER BY P_PRICE DESC;

13. AVG Function Output Examples Figure 3.26

14. Grouping Data GROUP BY SELECT P_SALECODE, MIN(P_PRICE)FROM PRODUCT_2GROUP BY P_SALECODE;

15. Improper Use of the GROUP BY Clause Figure 3.28

16. An Application of the HAVING Clause Figure 3.29

17. More Complex Queries and SQL Functions • Virtual Tables: Creating a View Figure 3.30

18. More Complex Queries and SQL Functions • SQL Indexes CREATE INDEX P_CODEXON PRODUCT(P_CODE); CREATE UNIQUE INDEX P_CODEXON PRODUCT(P_CODE);

19. More Complex Queries and SQL Functions • Joining Database Tables 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; Table 3.7 Creating Links Through Foreign Keys

20. The Results of a JOIN Figure 3.31

21. More Complex Queries and SQL Functions SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONEFROM PRODUCT, VENDORWHERE PRODUCT.V_CODE = VENDOR.V_CODEAND P_INDATE > ‘08/15/1999’; Figure 3.32An Ordered and Limited Listing After a JOIN

22. Procedural SQL • Shortcomings of SQL • SQL doesn’t support execution of a stored set of procedures based on some logical condition. • SQL fails to support the looping operations. • Solutions • Embedded SQL • To remedy the above shortcomings, SQL statements can be inserted within the procedural programming language • The embedded SQL approach involves the duplication of application code in many programs. • Shared Code • Critical code is isolated and shared by all application programs. • This approach allows better maintenance and logic control. • Procedural SQL

23. Procedural SQL • Procedural SQL • Procedural SQL allows the use of procedural code and SQL statements that are stored within the database. • The procedural code is executed by the DBMS when it is invoked by the end user. • End users can use procedural SQL (PL/SQL) to create: • Triggers • Stored procedures • PL/SQL functions

24. Procedural SQL • Triggers • A trigger is procedural SQL code that is automatically invoked by the RDBMS upon the occurrence of a data manipulation event. • A trigger is always invoked before or after a data row is selected, inserted, or updated. • A trigger is always associated with a database table. • Each database table may have one or more triggers. • A trigger is executed as part of the transaction that triggered it.

25. Procedural SQL • Role of triggers • Triggers can be used to enforce constraints that cannot be enforced at the design and implementation levels. • Triggers add functionality by automating critical actions and providing appropriate warnings and suggestions for remedial action. • Triggers can be used to update table values, insert records in tables, and call other stored procedures. • Triggers add processing power to the RDBMS and to the database system.

26. The Revised PRODUCT Table Figure 3.33

27. The PRODUCT List Output in the Oracle RDBMS Figure 3.34

28. Procedural SQL • Syntax to create a trigger in ORACLE CREATE OR REPLACE TRIGGER <trigger_name>[BEFORE/AFTER][DELETE/INSERT/UPDATE OF <column_name] ON <table_name>[FOR EACH ROW]BEGIN PL/SQL instructions; ……………END;

29. Creation of the Oracle Trigger for the PRODUCT Table Figure 3.35

30. The PRODUCT Table’s P_REORDER Field is Updated by the Trigger Figure 3.36

31. The P_REORDER Value Mismatch Figure 3.37

32. The Second Version of the PRODUCT_REORDER Trigger Figure 3.38

33. Figure 3.39

34. The P_REORDER Flag Has Not Been Properly Set After Increasing the P_ONHAND Value Figure 3.40

35. The Third Version of the Product Reorder Trigger Figure 3.41

36. Execution of the Third Trigger Version Figure 3.42

37. Procedural SQL • Stored Procedures • A stored procedure is a named collection of procedural and SQL statements. • Stored procedures are stored in the database and invoked by name. • Stored procedures are executed as a unit. • The use of stored procedures reduces network traffic, thus improving performance.

38. Procedural SQL • Syntax to create a stored procedure CREATE OR REPLACE PROCEDURE procedure_name (argument IN/OUT data-type, etc) IS/AS BEGIN DECLARE variable name and data type PL/SQL or SQL statements;END; • Syntax to invoke a stored procedure EXEC store_procedure_name (parameter, parameter, …)

39. Procedural SQL • Stored Procedures • DECLARE is used to specify the variables used within the procedure. • Argument specifies the parameters that are passed to the stored procedure. • IN / OUT indicates whether the parameter is for INPUT or OUTPUT or both. • Data-type is one of the procedural SQL data types used in the RDBMS.

40. Creating and Invoking A Simple Stored Procedure Figure 3.43

41. The PROD_SALE Stored Procedure Figure 3.44

42. Creation of the PROD_SALE Stored Procedure Figure 3.45

43. Executing the PROD_SALE Stored Procedure Figure 3.46

44. Procedural SQL • PL/SQL Stored Functions • A stored function is a named group of procedural and SQL statements that returns a value. • Syntax to create a function: CREATE FUNCTION function_name (argument IN data-type, etc)RETURN data-typeAS BEGIN PL/SQL statements; RETURN (value); ……END;

45. The Y2K Problem • Problem • Many database vendors use 2-digit date formats as the default. How the 2-digit year is viewed depends on how the DBMS vendor treats dates. • Solutions • Design and implement database applications that always enter and display dates with four-digit years and use a Julian date field format. • Julian date stores date field values as the number of days since a predetermined date.

46. The Default P_INDICATE Two-Digit Year Format Figure 3.47

47. Formatting the Date Fields to Four-Digit Years Figure 3.48

48. Using the Input Mask to Force Four-Digit Year Entries in MS Access Figure 3.49