projectsdb_assignment.sql

-- 1. How many people work in the Sales department?
SELECT
  COUNT(e.id)
FROM 
  employee e
  JOIN 
  department d
  ON
    e.department = d.id
WHERE
  	d.dept_name = 'Sales';
    
-- 2. List the names of all employees assigned to the 'Plan Christmas party' project.
SELECT
  e.emp_name
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 ILIKE 'Plan Christmas party'
  
-- 3. 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
d.dept_name = 'Warehouse'
AND
p.project_name ILIKE 'Watch paint dry'

-- 4. Which projects are the Sales department employees assigned to?
SELECT
  DISTINCT(p.project_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
d.dept_name = 'Sales'

-- 5. List only the managers that are assigned to the 'Watch paint dry' project.
SELECT
  e.emp_name
FROM 
  department d
JOIN
  employee e
on
  d.manager = e.id
join
  employee_project ep
on 
  e.id = ep.emp_id
join 
  project p 
on
  ep.project_id = p.id
where
  p.project_name ilike 'Watch paint dry'