Module 20 Checkpoint 18 Answers

gistfile1.txt

-- HOW MANY PEOPLE WORK IN THE SALES DEPARTMENT
-- 4
select
	count(e.emp_name)
from employee e
join department d on e.department = d.id
where d.dept_name = 'Sales';
 

-- LIST THE NAMES of EMPLOYEES ASSIGNED TO THE 'PLAN CHRISTMAS PARTY' PROJECT
-- Toby Flenderson
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 = 'Plan christmas party';
   
   
-- LIST THE NAMES OF EMPLOYEES FROM THE WAREHOUSE DEPARTMENT THAT
-- ARE ASSIGNED TO THE 'WATCH THE PAINT DRY' PROJECT
-- NONE
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 SALES EMPLOYEES ASSIGNED TO
-- Watch paint dry
select
	e.emp_name,
	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';
   
   
-- LIST ONLY THE MANAGERS THAT ARE ASSIGNED TO THE 'WATCH PAINT DRY' PROJECT
-- Jim Halpert
select
	e.emp_name,
	p.project_name
from employee e
join department d on e.id = d.manager
join employee_project ep on d.manager = ep.emp_id
join project p on p.id = ep.project_id
where p.project_name = 'Watch paint dry';