MIDTERM
CS122, Spring 2004

Name:_________________


[About Products]

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

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

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.


[About Orders]

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

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

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.

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.


[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.

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.


[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.

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.

12.(5pt) List the three months of last year which have the lowest monthly
sales, ordered by monthly sales.