Chapter 12 – Queries
INTRODUCTION
Now we get to the fun part of data modeling – queries. Our data structures are in place with good table design, sound field types, and necessary relationships. Now we can use queries to perform a variety of tasks, most notably pulling data from those tables in unique ways as determined by the business scenarios.
- Create and edit simple queries to obtain data from a single Access table
Guided Exercises
From your cloud storage account, open your username-HomeGoodsStore database.
When two or more tables are used in a query, those tables need to be related to each other in the database. Create relationships between the tables in this database according to the image below. For each relationship, Enforce Referential Integrity and Cascade Update Related Fields.
Save your Relationships diagram when you are finished creating it.
You are working at the home goods store, and since you know about database design, you have been asked to retrieve some data based on specific conditions and requirements. Perform each problem carefully, making sure the results are exactly what your manager wants.
- Your manager would like a list of all the customers in alphabetical order. Create a query that displays all customers’ names, showing the FirstName and LastName, but sorting in ascending order by LastName, then ascending by FirstName. Save the results as qryGE1.
- What table(s) do we need?
- What field(s) do we need?
- How do we sort in the opposite way that we need to display the fields?
- Create a query that lists the FirstName and LastName for all customers from Indiana. Sort the results in ascending order by LastName. Save the results as qryGE2.
- What table(s) do we need?
- What field(s) do we need?
- What is special about Indiana?
- How many fields are being used in the design?
- How many fields are being displayed in the results?
- Create a query that lists the FirstName, LastName, and PhoneNumber for all customers from the Midwest region. Sort the results in ascending order by LastName, then ascending by FirstName. Save the results as qryGE3.
- Create a query that shows the FirstName, LastName, and PhoneNumber for all customers who have placed orders. Each customer should only appear once in the results. Sort in ascending order by LastName, then ascending by FirstName. Save the results as qryGE4.
- What did we do to make each customer appear only once?
- What did we do to make each customer appear only once?
Make sure all objects are closed. Close the database. Double-check the Last Modified date for your database file.
More Practice
Work you should do before our next class
Go to your IU OneDrive and open your username-HomeGoodsStore database.
- The CEO would like a list of all employees in alphabetical order. Create a query that displays all employees’ names, showing the FirstName and LastName, but sorting in ascending order by LastName, then ascending by FirstName. Save the results as qryMP1.
- Create a query that lists the FirstName, LastName, and Salary for all employees that work in the Watson building who are in the Quality Assurance department. Sort descending by Salary. Save the results as qryMP2.
- Create a query that shows the FirstName, LastName, and Department for all employees who are associated with an order. Make sure each employee’s name appears only once in the results. Sort in ascending order by LastName. Save the results as qryMP3.
- Create a query that shows the ProductName, Color, and Rating for items whose color is Natural and who have a rating that is greater than 5. No sorting is needed. Save the query as qryMP4.
Make sure all objects are closed. Close the database. Double-check the Last Modified date for your database file.
NOTE
You will NOT be provided with screenshots like this on assignments or exams.