You have tables: Employees(id, name, dept_id), Departments(id, name), Projects(id, dept_id, project_name) Write a query to list employee names, their department names, and project names for projects in their department. Which query is correct?
ASELECT Employees.name, Departments.name, Projects.project_name FROM Employees JOIN Projects ON Employees.id = Projects.dept_id JOIN Departments ON Projects.dept_id = Departments.id;
BSELECT Employees.name, Departments.name, Projects.project_name FROM Employees JOIN Departments ON Employees.id = Departments.id JOIN Projects ON Departments.id = Projects.id;
CSELECT Employees.name, Departments.name, Projects.project_name FROM Employees LEFT JOIN Departments ON Employees.dept_id = Departments.id LEFT JOIN Projects ON Employees.dept_id = Projects.dept_id;
DSELECT Employees.name, Departments.name, Projects.project_name FROM Employees JOIN Departments ON Employees.dept_id = Departments.id JOIN Projects ON Departments.id = Projects.dept_id;
Step-by-Step Solution
Solution:
Step 1: Understand relationships
Employees link to Departments by dept_id; Projects link to Departments by dept_id.
Step 2: Check join conditions
SELECT Employees.name, Departments.name, Projects.project_name FROM Employees JOIN Departments ON Employees.dept_id = Departments.id JOIN Projects ON Departments.id = Projects.dept_id; correctly joins Employees to Departments on dept_id, then Departments to Projects on dept_id.
Final Answer:
SELECT Employees.name, Departments.name, Projects.project_name FROM Employees JOIN Departments ON Employees.dept_id = Departments.id JOIN Projects ON Departments.id = Projects.dept_id; -> Option D
Quick Check:
Correct chaining of joins by department id = SELECT Employees.name, Departments.name, Projects.project_name FROM Employees JOIN Departments ON Employees.dept_id = Departments.id JOIN Projects ON Departments.id = Projects.dept_id; [OK]
Quick Trick:Join tables by shared keys in logical order [OK]
Common Mistakes:
MISTAKES
Joining on wrong columns
Using employee id instead of department id
Master "Advanced Joins" in SQL
9 interactive learning modes - each teaches the same concept differently
