IS 441, Spring 2017

HW6 (SQL 3)

(20+1 points total)

Write the SQL codes needed to complete each of tasks listed below. The data are in a file called Order.mdb. Run your SQL codes on the given DB, and paste the query results immediately following the corresponding SQL statement: code, result; code, result.

Note: One of the tables is named “Order”. Please note that “Order” is also an SQL reserved word; so the table Order, when being referred to in its full name, must be enclosed in a pair of brackets as in the form [Order]. || 1-3: 2 pts each; 4-8: 3 pts each

  1. List all items that were sold for at least 30% more than cost in the month of December. Hint: Use the function MONTH(date) to return the month of a date.
  2. List the inventory category and total quantity (by each category) ordered in the year 2015 (in descending order of total quantity).Hint: Use the function YEAR(date) similar to MOMTH(date).
  3. List the Spring products ordered by florists that had a list price over $200.

[Watch: (1) Join MANY tables!! (2) same field name appear in multiple tables; (3) same field name in multiple tables could mean different things!!]

  1. [This problem has TWO questions] List the customer ID and total orders for each customer, in descending order of total orders. (Compute once for total number of orders, andonce for total dollar amount – so two queries)
  2. List the item ID and description of each product that has not been ordered during the last quarter of any year.[Please be EXACT in implementing the logic AS IS STATED – do NOT do your “substitution” to anything that is not EXACTLY stated in the problem; i.e., “not been ordered during the last quarter” EXACTLY means it states; it is NOT “those ordered outside last quarter” – because those ordered outside Q4 could also be ordered in Q4]
  3. List the names of the employees who did not sell any items from the garden shop during the period from June to July. [Note: Same note as that for the previous question]
  4. List the manager name and total sales revenue attributable to each manager. (The list should be in descending order by total sales revenue.)【Self-join!】
  5. List the item ID and description of those items whose price is higher than the average price of item in its category. Display also the average price of the categories and name them CatAvg (so the columns will be: itemID, description, listPrice, CatAvg). Hint: The same logic as the Week 15 demo “List restaurants whose sales are higher than the average sales in its own city”

– passing a parameter from outer query into the inner query.