Reference no: EM13930440

With the file attached please answer in SQL form the following.

1. Which employees (by name) have degrees? (the "answer" is Smith, Johnson and Williams)

SELECT at least the employee first and last names - SELECT other columns you think would be meaningful.

HINT: do a JOIN between the employees and degrees table ON EMPLOYEE_ID...this will in effect help you translate the

EMPLOYEE_IDs in the degree table into names.

2. Which employees (by name) do not have degrees? (the "answer" is Jones, Green, Reagan, Washington)

SELECT at least the employee first and last names.

HINTS:

- do a join LEFT OUTER JOIN between employees table and degrees table, keeping the employees table on the "left"

- use "IS NULL" in the WHERE line: WHERE DEGREES.EMPLOYEE_ID Is Null;

3. Which employees (by name) have no children? (the "answer" is Johnson, Reagan and Washington) SELECT at least the

employee first and last names.

HINT: exact same concepts apply as in Question 2

4. Which employee(s) (by name) have 2 or more children? Show a count of the number of children that the employee has AND

only show those employees with 2 or more children. (the "answer" is Green (4 kids), Jones (2 kids), Smith (2 kids))

HINT: To do this really easily, I'd suggest using two queries. (and when you use the 1st query in the 2nd query, you can refer

to it just like referring to a table: query1.fieldname OK, first: who has two or more children? Just count the employee IDs that

appear in the children table more than once. At this point you should have as a query result the employee IDs and number of

kids. Treat that query as a table (like I explain above) and do an inner join using the employees table so that you can get the

employee names.

5. Which employee(s) (by name) have a degree and at least one child? (the "answer" is Smith and Williams)

HINT: If you did an inner join between the degree table and the children table you'd have the employee IDs of people with

degrees and children, wouldn't you? You could treat that query as a table and do an inner join with the employee table in order

to translate the employee IDs into names.

6. Which employee(s) (by name) have neither a degree nor a child? (the "answer" is Reagan and Washington)

HINT: An easy way to approach this: do an OUTER JOIN (also using "IS NULL") between the employee table and the degree

table to figure out who doesnt have a degree. Do the same with the employee table and the children table (to see who doesnt

have children). Do an inner join between the results of the 2 queries to see where they intersect (to show who doesnt have a

degree or a child).

7. For those employees who have kids, what is the average number of kids they have? (the "answer" is 2.25)

HINT: First do a grouping of employee IDs and their count in the children table. Then perform a query on these query results,

figuring the average of those counts.

 

Attachment:- WK4_DB_ACCESS2000.zip