- 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 24 times
(IF IMAGE NOT CLEAR, TRY ZOOM IN BROWSER AS UPLOADED ON
DESKTOP, THANKS)
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 2 (5.0 marks) Stored PL/SQL procedure Implement a stored PL/SQL procedure PARTSUPPLIER that lists information about the supplier key and supplier name supplying parts. The procedure first computes the total number of suppliers supplying the specified part. The procedure then extracts the supplier's information supplying the specified part. The information to be displayed include the part key, part name, the supplier key and the supplier's name. An example of a segment of the output for the list of suppliers supplying a specified part (part 59396) is as follow: part key: 59396, orange cream sandy lavender drab Number of suppliers supplying the part: 4 supplier Key supplier Name 166 Supplier#000000166 935 Supplier#000000935 1704 Supplier#000001704 2397 Supplier#000002397 It is up to you to decide if you want to handle exception in your procedure. Deliverables Submit your spooled file solution 2.Ist (or solution2.pdf) that contains your SQL script and the output from the execution of the script. The report must have no errors related to the implementation of your task and it must list all PL/SQL and SQL statements processed. Remember to set ECHO option of SQL*Plus to ON!