Given a students table with columns class, student_id, and score, which query finds classes with more than 5 students scoring above 80?

hard📝 Application Q9 of 15

SQL - GROUP BY and HAVING

Given a students table with columns class, student_id, and score, which query finds classes with more than 5 students scoring above 80?

ASELECT class FROM students WHERE score > 80 GROUP BY class;

BSELECT class FROM students GROUP BY class HAVING COUNT(CASE WHEN score > 80 THEN 1 END) > 5;

CSELECT class FROM students GROUP BY class WHERE COUNT(score > 80) > 5;

DSELECT class FROM students HAVING COUNT(student_id) > 5 WHERE score > 80 GROUP BY class;

Step-by-Step Solution

Solution:

Step 1: Filter scores above 80 within groups
Use conditional counting inside HAVING with CASE WHEN to count students scoring > 80.
Step 2: Group by class and filter groups with count > 5
HAVING COUNT(CASE WHEN score > 80 THEN 1 END) > 5 filters classes with more than 5 such students.
Final Answer:
SELECT class FROM students GROUP BY class HAVING COUNT(CASE WHEN score > 80 THEN 1 END) > 5; -> Option B
Quick Check:
Use CASE in HAVING for conditional counts = B [OK]

Quick Trick: Use CASE inside HAVING to count conditionally [OK]

Common Mistakes:

MISTAKES

Master "GROUP BY and HAVING" in SQL

9 interactive learning modes - each teaches the same concept differently

Want More Practice?

15+ quiz questions · All difficulty levels · Free

More SQL Quizzes