応用情報技術者試験 平成31年春 午前問28 解説付き過去問
問題
過去3年分の記録を保存している"試験結果"表から、2018年度の平均点数が600点以上となったクラスのクラス名と平均点数の一覧を取得するSQL文はどれか。
ここで、実線の下線は主キーを表す。
試験結果(学生番号, 受験年月日, 点数, クラス名)
試験結果(学生番号, 受験年月日, 点数, クラス名)
正解
解説
- 問題の目的
この問題では、"試験結果"テーブルを対象として、2018年度の試験結果における各クラスの平均点数を求め、平均点数が600点以上のクラス名とその平均点数を抽出するSQL文を選択する必要がある。 - 年度の絞り込み条件
受験年月日は通常「YYYYMMDD」形式の文字列または数値として格納されていると考えられるため、2018年度のデータを抽出するには、20180101以上かつ20181231以下のデータをWHERE句で絞り込む必要がある。
この条件を満たすには、WHERE 受験年月日 BETWEEN 20180101 AND 20181231 を使用するのが適切である。 - グループ化と平均点数の計算
クラス単位で平均点数を算出するためには、GROUP BY句でクラス名をグループ化対象とし、AVG関数を用いて各クラスの平均点数を算出する。
そのうえで、HAVING句を用いて平均点数が600以上のグループを抽出する必要がある。HAVING句はGROUP BYのあとに使用される集計条件句であり、HAVING AVG(点数) >= 600 のように記述する。 - 選択肢の検証
- 受験年月日の範囲指定がなく、全期間のデータを対象として集計しているため、2018年度の平均に限定されず条件を満たさない。
- WHERE句にBETWEEN 20180101 AND 20181231で2018年度のデータに限定し、GROUP BY クラス名でグループ化し、HAVING AVG(点数) >= 600により平均点数600以上のクラスのみを抽出している。
このSQL文は、すべての条件を正しく満たしており、正解である。 - HAVING句に点数 >= 600という非集計列を用いているため、構文的に誤りである。HAVING句には集計関数やGROUP BYに含まれる列しか使えない。
- WHERE句で点数 >= 600を用いており、600点未満のレコードを除外した後で平均点数を算出するため、意図する「すべての点数を含めた平均値」とは異なる集計結果となる。
- 受験年月日の範囲指定がなく、全期間のデータを対象として集計しているため、2018年度の平均に限定されず条件を満たさない。
- 結論
2018年度の試験結果において、クラスごとの平均点数を求め、平均点数が600点以上であるクラス名と平均点数を正しく抽出するSQL文は、2番目の選択肢である。
WHERE句、GROUP BY句、HAVING句、AVG関数の使い方が正しく記述されており、要件を正確に満たしている。