ITPEC FE Morning April 2022 Question 26

SQL GROUP BY and ORDER BY — select the correct grouping and sorting clause.

The query calculates AVG(Score) for each class and subject, then sorts results in ascending order of class and subject.

Correct: GROUP BY Class, Subject ORDER BY Class, Subject

• •GROUP BY must include exactly the non-aggregated columns in SELECT: Class, Subject
• •ORDER BY Class, Subject sorts ascending by default (ASC)

Why not others:
- (a) ORDER BY Class, AVG(Score) — sorts by average score, not by Subject as required
- (c) GROUP BY Class, Subject, StudentNumber — adding StudentNumber breaks groups down to individual students, defeating the purpose of averaging
- (d) GROUP BY Class, AverageScore — cannot group by an aggregate alias; also missing Subject in GROUP BY

Key rule: Every non-aggregated column in SELECT must appear in GROUP BY. ORDER BY defaults to ASC.

AI-generated — may contain errors