Purpose

The purpose of this exercise is to practice SQL queries on a single table.

Assignment

Perform the queries listed below on the PVFC11e database. Use MySQL Workbench and save all your queries to a single script (HW3_yourlastname.sql). Label each query with the comment containing the question number and your analysis, for example:

-- #1
-- Table:
-- Columns:
-- Condition:
SELECT ...

You can run an individual query by using on the toolbar or pressing CTRL-Enter with the cursor in that query.

1. Show all the products in product line 3 with a standard price below $400

2. Show how many different items were ordered on order number 1004.

3. List the employee names and ids of employees whose first name starts with an "R".

4. Show the average standard price for all items that contain the word "Natural"

5. List the name, city, state and postal code of all customers that live in Washington, Utah, and Colorado, sorted by zip code. (Be aware that "live in XX, XX, and XX" in English translates to "live in XX, XX, or XX" in MySQL).

6. For every product that has been ordered, display the product ID and the total quantity ordered (label this result TotalOrdered). List the least popular product first and the most popular last.

7. Display the product line ID and the average standard price for all products in each product line.

8. For each customer, list the Customer ID and the total number of orders placed.

9. Display the product ID and the number of orders placed for each product. Show the results in decreasing order by the number of times the product has been ordered, and label result column NumOrders.

10. For each customer who had exactly one order, list the Customer ID and the number of orders placed.

11. For each date, list the total number of orders placed on that date, sorted by date.

12. For each date, list the total number of orders placed on that date, sorted by number of orders with the largest numbers first.

13. For each date, list the total number of orders placed on that date, sorted by number of orders with the largest numbers first. If there are multiple dates having the same number of orders, then list the most recent dates first.