Relationship and schema design assignment

1.sql

-- How many people work in the Sales department?
SELECT 
COUNT(emp_name) AS employees_in_sales 
FROM 
employee 
JOIN 
department 
ON 
employee.department = department.id 
WHERE
department.dept_name = 'Sales';

2.sql

-- List the names of all employees assigned to the 'Plan Christmas party' project.
SELECT 
emp_name 
FROM 
employee 
JOIN 
employee_project 
ON 
employee.id = employee_project.emp_id 
JOIN 
project 
ON 
employee_project.project_id = project.id 
WHERE 
project_name = 'Plan christmas party';

3.sql

-- List the names of employees from the Warehouse department that are assigned to the 'Watch paint dry' project.
SELECT 
emp_name 
FROM 
employee 
JOIN 
department 
ON 
employee.department = department.id 
JOIN 
employee_project 
ON 
employee.id = employee_project.emp_id 
JOIN 
project 
ON 
project.id = employee_project.project_id 
WHERE 
department.dept_name = 'Warehouse' 
AND 
project_name = 'Watch paint dry';

4.sql

-- Which projects are the Sales department employees assigned to?
SELECT 
project_name 
FROM project 
JOIN 
employee_project 
ON 
project.id = employee_project.project_id 
JOIN 
employee 
ON 
employee_project.emp_id = employee.id 
JOIN 
department 
ON 
employee.department = department.id 
WHERE 
dept_name = 'Sales' 
GROUP BY 
project_name;

5.sql

-- List only the managers that are assigned to the 'Watch paint dry' project
SELECT 
employee.emp_name 
FROM 
employee 
JOIN 
department 
ON 
employee.department = department.id 
JOIN 
employee_project 
ON 
employee.id = employee_project.emp_id 
JOIN 
project 
ON 
employee_project.project_id = project.id 
WHERE 
department.manager = employee.id 
AND 
project.project_name = 'Watch paint dry';