平成27年度 春期 午前Ⅱ 問7
データ操作
過去3年分の記録を保存している“試験結果”表から，2014年度の平均点数が600点以上となったクラスのクラス名と平均点数の一覧を取得するSQL文はどれか。ここで，実線の下線は主キーを表す。
表の定義（原典の実線の下線は …、破線の下線は〔破線の下線〕で表した）:
試験結果（学生番号，受験年月日，点数，クラス名）
- アSELECT クラス名, AVG(点数) FROM 試験結果 GROUP BY クラス名 HAVING AVG(点数) >= 600
- イSELECT クラス名, AVG(点数) FROM 試験結果 WHERE 受験年月日 BETWEEN '2014-04-01' AND '2015-03-31' GROUP BY クラス名 HAVING AVG(点数) >= 600
- ウSELECT クラス名, AVG(点数) FROM 試験結果 WHERE 受験年月日 BETWEEN '2014-04-01' AND '2015-03-31' GROUP BY クラス名 HAVING 点数 >= 600
- エSELECT クラス名, AVG(点数) FROM 試験結果 WHERE 点数 >= 600 GROUP BY クラス名 HAVING (MAX(受験年月日) BETWEEN '2014-04-01' AND '2015-03-31')
答えと解説を見る
✓ これが正解イSELECT クラス名, AVG(点数) FROM 試験結果 WHERE 受験年月日 BETWEEN '2014-04-01' AND '2015-03-31' GROUP BY クラス名 HAVING AVG(点数) >= 600
解説
年度はWHEREで絞り、クラス別の平均はHAVINGで条件にします。
SQLでは、WHEREで行を絞り込み、残った行をGROUP BYでまとめ、まとめた結果に対してHAVINGで条件をかける、という順に処理が進みます。この問いの表には3年分の記録があるので、まず受験年月日が2014年4月1日から2015年3月31日までの行だけをWHEREで残す必要があります。そのうえでクラス名ごとにまとめ、AVG(点数)が600以上のグループだけをHAVINGで残します。正解のSQLはこの順序どおりに書かれています。行に対する条件はWHERE、グループに対する条件はHAVINGと分けて考えると、条件を書く位置で迷わなくなります。
ほかの選択肢はなぜ違うのか
- アSELECT クラス名, AVG(点数)…：年度の条件がどこにもないため、3年分すべての点数を使ってクラスごとの平均を出してしまいます。2014年度だけの平均を求めるには、集計の前に受験年月日で行を絞る必要があります。
- ウSELECT クラス名, AVG(点数)…：HAVINGに書いている点数は、グループごとに一つに決まる値ではありません。グループに対する条件には集計した値かグループ化に使った列を使うので、個々の点数で比べるこの書き方では平均点数の条件になりません。
- エSELECT クラス名, AVG(点数)…：600点以上の行だけを残してから平均を出すので、600点未満の点数が平均から除かれてしまいます。さらに年度の判定も最新の受験日だけで行っており、2014年度の記録だけを集計する形になっていません。
出典：平成27年度 春期 データベーススペシャリスト試験 午前Ⅱ 問7
この解説に誤りを見つけたら教えてください。直して、直した記録を残します。誤りを報告する（メールが開きます）