Directions and Requirements:

All problems are based on the Pine Valley Furniture (PVF) database, which can be downloaded from: http://faculty.uml.edu/xli/Courses/MIST.3030(63.303)/. Do all problems using Microsoft Access.

Turn in a printed copy of your solutions. No email.

For each problem, your solution should include both the SQL commands, followed by the query results (press the Alt + Print Screen key combination to copy-paste them from Access to Word).

Problems (50 points):

1. (6 points) How many customers are located in Florida?

2. (6 points) List all states (CustomerState) where there is at least one customer. Do not repeat the same state in your result table.

3. (6 points) Display OrderID, ProductID, OrderDate and OrderedQuantity for orders made after October 23, 2010, with at least four units for a product.

4. (6 points) Display CustomerID, CustomerName and OrderID of all customer orders. Include in this list any customer who has no orders in the database.

5. (8 points) Display a list of products (show ProductID and ProductDescription as the first two columns) for the products that have been ordered for at least three times. Display (as the third column) the number of times these products have been ordered.

6. (6 points) Find ProductID, OrderedQuantity, ProductStandardPrice and total price (= OrderedQuantity x ProductStandardPrice) for each product that has been ordered by customer #5.

7. (6 points) Find all customers (show CustomerID and CustomerName) who have not placed any order.

8. (6 points) List OrderID, ProductID and OrderedQuantity for all customer orders for which the order quantity is greater than the overall average order quantity.