The project sponsors of the U.S. Student Aid Data project want you to participate in the project debrief meeting. You are to provide information around the methodology and practices you used to develo
Eric Case
DAT 390
May 19, 2019
U.S. Student Aid Data Warehouse Development
SQL> CREATE OR REPLACE PROCEDURE TOP_Store IS
2 Ord_Amt Orders.Ord_Amt%TYPE;
3 Store_NM Store.Store_NM%TYPE;
CURSOR TOPStore is
4 5 SELECT Store_NM, Sum(Ord_Amt) as Ord_Amt
6 FROM Orders ORD,Store ST
7 WHERE ORD.Store_ID=ST.Store_ID
8 GROUP BY Store_NM
9 ORDER BY 2 DESC;
10 BEGIN
11 DBMS_OUTPUT.PUT_LINE('------------------------');
12 DBMS_OUTPUT.PUT_LINE('STORE_NM Tot_Ord_Amt ');
13 DBMS_OUTPUT.PUT_LINE('------------------------');,
OPEN TOPStore;
LOOP
FETCH TOPStore INTO Store_NM,Ord_Amt;
EXIT WHEN TOPStore%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(Store_NM||' '||Ord_Amt);
END LOOP;
DBMS_OUTPUT.PUT_LINE('------------------------');
14 15 16 17 18 19 20 21 CLOSE TOPStore;
22 END;
23 /
Procedure created.
SQL> exec TOP_Store;
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> exec TOP_Store;
------------------------
STORE_NM Tot_Ord_Amt
------------------------
Store1 145
------------------------
PL/SQL procedure successfully completed.
SQL>
SQL> CREATE OR REPLACE PROCEDURE TOP_Products IS
2 Prod_ID Product.Prod_ID%TYPE;
Prod_NM Product.Prod_NM%TYPE;
3 4 Prod_Cnt int;
5 CURSOR TOPProduct is
6 SELECT Prod_NM,count(ORD.Prod_ID) as Prod_Cnt
7 FROM Product PROD,Orders ORD
8 WHERE PROD.Prod_ID = ORD.Prod_ID
9 GROUP BY Prod_NM
ORDER BY 2 desc;
10 11 BEGIN
12 DBMS_OUTPUT.PUT_LINE('--------------------');
13 DBMS_OUTPUT.PUT_LINE('PROD_NM Prod_Cnt ');
DBMS_OUTPUT.PUT_LINE('--------------------');
14 15 OPEN TOPProduct;
16 LOOP
17 FETCH TOPProduct INTO Prod_NM,Prod_Cnt;
18 EXIT WHEN TOPProduct%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(Prod_NM||' '||Prod_Cnt);
19 20 END LOOP;
DBMS_OUTPUT.PUT_LINE('------------------------');
21 22 CLOSE TOPProduct;
END;
23 24
Procedure created.
SQL> exec TOP_Products;
--------------------
PROD_NM Prod_Cnt
--------------------
Prod1 2
Prod3 1
Prod2 1
------------------------
PL/SQL procedure successfully completed.
SQL>
