Notes

Question 44: Which departments have projects taking place in NY?

SELECT department_name
FROM department AS d
WHERE EXISTS (
SELECT p.project_id
FROM project AS p
WHERE p.departmentid = d.departmentid
AND p.project_location = 'NY'
);

EXISTS is TRUE if the nested query returns any records at all.
EXISTS is FALSE if the nested query does not return any records.

Question 45: Who is the highest paid salary?
(Show the employee where a higher paid employee does not exist)

Method 1:
SELECT first_name, last_name, salary
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee) ;

Method 2:
Show the employee where a higher paid employee does not exist.
SELECT e1.first_name, e1.last_name, e1.salary
FROM employee AS e1
WHERE NOT EXISTS
(SELECT e2.employeeid
FROM employee AS e2
WHERE e2.salary > e1.salary) ;

Question 47: Find the total hours per week each employee works on their projects.
If the total hours per week is more than 32 then classify them as 'Full Time'.
Otherwise classify the employee as 'Part Time'.

Step 1 Get the basic join, SUM and GROUP BY working
SELECT e.first_name, e.last_name, SUM(pa.hours_per_week) AS total_hours
FROM employee AS e INNER JOIN project_assignment AS pa
ON e.employeeid = pa.employeeid
GROUP BY e.first_name, e.last_name;

Step 2 Take the query just written and use it like a table.
SELECT first_name, last_name, total_hours
FROM ( SELECT e.first_name, e.last_name, SUM(pa.hours_per_week) AS total_hours
FROM employee AS e INNER JOIN project_assignment AS pa
ON e.employeeid = pa.employeeid
GROUP BY e.first_name, e.last_name
) AS data;