Using PL/SQL populate the SALES_FACTS table. One way to do this is to use a curs

ID: 3560152 • Letter: U

Question

Using PL/SQL populate the SALES_FACTS table. One way to do this is to use a cursor to go through
all of your SALES table rows, joined with other tables as necessary, to determine each combination of
Sale_Day, Vehicle_Code, Plan_Code, and Dealer_ID. Then for that combination of the four columns,
calculate the total Vehicles_Sold and the Gross_Sales_Amount. If these values are zero then don't
INSERT a row into the SALES_FACTS table. Only insert rows for combinations of the four foreign key
columns where there were some vehicles sold. After populating your SALES_FACTS table execute the
query "SELECT COUNT(*) FROM sales_facts;" to show the row count. Also execute the query
"SELECT SUM(vehicles_sold) FROM sales_facts;" to ensure that you have included all of your 500
or more sales.

SALES TABLE

SALES_ID NOT NULL VARCHAR2(6) PK
VEHICLE_STATUS NOT NULL VARCHAR2(25)
SALE_DATE NOT NULL DATE
MILEAGE NOT NULL NUMBER(6)
CUST_ID NOT NULL VARCHAR2(6) FK
SALESPERSON_ID NOT NULL VARCHAR2(6) FK
VIN NOT NULL VARCHAR2(20) FK
GROSSSALEPRICE NOT NULL NUMBER(10,2)

SALES_ID VEHICLE_STATUS SALE_DATE MILEAGE CUST_ID SALESPERSON_ID VIN GROSSSALEPRICE
-------- ------------------------- --------- ---------- ------- -------------- -------------------- --------------
s69228 used 13-JUL-09 56734 c10028 sp0001 WAUAF78E17A810752 12345
s69229 new 29-JUN-14 12 c10029 sp0010 5TDZT64AX9S741402 48234
s69230 new 02-APR-14 2345 c10030 sp0005 3N1BB51D81L946744 12345
s69231 new 24-JUL-14 23671 c10031 sp0010 1D7HE52N98S926753 29456
s69232 new 22-JUN-14 100 c10032 sp0001 1GCEK14VX6E522349 23000
s69233 new 01-JUN-14 300 c10033 sp0001 JN1AY1AR4CM024432 20000
s69234 new 23-MAR-14 1000 c10034 sp0001 1GTEK14TX2Z165398 10000
s69235 used 02-FEB-10 35000 c10035 sp0010 1G6KF5790YU479987 23000

Explanation / Answer

/* Procedure For Populating SALES_FACTS Table */

create or replace procedure SALES_FACTS_PROCEDURE
as
l_date date;
n NUMBER(5);
amt NUMBER(10,2);
CURSOR c1 IS SELECT SALE_DATE,COUNT(*),SUM(GROSSSALEPRICE) FROM SALES GROUP BY SALE_DATE ORDER BY SALE_DATE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO l_date,n,amt;
EXIT WHEN c1%NOTFOUND;
IF n <> 0
INSERT INTO SALES_FACTS(Sale_Day,Vehicles_Sold,Gross_Sale_Amt) VALUES (l_date,n,amt);
END IF;
END LOOP;
CLOSE c1;
END SALES_FACTS_PROCEDURE;

**************************************************************************************************************************************

/* Run the Procedure */

BEGIN

SALES_FACTS_PROCEDURE;

END;

***************************************************************************************************************************

Navigate

Using PHP, create a web page that contains a form The form allows the users to u

Using PL/SQL, create the exercises below Below is a function that will calculate

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

Using PL/SQL populate the SALES_FACTS table. One way to do this is to use a curs

Question

Explanation / Answer

Related Questions

Navigate