Complete Project #2 by ensuring that the FINANCING_PLANS, DEALERSHIPS, VEHICLES,

Question

Complete Project #2 by ensuring that the FINANCING_PLANS, DEALERSHIPS, VEHICLES, and TIMES dimension tables and the SALES_FACTS fact table is create

There is a SALES Table that has all the sales(SALE_ID primary) and a Salesperson table link by a the dealer_ID.

Using PL/SQL populate the SALES_FACTS table. One way to do this is to use four nested cursor loops to get every possible combination of the dimension tables’ primary keys and then the total vehicles sold and gross sales amount for each combination. If these values for Total_Vehicles_Sold and Gross_Sales_Amount for a combination are zero then don’t INSERT a row into the SALES_FACT table. Only insert rows for combinations of the four foreign key columns where there were some vehicles sold. Another approach besides nested cursor loops is to use a single INSERT statement with a GROUP BY clause. 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 200 or more sales.

Sale Fact Table

SALE_DAY DATE
VEHICLE_CODE VARCHAR2(10 BYTE) (Primary of the VEHICLE Table)
PLAN_ID VARCHAR2(10 BYTE) (Primary of the Financing_Plan table)
DEALER_ID VARCHAR2(10 BYTE) (Primary of the DEALEARSHIP Table)
VEHICLES_SOLD NUMBER(38,0)
GROSS_SALES_AMOUNT FLOAT

Explanation / Answer

DECLARE

CURSOR SALES_FACTS

IS

SELECT VECHICLE_CODE,

SALE_DATE,

SF. PLAN_ID,

SD.DEALER_ID,

COUNT(*) ASVEHICLE SOLD,

SUM(S.GROSS_SALE_PRICE)AS GROSS _SALES_AMOUNT

FROM SALES S,SALE_FINANCING SF,

WHERE S.SALE_ID=SF.SALE_ID ,

GROUP BY VECHICLE_CODE,

SALE_DATE,

SF. PLAN_ID,

SD.DEALER_ID,

HAVING COUNT (*)>0;

BEGIN

FOR VEHICLE_REC IN SALES_FACTS

LOOP

INSERT INTO SALES_FACTS(SALE_DAY,

VEHICLE_CODE,

PLAN_CODE,

VEHICLES_SOLD,

DEALER_ID,

GROSS_SALES_AMOUNT)

VALUES(VEHICLE_REC.SALE_DATE,

VEHICLE.REC.PALN_ID,

VEHICLE_REC.DEALER_ID,

VEHICLE.REC.VEHICLE_SOLD,

VEHICLE_REC,GROSS_SALES_AMOUNT) ;
COMMIT;

END LOOP;

COMMIT;

END;

/

SELECT COUNT(*) FROM SALES_FACTS;

COUNT(*)

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

38

SELECT SUM(VEHICLES_SOLD)FROM SALES_FACTS;

SUM(VEHICLES_SOLD)

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

200

NOTE:IF YOU FOUND ANY ERRORS OR QUESTIONS PLEASE LET ME KNOW.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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