CMIS 420 Project 1 Online Vehicle Sales OVS Database

CMIS 420 Project 1 Online Vehicle Sales OVS Database

This first project is a combination of the four homeworks preceeding it. Therefore, if you’ve successfully completed all the homeworks you will have little to do this week. If not, you will have some catching up to do. Take a look at your fellow students’ shared solutions to see their approaches to setting up SQL script files for the homeworks.
1) Demonstrate that you have successfully created the 10 OVS database tables by executing the following query “SELECT table_name FROM user_tables”. Then perform a DESC (i.e. DESCRIBE) of each table to show its columns, data types, and NOT NULL constraints. Number this step and show both the SQL and the results in your SPOOL/HTML file.
2) Show that you have 100 total customers in your database by executing the query “SELECT COUNT(*) FROM customers” (your table name may be different). Number this step and show both the SQL and the results in your SPOOL/HTML file.
3) Show that you have 200 total vehicles in your database by executing the query “SELECT COUNT(*) FROM vehicles” (your table name may be different). Number this step and show both the SQL and the results in your SPOOL/HTML file.
4) Show that you have 500 total sales in your database by executing the query “SELECT COUNT(*) FROM sales” (your table name may be different). Number this step and show both the SQL and the results in your SPOOL/HTML file.
5) Show that you have 500 total sales_financings in your database by executing the query “SELECT COUNT(*) FROM sales_financings” (your table name may be different). Number this step and show both the SQL and the results in your SPOOL/HTML file.
6) Perform the following query for your other 6 tables “SELECT COUNT(*) FROM <table_name>” (insert the appropriate table name). Number this step and show both the SQL and the results in your SPOOL/HTML file.
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. Number this step and show both 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. Number this step and show both the SQL and the results in your SPOOL/HTML file.
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. Number this step and show both the SQL and the results in your SPOOL/HTML file.

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.