Lab#1:
Part2
XYZ Fitness has many
fitness clubs around southern California.
They maintain a database of 4 tables: (1) the Employees table keeps the records of the basic personal information
of the employees, (2) the Clubs table
keeps the records of the locations of the clubs the employees work in and their
positions, (3) the Pay table keeps
the records of the hourly pay rates of the workers, and (4) the Hours table keeps the records of the
numbers of work hours of the employees accumulate in different weeks.
Step 1: Download this zipped sample database to
your folder and extract it.
Step 2: Browse the fields (design view) and data (data sheet view) of the
tables in the database.
Step 3: Creating Queries
Create queries (name the queries as noted) to retrieve the information
described in the following.
- Query Employee 1: Find all employees who were born after 12/31/1973 and hired after 12/31/2001.
Show the first name, last name, birthday, and hire date.
- Query Employee 2: Find all male
employees (those who with M
in the gender field) who were born
after 12/31/1973 and who live in either Landis
or Maldin. Show the first
name, last name, birthday, and city.
- Query Employee 3: Find all employees who hold the positions of Club Director or Club Manager.
Show the employee ID, position, and location.
- Query Employee 4: Find all employees who hold the positions of Club Director or Club Manager.
Show the employee ID, first name, last name, position, and location.
- Query Employee 5: Find all employees who work at least 40 hours in the week ending in 6/14/2005. Show the
employee ID, position, first name, and last name.
- Query Employee 6: Find all employees who are fitness instructors and work
at least 10 hours in the week ending in 6/14/2005.
Show the employee ID, position, location, first name, and last name.
- Query Employee 7: Find all employees who were either (i) born after 1/1/1985
with a pay rate more than $17 or
(ii) born before 1/1/1975
with a pay rate less than $10.
Show the employee ID, first name, last name, birthday, and pay rate.
Step
4: Verify your results with a classmate. Record in your reading &
progress report whether you can complete this part, mistakes you made in the
process, and whether you get the correct results eventually.