ITPEC FE Morning April 2022 Question 26

Topic: DatabasesAlso asked in April 2024

Which of the following is a clause that is inserted into blank A of the SQL statement that calculates the average scores for each class and each subject from the “MidtermTest” table, and displays them in ascending order of class and subject?

MidtermTest (Class, Subject, StudentNumber, Name, Score)

[SQL statement]
SELECT Class, Subject, AVG(Score) AS AverageScore
FROM MidtermTest
A

Explanation

GROUP BY Class, Subject ORDER BY Class, Subject — group by the two non-aggregated columns and sort by the same two columns.

The result needs one row per class and subject, so the groups are formed on exactly the columns listed in SELECT besides the aggregate: Class and Subject. AVG(Score) is then computed within each group. The display order asked for is class first, then subject, both ascending, which is the default for ORDER BY.

Answer (b)

Why not others:

  • •(a) — groups correctly but sorts by AVG(Score) inside each class, not by subject as required
  • •(c) — adding StudentNumber to GROUP BY makes each student a group of their own, so the "average" is just each student's score, and the rows are then ordered by that score as well
  • •(d) — groups by AverageScore, an aggregate computed from the groups themselves, and leaves Subject out of GROUP BY although it is selected, so the query is invalid

Key rule: Every column in SELECT that is not inside an aggregate function must appear in GROUP BY, and nothing finer-grained than the wanted result row should be added to it.

Databases · AI-written, may contain errors

Practice this exam

This question comes from an official ITPEC past paper. ITPEC Practice is an independent study tool and is not affiliated with ITPEC. See the official FE past-paper collection or Report an issue.