I need this project completed by Saturday 3/4 10 AM EST
In this assignment you complete exhaustive the falsehood of your OVS, Inc. grounds repository groundsbase boards and then discharge a enumerate of resolution food queries using the boards.
You should use one or past SQL script smooths to exhaustive this assignment. Your script smooths should hold all your SQL and PL/SQL statute. Do NOT present your SQL script smooths. Doing so may issue in laziness and complete issue in obsolete points.
Everything for this assignment must be in a uncompounded smooth. If you are using SQL*Plus you must put all your SQL, PL/SQL, and issues unitedly in a uncompounded SPOOL smooth. If you are using SQL Developer or other GUI, put all your shade snapshots in a uncompounded smooth for twain your SQL assertions and PL/SQL as they effected and the issues. Failure to understand all your SQL, PL/SQL, and all your Oracle effort issues along delay them complete issue in obsolete points.
Do NOT present subjoined smooths as this merely complicates the grading, and complete issue in obsolete points.
The particular assignment treads are listed beneath. In appoint to deserve bountiful praise you must conceal your treads in the appoint paraden, enumerate your treads, and put perfectthing in a uncompounded smooth.
1) Create the TIMES colossus schema extent board via SQL. This is “TIME” board in the colossus schema symbol. The Sale_Day important key column appraises should be all limits from your highest sale limit through and including your terminal sale limit from your SALES board. The Day_Type appraises should be 'Weekday', 'Weekend', or 'Holiday' (this trumps Weekday and Weekend). Set all occurrences of the subjoined days for your limit dispose to be leisures: New Year’s Day, Martin Luther King Jr’s Birthday, President’s Day, Memorial Day, 4th of July, Labor Day, Columbus Day, Veterans Day, Thanksgiving, and Christmas. Use a PL/SQL fill to populate the TIMES board. Succeeding populating your TIMES board consummate the SQL assertion "SELECT day_type, COUNT(*),MIN(sale_day),MAX(sale_day) FROM era GROUP BY day_character ORDER BY day_type" to parade the summarized divergency of your board. Parade all your SQL and PL/SQL statute for this tread and the issues.
2) Create the SALES_FACTS colossus schema occurrence board via SQL. Fix that you enjoy apparent strange keys of Sale_Day, Vehicle_Code, Plan_Code, and Dealer_ID to relation your TIMES, VEHICLES, FINANCING_PLANS, and DEALERSHIPS boards, respectfully. Fix that you enjoy a important key for the SALES_FACTS board that is a composite of the Sale_Day, Vehicle_Code, Plan_Code, and Dealer_ID columns. Do a DESC (i.e. DESCRIBE) of your SALES_FACTS board succeeding it’s created.
3) Fix that the FINANCING_PLANS, DEALERSHIPS, VEHICLES, and TIMES extent boards and the SALES_FACTS occurrence board are all created and assiduous as required. Do a SELECT COUNT(*) FROM <table_name> for each of the five boards.
4) Using PL/SQL stored process populate the SALES_FACTS board. One way to do this is to use impure nested cursor loops to get perfect potential co-operation of the extent boards’ important keys and then the whole vehicles sold and outrageous sales aggregateity for each co-operation. If these appraises for
Total_Vehicles_Sold and Gross_Sales_Amount for a co-operation are cipher then don’t INSERT a row into the SALES_FACT board. Merely insinuate rows for co-operations of the impure strange key columns where there were some vehicles sold. Another appropinquation also nested cursor loops is to use a uncompounded INSERT assertion delay a GROUP BY chapter. Succeeding populating your SALES_FACTS board consummate the question “SELECT COUNT(*) FROM sales_facts;” to parade the row compute. Also consummate the question “SELECT SUM(vehicles_sold) FROM sales_facts;” to fix that you enjoy understandd all of your 200 or past sales.
5) Create a user-defined exercise named VEHICLES_BY_VEHICLE_TYPE that receives an input parameter of a concatenated fashion and character and then queries the VEHICLES and SALES_FACTS boards to come-back the whole vehicles sold by that co-operation. Consummate your exercise for a scantling input appraise of your choosing to manifest that it works rightly.
6) Create a user-defined exercise named DOLLARS_BY_VEHICLE_TYPE that receives an input parameter of a concatenated fashion and character and then queries the VEHICLES and SALES_FACTS boards to come-back the whole outrageous sales aggregateity of the sales by that co-operation. Consummate your exercise for a scantling input appraise of your choosing to manifest that it works rightly.
7) Create a stored process named STATS_BY_VEHICLE_TYPE that receives an input parameter of the concatenated fashion and character and then calls your two user-defined exercises VEHICLES_BY_VEHICLE_TYPE and DOLLARS_BY_VEHICLE_TYPE. Your stored process must come-back the issues of the two exercises' efforts via OUT parameters. Consummate your stored process for the identical scantling input appraises used precedent to manifest that it works rightly.
8) Develop an SQL question to designate which leisure had the most sales and then teach down via another question to designate for that leisure which dealership had the most sales. Then teach down by another question to designate for that leisure which dealership, by zip statute, and fashion had the most sales.
9) Develop an SQL question to designate how abundant days, by day character, didn’t enjoy past than 2 vehicles and $30,000 whole sales. In appoint to complete a unspotted similitude, dissect your issues by the whole enumerate of weekdays, weekend days, and leisures in your TIMES board.
Your acquiescence MUST be in a uncompounded quotation, Word, or PDF smooth delay all treads enumerateed and in appoint.