Define and execute a process to evaluate your data warehouse data for incompleteness, nulls, and the ability to provide consistent query data.Create a summary for your project sponsors to inform them
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>
