Assignment for Relationships and schema design

assignment_design_schemas.sql

-- How many people work in the Sales department?
SELECT
  count(e.emp_name)
FROM
  employee e
  INNER JOIN
  department d
  ON e.department = d.id
WHERE
  d.dept_name = 'Sales';
  
-- 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 e
  JOIN
  employee_project ep
  ON e.id = ep.emp_id
  JOIN
  project p
  ON ep.project_id = p.id
 WHERE p.project_name = 'Plan Christmas party';

-- List the names of employees from the Warehouse department 
-- that are assigned to the 'Watch paint dry' project.
 SELECT
  e.emp_name as Full_Name,
  p.project_name as project
FROM
  employee e
  INNER 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 d.dept_name = 'Sales' AND p.project_name = 'Watch paint dry';
 
-- Which projects are the Sales department employees assigned to?
 select
  p.project_name as project,
  e.emp_name as Full_Name,
  e.id 
FROM
  project p
  INNER JOIN
  employee_project ep
  ON p.id = ep.project_id 
  JOIN
  employee e
  ON ep.emp_id = e.id
  JOIN
  department d2 
  ON e.department = d2.id 
 WHERE d2.dept_name = 'Sales';

-- List only the managers that are assigned to the 'Watch paint dry' project.
 select
  e.emp_name as Full_Name,
  d2.id as manager_id,
  e.id ,
  p.project_name 
from
  employee e
  join
  department d2 
  ON e.id = d2.manager 
  INNER JOIN
  employee_project ep
  ON e.id = ep.emp_id 
  inner JOIN
  project p
  ON p.id = ep.project_id 
 WHERE p.project_name = 'Watch paint dry';