Write SQL SELECT statements using WHERE, GROUP BY, HAVING and ORDER BY.

Description

Write a series of SQL statements to query the OrderEntry database which consists of the tables and rows as shown on pages 121 - 123 of your text.

This assignment will be completed using MS Access. You may use MS Access 2003 or later. The CS Athena remote terminal server has MS Access 2008 installed and available for student use. Instructions for using the Athena server can be found at
http://rbhilton.com/wsucs/cs2550/CS2550TSInstructions.pdf

Complete the assignment by performing the following

1. Download the OrderEntry.mdb database for use in this and future lab work.
   The link for downloading the file is http://rbhilton.com/wsucs/cs2550/OrderEntry.mdb.removethis
   (You will need to rename the file to OrderEntry.mdb after downloading)
   
   
2. Run notepad and create a text file named "CS2550A03.txt". At the top type
   CS2550 SQL Assignment 3
   Spring 2010
   <your name>
   
   
3. Use MS Access to create and test SQL queries to perform the following. After you have each query working correctly, copy and paste the full SQL statement to the notepad text file. Number each query accordingly. SAVE YOUR TEXT FILE between each query.
   
   1. List the number of orders shipped to each city. The columns should be “Order City” and “Number of Orders”. Arrange the list by number of orders from highest to lowest.
   2. List the customer number, the name (first and last), and the balance of customers who reside in Colorado and have a balance less than $1000.
   3. List the average product price for each manufacturer. 
   4. List the city, state and number of orders for each order city.
   5. List the customer number, the first and last name, the city and the balance of customers who either reside in Denver and have a balance greater than $150 or who reside in Seattle with a balance greater than $300.
   6. Provide an alphabetic listing of the names of all cities that have two or more customers.
   7. List the product numbers for all products that with a shipped quantity greater than 3.
   8. For each city, list the number of orders that have been assigned to an employee.
   9. List the maximum price of any products with "Ink Jet" in the product name.
   10. List the order number, order date, and customer number of orders placed after January 23, 2007, shipped to Washington residents.

Number each SQL statement and put them into a single text file named CS2550A03.txt  and upload the file using the Assignment Uploader.