Perform SQL queries using JOINS

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 "CS2550A04.txt". At the top type
   CS2550 SQL Assignment 4
   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 order number, the order date, the customer number, the customer state and the shipping state in which the customer state differs from the shipping state.
   2. For Colorado customers, compute the number of orders placed during January 2007. The result should include the customer number, last name, and number of orders placed.
   3. List the product manufacturer, product name and number of times that the product has appeared on an order for products that have appeared on an order at least 5 times.
   4. List the first and last name and phone numbers of employees with customers in Seattle.
   5. List the name of each employee along with the name of their supervisor.
   6. List each customer city and state and the number of customers in that city and state. Order the list such that the city with the highest number of customers is at the top of the list
   7. For each employee with a commission rate greater than or equal to 0.05, compute the number of orders taken in January 2007. The result should include the employee number, employee last name, and number of orders taken.
   8. List each customer name and total number of items ordered for customers with zero balances.
   9. List the names of customers that have purchased Battery Back-up Systems.
   10. List each employee name and their January 2007 commission. The commission is the product of the monthly order amount times the commission rate.

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