Microsoft Access

Using SQL

Purpose: To demonstrate the ability to use SQL when working with a database.

Problem: You have been researching SQL and its uses. You decide to test it out by creating several queries upon the HaveMore Electronics database.

Instructions: Open Access_Chapter_10_-_Lab_Test_A_-_HaveMore_Electronics provided in a location specified by your instructor and execute the following tasks:

1. Find all outlets that have online orders. Display all fields. Save the query as Chap 10 Lab Test A Step 1 Query.

2. Find all sales representatives who have YTD sales greater than 1,800,000. Show the Sales Rep ID, Full Name, and YTD Sales fields only. Save the query as Chap 10 Lab Test A Step 2 Query.

3. Find all sales representatives whose full name begins with a B. Display the Sales Rep ID, Full Name, and Age. Save the query as Chap 10 Lab Test A Step 3 Query.

4. List all inventory item descriptions in descending order. Save the query as Chap 10 Lab Test A Step 4 Query.

5. Display the Outlet ID, Item ID, Item Descr, Qty on Hand, and Date Updated for all inventory items. Sort the results in ascending order by Outlet ID and Item ID. Save the query as Chap 10 Lab Test A Step 5 Query.

6. List the average quota amount grouped by Sales Rep ID. Name the average quota as Average Quota. Save the query as Chap 10 Lab Test A Step 6 Query.

7. Find the Sales Rep ID and Full Name for every pair of sales representatives who are located in each outlet. Save the query as Chap 10 Lab Test A Step 7 Query.

8. Calculate the current value of each inventory item by calculating Qty on Hand times Price. Display the Item ID, Item Description, and Current Value. Save the query as Chap 10 Lab Test A Step 8 Query.

9. Use a subquery to find all item information for inventory items that have a Qty on Hand less than 10. Save the query as Chap 10 Lab Test A Step 9 Query.

10. Submit the revised database in the format specified by your instructor.

Page 1

© 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.