Alex-Relationships-And-Schema-Drills

Using the same projects database from above write queries to answer the following questions:

How many people work in the Sales department?

SELECT
  count(e.emp_name) as emp_count,
  d.dept_name
FROM
  employee e
  JOIN department d ON e.department = d.id
WHERE
  d.dept_name = 'Sales'
GROUP BY
  d.dept_name;

List the names of all employees assigned to the 'Plan Christmas party' project.

SELECT
  e.emp_name AS Full_name,
  p.project_name AS Project
FROM
  employee_project ep
  JOIN employee e ON ep.emp_id = e.id
  JOIN project p on ep.project_id = p.id
WHERE
  p.id = '2';

List the names of employees from the Warehouse department that are assigned to the 'Watch paint dry' project.

SELECT
  e.emp_name
FROM
  employee e
  JOIN department d ON e.department = d.id
  JOIN employee_project ep ON e.id = ep.emp_id
  JOIN project p ON ep.project_id = p.id
WHERE
  p.project_name = 'Watch paint dry'
  AND d.dept_name = 'Warehouse';

Which projects are the Sales department employees assigned to?

SELECT
  p.project_name as project,
  e.emp_name as employee
FROM
  employee e
  JOIN employee_project ep ON e.id = ep.emp_id
  JOIN project p ON p.id = ep.project_id
WHERE
  e.department = '2';

List only the managers that are assigned to the 'Watch paint dry' project.

SELECT
  e.emp_name as Manager
FROM
  employee e
  JOIN department d ON e.id = d.manager;