You have tables Employee, Project, and junction table EmployeeProject with EmployeeID and ProjectID. How do you find employees who work on all projects listed in Project?
hard📝 Application Q15 of 15
SQL - Table Relationships
You have tables Employee, Project, and junction table EmployeeProject with EmployeeID and ProjectID. How do you find employees who work on all projects listed in Project?
AJoin EmployeeProject and Project, then filter with WHERE ProjectID IS NOT NULL
BUse GROUP BY EmployeeID and HAVING count of projects equal to total projects count
CSelect employees with a simple JOIN to EmployeeProject without grouping
DUse DISTINCT on EmployeeID in EmployeeProject without counting projects
Step-by-Step Solution
Solution:
Step 1: Count total projects
Find total number of projects from Project table.
Step 2: Group EmployeeProject by EmployeeID
Count how many projects each employee works on.
Step 3: Use HAVING to compare counts
Only select employees whose project count equals total projects count.
Final Answer:
Use GROUP BY EmployeeID and HAVING count of projects equal to total projects count -> Option B
Quick Check:
Group and count projects per employee = all projects [OK]
Quick Trick:Group by employee, HAVING count = total projects [OK]
Common Mistakes:
MISTAKES
Not grouping and counting projects per employee
Using WHERE instead of HAVING for aggregate filtering
Ignoring total projects count in comparison
Master "Table Relationships" in SQL
9 interactive learning modes - each teaches the same concept differently
