Microsoft Access 2010 Chapter 7 Lab 1 Querying the ECO Clothesline Database Using SQL

Microsoft Access 2010 Chapter 7 Lab 1 Querying the ECO Clothesline Database Using SQL

Problem: The management of ECO Clothesline wants to learn more about SQL and has determined a number of questions it wants SQL to answer. You must obtain answers to the questions posed by management.

Instructions: If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the ECO Clothesline database that you used in Chapter 6. Otherwise, see your instructor for information on accessing the files required in this book.

Perform the following tasks:
1. Find all customers where the customer type is SAL. Include the Customer Number, Customer Name, and Sales Rep Number fields in the result. Save the query as Lab 7-1 Step 1 Query.
2. Find all customers located in Tennessee (TN) with a paid amount greater than $1,500.00. Include the Customer Number, Customer Name, and Amount Paid fields in the result. Save the query as Lab 7-1 Step 2 Query.
3. Find all customers whose names begin with the letter, C. Include the Customer Number, Customer Name, and City fields in the result. Save the query as Lab 7-1 Step 3 Query.
4. List all cities in descending order. Each city should appear only once. Save the query as Lab 7-1 Step 4 Query.
5. Display the customer number, name, sales rep number, first name, and last name for all customers. Sort the results in ascending order by sales rep number and customer number. Save the query as Lab 7-1 Step 5 Query.
6. List the average balance amount grouped by sales rep number. Name the average balance as Average Billed. Save the query as Lab 7-1 Step 6 Query.
7. Find the customer number and name for every pair of customers who are located in the same city. Save the query as Lab 7-1 Step 7 Query.
8. Find the customer numbers, names, and sales rep numbers for all customers that have open orders. Use the alias O for the Open Orders table and C for the Customer table. Each customer should appear only once. Save the query as Lab 7-1 Step 8 Query.
9. Use a subquery to find all sales reps whose customers are located in Pineville. Save the query as Lab 7-1 Step 9 Query.
10. Find the average balance amount for sales rep 44. Save the query as Lab 7-1 Step 10 Query.
11. Submit the revised database in the format specified by your instructor.