Attached would be the Excel file that you can make use of to analyze further or to import the details in other DBMS system to extract the answer for the questions as below.
The owner of Pitt Fitness knows that the power of the company’s database is in running queries to find out important information for making good business decisions. In the following exercises, use the data in the Pitt Fitness database shown in Figures 1-15, 1-16, 1-17, 1-18, and 1-19 in Module 1. When you use Microsoft Access to respond to these exercises, make a copy of the original database to create the queries. In each step, use QBE to obtain the desired results.
Examine the Pitt Fitness data and complete the following case exercises/problems:
- Which customer lives on Negley Avenue?
- How many customers were born from 2001 onward?
- Which instructors live in zip code 15217?
- Which classes (by name) have the highest number of reservations?
- How many customers have registered for a class on Wednesdays?
- What are the three most popular class names on Saturday?
- How many classes did Pablo Brough sign up for?
- How many customers have signed up for a class on January 8, 2021?
- Which customers prefer the 45-minute class length?
- Which instructor teaches the most classes?
- Which instructor teaches Zumba?
- Of all reservations for classes containing the word Cycle, which customer does not owe another fee besides the class price? (Note: This field is called “OtherFees.”) Create a calculated field to total both costs.
- According to the reservations so far, how much money will the Combination classes generate?
- Instructor Michael Nguyen is injured and has to cancel his class on Wednesday. Delete that record. How many classes does he have left to teach?
- Which instructor will customer Margo Patterson get for her HIIT class?
- How many classes are offered on Tuesday at 6 am and last 45 minutes?
- Which day of the week has the most classes?
Apply key module concepts to the Pitt Fitness case:
Suppose you want to list information on all the classes that Pitt Fitness offers, including the day of the week, time, location, and length of class. To do this, you could create a query. What table(s) should you add to the query? If you use the Classes table instead of all the tables together, does it make a difference to the output? What if someone had never reserved a specific class?
The owner of Pitt Fitness is considering whether to consolidate his clubs and offer classes in only one location to ensure profitability. To explore his question, what query or queries would you create to answer this business strategy question? In the following exercises, you use data in the Sports Physical Therapy database shown in Figures 1-21 through 1-24 in Module 1. When you use Microsoft Access to answer these questions, make a copy of the original database to create the queries to answer these questions. In each step, use QBE to obtain the desired results.
HINTS (ignore & or and used anywhere - this is also just a hint and not the only way you can solve it):
Q1: Use LIKE & *
Q2: Use >= and COUNT (optional)
Q3: =
Q4: GROUP BY & COUNT
Q5: Use LIKE & *
Q6: GROUP BY, COUNT, WHERE (where "WHERE" is used make sure "Show" field is unchecked i.e. not ticked) and shorting: descending
Q7: AND (just refers to criteria check in 2 locations)
Q8: =, GROUP BY & COUNT
Q9: =
Q10: GROUP BY & COUNT
Q11: GROUP BY & WHERE
More hints:
Q12: Like "*Cycle*"; For OtherFees - you can update Reservations table if you like to make it all 0 and then use "=0" for condition or as is you can check if the field contains null value; here, you will also need to create a calculate field. This is how you can do it:
TotalCost: [Field1]+[Field2]
It is to be added under a new field. "TotalCost" can read anything which you want that field to be called as. While the other elements in the [...] are the field names which you want to add. Update it as is required.
Top comments (0)