ITPEC FE Subject A April 2024 Question 21

Topic: DatabasesAlso asked in April 2022

Which of the following is a clause that is inserted into blank A of the SQL statement below that calculates the average scores for each class and 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 (c)

Why not others:

  • •(a) — 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
  • •(b) — groups correctly but sorts by AVG(Score) inside each class, not by subject as required
  • •(d) — adding StudentNumber to GROUP BY makes each student a group of their own, so the "average" is just each student's score (and the clause is misspelled ROUP BY)

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.