Queries

1. It is time again for the company picnic. There is concern that there may be some employees who aren't age 21 yet, so COUNT how many employees we have who aren't yet age 21 as of today, and we'll decide if alcohol will be served or not.

* To determine an employee's current age, you'll want to know how many years old they are (obviously) - you can do this by subtracting their birthdate from today's date. To get today's date, use the function: Now(). To get today's date without the minutes, use DateValue(Now()).
* In Access, if you subtract one date from another, the answer will be expressed in days. So, divide it by 365 to come up with the person's age in years (and don't worry about leap years and all that - this is as accurate as I want you to get)

In any event, one of your queries may look something like this:
SELECT EMPLOYEES.EMPLOYEE_ID, (Now()-[DOB])/365 AS AGE

* you can easily handle my question in 2 queries - the first query could list the employees under age 21. The 2nd should count the employee IDs in the first query.

2. We want to be sure we have fair hiring practices - what percent of employees are female? (you can express this in as many decimal places you want & I don't need to see the & sign...so, 0.428571 would be fine as an answer) (Hint: do this in 3 queries - the first one should count how many females there are. The 2nd one should count how many total employees there are, counting the employee_ids. The 3rd one should do the math, such as:

SELECT Query1.TTL_FEMALES / Query2.TTL_EMPLOYEES
FROM Query1, Query2;

3. We are going to play company XYZ in coed basketball next week, what is the average height of our employees in inches? (Hint - this should be very easy and you can do this in one query with two lines!)

4. There is one position listed in the JOB_TITLES_TABLE table (the security person) that isn't reflected in the EMPLOYEES table. I've given you the answer already, but use SQL to find the answer. Write the query to work in any situation, against any job title that isnt filled...in other words, dont make specific reference to the "security guard" (HINT: you can do this in one query with an OUTER JOIN and IS NULL)

5. Which employee (by name) has a PHD? (Hint: you can do this in one query with an INNER JOIN). Only display the name of the employee with the PHD.

6. Count many employees have brown eyes. (HINT: use the Count function, since I'm asking for how many)