MS Access – Queries
Scenario: This assignment will make use of a small company's bike rental database. Use the attached MS Excel file to create the database on MS Access. The data model is as follows:
Make sure to designate the correct primary key(s), data types, and establish the relationships/referential integrity. After you have setup the database, complete the following queries in part 1 and 2.
Part 1 - Complete the following queries on MS Access and rename each query as E1, E2, E3, etc.
E1: What are the customer names, from which state do they reside, and when did they rent bike? E2: What is the name of customers who returned their bike damaged?
E3: Provide the customer name, time out, time in, and cost perhour.
E4: a) Show all the data fields in the Customer table; b) show all data fields in the Customer table and rental table.
E5: Show all customers who rented bikes from 5/1/2004 to 7/1/2004. E6: Show a list of bikes that cost more than $16.00.
E7: Show any customers who lives in a city where the city name begins with the word "New". E8: Show a count of how many rentals per bike description.
E9: Update the Cost per Hour table to add additional two dollars for all bikes.
Part 2 - Complete the following queries on MS Access. Each query should be renamed as Q plus the query number underscore your initials (e.g., the first query below, Q1, would be saved as Q1_BA for me on MS Access).
Q1: Create a query that will display Bike ID, Description, and Cost per Hour for any bike that between
$15 and $20 per hour.
Q2: Create a query that lists each customer's name (sorted by last name), state, date rented, and the name of the bike that was rented.
Q3: Create a query that lists all the bikes with the word "Specialized" and its cost per hour sorted from most expensive to least expensive.
Q4: Create a query that displays the name of customers who have returned their bike not damaged.
Q5: Create a query that will group customers by their first and last names and tallies up (i.e., count) the number of time they have rented a bike.
Q6: Create a query that lists the customers who have rented a bike after June 1, 2004. The query must show the customer's name, date, and bike description.
Part 3
In addition to the three tables required to solve part 1 and part 2, create a new table and rename it your last name330 (e.g., Abdelfattah303). Next, create three data fields: First Name, Last Name, and Student ID with the correct data types. Inside each field, enter the data: your actual first name, last name, and Mason ID. Save the table to your Access file.