The Full Picture
Concept:
Combining multiple JOINs and aggregates for complex queries.
Detective Indecks:
I need to know which engineers are working on Engineering projects. That means connecting employees to projects through assignments. Three tables, one answer.
Database:
Now you're thinking like a pro, detective. You need to chain JOINs—employees to employee_projects to projects. Each link reveals another piece of the puzzle.
Detective Indecks:
Walk me through it.
Database:
Start with employees. Join to employee_projects using employee_id. Then join employee_projects to projects using project_id. Like this: `FROM employees e INNER JOIN employee_projects ep ON e.id = ep.employee_id INNER JOIN projects p ON ep.project_id = p.id`.
Detective Indecks:
And I filter for Engineering department projects?
Database:
Add WHERE p.department_id = 1 at the end. Now you're only seeing Engineering projects and the employees assigned to them.
Detective Indecks:
What if I want to count how many engineers are on these projects?
Database:
Wrap it in COUNT: `SELECT COUNT(DISTINCT e.id) ...`. DISTINCT makes sure you don't count the same employee twice if they're on multiple projects.
Detective Indecks:
This is it. The final piece. Let's see who's really involved in Engineering.
Example Code:
SELECT d.name, COUNT(e.id) as emp_count, AVG(e.salary) as avg_salary FROM departments d LEFT JOIN employees e ON d.id = e.department_id GROUP BY d.name;Your Assignment
Find all employees working on projects in the Engineering department (department_id = 1). Join employees, employee_projects, and projects tables to see the complete picture.
Sql Console
sql>