Chapter 8 - Solutions.pdf 5g4a1s

Chapter 8 Advanced SQL

Chapter 8 Advanced SQL Answers to Review Questions 1. What is a CROSS ? Give an example of its syntax. A CROSS is identical to the PRODUCT relational operator. The CROSS is also known as the Cartesian product of two tables. For example, if you have two tables, AGENT, with 10 rows and CUSTOMER, with 21 rows, the CROSS resulting set will have 210 rows and will include all of the columns from both tables. Syntax examples are: SELECT * FROM CUSTOMER CROSS AGENT; or SELECT * FROM CUSTOMER, AGENT If you do not specify a condition when ing tables, the result will be a CROSS or PRODUCT operation. 2. What three types are included in the OUTER classification? An OUTER is a type of operation that yields all rows with matching values in the columns as well as all unmatched rows. (Unmatched rows are those without matching values in the columns). The SQL standard prescribes three different types of operations: LEFT [OUTER] RIGHT [OUTER] FULL [OUTER] . The LEFT [OUTER] will yield all rows with matching values in the columns, plus all of the unmatched rows from the left table. (The left table is the first table named in the FROM clause.) The RIGHT [OUTER] will yield all rows with matching values in the columns, plus all of the unmatched rows from the right table. (The right table is the second table named in the FROM clause.) The FULL [OUTER] will yield all rows with matching values in the columns, plus all the unmatched rows from both tables named in the FROM clause. 3. Using tables named T1 and T2, write a query example for each of the three types you described in Question 2. Assume that T1 and T2 share a common column named C1.

291

Chapter 8 Advanced SQL

LEFT OUTER example: SELECT * FROM T1 LEFT OUTER T2 ON T1.C1 = T2.C1; RIGHT OUTER example: SELECT * FROM T1 RIGHT OUTER T2 ON T1.C1 = T2.C1; FULL OUTER example: SELECT * FROM T1 FULL OUTER T2 ON T1.C1 = T2.C1; 4. What is a subquery, and what are its basic characteristics? A subquery is a query (expressed as a SELECT statement) that is located inside another query. The first SQL statement is known as the outer query, the second is known as the inner query or subquery. The inner query or subquery is normally executed first. The output of the inner query is used as the input for the outer query. A subquery is normally expressed inside parenthesis and can return zero, one, or more rows and each row can have one or more columns. A subquery can appear in many places in a SQL statement:  as part of a FROM clause,  to the right of a WHERE conditional expression,  to the right of the IN clause,  in a EXISTS operator,  to the right of a HAVING clause conditional operator,  in the attribute list of a SELECT clause. Examples of subqueries are: INSERT INTO PRODUCT SELECT * FROM P; DELETE FROM PRODUCT WHERE V_CODE IN (SELECT V_CODE FROM VENDOR WHERE V_AREACODE = „615‟); SELECT FROM WHERE

V_CODE, V_NAME VENDOR V_CODE NOT IN (SELECT V_CODE FROM PRODUCT);

5. What are the three types of results a subquery can return? A subquery can return 1) a single value (one row, one column), 2) a list of values (many rows, one column), or 3) a virtual table (many rows, many columns).

292

Chapter 8 Advanced SQL 6. What is a correlated subquery? Give an example. A correlated subquery is subquery that executes once for each row in the outer query. This process is similar to the typical nested loop in a programming language. Contrast this type of subquery to the typical subquery that will execute the innermost subquery first, and then the next outer query … until the last outer query is executed. That is, the typical subquery will execute in serial order, one after another, starting with the innermost subquery. In contrast, a correlated subquery will run the outer query first, and then it will run the inner subquery once for each row returned in the outer subquery. For example, the following subquery will list all the product line sales in which the “units sold” value is greater than the “average units sold” value for that product (as opposed to the average for all products.) SELECT FROM WHERE

INV_NUMBER, P_CODE, LINE_UNITS LINE LS LS.LINE_UNITS > (SELECT AVG(LINE_UNITS) FROM LINE LA WHERE LA.P_CODE = LS.P_CODE);

The previous nested query will execute the inner subquery once to compute the average sold units for each product code returned by the outer query. 7. Explain the difference between a regular subquery and a correlated subquery. A regular, or uncorrelated subquery, executes before the outer query. It executes only once and the result is held for use by the outer query. A correlated subquery relies in part on the outer query, usually through a WHERE criteria in the subquery that references an attribute in the outer query. Therefore, a correlated subquery will execute once for each row evaluated by the outer query; and the correlated subquery can potentially produce a different result for each row in the outer query. 8. What does it mean to say that SQL operators are set-oriented? The description of SQL operators as set-oriented means that the commands work over entire tables at a time, not row-by-row. 9. The relational set operators UNION, INTERSECT, and MINUS work properly only if the relations are union-compatible. What does union-compatible mean, and how would you check for this condition? Union compatible means that the relations yield attributes with identical names and compatible data types. That is, the relation A(c1,c2,c3) and the relation B(c1,c2,c3) have union compatibility if both relations have the same number of attributes, and corresponding attributes in the relations have “compatible” data types. Compatible data types do not require that the attributes be exactly identical -- only that they are comparable. For example, VARCHAR(15) and CHAR(15) are comparable, as are NUMBER (3,0) and INTEGER, and so on. Note that this is a practical definition of unioncompatibility, which is different than the theoretical definition discussed in Chapter 3. From a theoretical perspective, corresponding attributes must have the same domain. However, the DBMS

293

Chapter 8 Advanced SQL does not understand the meaning of the business domain so it must work with a more concrete understanding of the data in the corresponding columns. Thus, it only considers the data types. 10. What is the difference between UNION and UNION ALL? Write the syntax for each. UNION yields unique rows. In other words, UNION eliminates duplicates rows. On the other hand, a UNION ALL operator will yield all rows of both relations, including duplicates. Notice that for two rows to be duplicated, they must have the same values in all columns. To illustrate the difference between UNION and UNION ALL, let‟s assume two relations: A (ID, Name) with rows (1, Lake, 2, River, and 3, Ocean) and B (ID, Name) with rows (1, River, 2, Lake, and 3, Ocean). Given this description, SELECT * FROM A UNION SELECT * FROM B will yield: ID 1 2 3 1 2

Name Lake River Ocean River Lake

while SELECT * FROM A UNION ALL SELECT * FROM B will yield: ID 1 2 3 1 2 3

Name Lake River Ocean River Lake Ocean

294

Chapter 8 Advanced SQL 11. Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for three employees: Alice Cordoza, John Cretchakov, and Anne McDonald. The EMPLOYEE_1 table contains the records for employees John Cretchakov and Mary Chen. Given that information, what is the query output for the UNION query? (List the query output.) The query output will be: Alice Cordoza John Cretchakov Anne McDonald Mary Chen 12. Given the employee information in Question 11, what is the query output for the UNION ALL query? (List the query output.) The query output will be: Alice Cordoza John Cretchakov Anne McDonald John Cretchakov Mary Chen 13. Given the employee information in Question 11, what is the query output for the INTERSECT query? (List the query output.) The query output will be: John Cretchakov 14. Given the employee information in Question 1, what is the query output for the MINUS query? (List the query output.) This question can yield two different answers. If you use SELECT * FROM EMPLOYEE MINUS SELECT * FROM EMPLOYEE_1 the answer is Alice Cordoza Ann McDonald If you use

295

Chapter 8 Advanced SQL

SELECT * FROM EMPLOYEE_1 MINUS SELECT * FROM EMPLOYEE the answer is Mary Chen 15. Why does the order of the operands (tables) matter in a MINUS query but not in a UNION query? MINUS queries are analogous to algebraic subtraction – it results in the value that existed in the first operand that is not in the second operand. UNION queries are analogous to algebraic addition – it results in a combination of the two operands. (These analogies are not perfect, obviously, but they are helpful when learning the basics.) Addition and UNION have the commutative property (a + b = b + a), while subtraction and MINUS do not (a – b ≠ b – a).

16. What MS Access/SQL Server function should you use to calculate the number of days between the current date and January 25, 1999? SELECT DATE()-#25-JAN-1999# NOTE: In MS Access you do not need to specify a FROM clause for this type of query. 17. What Oracle function should you use to calculate the number of days between the current date and January 25, 1999? SELECT FROM

SYSDATE – TO_DATE(‟25-JAN-1999‟, „DD-MON-YYYY‟) DUAL;

Note that in Oracle, the SQL statement requires the use of the FROM clause. In this case, you may use the DUAL table. (The DUAL table is a dummy “virtual” table provided by Oracle for this type of query. The table contains only one row and one column so queries against it can return just one value.) 18. Suppose that a PRODUCT table contains two attributes, PROD_CODE and VEND_CODE. Those two attributes have values of ABC, 125, DEF, 124, GHI, 124, and JKL, 123, respectively. The VENDOR table contains a single attribute, VEND_CODE, with values 123, 124, 125, and 126, respectively. (The VEND_CODE attribute in the PRODUCT table is a foreign key to the VEND_CODE in the VENDOR table.) Given that information, what would be the query output for:

296

Chapter 8 Advanced SQL Because the common attribute is V_CODE, the output will only show the V_CODE values generated by the each query. a. A UNION query based on these two tables? 125,124,123,126 b. A UNION ALL query based on these two tables? 125,124,124,123,123,124,125,126 c. An INTERSECT query based on these two tables? 123,124,125 d. A MINUS query based on these two tables? If you use PRODUCT MINUS VENDOR, the output will be NULL If you use VENDOR MINUS PRODUCT, the output will be 126 19. What string function should you use to list the first three characters of a company‟s EMP_LNAME values? Give an example, using a table named EMPLOYEE. In Oracle, you use the SUBSTR function as illustrated next: SELECT SUBSTR(EMP_LNAME, 1, 3) FROM EMPLOYEE; In SQL Server, you use the SUBSTRING function as shown: SELECT SUBSTRING(EMP_LNAME, 1, 3) FROM EMPLOYEE; 20. What is an Oracle sequence? Write its syntax. An Oracle sequence is a special type of object that generates unique numeric values in ascending or descending order. You can use a sequence to assign values to a primary key field in a table. A sequence provides functionality similar to the Autonumber data type in MS Access. For example, both, sequences and Autonumber data types provide unique ascending or descending values. However, there are some subtle differences between the two:  In MS Access an Autonumber is a data type; in Oracle a sequence is a completely independent object, rather than a data type.  In MS Access, you can only have one Autonumber per table; in Oracle you can have as many sequences as you want and they are not tied to any particular table.  In MS Access, the Autonumber data type is tied to a field in a table; in Oracle, the sequencegenerated value is not tied to any field in any table and can, therefore, be used on any attribute in any table.

297

Chapter 8 Advanced SQL

The syntax used to create a sequence is: CREATE SEQUENCE CUS_NUM_SQ START WITH 100 INCREMENT BY 10 NOCACHE; 21. What is a trigger, and what is its purpose? Give an example. A trigger is a block of PL/SQL code that is automatically invoked by the DBMS upon the occurrence of a data manipulation event (INSERT, UPDATE or DELETE.) Triggers are always associated with a table and are invoked before or after a data row is inserted, updated, or deleted. Any table can have one or more triggers. Triggers provide a method of enforcing business rules such as:  A customer making a credit purchase must have an active .  A student taking a class with a prerequisite must have completed that prerequisite with a B grade.  To be scheduled for a flight, a pilot must have a valid medical certificate and a valid training completion record. Triggers are also excellent for enforcing data constraints that cannot be directly enforced by the data model. For example, suppose that you must enforce the following business rule: If the quantity on hand of a product falls below the minimum quantity, the P_REORDER attribute must the automatically set to 1. To enforce this business rule, you can create the following TRG_PRODUCT_REORDER trigger: CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDER BEFORE INSERT OR UPDATE OF P_ONHAND, P_MIN ON PRODUCT FOR EACH ROW BEGIN IF :NEW.P_ONHAND <= :NEW.P_MIN THEN NEW.P_REORDER := 1; ELSE :NEW.P_REORDER := 0; END IF; END; 22. What is a stored procedure, and why is it particularly useful? Give an example. A stored procedure is a named block of PL/SQL and SQL statements. One of the major advantages of stored procedures is that they can be used to encapsulate and represent business transactions. For example, you can create a stored procedure to represent a product sale, a credit update, or the addition of a new customer. You can encapsulate SQL statements within a single stored procedure and execute them as a single transaction.

298

Chapter 8 Advanced SQL There are two clear advantages to the use of stored procedures: 1. Stored procedures substantially reduce network traffic and increase performance. Because the stored procedure is stored at the server, there is no transmission of individual SQL statements over the network. 2. Stored procedures help reduce code duplication through code isolation and code sharing (creating unique PL/SQL modules that are called by application programs), thereby minimizing the chance of errors and the cost of application development and maintenance. For example, the following PRC_LINE_ADD stored procedure will add a new invoice line to the LINE table and it will automatically retrieve the correct price from the PRODUCT table. CREATE OR REPLACE PROCEDURE PRC_LINE_ADD (W_LN IN NUMBER, W_P_CODE IN VARCHAR2, W_LU NUMBER) AS W_LP NUMBER := 0.00; BEGIN -- GET THE PRODUCT PRICE SELECT P_PRICE INTO W_LP FROM PRODUCT WHERE P_CODE = W_P_CODE; -- ADDS THE NEW LINE ROW INSERT INTO LINE VALUES(INV_NUMBER_SEQ.CURRVAL, W_LN, W_P_CODE, W_LU, W_LP); DBMS_OUTPUT.PUT_LINE('Invoice line ' || W_LN || ' added'); END; 23. What is embedded SQL, and how is it used? Embedded SQL is a term used to refer to SQL statements that are contained within an application programming language such as COBOL, C++, ASP, Java, or ColdFusion. The program may be a standard binary executable in Windows or Linux, or it may be a Web application designed to run over the Internet. No matter what language you use, if it contains embedded SQL statements it is called the host language. Embedded SQL is still the most common approach to maintaining procedural capabilities in DBMS-based applications. For example, the following embedded SQL code will delete the employee 109, George Smith, from the EMPLOYEE table: EXEC SQL DELETE FROM EMPLOYEE WHERE EMP_NUM = 109; END-EXEC. that the preceding embedded SQL statement is compiled to generate an executable statement. Therefore, the statement is fixed permanently; that is, it cannot change -- unless, of

299

Chapter 8 Advanced SQL course, the programmer changes it. Each time the program runs, it deletes the same row. In short, the preceding code is good only for the first run; all subsequent runs will more than likely give an error. (Employee 109 is deleted the first time the embedded statement is executed. If you run the code again, it tries to delete an employee who has already been deleted.) Clearly, this code would be more useful if you are able to specify a variable to indicate which employee number is to be deleted. 24. What is dynamic SQL, and how does it differ from static SQL? Dynamic SQL is a term used to describe an environment in which the SQL statement is not known in advance; instead, the SQL statement is generated at run time. In a dynamic SQL environment, a program can generate the SQL statements (at run time) that are required to respond to ad-hoc queries. In such an environment, neither the programmers nor end s are likely to know precisely what kind of queries are to be generated or how those queries are to be structured. For example, a dynamic SQL equivalent of the example shown in question 19 might be: SELECT :W_ATTRIBUTE_LIST FROM :W_TABLE WHERE :W_CONDITION; Note that the attribute list and the condition are not known until the end specifies them. W_TABLE, W_ATRIBUTE_LIST and W_CONDITION are text variables that contain the end- input values used in the query generation. Because the program uses the end input to build the text variables, the end can run the same program multiple times to generate different outputs. For example, in one case the end may one to know what products have a price less than $100; in another case, the end may want to know how many units of a given product are available for sale at any given moment. Although dynamic SQL is clearly flexible, such flexibility carries a price. Dynamic SQL tends to be much slower that static SQL and it requires more computer resources (overhead). In addition, you are more likely to find different levels of and incompatibilities between DBMS vendors.

300

Chapter 8 Advanced SQL

Problem Solutions Use the database tables in Figure P8.1 as the basis for problems 1-18.

Figure P8.1 Ch08_SimpleCo Database Tables

1. Create the tables. (Use the MS Access example shown in Figure P8.1 to see what table names and attributes to use.) CREATE TABLE CUSTOMER ( CUST_NUM NUMBER PRIMARY KEY, CUST_LNAME VARCHAR(20), CUST_FNAME VARCHAR(20), CUST_BALANCE NUMBER); CREATE TABLE CUSTOMER_2 ( CUST_NUM NUMBER PRIMARY KEY, CUST_LNAME VARCHAR(20), CUST_FNAME VARCHAR(20));

301

Chapter 8 Advanced SQL CREATE TABLE INVOICE ( INV_NUM NUMBER PRIMARY KEY, CUST_NUM NUMBER, INV_DATE DATE, INV_AMOUNT NUMBER); 2. Insert the data into the tables you created in Problem 1. INSERT INTO CUSTOMER VALUES(1000 ,'Smith' ,'Jeanne' ,1050.11); INSERT INTO CUSTOMER VALUES(1001 ,'Ortega' ,'Juan' ,840.92); INSERT INTO CUSTOMER_2 VALUES(2000 ,'Mherson' ,'Anne'); INSERT INTO CUSTOMER_2 VALUES(2001 ,'Ortega' ,'Juan'); INSERT INTO CUSTOMER_2 VALUES(2002 ,'Kowalski' ,'Jan'); INSERT INTO CUSTOMER_2 VALUES(2003 ,'Chen' ,'George'); INSERT INTO INVOICE VALUES(8000 ,1000 ,'23-APR-2014' ,235.89); INSERT INTO INVOICE VALUES(8001 ,1001 ,'23-MAR-2014' ,312.82); INSERT INTO INVOICE VALUES(8002 ,1001 ,'30-MAR-2014' ,528.1); INSERT INTO INVOICE VALUES(8003 ,1000 ,'12-APR-2014' ,194.78); INSERT INTO INVOICE VALUES(8004 ,1000 ,'23-APR-2014' ,619.44); 3. Write the query that will generate a combined list of customers (from tables CUSTOMER and CUSTOMER_2) that do not include the duplicate customer records. (Note that only the customer named Juan Ortega shows up in both customer tables.) SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER UNION SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER_2; 4. Write the query that will generate a combined list of customers to include the duplicate customer records. SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER UNION ALL SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER_2; 5. Write the query that will show only the duplicate customer records. We have shown both Oracle and MS Access query formats: In Oracle: SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER INTERSECT SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER_2;

302

Chapter 8 Advanced SQL

In MS Access: SELECT C.CUST_LNAME, C.CUST_FNAME FROM CUSTOMER AS C, CUSTOMER_2 AS C2 WHERE C.CUST_LNAME=C2.CUST_LNAME AND C.CUST_FNAME=C2.CUST_FNAME; Because Access doesn‟t the INTERSECT SQL operator, you need to list only the rows in which all the attributes match. 6. Write the query that will generate only the records that are unique to the CUSTOMER_2 table. We have shown both Oracle and MS Access query formats: In Oracle: SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER_2 MINUS SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER; In MS SQL Server: SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER_2 EXCEPT SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER; In MySQL SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER_2 WHERE (CUST_LNAME, CUST_FNAME) NOT IN (SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER); In MS Access: SELECT FROM WHERE

C2.CUST_LNAME, C2.CUST_FNAME CUSTOMER_2 AS C2 C2.CUST_LNAME + C2.CUST_FNAME NOT IN (SELECT C1.CUST_LNAME + C1.CUST_FNAME FROM CUSTOMER C1);

Because Access doesn‟t the MINUS SQL operator, you need to list only the rows that are in CUSTOMER_2 that do not have a matching row in CUSTOMER.

303

Chapter 8 Advanced SQL 7. Write the query to show the invoice number, the customer number, the customer name, the invoice date, and the invoice amount for all customers with a customer balance of $1,000 or more. SELECT INV_NUM, CUSTOMER.CUST_NUM, CUST_LNAME, CUST_FNAME, INV_DATE, INV_AMOUNT FROM INVOICE INNER CUSTOMER ON INVOICE.CUST_NUM=CUSTOMER.CUST_NUM WHERE CUST_BALANCE>=1000;

8. Write the query that will show the invoice number, the invoice amount, the average invoice amount, and the difference between the average invoice amount and the actual invoice amount. There are at least two ways to do this query. SELECT FROM GROUP BY

INV_NUM, AVG_INV, (INV_AMOUNT - AVG_INV) AS DIFF INVOICE, (SELECT AVG(INV_AMOUNT) AS AVG_INV FROM INVOICE) INV_NUM, AVG_INV, INV_AMOUNT- AVG_INV

Another way to write this query is: SELECT

FROM GROUP BY

INV_NUM, INV_AMOUNT, (SELECT AVG(INV_AMOUNT) FROM INVOICE) AS AVG_INV, (INV_AMOUNT-(SELECT AVG(INV_AMOUNT) FROM INVOICE)) AS DIFF INVOICE INV_NUM, INV_AMOUNT;

The preceding code examples will run in both Oracle and MS Access. 9. Write the query that will write Oracle sequences to produce automatic customer number and invoice number values. Start the customer numbers at 1000 and the invoice numbers at 5000. The following code will only run in Oracle: CREATE SEQUENCE CUST_NUM_SQ START WITH 1000 NOCACHE; CREATE SEQUENCE INV_NUM_SQ START WITH 5000 NOCACHE; 10. Modify the CUSTOMER table to included two new attributes: CUST_DOB and CUST_AGE. Customer 1000 was born on March 15, 1979, and customer 1001 was born on December 22, 1988. In Oracle: ALTER TABLE CUSTOMER ADD (CUST_DOB DATE) ADD (CUST_AGE NUMBER); The SQL code required to enter the date values is: UPDATE CUSTOMER

304

Chapter 8 Advanced SQL SET CUST_DOB = ‟15-MAR-1979‟ WHERE CUST_NUM = 1000; UPDATE CUSTOMER SET CUST_DOB = „2-DEC-1988‟ WHERE CUST_NUM = 1001; 11. Assuming you completed problem 10, write the query that will list the names and ages of your customers. In Oracle: SELECT CUST_LNAME, CUST_FNAME, ROUND((SYSDATE-CUST_DOB)/365,0) AS AGE FROM CUSTOMER; In MySQL: SELECT CUST_LNAME, CUST_FNAME, ROUND((NOW() – CUST_DOB)/365,0) AS AGE FROM CUSTOMER; In MS SQL Server: SELECT CUST_LNAME, CUST_FNAME, ROUND((GETDATE() – CUST_DOB)/365,0) AS AGE FROM CUSTOMER;

In MS Access: SELECT CUST_LNAME, CUST_FNAME, ROUND((DATE()-CUST_DOB)/365,0) AS AGE FROM CUSTOMER;

NOTE The correct age computation may be computed by INT((DATE()-CUST_DOB)/365) However, students have not (yet) seen the INT function at this point -- which is why we used ROUND() function.

12. Assuming the CUSTOMER table contains a CUST_AGE attribute, write the query to update the values in that attribute. Hint: Use the results of the previous query. In Oracle:

305

Chapter 8 Advanced SQL UPDATE CUSTOMER SET CUST_AGE = ROUND((SYSDATE-CUST_DOB)/365,0); In MS Access: UPDATE CUSTOMER SET CUST_AGE = ROUND((DATE()-CUST_DOB)/365,0); 13. Write the query that will list the average age of your customers. (Assume that the CUSTOMER table has been modified to include the CUST_DOB and the derived CUST_AGE attribute.) SELECT AVG(CUST_AGE) FROM CUSTOMER; 14. Write the trigger to update the CUST_BALANCE in the CUSTOMER table when a new invoice record is entered. (Assume that the sale is a credit sale.) Test the trigger using the following new INVOICE record: 8005, 1001, ‟27-APR-14‟, 225.40 Name the trigger trg_updatecustbalance. CREATE OR REPLACE TRIGGER TRG_UPDATECUSTBALANCE AFTER INSERT ON INVOICE FOR EACH ROW BEGIN UPDATE CUSTOMER SET CUST_BALANCE = CUST_BALANCE + :NEW.INV_AMOUNT WHERE CUST_NUM = :NEW.CUST_NUM; END; To test the trigger you do the following: SELECT * FROM CUSTOMER; INSERT INTO INVOICE VALUES (8005,1001,‟27-APR-14‟,225.40); SELECT * FROM CUSTOMER;

306

Chapter 8 Advanced SQL 15. Write a procedure to add a new customer to the CUSTOMER table. Use the following values in the new record: 1002, „Rauthor‟, „Peter‟, 0.00 Name the procedure prc_cust_add. Run a query to see if the record has been added. CREATE OR REPLACE PROCEDURE PRC_CUST_ADD (W_CN IN NUMBER, W_CLN IN VARCHAR, W_CFN IN VARCHAR, W_CBAL IN NUMBER) AS BEGIN INSERT INTO CUSTOMER (CUST_NUM, CUST_LNAME, CUST_FNAME, CUST_BALANCE) VALUES (W_CN, W_CLN, W_CFN, W_CBAL); END;

To test the procedure: EXEC PRC_CUST_ADD(1002,‟Rauthor‟,‟Peter‟,0.00); SELECT * FROM CUSTOMER; 16. Write a procedure to add a new invoice record to the INVOICE table. Use the following values in the new record: 8006, 1000, ‟30-APR-14‟, 301.72 Name the procedure prc_invoice_add. Run a query to see if the record has been added. CREATE OR REPLACE PROCEDURE PRC_INVOICE_ADD (W_IN IN NUMBER, W_CN IN NUMBER, W_ID IN DATE, W_IA IN NUMBER) AS BEGIN INSERT INTO INVOICE VALUES (W_IN, W_CN, W_ID, W_IA); END; To test the procedure: SELECT * FROM CUSTOMER; SELECT * FROM INVOICE; EXEC PRC_INVOICE_ADD(8006,1000,‟30-APR-14‟,301.72); SELECT * FROM INVOICE; SELECT * FROM CUSTOMER;

307

Chapter 8 Advanced SQL 17. Write a trigger to update the customer balance when an invoice is deleted. Name the trigger trg_updatecustbalance2. CREATE OR REPLACE TRIGGER TRG_UPDATECUSTBALANCE2 AFTER DELETE ON INVOICE FOR EACH ROW BEGIN UPDATE CUSTOMER SET CUST_BALANCE = CUST_BALANCE - :OLD.INV_AMOUNT WHERE CUST_NUM = :OLD.CUST_NUM; END; 18. Write a procedure to delete an invoice given the invoice number as a parameter. Name the procedure prc_inv_delete. Test the procedure by deleting invoices 8005 and 8006. CREATE OR REPLACE PROCEDURE PRC_INV_DELETE (W_IN IN NUMBER) AS BEGIN IF W_IN IS NOT NULL THEN DELETE FROM INVOICE WHERE INV_NUM = W_IN; END IF; END; To test the procedure: SELECT * FROM CUSTOMER; SELECT * FROM INVOICE; EXEC PRC_INV_DELETE(8005); EXEC PRC_INV_DELETE(8006); SELECT * FROM INVOICE; SELECT * FROM CUSTOMER;

308

Chapter 8 Advanced SQL Use the Ch08_LargeCo database shown in Figure P8.19 to work Problems 19-27. For problems with very large result sets, only the first several rows of output are shown in the following figures.

Figure P8.19 Ch08_SaleCo2 Database Tables

309

Chapter 8 Advanced SQL

19. Write a query to display the products that have a price greater than $50.

SELECT * FROM LGPRODUCT WHERE PROD_PRICE > 50; 20. Write a query to display the current salary for each employee in department 300. Assume that only current employees are kept in the system, and therefore the most current salary for each employee is the entry in the salary history with a NULL end date. Sort the output in descending order by salary amount. Figure P8.20 Current salary for employees in department 300

SELECT e.emp_num, emp_lname, emp_fname, sal_amount FROM lgemployee e lgsalary_history s ON e.emp_num = s.emp_num WHERE sal_end IS NULL AND dept_num = 300 ORDER BY sal_amount DESC; 21. Write a query to display the starting salary for each employee. The starting salary would be the entry in the salary history with the oldest salary start date for each employee. Sort the output by employee number. Figure P8.21 Starting salary for each employee

310

Chapter 8 Advanced SQL

SELECT e.emp_num, emp_lname, emp_fname, sal_amount FROM lgemployee e lgsalary_history s ON e.emp_num = s.emp_num WHERE sal_from = (SELECT Min(sal_from) FROM lgsalary_history s2 WHERE e.emp_num = s2.emp_num) ORDER BY e.emp_num; 22. Write a query to display the invoice number, line numbers, product SKUs, product descriptions, and brand ID for sales of sealer and top coat products of the same brand on the same invoice. Figure P8.22 Invoices for sealer and top coat of the same brand

SELECT l.inv_num, l.line_num, p.prod_sku, p.prod_descript, l2.line_num, p2.prod_sku, p2.prod_descript, p.brand_id FROM (lgline l lgproduct p ON l.prod_sku = p.prod_sku) (lgline l2 lgproduct p2 ON l2.prod_sku = p2.prod_sku) ON l.inv_num = l2.inv_num WHERE p.brand_id = p2.brand_id AND p.prod_category = 'Sealer' AND p2.prod_category = 'Top Coat' ORDER BY l.inv_num, l.line_num; 23. The Binder Prime Company wants to recognize the employee who sold the most of their products during a specified period. Write a query to display the employee number, employee first name, employee last name, e-mail address, and total units sold for the employee who sold the most Binder Prime brand products between November 1, 2013, and December 5, 2013. If there is a tie for most units sold, sort the output by employee last name.

311

Chapter 8 Advanced SQL Figure P8.23 Employees with most Binder Prime units sold

SELECT emp.emp_num, emp_fname, emp_lname, emp_email, total FROM lgemployee emp (SELECT employee_id, Sum(line_qty) AS total FROM lginvoice i lgline l ON i.inv_num = l.inv_num lgproduct p ON l.prod_sku = p.prod_sku lgbrand b ON b.brand_id = p.brand_id WHERE brand_name = 'BINDER PRIME' AND INV_DATE BETWEEN '01-NOV-11' AND '06-DEC-11' GROUP BY employee_id) sub ON emp.emp_num = sub.employee_id WHERE total = (SELECT Max(total) FROM (SELECT employee_id, Sum(line_qty) AS total FROM lginvoice i lgline l ON i.inv_num = l.inv_num lgproduct p ON l.prod_sku = p.prod_sku lgbrand b ON b.brand_id = p.brand_id WHERE brand_name = 'BINDER PRIME' AND INV_DATE BETWEEN '01-NOV-13' AND '06-DEC-13' GROUP BY employee_id));

24. Write a query to display the customer code, first name, and last name of all customers who have had at least one invoice completed by employee 83649 and at least one invoice completed by employee 83677. Sort the output by customer last name and then first name. Figure P8.24 Customers with invoices filled by employees 83649 and 83677

312

Chapter 8 Advanced SQL

SELECT c.cust_code, cust_fname, cust_lname FROM lgcustomer c lginvoice i ON c.cust_code = i.cust_code WHERE employee_id = 83649 INTERSECT SELECT c.cust_code, cust_fname, cust_lname FROM lgcustomer c lginvoice i ON c.cust_code = i.cust_code WHERE employee_id = 83677 ORDER BY cust_lname, cust_fname;

25. LargeCo is planning a new promotion in Alabama (AL) and wants to know about the largest purchases made by customers in that state. Write a query to display the customer code, customer first name, last name, full address, invoice date, and invoice total of the largest purchase made by each customer in Alabama. Be certain to include any customers in Alabama who have never made a purchase (their invoice dates should be NULL and the invoice totals should display as 0). Figure P8.25 Largest purchases of customers in Alabama

SELECT c.cust_code, cust_fname, cust_lname, cust_street, cust_city, cust_state, cust_zip, inv_date, inv_total AS "Largest Invoice" FROM lgcustomer c lginvoice i ON c.cust_code = i.cust_code WHERE cust_state = 'AL' AND inv_total = (SELECT Max(inv_total)

313

Chapter 8 Advanced SQL FROM lginvoice i2 WHERE i2.cust_code = c.cust_code) UNION SELECT cust_code, cust_fname, cust_lname, cust_street, cust_city, cust_state, cust_zip, NULL, 0 FROM lgcustomer WHERE cust_state = 'AL' AND cust_code NOT IN (SELECT cust_code FROM lginvoice) ORDER BY cust_lname, cust_fname;

314

Chapter 8 Advanced SQL

26. One of the purchasing managers is interested in the impact of product prices on the sale of products of each brand. Write a query to display the brand name, brand type, average price of products of each brand, and total units sold of products of each brand. Even if a product has been sold more than once, its price should only be included once in the calculation of the average price. However, you must be careful because multiple products of the same brand can have the same price, and each of those products must be included in the calculation of the brand‟s average price. Figure P8.26 Average price and total units sold of products by brand

SELECT brand_name, brand_type, Round(avgprice,2) AS "Average Price", "Units Sold" FROM lgbrand b (SELECT brand_id, Avg(prod_price) AS avgprice FROM lgproduct GROUP BY brand_id) sub1 ON b.brand_id = sub1.brand_id (SELECT brand_id, Sum(line_qty) AS "Units Sold" FROM lgproduct p lgline l ON p.prod_sku = l.prod_sku GROUP BY brand_id) sub2 ON b.brand_id = sub2.brand_id ORDER BY brand_name; 27. The purchasing manager is still concerned about the impact of price on sales. Write a query to display the brand name, brand type, product SKU, product description, and price of any products that are not a brand, but that cost more than the most expensive brand products. Figure P8.27 Non products that are more expensive than products

SELECT brand_name, brand_type, prod_sku, prod_descript, prod_price FROM lgproduct p lgbrand b ON p.brand_id = b.brand_id WHERE brand_type <> '' AND prod_price > (SELECT Max(prod_price) FROM lgproduct p lgbrand b ON p.brand_id = b.brand_id WHERE brand_type = '');

315

Chapter 8 Advanced SQL

Use the Ch08_SaleCo2 database to work Problems 28-31.

316

Chapter 8 Advanced SQL 28. Create a trigger named trg_line_total to write the LINE_TOTAL value in the LINE table every time you add a new LINE row. (The LINE_TOTAL value is the product of the LINE_UNITS and the LINE_PRICE values.) CREATE OR REPLACE TRIGGER TRG_LINE_TOTAL BEFORE INSERT ON LINE FOR EACH ROW BEGIN :NEW.LINE_TOTAL := :NEW.LINE_UNITS * :NEW.LINE_PRICE; END; 29. Create a trigger named trg_line_prod that will automatically update the product quantity on hand for each product sold after a new LINE row is added. CREATE OR REPLACE TRIGGER TRG_LINE_PROD AFTER INSERT ON LINE FOR EACH ROW BEGIN UPDATE PRODUCT SET P_QOH = P_QOH - :NEW.LINE_UNITS WHERE PRODUCT.P_CODE = :NEW.P_CODE; END; 30. Create a stored procedure named prc_inv_amounts to update the INV_SUBTOTAL, INV_TAX, and INV_TOTAL. The procedure takes the invoice number as a parameter. The INV_SUBTOTAL is the sum of the LINE_TOTAL amounts for the invoice, the INV_TAX is the product of the INV_SUBTOTAL and the tax rate (8%), and the INV_TOTAL is the sum of the INV_SUBTOTAL and the INV_TAX. CREATE OR REPLACE PROCEDURE PRC_INV_AMOUNTS (W_IN IN NUMBER) AS W_CHK NUMBER := 0; W_SUBT NUMBER := 0; W_TAX NUMBER := 0; BEGIN -- VALIDATE INVOICE NUMBER SELECT COUNT(*) INTO W_CHK FROM INVOICE WHERE INV_NUMBER = W_IN; IF W_CHK = 1 THEN -- COMPUTE THE W_SUBT SELECT SUM(LINE_TOTAL) INTO W_SUBT FROM LINE WHERE LINE.INV_NUMBER = W_IN; -- COMPUTE W_TAX W_TAX := W_SUBT * 0.08; -- UPDATE INVOICE UPDATE INVOICE

317

Chapter 8 Advanced SQL SET INV_SUBTOTAL = W_SUBT, INV_TAX = W_TAX, INV_TOTAL = W_SUBT + W_TAX WHERE INV_NUMBER = W_IN; END IF; END; 31. Create a procedure named prc_cus_balance_update that will take the invoice number as a parameter and update the customer balance. (Hint: You can use the DECLARE section to define a TOTINV numeric variable that holds the computed invoice total.)

NOTE Actually, the TOTINV is not really needed – because the INVOICE table already contains the INV_TOTAL attribute. The procedure we have shown next uses the INV_TOTAL attribute.

CREATE OR REPLACE PROCEDURE PRC_CUS_BALANCE_UPDATE (W_IN IN NUMBER) AS W_CUS NUMBER := 0; W_TOT NUMBER := 0; BEGIN -- GET THE CUS_CODE SELECT CUS_CODE INTO W_CUS FROM INVOICE WHERE INVOICE.INV_NUMBER = W_IN; -- UPDATES CUSTOMER IF W_CUS > 0 IF W_CUS > 0 THEN UPDATE CUSTOMER SET CUS_BALANCE = CUS_BALANCE + (SELECT INV_TOTAL FROM INVOICE WHERE INV_NUMBER = W_IN) WHERE CUS_CODE = W_CUS; END IF; END;

318

Chapter 8 Advanced SQL Use the Ch08_AviaCo database to work Problems 32-43.

ONLINE CONTENT The Ch08_AviaCo database used for Problems 32−43 is available at www.cengagebrain.com, as are the script files to duplicate the data set in Oracle, MySQL, and MS SQL Server.

Figure P8.32 Ch08_AviaCo Database Tables

319

Chapter 8 Advanced SQL 32. Modify the MODEL table to add the following attribute and insert the values shown in Table P8.23.

Table P8.32 The New Attribute for the MODEL Table Attribute name MOD_WAIT_CHG

Attribute Description Attribute type Waiting charge per Numeric hour for each model:

Attribute Values $100 for C-90A $50 for PA23-250 $75 for PA31-350

ALTER TABLE MODEL ADD MOD_WAIT_CHG NUMBER; UPDATE MODEL SET MOD_WAIT_CHG = 100 WHERE MOD_CODE = „C-90A‟; UPDATE MODEL SET MOD_WAIT_CHG = 50 WHERE MOD_CODE = „PA23-250‟; UPDATE MODEL SET MOD_WAIT_CHG = 75 WHERE MOD_CODE = „PA31-350‟; 33. Write the queries to update the MOD_WAIT_CHG attribute values based on problem 32. UPDATE MODEL SET MOD_WAIT_CHG = 100 UPDATE MODEL SET MOD_WAIT_CHG = 50 UPDATE MODEL SET MOD_WAIT_CHG = 75

WHERE MOD_CODE = 'C-90A'; WHERE MOD_CODE = 'PA23-250'; WHERE MOD_CODE = 'PA31-350';

34. Modify the CHARTER table to add the attributes shown in the following table.

Table P8.34 The New Attributes for the CHARTER Table Attribute name CHAR_WAIT_CHG

Attribute Description Attribute type Waiting charge for each model (copied from the Numeric MODEL table.) CHAR_FLT_CHG_HR Flight charge per mile for each model (copied Numeric from the MODEL table using the MOD_CHG_MILE attribute.) CHAR_FLT_CHG Flight charge (calculated by Numeric CHAR_HOURS_FLOWN x CHAR_FLT_CHG_HR)

CHAR_TAX_CHG CHAR_TOT_CHG CHAR_PYMT CHAR_BALANCE

CHAR_FLT_CHG x tax rate (8%) CHAR_FLT_CHG + CHAR_TAX_CHG Amount paid by customer Balance remaining after payment

320

Numeric Numeric Numeric Numeric

Chapter 8 Advanced SQL ALTER TABLE CHARTER ADD CHAR_WAIT_CHG NUMBER ADD CHAR_FLT_CHG_HR NUMBER ADD CHAR_FLT_CHG NUMBER ADD CHAR_TAX_CHG NUMBER ADD CHAR_TOT_CHG NUMBER ADD CHAR_PYMT NUMBER ADD CHAR_BALANCE NUMBER; 35. Write the sequence of commands required to update the CHAR_WAIT_CHG attribute values in the CHARTER table. Hint: Use either an updatable view or a stored procedure. UPDATE CHARTER SET CHAR_WAIT_CHG = ( SELECT MOD_WAIT_CHG FROM MODEL, AIRCRAFT WHERE MODEL.MOD_CODE = AIRCRAFT.MOD_CODE AND AIRCRAFT.AC_NUMBER = CHARTER.AC_NUMBER); 36. Write the sequence of commands required to update the CHAR_FLT_CHG_HR attribute values in the CHARTER table. Hint: Use either an updatable view or a stored procedure. UPDATE CHARTER SET CHAR_FLT_CHG_HR = ( SELECT MOD_CHG_MILE FROM MODEL, AIRCRAFT WHERE MODEL.MOD_CODE = AIRCRAFT.MOD_CODE AND AIRCRAFT.AC_NUMBER = CHARTER.AC_NUMBER); 37. Write the command required to update the CHAR_FLT_CHG attribute values in the CHARTER table. UPDATE CHARTER SET CHAR_FLT_CHG = CHAR_HOURS_FLOWN * CHAR_FLT_CHG_HR; 38. Write the command required to update the CHAR_TAX_CHG attribute values in the CHARTER table. UPDATE CHARTER SET CHAR_TAX_CHG = CHAR_FLT_CHG * 0.08; 39. Write the command required to update the CHAR_TOT_CHG attribute values in the CHARTER table. UPDATE CHARTER SET CHAR_TOT_CHG = CHAR_FLT_CHG + CHAR_TAX_CHG; 40. Modify the PILOT table to add the attribute shown in the following table.

321

Chapter 8 Advanced SQL

Table P8.40 The New Attribute for the PILOT Table Attribute name PIL_PIC_HRS

Attribute Description Attribute type Pilot in command (PIC) hours. Updated by adding the Numeric CHARTER table‟s CHAR_HOURS_FLOWN to the PIL_PIC_HRS when the CREW table shows the CREW_JOB to be pilot.

ALTER TABLE PILOT ADD PIL_PIC_HRS NUMBER; 41. Create a trigger named trg_char_hours that will automatically update the AIRCRAFT table when a new CHARTER row is added. Use the CHARTER table‟s CHAR_HOURS_FLOWN to update the AIRCRAFT table‟s AC_TTAF, AC_TTEL, and AC_TTER values. CREATE OR REPLACE TRIGGER TRG_CHAR_HOURS AFTER INSERT ON CHARTER FOR EACH ROW BEGIN UPDATE AIRCRAFT SET AC_TTAF = AC_TTAF + :NEW.CHAR_HOURS_FLOWN, AC_TTEL = AC_TTEL + :NEW.CHAR_HOURS_FLOWN, AC_TTER = AC_TTER + :NEW.CHAR_HOURS_FLOWN WHERE AIRCRAFT.AC_NUMBER = :NEW.AC_NUMBER; END; 42. Create a trigger named trg_pic_hours that will automatically update the PILOT table when a new CREW row is added and the CREW table uses a „pilot‟ CREW_JOB entry. Use the CHARTER table‟s CHAR_HOURS_FLOWN to update the PILOT table‟s PIL_PIC_HRS only when the CREW table uses a „pilot‟ CREW_JOB entry. CREATE OR REPLACE TRIGGER TRG_PIC_HOURS AFTER INSERT ON CREW FOR EACH ROW BEGIN IF :NEW.CREW_JOB = 'Pilot' THEN UPDATE PILOT SET PIL_PIC_HRS = PIL_PIC_HRS + (SELECT CHAR_HOURS_FLOWN FROM CHARTER WHERE CHAR_TRIP = :NEW.CHAR_TRIP) WHERE EMP_NUM = :NEW.EMP_NUM; END IF; END;

322

Chapter 8 Advanced SQL 43. Create a trigger named trg_cust_balance that will automatically update the CUSTOMER table‟s CUST_BALANCE when a new CHARTER row is added. Use the CHARTER table‟s CHAR_TOT_CHG as the update source (Assume that all charter charges are charged to the customer balance.) CREATE OR REPLACE TRIGGER TRG_CUST_BALANCE AFTER INSERT ON CHARTER FOR EACH ROW BEGIN UPDATE CUSTOMER SET CUS_BALANCE = CUS_BALANCE + :NEW.CHAR_TOT_CHG WHERE CUSTOMER.CUS_CODE = :NEW.CUS_CODE; END;

Case Solutions The following problems expand on the EliteVideo case from Chapter 7. To complete the following problems, it is necessary to have first completed the table creation and data entry requirements specified in Problems 65 and 66 in Chapter 7. 44. Alter the DETAILRENTAL table to include a derived attribute named DETAIL_DAYSLATE to store integers up to 3 digits. The attribute should accept null values. ALTER TABLE DETAILRENTAL ADD DETAIL_DAYSLATE NUMBER(3,0); 45. Alter the VIDEO table to include an attribute named VID_STATUS to store character data up to 4 characters long. The attribute should not accept null values. The attribute should have a constraint to enforce the domain (“IN”, “OUT”, and “LOST”), and have a default value of “IN”. ALTER TABLE VIDEO ADD VID_STATUS VARCHAR(4) DEFAULT 'IN' NOT NULL CHECK (VID_STATUS IN ('IN', 'OUT', 'LOST')); 46. Update the VID_STATUS attribute of the VIDEO table using a subquery to set the VID_STATUS to “OUT” for all videos that have a null value in the DETAIL_RETURNDATE attribute of the DETAILRENTAL table. UPDATE VIDEO SET VID_STATUS = 'OUT' WHERE VID_NUM IN (SELECT VID_NUM FROM DETAILRENTAL WHERE DETAIL_RETURNDATE IS NULL); 47. Alter the PRICE table to include an attribute named PRICE_RENTDAYS to store integers up to 2 digits. The attribute should not accept null values, and should have a default value of 3.

323

Chapter 8 Advanced SQL ALTER TABLE PRICE ADD PRICE_RENTDAYS NUMBER(2,0) DEFAULT 3 NOT NULL; 48. Update the PRICE table to place the values shown in the following table in the PRICE_RENTDAYS attribute. PRICE_CODE PRICE_RENTDAYS 1 5 2 3 3 5 4 7 UPDATE PRICE SET PRICE_RENTDAYS = 5 WHERE PRICE_CODE IN (1, 3); UPDATE PRICE SET PRICE_RENTDAYS = 7 WHERE PRICE_CODE = 4; 49. Create a trigger named trg_late_return that will write the correct value to DETAIL_DAYSLATE in the DETAILRENTAL table whenever a video is returned. The trigger should execute as a BEFORE trigger when the DETAIL_RETURNDATE or DETAIL_DUEDATE attributes are updated. The trigger should satisfy the following conditions. a. If the return date is null, then the days late should be null also. b. If the return date is not null, then the days late should determine if the video is returned late. c. If the return date is noon of the day after the due date or earlier, then the video is not considered late, and the days late should have a value of zero (0). d. If the return date is past noon of the day after the due date, then the video is considered late so the number days late must be calculated and stored. CREATE OR REPLACE TRIGGER TRG_LATE_RETURN BEFORE UPDATE OF DETAIL_RETURNDATE, DETAIL_DUEDATE ON DETAILRENTAL FOR EACH ROW BEGIN IF :NEW.DETAIL_RETURNDATE IS NULL THEN :NEW.DETAIL_DAYSLATE := NULL; ELSIF Trunc(:NEW.DETAIL_RETURNDATE) <= Trunc(:NEW.DETAIL_DUEDATE) OR (Trunc(:NEW.DETAIL_RETURNDATE) = Trunc(:NEW.DETAIL_DUEDATE) + 1 AND To_CHAR(:NEW.DETAIL_RETURNDATE, 'HH24:MI:SS') <= '12:00:00') THEN :NEW.DETAIL_DAYSLATE := 0; ELSE :NEW.DETAIL_DAYSLATE := Trunc(:NEW.DETAIL_RETURNDATE) Trunc(:NEW.DETAIL_DUEDATE); END IF;

324

Chapter 8 Advanced SQL END; / 50. Create a trigger named trg_mem_balance that will maintain the correct value in the hip balance in the HIP table when videos are returned late. The trigger should execute as an AFTER trigger when the due date or return date attributes are updated in the DETAILRENTAL table. The trigger should satisfy the following conditions. a. Calculate the value of the late fee prior to the update that triggered this execution of the trigger. The value of the late fee is the days late times the daily late fee. If the previous value of the late fee was null, then treat it as zero (0). b. Calculate the value of the late fee after the update that triggered this execution of the trigger. If the value of the late fee is now null, then treat it as zero (0). c. Subtract the prior value of the late fee from the current value of the late fee to determine the change in late fee for this video rental. d. If the amount calculated in part c is not zero (0), then update the hip balance by the amount calculated for the hip associated the rental that this detail is a part of. CREATE OR REPLACE TRIGGER TRG_MEM_BALANCE AFTER UPDATE OF DETAIL_DUEDATE, DETAIL_RETURNDATE ON DETAILRENTAL FOR EACH ROW DECLARE PRIOR_LATEFEE NUMBER; NEW_LATEFEE NUMBER; UPDATE_AMOUNT NUMBER; RENTAL_MEMBER RENTAL.MEM_NUM%TYPE; BEGIN PRIOR_LATEFEE := :OLD.DETAIL_DAYSLATE * :OLD.DETAIL_DAILYLATEFEE; IF PRIOR_LATEFEE IS NULL THEN PRIOR_LATEFEE := 0; END IF; NEW_LATEFEE := :NEW.DETAIL_DAYSLATE * :NEW.DETAIL_DAILYLATEFEE; IF NEW_LATEFEE IS NULL THEN NEW_LATEFEE := 0; END IF; UPDATE_AMOUNT := NEW_LATEFEE - PRIOR_LATEFEE; IF UPDATE_AMOUNT <> 0 THEN SELECT MEM_NUM INTO RENTAL_MEMBER FROM RENTAL WHERE RENT_NUM = :NEW.RENT_NUM; UPDATE HIP SET MEM_BALANCE = MEM_BALANCE + UPDATE_AMOUNT WHERE MEM_NUM = RENTAL_MEMBER; END IF; END;

325

Chapter 8 Advanced SQL / 51. Create a sequence named rent_num_seq to start with 1100, increment by 1, and not cache any values. CREATE SEQUENCE RENT_NUM_SEQ START WITH 1100 INCREMENT BY 1 NOCACHE; 52. Create a stored procedure named prc_new_rental to insert new rows in the RENTAL table. The procedure should satisfy the following conditions. a. The hip number will be provided as a parameter. b. Use a Count() function to that the hip number exists in the HIP table. If it does not exist, then a message should be displayed stating that the hip does not exist and no data should be written to the database. c. If the hip does exist, then retrieve the hip balance and display a message stating the balance amount as the previous balance. (For example, if the hip has a balance of $5.00, then display “Previous balance: $5.00”.) d. Insert a new row in the rental table using the sequence created in #42 above to generate the value for RENT_NUM, the current system date for the value for RENT_DATE, and the hip number provided as the value for MEM_NUM. CREATE OR REPLACE PROCEDURE PRC_NEW_RENTAL (MEM_NUM_TEMP IN HIP.MEM_NUM%TYPE) AS MEM_NUM_COUNT NUMBER; PREV_MEM_BALANCE HIP.MEM_BALANCE%TYPE; BEGIN SELECT Count(*) INTO MEM_NUM_COUNT FROM HIP WHERE MEM_NUM = MEM_NUM_TEMP; IF MEM_NUM_COUNT = 0 THEN Dbms_Output.PUT_LINE('No hip with number: ' || MEM_NUM_TEMP || ' exists.'); ELSE SELECT MEM_BALANCE INTO PREV_MEM_BALANCE FROM HIP WHERE MEM_NUM = MEM_NUM_TEMP; Dbms_Output.PUT_LINE('Previous balance: ' || To_Char(PREV_MEM_BALANCE, '$999,999,990.99')); INSERT INTO RENTAL (RENT_NUM, RENT_DATE, MEM_NUM) VALUES (RENT_NUM_SEQ.NEXTVAL, SYSDATE, MEM_NUM_TEMP);

326

Chapter 8 Advanced SQL END IF; END; / 53. Create a stored procedure named prc_new_detail to insert new rows in the DETAILRENTAL table. The procedure should satisfy the following requirements. a. The video number will be provided as a parameter. b. the video number exists in the VIDEO table. If it does not exist, then display a message that the video does not exist, and do not write any data to the database. c. If the video number does exist, then that the VID_STATUS for that video is “IN”. If the status is not “IN”, then display a message that the return of the video must be entered before it can be rented again, and do not write any data to the database. d. If the status is “IN”, then retrieve the values of PRICE_RENTFEE, PRICE_DAILYLATEFEE, and PRICE_RENTDAYS associated with the video from the PRICE table. e. Calculate the due date for the video rental by adding the number of days found in PRICE_RENTDAYS above to 11:59:59PM (hours:minutes:seconds) on the current system date. f. Insert a new row in the DETAILRENTAL table using the previous value returned by rent_num_seq as the RENT_NUM, the video number provided in the parameter as the VID_NUM, the PRICE_RENTFEE as the value for DETAIL_FEE, the due date calculated above for the DETAIL_DUEDATE, PRICE_DAILYLATEFEE as the value for DETAIL_DAILYLATEFEE, and null for the DETAIL_RETURNDATE. CREATE OR REPLACE PROCEDURE PRC_NEW_DETAIL(VID_NUM_TEMP IN VIDEO.VID_NUM%TYPE) AS VID_NUM_COUNT NUMBER; STATUS_TEMP VIDEO.VID_STATUS%TYPE; RENT_FEE PRICE.PRICE_RENTFEE%TYPE; LATE_FEE PRICE.PRICE_DAILYLATEFEE%TYPE; RENT_DAYS PRICE.PRICE_RENTDAYS%TYPE; DUE_DATE DATE; BEGIN SELECT Count(*) INTO VID_NUM_COUNT FROM VIDEO WHERE VID_NUM = VID_NUM_TEMP; IF VID_NUM_COUNT = 0 THEN Dbms_Output.PUT_LINE('No Video with number ' || VID_NUM_TEMP || ' was found.'); ELSE SELECT VID_STATUS INTO STATUS_TEMP FROM VIDEO WHERE VID_NUM = VID_NUM_TEMP;

327

Chapter 8 Advanced SQL IF STATUS_TEMP <> 'IN' THEN Dbms_Output.PUT_LINE('The video is not currently IN. Video return must be entered before it can be rented again.'); ELSE SELECT PRICE_RENTFEE, PRICE_DAILYLATEFEE, PRICE_RENTDAYS INTO RENT_FEE, LATE_FEE, RENT_DAYS FROM VIDEO MOVIE USING (MOVIE_NUM) PRICE USING (PRICE_CODE) WHERE VID_NUM = VID_NUM_TEMP; DUE_DATE := To_Date(To_Char(SYSDATE, 'MM/DD/YYYY') || ' 23:59:59', 'MM/DD/YYYY HH24:MI:SS') + RENT_DAYS; INSERT INTO DETAILRENTAL(RENT_NUM, VID_NUM, DETAIL_FEE, DETAIL_DUEDATE, DETAIL_DAILYLATEFEE) VALUES (RENT_NUM_SEQ.CURRVAL, VID_NUM_TEMP, RENT_FEE, DUE_DATE, LATE_FEE); UPDATE VIDEO SET VID_STATUS = 'OUT' WHERE VID_NUM = VID_NUM_TEMP; END IF; END IF; END; / 54. Create a stored procedure named prc_return_video enter data about the return of videos that had been rented. The procedure should satisfy the following requirements. a. The video number will be provided as a parameter. b. the video number exists in the VIDEO table. If it does not exist, display a message that the video number provided was not found and do not write any data to the database. c. If the video number does exist, then use a Count() function to ensure that the video has only one record in DETAILRENTAL for which it does not have a return date. If more than one row in DETAILRENTAL indicates that the video is rented but not returned, display an error message that the video has multiple outstanding rentals and do not write any data to the database. d. If the video does not have any outstanding rentals, then update the video status to “IN” for the video in the VIDEO table, and display a message that the video had no outstanding rentals but it is now available for rental. If the video has only one outstanding rental, then update the return date to the current system date, and update the video status to “IN” for that video in the VIDEO. Then display a message stating that the video was successfully returned. CREATE OR REPLACE PROCEDURE PRC_RETURN_VIDEO (VID_NUM_TEMP IN VIDEO.VID_NUM%TYPE) AS VID_NUM_COUNT NUMBER;

328

Chapter 8 Advanced SQL RENTALS_COUNT NUMBER; BEGIN SELECT Count(*) INTO VID_NUM_COUNT FROM VIDEO WHERE VID_NUM = VID_NUM_TEMP; IF VID_NUM_COUNT = 0 THEN Dbms_Output.PUT_LINE('Video number ' || VID_NUM_TEMP || ' not found.'); ELSE SELECT Count(*) INTO RENTALS_COUNT FROM DETAILRENTAL WHERE VID_NUM = VID_NUM_TEMP AND DETAIL_RETURNDATE IS NULL; IF RENTALS_COUNT > 1 THEN Dbms_Output.PUT_LINE('ERROR: Video has multiple outstanding Rentals.'); ELSE UPDATE VIDEO SET VID_STATUS = 'IN' WHERE VID_NUM = VID_NUM_TEMP; IF RENTALS_COUNT = 0 THEN Dbms_Output.PUT_LINE('No rentals found. Video is available for rental.'); ELSE UPDATE DETAILRENTAL SET DETAIL_RETURNDATE = SYSDATE WHERE VID_NUM = VID_NUM_TEMP AND DETAIL_RETURNDATE IS NULL; Dbms_Output.PUT_LINE('Video successfully returned and available for rental.'); END IF; END IF; END IF ; END; /

329