Notes

Question 18: Show the name, date of birth, and salary of all employees (use a table alias)

SELECT e.first_name, e.date_of_birth, e.salary
FROM employees AS e;

Question 19: Show the names of each employee and the name of the department they work in
(name of the department is in a different table, need to use JOIN)

SELECT first_name, last_name, department_name
FROM employee INNER JOIN department ON employee.departmentid = department.departmentid

INNER JOIN (only join together records from emp and dep that match after employee.departmentid = department.departmentid)
Joe Smith is in departmentid 1 in the employee table. departmentid 1 is Finance in the department table

Question 20: Show a list of Projects and the name and location of the department that manages each project.

SELECT project_name, department_name, department_location
FROM project INNER JOIN department ON project.departmentid = department.departmentid ;

Question 21: Show the name of each department and the total payroll

SELECT department_name, SUM(salary) AS department_payroll
FROM department INNER JOIN employee ON department.departmentid = employee.departmentid
GROUP BY department_name

Question 22: What is the total payroll for each department location?

SELECT department_location, SUM(salary) AS location_payroll
FROM department INNER JOIN employee ON department.departmentid = employee.departmentid
GROUP BY department_location

Question 23: What is the total payroll for each department location, but only show locations with total payroll more than $50,000?

SELECT department_location, SUM(salary) AS location_payroll
FROM department INNER JOIN employee ON department.departmentid = employee.departmentid
GROUP BY department_location
HAVING SUM (salary) > 50000;

(where clause filters record by record)
(having clause filters group by group)

How would you do this question:
Find the departments where the average salary is higher than the company-wide average salary. Would this also need a HAVING clause?

Question 24: Show the name of each department and the total payroll but only if the total payroll is more than $60,000

SELECT department_name, SUM (salary) AS department_payroll
FROM department INNER JOIN employee ON department.departmentid = employee.departmentid
GROUP BY department_name
HAVING salary > 60000

Question 25: Show the name of each department and the total payroll but only
if the employee's salary is less than $50,000.
4. SELECT department_name, SUM( salary) AS department_payroll
1. FROM department INNER JOIN employee ON department.departmentid = employee.departmentid
2. WHERE salary < 50000
3. GROUP BY department_name ;

Question 26: Find the departments where the average salary is higher than the
company-wide average salary.

SELECT department_name, AVG( salary) AS department_average_salary
FROM department INNER JOIN employee ON department.departmentid = employee.departmentid
GROUP BY department_name
HAVING AVG( salary) > 43166.6666666667;
-- How to get the company-wide average salary?
SELECT AVG(salary) FROM employee;

SELECT department_name, AVG( salary) AS department_average_salary
FROM department INNER JOIN employee ON department.departmentid = employee.departmentid
GROUP BY department_name
HAVING AVG( salary ) > (SELECT AVG(salary) FROM employee) ;