Given a table Scores with columns student_id, subject, and score, how to find the average score for each student only for subjects where score is above 50?
hard📝 Application Q9 of 15
SQL - Aggregate Functions
Given a table Scores with columns student_id, subject, and score, how to find the average score for each student only for subjects where score is above 50?
ASELECT student_id, AVG(score) FROM Scores GROUP BY student_id HAVING score > 50;
BSELECT student_id, AVG(score) FROM Scores HAVING score > 50 GROUP BY student_id;
CSELECT student_id, AVG(score) FROM Scores GROUP BY student_id WHERE score > 50;
DSELECT student_id, AVG(score) FROM Scores WHERE score > 50 GROUP BY student_id;
Step-by-Step Solution
Solution:
Step 1: Filter rows with score > 50 before grouping
WHERE clause filters rows before aggregation.
Step 2: Group by student_id and calculate AVG(score)
Use GROUP BY student_id to get average per student on filtered rows.
Final Answer:
SELECT student_id, AVG(score) FROM Scores WHERE score > 50 GROUP BY student_id; -> Option D
Quick Check:
WHERE filters before AVG(), HAVING filters after [OK]
Quick Trick:Use WHERE to filter rows before AVG() [OK]
Common Mistakes:
MISTAKES
Using HAVING instead of WHERE for filtering rows
Placing WHERE after GROUP BY
Master "Aggregate Functions" in SQL
9 interactive learning modes - each teaches the same concept differently
