ITPEC FE Morning October 2023 Question 28

Topic: Databases

From a “Score” table, the average score for all subjects is to be calculated for each student, and the student number and average score for students with an average score of 80 or higher are to be determined. Which of the following is the appropriate term or phrase to be entered in blank A? Here, a solid underline represents a primary key.

Score (StudentNumber, Subject, Score)

[SQL statement]
SELECT StudentNumber, AVG(Score)
FROM Score
GROUP BY A

Explanation

StudentNumber HAVING AVG(Score) >= 80 — group per student, then filter the groups on their average.

  • •The average is wanted per student, so the rows are grouped by StudentNumber
  • •The condition is on the average, an aggregate, so it goes in HAVING, which is applied after grouping
  • •WHERE is applied to single rows before grouping and cannot refer to AVG(Score)

The full statement ends GROUP BY StudentNumber HAVING AVG(Score) >= 80.

Answer (a)

Why not others:

  • •(b) — WHERE cannot follow GROUP BY, and a row filter Score >= 80 would average only the high scores instead of testing the average
  • •(c) — grouping by Subject gives the average per subject, and StudentNumber in the select list is not grouped
  • •(d) — wrong grouping key and a WHERE clause in the wrong place

Key rule: WHERE filters rows before grouping; HAVING filters groups after aggregation, so a condition on AVG, SUM or COUNT belongs in HAVING.

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.