Use SQL to create the tables described in table_descriptions. Use the exact table and column names provided. For “Data Type” determine the Oracle type that best fits the description. Create names for all constraints except NOT NULL (primary key, foreign key, unique, check, etc). That is, do not let these default to the Oracle created name for the constraint. Submit these statements in your assignment file.
Create the SQL statements necessary to solve the following problems. All your statements must work for an Oracle database.
A: Update the taxamount column for every car that has been sold. The new tax amount is 6% of the saleamount.
B: List the last name and state for every sales person who does not live in Florida. List the output by state in ascending (A to Z) order.
C: List the most expensive vehicle sold for each make. List the output in descending order (largest amount first) of sale amount.
D: List the last name and ID of every salesperson who has sold more than 1 vehicle. List the output in descending order (largest number of sales first).
E: List the make, model, year, color, and suggested price for all unsold vehicles. List the output in descending order of suggested price (largest suggested price first).
F: List the first name, last name, address, city, state, and ZIP for all customers. List the output in ascending order of state and city.
G: Find the average sale amount of all sold cars.
H: List the average sale amount and the maximum sale amount for each vehicle model within make. List the output by make then model (A to Z).
I: List the customer name and the total amount spent for all repairs by that customer. List the output in descending order of total amount spent (largest number first).
J: List all repair appointments scheduled in 2012. List the output in order by date and then time.
K: Delete all the tables. Make sure the statements to delete tables are in the proper order so they can be executed without error. The tables contain foreign keys and therefore you must delete the child table before the parent table.
Reviews
There are no reviews yet.