|
Homework # 5 - Key SQL 1 Due: 3/6/2001 |
70-455 Information Resources Management 2/27/2001 50 points |
These are possible answers. For the more complex queries, alternative answers which return the correct results may be given.
Complete all of the following (2 points each):
1. List the customer number, name (first and last), and balance of customers.
Select CustNo, CustFirstName, CustLastName, CustBal
From Customer
2. List the customer number, name, and balance of customers who reside in Colorado (CO).
Select CustNo, CustFirstName, CustLastName
From Customer
Where CustState = 'CO'
3. List all columns of the product table for products costing more than $50. Order the result by product manufacturer and product name.
Select *
From Product
Where ProdPrice > 50
Order By ProdMfg, ProdName
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.
Select CustNo, CustFirstName, CustLastName, CustCity, CustBal
From Customer
Where (CustCity = 'Denver' AND CustState = 'CO' AND CustBal > 150) OR (CustCity = 'Seattle' AND CustState = 'WA' AND CustBal > 300)
5. List the cities and states where orders have been delivered. Remove duplicates from the list.
Select Distinct OrdCity, OrdState
From Order
6. List all columns from the order table for internet orders placed in January 2001.
Select *
From Order
Where EmpNo IS NULL AND MONTH(OrdDate) = 1 AND YEAR(OrdDate) = 2001
7. List all columns from the product table that contain the word "printer" in the product name.
Select *
From Product
Where ProdName LIKE '%printer%'
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.
Select O.OrdNo, O.OrdDate, C.CustNo, C.CustFirstName, C.CustLastName
From Order as O, Customer as C
Where O.CustNo = C.CustNo AND O.OrdDate >= '02/01/2001' AND O.OrdDate <= '02/28/2001' AND C.CustState = 'CO' AND O.OrdState = 'WA'
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.
Select Distinct E.EmpFirstName, E.EmpLastName, E.EmpPhone
From Employee as E, Order as O, Customer as C
Where E.EmpNo = O.EmpNo AND O.CustNo = C.CustNo AND O.OrdDate > '12/31/200' AND O.OrdDate < '2/1/2001' AND C.CustBal > 200
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.
Select C.CustNo, C.CustFirstName, C.CustLastName, O.OrdNo, O.OrdDate, E.EmpNo, E.EmpFirstName, E.EmpLastName, P.ProdNo, P.ProdName, OL.Qty * P.Price AS ProdCost
From Customer as C, Order as O, Employee as E, OrdLine as OL, Product as P
Where C.CustNo = O.CustNo AND O.EmpNo = E.EmpNo AND O.OrdNo = OL.OrdNo AND OL.ProdNo = P.ProdNo AND O.OrdDate = '01/27/2001' AND ProdCost > 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.
Select C.CustNo, C.CustFirstName, C.CustLastName, O.OrdNo, O.OrdDate, E.EmpNo, E.EmpFirstName, E.EmpLastName, SUM (OL.Qty * P.ProdPrice)
From Customer as C, Order as O, Employee as E, OrdLine as OL, Product as P
Where C.CustNo = O.CustNo AND O.EmpNo = E.EmpNo AND O.OrdNo = OL.OrdNo AND OL.ProdNo = P.ProdNo AND O.OrdDate = '01/27/2001'
GROUP BY O.OrdNo
HAVING SUM (OL.Qty * P.ProdPrice) > 500
12. List the names of the customers with the highest and lowest balances.
Select CustFirstName, CustLastName
From Customer
Where CustBal IN
(Select MAX(CustBal) From Customer
UNION
Select MIN(CustBal) From Customer)
13. List the average balance of customers by city and state. Eliminate cities in the result with less than two customers.
Select AVG(CustBal), CustCity, CustState
From Customer
Group By CustState, CustCity
Having COUNT(CustNo) > 1
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.
Select C.CustNo, C.CustFirstName, C.CustLastName, SUM(OL.Qty), SUM(OL.Qty*P.ProdPrice)
From Customer AS C, Order AS O, OrdLine as OL, Product as P
Where C.CustNo = O.CustNo AND O.OrdNo = OL.OrdNo AND OL.ProdNo = P.ProdNo AND O.OrdDate >= '01/01/2001' AND P.ProdName LIKE '%printer%'
Group By C.CustNo
Having Count(P.ProdNo) > 3
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.
Select P.ProdNo, P.ProdName, SUM(OL.Qty), Count(O.OrdNo) AS TotOrders
From Product as P, OrdLine as OL, Order as O
Where P.ProdNo = OL.ProdNo AND OL.OrdNo = O.OrdNo AND O.OrdDate BETWEEN '01/01/2001' AND '01/31/2001'
Group By P.ProdNo
Having COUNT (O.OrdNo) > 5
Order By TotOrders DESC
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.
Select O.OrdNo, O.OrdDate, C.CustNo, C.CustFirstName, C.CustLastName, C.CustState, O.OrdState
From Order as O, Customer as C
Where O.CustNo = C.CustNo AND O.OrdState <> C.CustState
17. List the employee number, employee name, commission rate, supervising employee name, and commission rate of the supervisor.
Select E1.EmpNo, E1.EmpFirstName, E1.EmpLastName, E1.EmpCommRate, E2.EmpFirstName, E2.EmpLastName, E2.EmpCommRate
From Employee as E1, Employee as E2
Where E1.SupEmpNo = E2.EmpNo
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.
Select E.EmpNo, E.EmpFirstName, E.EmpLastName, SUM(E.EmpCommRate * OL.Qty * P.ProdPrice)
From Employee as E, Order as O, OrdLine as OL, Product as P
Where E.EmpNo = O.EmpNo AND O.OrdNo = OL.OrdNo AND O.ProdNo = P.ProdNo AND O.OrdDate LIKE '%-Jan-2001'
Group By E.EmpNo
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.
(With what you know, you can't do the sort)
Select Distinct CustStreet, CustCity, CustState, CustZip
From Customer
UNION
Select Distinct OrdStreet, OrdCity, OrdState, OrdZip
From Order
To include the sort, you would have to do this:
Select *
From (Select Distinct CustStreet, CustCity, CustState, CustZip
From Customer
UNION
Select Distinct OrdStreet, OrdCity, OrdState, OrdZip
From Order)
Order By CustZip
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.
Select O.OrdNo, O.OrdDate, E.EmpNo, E.EmpFirstName, E.EmpLastName, C.CustNo, C.CustFirstName, C.CustLastName
From Order as O, Employee as E, Customer as C
Where O.EmpNo = E.EmpNo AND O.CustNo = C.CustNo AND O.OrdDate = '01/23/2001'
UNION
Select O.OrdNo, O.OrdDate, ' ',' ', ' ', C.CustNo, C.CustFirstName, C.CustLastName
From Order as O, Customer as C
Where O.EmpNo IS NULL AND O.CustNo = C.CustNo AND O.OrdDate = '01/23/2001'
You could do the same query using an OUTER JOIN.
21. List the customer number and the customer name of Colorado customers who have not placed orders in February 2001.
Select C.CustNo, C.CustFirstName, C.CustLastName
From Customer
Where C.CustState = 'CO' AND C.CustNo NOT IN
(Select CustNo From Order Where Month(OrdDate) = 2 AND Year(OrdDate) = 2001)
22. List the number and the name of customers who have ordered only products manufactured by Connex. Remove any duplicate rows.
Select C.CustNo, C.CustFirstName, C.CustLastName
From Customer as C, Order as O
Where C.CustNo = O.CustNo
EXCEPT
Select C.CustNo, C.CustFirstName, C.CustLastName
From Customer as C, Order as O, OrdLine as OL, Product as P
Where C.CustNo = O.CustNo AND O.OrdNo = OL.OrdNo AND OL.ProdNo = P.ProdNo AND P.ProdMfg <> 'Connex'
The first select lists all customers who have placed an order. The second lists those who have ordered a product manufactured by someone other than Connex. By removing the second from the first, the result is as desired.
23. List the product number and name for products contained on every ordered placed on January 7, 2001 through (and including) January 9, 2001.
Select P.ProdNo, P.ProdName
From Product as P, OrdLine as OL, Order as O
Where P.ProdNo = OL.ProdNo AND OL.OrdNo = O.OrdNo AND O.OrdDate BETWEEN '01/07/2001' AND '01/09/2001'
Group By P.ProdNo
Having Count (Distinct O.OrdNo) IN
(Select Count(*) From Order Where OrdDate BETWEEN '01/07/2001' AND '01/09/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.
Select ProdNo, ProdName, ProdQOH * ProdPrice AS InvValue
From Product
Where InvValue < 5000 AND (ProdNextShipDate IS NULL OR ProdNextShipDate < '04/30/2001')
25. List the first and last names of customers who have the same name (first and last) as an employee.
Select C.CustFirstName, C.CustLastName
From Customer as C, Employee as E
Where C.CustFirstName = E.EmpFirstName AND C.CustLastName = E.EmpLastName