SQL Queries for the ClassicModels Database

Set A

These requirements require you to setup and connect to the ClassicModels database and write Java code to satisfy all 3 requirements in your allocated set.

Set A

1. List the product lines that contain 'Cars'.

2. Report the account representative for each customer.

3. Reports those products that have been sold with a markup of 100% or more (i.e., the priceEach is at least twice the buyPrice)

Set B

1. List all payments greater than twice the average payment.

2. Who are the employees in Boston?

3. List the names of customers and their corresponding order number where a particular order from that customer has a value greater than $25,000?

Set C

1. Report total payments for October 28, 2004.

2. List the amount paid by each customer.

3. List the names of products sold at less than 80% of the MSRP

Set D

1. Report those payments greater than $100,000.

2. Report the products that have not been sold.

3. Compute the profit generated by each product line, sorted by profit descending.

Set E

1. List all payments greater than twice the average payment.

2. Report the number of orders 'On Hold' for each customer.

3. Compute the revenue generated by each product, sorted by product name.

Set F

1. Report the name and city of customers who don't have sales representatives?

2. Report those payments greater than $100,000. Sort the report so the customer who made the highest payment appears first.

3. Compute the average time between order date and ship date for each customer ordered by the largest difference.

Set G

1. Which orders have a value greater than $5,000?

2. List the amount paid by each customer.

3. Compute the revenue generated by each sales representative based on the orders from the customers they serve.

Set H

1. List the products in each product line.

2. Report the total payments by date

3. List the names of customers and their corresponding order number where a particular order from that customer has a value greater than $25,000?