ITPEC FE Subject A April 2024 Question 21

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

The query needs the average score per class and subject, displayed in ascending order of class and subject.

• •SELECT Class, Subject, AVG(Score) → non-aggregated columns are Class and Subject
• •GROUP BY must include exactly those columns: GROUP BY Class, Subject
• •ORDER BY must match the requested sort: ORDER BY Class, Subject

Why not others:
- (a) GROUP BY Class, AverageScore — cannot group by an aggregate alias; also loses Subject grouping
- (b) ORDER BY Class, AVG(Score) — sorts by average score instead of subject name
- (d) GROUP BY Class, Subject, StudentNumber — adding StudentNumber breaks groups down to individual students, so AVG(Score) returns each student's own score

Key rule: Every non-aggregated column in SELECT must appear in GROUP BY. The ORDER BY clause must match the requested sort criteria, not the aggregation.

AI-generated — may contain errors