MIDTERM
CS122, Spring 2004

Name:_________________


[About Products]

1.(5pt) Find the average price of hard drives.

SELECT AVG(price) FROM Products WHERE pid LIKE 'HDD*';

2.(5pt) Find the id and description of the CPU product with the highest price.

SELECT TOP 1 pid, description FROM Products WHERE pid LIKE 'CPU*'
	ORDER BY price DESC;

3.(5pt) List the id's and descriptions of all the products that have the word
"Intel" in the product description. Note that "Intel" has to appear in the
description as a word, not part of a word.

SELECT pid, description FROM Products
	WHERE description LIKE 'Intel *' OR description LIKE '* Intel'
	OR description LIKE '* Intel *' OR description LIKE 'Intel';


[About Orders]

4.(5pt) Find the number of unshipped orders.

SELECT COUNT(oid) FROM Orders WHERE sdate IS NULL;

5.(5pt) Find the id's of the orders which were placed 10 days ago but still have
not been shipped yet.

SELECT oid FROM Orders WHERE sdate IS NULL AND DATE() - odate > 10;

6.(5pt) Calculate the total of Order 2014. The total of an order is the sum of
the prices of all the products in that order.

SELECT SUM(p.price*o.quantiy) FROM Order_details o, Products p WHERE
	o.oid = 2014 AND o.pid = p.pid;

7.(5pt) Find the shipping address of Order 2014. Assume shipping address of an
order is the address of the customer who placed the order.

SELECT c.address FROM Customers c, Orders o
	WHERE o.oid = 2014 AND c.cid = o.cid;


[About Customers]
 
8.(5pt) Find the name of the customer whose customer id is 1002. Show the
result in the form <last name>, comma, space, <first name initial>, period.
For example, Sun, C.

SELECT lname + ', ' + LEFT(fname,1) + '.' FROM Customers WHERE cid = 1002;

9.(10pt) Find "Customer of the Month", which is the customer who spent the
most money in the store this month. The result should include customer id,
name of the customer in the form of <first name>, space, <last name>, and
the amount s/he spent this month. Use proper column headings.

SELECT TOP 1 c.cid, c.fname + ' ' + c.lname, SUM(p.price*d.quantity)
	FROM Customer c, Orders o, Order_details d, Products p
	WHERE c.cid = o.cid AND o.oid = d.oid AND d.pid = p.pid
		AND YEAR(o.odate) = YEAR(DATE())
		AND MONTH(o.odate) = MONTH(DATE())
	GROUP BY c.cid, c.fname + ' ' + c.lname
	ORDER BY SUM(p.price*d.quantity) DESC;


[About Sales]

10.(5pt) Find the monthly sales of Feburary, 2004. Monthly sales is the sum
of the totals of all orders placed in that month, including those orders
which are not shipped yet.

SELECT SUM(p.price*d.quantity) FROM Orders o, Order_details d, Products p
	WHERE o.oid = d.oid AND d.pid = p.pid AND MONTH(o.odate) = 2
	AND YEAR(o.odate) = 2004;

11.(10pt) List the monthly sales of last year by month, ordered by monthly
sales from the highest to the lowest. Use proper column headings.

SELECT MONTH(o.odate), SUM(p.price*d.quantity)
	FROM Orders o, Order_details d, Products p
	WHERE o.oid = d.oid AND d.pid = p.pid
		AND YEAR(DATE()) - YEAR(o.odate) = 1
	GROUP BY MONTH(o.odate)
	ORDER BY SUM(p.price*d.quantity) DESC;

12.(5pt) List the three months of last year which have the lowest monthly
sales, ordered by monthly sales.
 
SELECT TOP 3 MONTH(o.odate), SUM(p.price*d.quantity) FROM Orders o,
	Order_details d, Products p
	WHERE o.oid = d.oid AND d.pid = p.pid
		AND YEAR(DATE()) - YEAR(o.odate) = 1
	GROUP BY MONTH(o.odate)
	ORDER BY SUM(p.price*d.quantity);