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.

  1. 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.
  2. 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.
  3. Query Employee 3: Find all employees who hold the positions of Club Director or Club Manager. Show the employee ID, position, and location.
  4. 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.
  5. 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.
  6. 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.
  7. 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.