Homework # 5

SQL 1

Due: 3/6/2001

70-455

Information Resources Management

2/27/2001

50 points

 

The diagram and database schema below describe a database for an order entry system. You are to create appropriate SQL for the questions below.

Order Entry Database Schema:

Customer = (CustNo, CustFirstName, CustLastName, CustStreet, CustCity, CustState, CustZip, CustBal)

Order = (OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity, OrdState, OrdZip)

Employee = (EmpNo, EmpFirstName, EmpLastName, EmpPhone, SupEmpNo, EmpCommRate)

Product = (ProdNo, ProdName, ProdMfg, ProdQOH, ProdPrice, ProdNextShipDate)

OrdLine = (OrdNo, ProdNo, Qty)

Notes:

CustBal is current customer balance due. Order address information is delivery information and isn't necessarily the same as the customer's address. Additionally, automated orders may be accepted (through a web site), in which case EmpNo is null.

Complete all of the following (2 points each):

1. List the customer number, name (first and last), and balance of customers.

2. List the customer number, name, and balance of customers who reside in Colorado (CO).

3. List all columns of the product table for products costing more than $50. Order the result by product manufacturer and product name.

4. List the customer number, name, city, and balance of customers who reside in Denver, CO with a balance of more than $150 or who reside in Seattle, WA with a balance greater than $300.

5. List the cities and states where orders have been delivered. Remove duplicates from the list.

6. List all columns from the order table for internet orders placed in January 2001.

7. List all columns from the product table that contain the word "printer" in the product name.

8. List the order number, order date, customer number, and customer name of orders placed in February 2001 by Colorado customers but sent to Washington recipients.

9. List the employee number, name, and phone of employees who have taken orders in January 2001 from customers with balances greater than $200. Remove any duplicate rows.

10. List the customer number and name, order number, order date, employee number and name, product number and name, and order cost of that product (quantity * price) for products ordered on January 27, 2001, in which the cost of product ordered exceeds $150.

Complete any ten (10) of the following (3 points each) Keep the numbers the same!

11. List the customer number and name, order number, order date, employee number and name, and total order cost for orders on January 27, 2001, in which the total order cost exceeds $500.

12. List the names of the customers with the highest and lowest balances.

13. List the average balance of customers by city and state. Eliminate cities in the result with less than two customers.

 

14. List the customer number and name, the sum of the quantity of products ordered, and the total amount of products ordered since the beginning of the year. Only include products in which the product name contains "printer". Only include customers who have ordered more than three "printer" products since the beginning of the year.

15. List the product number, product name, total quantity of products ordered, and total number of orders for products ordered in January 2001. Only include products that have more than five orders in January 2001. Sort the result by descending total number of orders.

16. List the order number, order date, customer number, customer name, customer state and shipping state in which the customer state differs from the shipping state.

17. List the employee number, employee name, commission rate, supervising employee name, and commission rate of the supervisor.

18. List the employee number, name, and total amount of commissions on orders taken in January 2001. The amount of a commission is the sum of the dollar amount of products ordered (quantity * price) times the commission rate of the employee.

19. List all addresses (street, city, state, zip) in the customer table or order table. Each address should only appear once in the result. Sort the result by zip code.

20. List the order number, order date, employee number, employee name, customer number, and customer name of orders placed on January 23, 2001. List the order even if there is not an associated employee.

21. List the customer number and the customer name of Colorado customers who have not placed orders in February 2001.

22. List the number and the name of customers who have ordered only products manufactured by Connex. Remove any duplicate rows.

23. List the product number and name for products contained on every ordered placed on January 7, 2001 through (and including) January 9, 2001.

24. List the product number, name, and inventory value (ProdQOH * Price) for all products whose inventory value is less than $5,000 and that either don't have a shipment scheduled (ProdNextShipDate is null) or don't have a shipment scheduled to arrive until after April 30, 2001.

25. List the first and last names of customers who have the same name (first and last) as an employee.