You have tables Teacher, Class, and junction table TeacherClass. You want to find all teachers who teach more than 3 classes. Which SQL query correctly achieves this?
hard📝 Application Q8 of 15
SQL - Table Relationships
You have tables Teacher, Class, and junction table TeacherClass. You want to find all teachers who teach more than 3 classes. Which SQL query correctly achieves this?
ASELECT t.Name FROM Teacher t JOIN TeacherClass tc ON t.ID = tc.TeacherID GROUP BY t.Name HAVING COUNT(tc.ClassID) >= 3;
BSELECT t.Name FROM Teacher t JOIN TeacherClass tc ON t.ID = tc.TeacherID GROUP BY t.ID HAVING COUNT(tc.ClassID) > 3;
CSELECT t.Name FROM Teacher t JOIN TeacherClass tc ON t.ID = tc.TeacherID WHERE COUNT(tc.ClassID) > 3;
DSELECT t.Name FROM Teacher t JOIN TeacherClass tc ON t.ID = tc.TeacherID GROUP BY t.ID HAVING COUNT(tc.ClassID) < 3;
Step-by-Step Solution
Solution:
Step 1: Use GROUP BY to group teachers
Grouping by teacher ID allows counting classes per teacher.
Step 2: Use HAVING to filter counts greater than 3
HAVING COUNT(tc.ClassID) > 3 filters teachers teaching more than 3 classes.
Final Answer:
SELECT t.Name FROM Teacher t JOIN TeacherClass tc ON t.ID = tc.TeacherID GROUP BY t.ID HAVING COUNT(tc.ClassID) > 3; -> Option B
Quick Check:
GROUP BY + HAVING with COUNT > 3 = SELECT t.Name FROM Teacher t JOIN TeacherClass tc ON t.ID = tc.TeacherID GROUP BY t.ID HAVING COUNT(tc.ClassID) > 3; [OK]
Quick Trick:Use GROUP BY and HAVING to filter aggregated counts [OK]
Common Mistakes:
MISTAKES
Using WHERE with aggregate functions
Using wrong comparison operator in HAVING
Grouping by name instead of ID causing errors
Master "Table Relationships" in SQL
9 interactive learning modes - each teaches the same concept differently
