Given tables: Directors(DirectorID, Name) Movies(MovieID, Title, DirectorID) Which SQL query lists directors who directed more than 5 movies?
ASELECT Name FROM Directors WHERE (SELECT COUNT(*) FROM Movies WHERE Movies.DirectorID = Directors.DirectorID) > 5;
BSELECT Name FROM Directors JOIN Movies ON Directors.DirectorID = Movies.DirectorID GROUP BY Name HAVING COUNT(MovieID) > 5;
CSELECT Name FROM Directors LEFT JOIN Movies ON Directors.DirectorID = Movies.DirectorID WHERE COUNT(MovieID) > 5;
DSELECT Name FROM Directors GROUP BY DirectorID HAVING SUM(MovieID) > 5;
Step-by-Step Solution
Solution:
Step 1: Understand the requirement
We want directors with more than 5 movies.
Step 2: Use JOIN and GROUP BY
SELECT Name FROM Directors JOIN Movies ON Directors.DirectorID = Movies.DirectorID GROUP BY Name HAVING COUNT(MovieID) > 5; joins Directors and Movies, groups by Name, and filters with HAVING COUNT(MovieID) > 5.
Step 3: Analyze other options
SELECT Name FROM Directors WHERE (SELECT COUNT(*) FROM Movies WHERE Movies.DirectorID = Directors.DirectorID) > 5; uses a subquery but lacks aliasing and may be less efficient; SELECT Name FROM Directors LEFT JOIN Movies ON Directors.DirectorID = Movies.DirectorID WHERE COUNT(MovieID) > 5; misuses WHERE with aggregate; SELECT Name FROM Directors GROUP BY DirectorID HAVING SUM(MovieID) > 5; sums MovieID which is invalid.
Final Answer:
SELECT Name FROM Directors JOIN Movies ON Directors.DirectorID = Movies.DirectorID GROUP BY Name HAVING COUNT(MovieID) > 5; -> Option B
Quick Check:
Use GROUP BY and HAVING with COUNT for filtering [OK]
Quick Trick:Use GROUP BY and HAVING COUNT > threshold [OK]
Common Mistakes:
MISTAKES
Using WHERE instead of HAVING for aggregate filters
Summing IDs instead of counting
Incorrect JOIN types or missing GROUP BY
Master "Table Relationships" in SQL
9 interactive learning modes - each teaches the same concept differently
