CMIS 420 Homework 4 Online Vehicle Sales OVS Database

CMIS 420 Homework 4 Online Vehicle Sales OVS Database

1) Complete your OVS database through the Homework #3 requirements.
2) Add 50 customers so that you now have 100 total. Consider using a copy, paste, and edit of your SQL statements in your overall SQL script file to make this process easier. Show all your INSERT SQL statements and the results in your SPOOL/HTML file. Execute a SELECT COUNT(*) statement to show the total row count after all data has been added.
3) Add 150 vehicles so that you now have 200 total. Consider using a copy, paste, and edit of your SQL statements in your overall SQL script file to make this process easier. Show all your INSERT SQL statements and the results in your SPOOL/HTML file. Execute a SELECT COUNT(*) statement to show the total row count after all data has been added.
4) Add 250 sales so that you now have 500 total (the same vehicle can be sold multiple times). Consider using a copy, paste, and edit of your SQL statements in your overall SQL script file to make this process easier. Show all your INSERT SQL statements and the results in your SPOOL/HTML file. Execute a SELECT COUNT(*) statement to show the total row count after all data has been added.
5) Ensure that you add enough sales financing rows so that you now have 500 total. Consider using a copy, paste, and edit of your SQL statements in your overall SQL script file to make this process easier. Show all your INSERT SQL statements and the results in your SPOOL/HTML file. Execute a SELECT COUNT(*) statement to show the total row count after all data has been added.
6) Add data to other tables as necessary, show all the SQL, and execute a SELECT COUNT(*) statement to show the total row counts after all data has been added.
7) Via a single SELECT query display the total count of sales, by model and then by zip code, with the highest values first. Use SQL99 syntax for your join conditions. Show the SQL and the results in your SPOOL/HTML file.
8) Via a single SELECT query with a correlated subquery display the sale ID and plan ID of all sales in the last 30 days, sorted by sale ID.
9) Via a single SELECT query display the zip code, make, and count of the largest car purchases (there may be a tie with two or more). One way to do this is to have a subquery that produces the counts by zip code and make, take the maximum, and then have the outer query display the zip code and make combination(s), with the count, that match this maximum count.

All SQL should be executed via a single SQL script file. Submit a single SQL*Plus SPOOL file produced by the above SQL in your SQL script file showing all your SQL and the results, or if using iSQL*Plus or other GUI (e.g. SQL Developer), a single Word or PDF file of screen snapshots showing both your SQL and the results. Do NOT submit your SQL script file.