thx!
- Will Be Much Appreciated If My Question Can Be Solved Thx 1 (64.3 KiB) Viewed 64 times
Task 1 (5.0 marks) Indexing a. Implement SQL script solution1.sql that performs the actions listed below. 1. First, the script finds query processing plans for each one of the queries listed below. Use EXPLAIN PLAN statement and a script showplan.sql to display the query processing plans Find all distinct pair of values of receipt-date (L_RECEIPTDATE) and discount (L_DISCOUNT) in the relational table LINEITEM. b. Find the total number of rows in the relational table ORDERS where the total price (O_TOTALPRICE) is greater than the average total price (O_TOTALPRICE). Find the total number of ORDERS submitted by each customer, list the values of an attribute O_CUSTKEY together with the total number of orders submitted. Do not ignore customers who submitted no orders. d. Find the total number of discount (L_DISCOUNT) in the relational table LINEITEM. e. Find the total number of distinct values in a column O_ORDERPRIORITY IN a relational table ORDERS. C. 2. Next, the script creates the smallest number of indexes such that processing of each one of the queries listed above benefits from the existence of at least one index.
3. Next, script lists the query processing plans for each one of the queries listed in a step. Use EXPLAIN PLAN statement and a script showplan.sql to display the query processing plans. 4. Finally, the script drops all indexes created in a step (2). Note, the report from processing of the script must have NO syntax errors! Deliverables A file solution1.Ist (or solution1.pdf) that contains a report from the processing of a script solution1.sql. Task 2 (2.0 marks) PLSQL Function Implement a stored PL/SQL function CustWith HighestOrder that finds the details of customer who has made the highest number of orders. The function is not required to accept any parameter. The function is to automatically find the details of the customer and returns a string of characters that contains the customer key, customer name, customer address, customer telephone and the number of orders made. Execute the stored PL/SQL function CustWith HighestOrder. A fragment of sample printout is given below: CUSTWITHHIGHESTORDERS 9787 Customer#000009787 NaTsOvApYGU26iE3Wz4UaVE8s 14-486-611-5297 41 Deliverables Submit a file solution2.Ist (or solution2.pdf) with a report from processing of SQL script solution2.sql. The report MUST have no errors and the report MUST list all SQL statements processed. The report MUST include ONLY SQL statements and control statements that implement the specifications of Task 1 and NO OTHER statements.
Task 3 (3.0 marks) PLSQL Procedure Implement a stored PL/SQL procedure showCustomerOrders to list the customer number, names of customer, the orders number made, the order date, and the total price of order. The names of the customers must be listed in the ascending order, and the total price of order must be in descending order. If a customer did not make any order, list only the customer number and names. No details on order will be listed. Execute the stored PL/SQL procedure showCustomerOrders for the first 10 customers, that is, the customer key less than or equal to 10. A fragment of expected sample printout is given below. 1 - Customer#000000001: 385825, 01-Nov-1995, 1374019, 05-Apr-1992, 1071617, 10-Mar-1995, 454791, 19-Apr-1992, 1590469, 07-Mar-1997, 579908, 09-Dec-1996, 430243, 24-Dec-1994, 1763205, 28-Aug-1994, 1755398, 12-Jun-1997, $254,563.49 $189,636.00 $156, 748.63 $78, 172.70 $59,936.41 $43,874.94 $37,713.17 $18, 112.74 $1,466.82 2 - Customer#000000002: 164711, 26-Apr-1992, 905633, 05-Jul-1995, 135943, 22-Jun-1993, 1485505, 24-Jul-1998, 1192231, 03-Jun-1996, 224167, 08-May-1996, 1226497, 04-Oct-1993, 287619, 26-Dec-1996, $311,344.63 $255, 261.98 $249,828.07 $ 230,389.81 $100,551.33 $85,477.93 $81,926.50 $16,946.76 3 - Customer#000000003: 4 - Customer#000000004: 9154, 23-Jun-1997, 36422, 04-Mar-1997, 816323, 23-Jan-1996, 1603585, 26-Mar-1997, $336,929.37 $266,881.39 $265,441.63 $243,002.67
306439, 895172, 916775, 1406820, 835173, 1490087, 1201223, 491620, 212870, 1718016, 859108, 1073670, 869574, 883557, 1774689, 17-May-1997, 04-Dec-1995, 26-Apr-1996, 24-Feb-1996, 18-Aug-1993, 10-Jul-1996, 13-Jan-1996, 22-May-1998, 30-Oct-1996, 30-Aug-1994, 20-Feb-1996, 24-May-1994, 21-Jan-1998, 30-Mar-1998, 08-Jul-1993, $234,026.80 $229,991.06 $215, 744.35 $195,275.97 $187,151.50 $177,431.73 $155, 250.48 $154,443.20 $152,662.65 $123,028.08 $105,414.33 $76,478.32 $58,714.84 $44,043.89 $15,444.64 5 - Customer#000000005: 374723, 20-Nov-1996, 1572644, 01-Jun-1998, 1478917, 06-Oct-1992, 1521157, 23-Aug-1997, 269922, 19-Mar-1996, 1177350, 03-Jul-1997, $241,348.35 $201,565.95 $187,297.10 $141,934.18 $122,008.56 $ 47,596.96 Deliverables Submit a file solution 3.Ist (or solution3.pdf) with a report from processing of SQL script solution2.sql. The report MUST have no errors the report MUST list all SQL statements processed. The report MUST include ONLY SQL statements and control statements that implement the specifications of Task 3 and NO OTHER statements.