CMIS 420 Homework 2 Online Vehicle Sales Database

Description

CMIS 420 Homework 2 Online Vehicle Sales Database

1) Complete the creation and population of your Homework #1 tables and include all necessary integrity constraints including primary keys, foreign keys, CHECK constraints, UNIQUE constraints, and NOT NULL constraints. All tables must have a primary key and you must have a foreign key on the many side of each one-to-many relationship in the instructor-supplied ERD. Note that you must use Oracle for your database and and you must use actual SQL statements vs. a graphical approach. You may use Nova or any other Oracle instance you wish. You may use SQL*Plus, iSQL*Plus, or other command line tool (e.g. SQL Developer).
2) Add 20 more rows to both your VEHICLES and CUSTOMERS tables to have 25 rows total in each table and display the row counts of these tables.
3) Add 40 more rows to your SALES table to have 50 rows total and display the row count.
4) Create the SALE_FINANCINGS table if necessary, populate it as necessary, and show the row count.
5) Execute a multi-table join SELECT statement to display the number of sales by make of car, sorted by make.
6) Execute a multi-table join SELECT statement to display the number of sales by customer zip code, sorted by zip code.
7) Execute a multi-table join SELECT statement to display the number of sales by make of car and customer zip code, sorted by make and then by zip code.
8) Using a set operator execute a single SELECT statement to display together all the customer last names and all the salesperson last names, with no duplicates.
9) Using a set operator, subquery, and/or join query execute a SELECT statement to display the customers by customer ID, last name, and zip code who have not been involved in a sale, sorted by customer ID.
10) Using a correlated subquery execute a SELECT statement to display the sale ID and plan ID of all sales in the last 30 days, sorted by sale ID.

The SQL for Steps #5 through #10 above must all be a single SELECT statement.
You must show ALL your SQL statements and ALL your results, together, for ALL SQL statements in Steps #1 through #10 above.
If using SQL*Plus then submit a single SPOOL file with everything. If using iSQL*Plus or another interface tool then include a single Word or PDF file of screen snapshots for everything.
In your single file label your SQL statements (#1 through #10) and results and put them in order.
You must use an SQL script file vs. typing ad hoc SQL, but do NOT submit your SQL script file.