Description

This is a required assignment. You will connect to the database that you previously created and build queries to answer the questions in the assignment. Completing this assignment will demonstrate your ability to apply complex SQL skills, your ability to build an sql-answer for the given problems, your ability to choose the proper strategy for the solution of the problem.

Write SQL queries to answer the following questions:

1. List the Order ID and payment amount

A: Only for orders which have payments on them.

B: For all orders, regardless of if a payment has been made.  For orders with null payments, substitute 0.

2. For every employee, list the name of the employee and the number of skills they have.

3. List all the customers and their orders, but only for customers from New Mexico.

4. List each customer and the number of different shipping addresses for each but only for customers with 2 or more locations.

5.

A: List the customer name, order ID, orderlineId, productid and Product quantity for all customers who have placed orders. Include the orders that are  associated with an orderline and product.

B: List the customer name, order ID, orderlineId, productid and Product quantity for all customers, even those who have not placed orders.

C: List the customer name, order ID, orderlineId, productid and Product quantity for all customers who have placed orders but even if these orders are not associated with any products or orderliness.

6.

A: For all orders with payments made on those orders list the customerid, total cost of the order, and payment amount on the order.

B: Now filter the above results so that only orders with balances remaining are displayed. Additionally report the balance remaining on the order and order the results from highest remaining balance to lowest.

7. Report the OrderID and OrderDate for all orders for Salespersons from New York:

A: A use a join

B: Use a noncorrelated Subquery

C: use a correlated subquery

D: use where exists

8. Report the name and hiredate of employees who were hired before their manager as well as the name and hire date of their manager, label these columns ManagerName and ManagerDateHired.

9. Report the ID and name of employees who do not supervise any other employees.

10. List the order id for all orders where the customer has purchased a product from the Basic product line. How many orders do you see? How many distinct orders?

11. Now using set operators answer the following questions:

A: List the order id for all orders where the customer has purchased a product from the Basic product line or from the Antique product line. How many orders do you see? How many distinct orders?   

B: List the order id for all orders where the customer has purchased a product from the Basic product line and not from the Antique product line.  

C: List the order id for all orders where the customer has purchased a product from the Basic product line and the Antique product line.

D: Answer part C again but this time without using set operators.   

12. Find all the orders where at least 4 different products were ordered AND the total cost of the order is over $3000. Hint: Use set operators (union, intersect, minus) for your query.

13. Find all orders with a higher total cost than the average (Hint: you may need a subquery in the having clause, although other ways of writing it also exist)

14. Find the customer and total dollar amount spent for the customer who has spent the most.

15. For every product, report the total quantity ordered. Additionally, report the popularity as follows:

if none have been sold, report "nobody wants these"

if 1 or more has been sold, report "not very popular"

if 5 or more have been sold, report "popular"

if 10 or more have been sold report "very popular"