Microsoft Office Tutorials and References
In Depth Information
In the Lab continued
In the Lab
Lab 3: Querying the Ada Beauty Supply Database
Problem: The management of Ada Beauty Supply has determined a number of questions it wants
the database management system to answer. You must obtain answers to the questions posed by
Instructions: Use the database created in the In the Lab 3 of Chapter 1 on page AC 69 for this
assignment, or see your instructor for information on accessing the ﬁ les required for this topic. For
Part 1 and Part 3, save each query using a format similar to the following: Lab 2-3 Part 1a Query, Lab
2-3 Part 3a Query, and so on. Submit the revised database in the format speciﬁ ed by your instructor.
Instructions Part 1: Create a new query for the Customer table and include the Customer Number,
Customer Name, Balance, and Amount Paid ﬁ elds in the design grid. Answer the following questions:
(a) Which customers’ names begin with C? (b) Which customers are located on Devon? (c) Which
customers have a balance of $0.00? (d) Which customers have a balance greater than $200.00 and have
an amount paid less than $800.00? (e) Which two customers have the highest balances? (f) For each
customer, what is the total of the balance and amount paid amounts?
Instructions Part 2: Join the Sales Rep and the Customer table. Include the Sales Rep Number, First
Name, and Last Name from the Sales Rep table and the Customer Number, Customer Name, and
Amount Paid from the Customer table in the design grid. Sort the records in ascending order by Sales
Rep Number and Customer Number. All sales reps should appear in the result even if they currently
have no customers. Save the query as Sales Rep-Customer Query.
Instructions Part 3: Calculate the following statistics: (a) What is the average balance for customers
assigned to sales rep 44? (b) What is the total balance for all customers? (c) What is the total amount
paid for each sales rep?