Write SQL Query to get the list of grades with total score more than average score.

Consider Student and Grade tables
Student: ID, name, grade_ID, score
Grade: ID, grade_num

Answer: We can use WITH clause to get the total score in each grade. We can also use WITH clause to get the average score among all grades. Then we can use the two sub-queries to get the list of GRADES with Score total more than average score.

Query will be as follows:

WITH
grade_score AS (
SELECT grade_num, SUM(s.score) grade_total
FROM student s, grade g
WHERE s.grade_ID = g.ID
GROUP BY grade_num),
avg_score AS (
SELECT SUM(grade_total)/COUNT(*) avg
FROM grade_score)
SELECT *
FROM grade_score
WHERE grade_total > (SELECT avg FROM avg_score)
ORDER BY grade_num;

Read the full book at www.amazon.com
Posted in SQL, SQL Interview Questions

Leave a Reply

Your email address will not be published. Required fields are marked *

*