応用情報技術者試験 応用情報技術者試験 平成31年度春期 午前 問28: 過去 3 年分の記録を保存している “試験結果” 表から，2018 年度の平均点数が 600 点以上となったクラスのクラス名と平均点数の一覧を取得する SQL

問題本文

過去 3 年分の記録を保存している “試験結果” 表から，2018 年度の平均点数が 600 点以上となったクラスのクラス名と平均点数の一覧を取得する SQL 文はどれか。ここで，実線の下線は主キーを表す。 試験結果（学生番号，受験年月日，点数，クラス名）

選択肢

• ア.SELECT クラス名, AVG(点数) FROM 試験結果 GROUP BY クラス名 HAVING AVG(点数) = 600
• イ.SELECT クラス名, AVG(点数) FROM 試験結果 WHERE 受験年月日 BETWEEN '2018-04-01' AND '2019-03-31' GROUP BY クラス名 HAVING AVG(点数) = 600
• ウ.SELECT クラス名, AVG(点数) FROM 試験結果 WHERE 受験年月日 BETWEEN '2018-04-01' AND '2019-03-31' GROUP BY クラス名 HAVING 点数 = 600
• エ.SELECT クラス名, AVG(点数) FROM 試験結果 WHERE 点数 = 600 GROUP BY クラス名 HAVING (MAX(受験年月日) BETWEEN '2018-04-01' AND '2019-03-31')

正解

イ. SELECT クラス名, AVG(点数) FROM 試験結果 WHERE 受験年月日 BETWEEN '2018-04-01' AND '2019-03-31' GROUP BY クラス名 HAVING AVG(点数) = 600

解説

SQL の WHERE（行の絞り込み）と GROUP BY・HAVING（グループ化後の集計と絞り込み）の正しい使い分けを問う問題である。まず WHERE で 2018 年度（2018-04-01〜2019-03-31）の行だけに絞り、クラス名で GROUP BY して平均点を集計し、グループ単位の集計結果に対する条件は HAVING AVG(点数) = 600 で絞り込むのが正しい。これらをすべて満たすイが正解である。

選択肢ごとの解説

• ア.WHERE による 2018 年度の絞り込みがなく、過去 3 年分すべての点数で平均を計算してしまうため、2018 年度だけの条件を満たさず誤り。
• イ.WHERE で 2018 年度に絞り、クラス名で GROUP BY し、HAVING AVG(点数) = 600 でグループ平均を絞り込んでおり、要件をすべて満たすので正しい。
• ウ.HAVING に集計関数でない「点数 = 600」を書いており、GROUP BY 後は個々の点数を参照できないため構文的に不正で誤り。
• エ.WHERE で点数 600 以上の行だけに絞ってから平均を取るため平均点が実際より高くなり、また期間の判定を MAX(受験年月日) で行うなど条件の意味が異なるため誤り。