- Tpc R Benchmark Database Part P Partkey Id P Name P Mfgr P Brand P Type P Size P Container P Retailprice P Comment Linei 1 (51.15 KiB) Viewed 44 times
TPC R benchmark database PART P_PARTKEY ID P_NAME P_MFGR P_BRAND P_TYPE P_SIZE P_CONTAINER P_RETAILPRICE P COMMENT LINEITEM L_QUANTITY L_EXTENDEDPRICE L_DISCOUNT L_TAX L_RETURNFLAG L_LINESTATUS L_SHIPDATE L_COMMITDATE L_RECEIPTDATE L_SHIPINSTRUCT L_SHIPMODE L_COMMENT 0..1 A Contains PART-SHIIPPED-BY PS_AVAILQTY PS SUPPLYCOST PS COMMENT Includes 1.. Supplies L_LINENUMBER ORDERS O ORDERKEY ID O_ORDERSTATUS O TOTAL PRICE O_ORDERDATE O_ORDERPRIORITY O_CLERK 0 O_SHIPPRIORITY O_COMMENT SUPPLIER S_SUPPKEY ID S_NAME S_ADDRESS S_PHONE S_ACCTBAL S_COMMENT 1..* Belongs to NATION N_NATIONKEY ID N_NAME N_COMMENT Is-in A Submits CUSTOMER C_CUSTKEY ID C_NAME C_ADDRESS C_PHONE C_ACCTBAL C_MKTSEGMENT C_COMMENT Belongs to REGION R_REGIONKEY ID R_NAME R_COMMENT
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, 0B-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