You can perform this assignment based on a database on Nova or any other Oracle system you wish, but you must use the Oracle RDBMS.

You should use one or more SQL script files to complete this assignment.  Your script files should contain all your SQL and PL/SQL code.  Do NOT submit your SQL script files.  Doing so may result in confusion and will result in lost points.

Everything for this assignment must be in a single file.  If you are using SQL*Plus you must put all your SQL, PL/SQL, and results together in a single SPOOL file.  If you are using SQL Developer or other GUI, put all your screen snapshots in a single file for both your SQL statements and PL/SQL as they executed and the results.  Failure to include all your SQL, PL/SQL, and all your Oracle execution results along with them will result in lost points.

Do NOT submit additional files as this only complicates the grading, and will result in lost points.

The specific assignment steps are listed below.  In order to earn full credit you must keep your steps in the order shown, number your steps, and put everything in a single file.

1) Complete Project #2 by ensuring that the FINANCING_PLANS, DEALERSHIPS, VEHICLES, and TIMES dimension tables and the SALES_FACTS fact table is created.  Do a SELECT COUNT(*) FROM <table_name> for each of the five tables.

2)     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.

3) Create a user-defined function called VEHICLES_BY_VEHICLE_TYPE that receives an input parameter of a concatenated make and model and then queries the VEHICLES and SALES_FACTS tables to return the total vehicles sold by that combination.  Execute your function for a sample input value of your choosing to demonstrate that it works correctly.

4) Create a user-defined function called DOLLARS_BY_VEHICLE_TYPE that receives an input parameter of a concatenated make and model and then queries the VEHICLES and SALES_FACTS tables to return the total gross sales amount of the sales by that combination.  Execute your function for a sample input value of your choosing to demonstrate that it works correctly.

5) Create a stored procedure called STATS_BY_VEHICLE_TYPE that receives an input parameter of the concatenated make and model and then calls your two user-defined functions VEHICLES_BY_VEHICLE_TYPE and DOLLARS_BY_VEHICLE_TYPE.  Your stored procedure must return the results of the two functions’ executions via OUT parameters.  Execute your stored procedure for the same sample input values used earlier to demonstrate that it works correctly.

6) Develop an SQL query to determine which holiday had the most sales and then drill down via another query to determine for that holiday which dealership, by zip code, had the most sales. Then drill down by another query to determine for that holiday which dealership, by zip code, and make had the most sales.

7) Develop an SQL query to determine how many days, by day type, didn’t have more than 2 vehicles and $50,000 total sales.  In order to achieve a fair comparison, analyze your results by the total number of weekdays, weekend days, and holidays in your TIMES table.

Your submission MUST be in a single Word or PDF file with all steps numbered and in order.