データベーススペシャリスト 2015年 午前2 問07
問題文
過去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')
🔒 解説は解答すると表示されます
年度別平均点の集計【午前2解説】
正解の理由
イのSQL文は、まず
その後、
WHERE句で受験年月日を2014年度（2014年4月1日から2015年3月31日まで）に絞り込み、GROUP BY クラス名でクラスごとに集計しています。その後、
HAVING AVG(点数) >= 600で平均点が600点以上のクラスだけを抽出しているため、問題文の条件を正確に満たしています。解法ステップ
- 2014年度の期間を
受験年月日で絞り込むため、WHERE句にBETWEEN '2014-04-01' AND '2015-03-31'を指定する。 - クラスごとに平均点を計算するため、
GROUP BY クラス名を使う。 - 平均点が600点以上のクラスを抽出するため、
HAVING AVG(点数) >= 600を指定する。 - SELECT句で
クラス名とAVG(点数)を出力する。
選択肢別の誤答解説
- ア：
WHERE句がないので、過去3年分すべての記録でクラスごとの平均点を求めてしまい、2014年度だけの平均点にならない。 - イ：正解。日付範囲指定、グループ化、平均点条件が正しく記述されている。
- ウ：
HAVING 点数 >= 600は集計関数を使っておらず、グループごとの平均点の条件にならないため誤り。 - エ：
WHERE 点数 >= 600で600点以上の行だけに絞ってから平均を求めており、平均点600点以上のクラス抽出にはならない。HAVING句の条件も年度の絞り込みになっていない。
よくある誤解
- 年度で絞り込む
WHERE句を忘れると、過去3年分すべての記録で平均を求めてしまいます。 - HAVING句で集計関数を使わずに単純な列名で条件を指定し、正しく絞り込めないケースがあります。
補足コラム
SQLで集計結果に条件を付ける場合は
また、日付の範囲指定は
WHERE句で行単位の絞り込み、HAVING句でグループ化後の集計結果に対する条件指定を行います。また、日付の範囲指定は
BETWEENを使う際に対象の列名を必ず明記し、文字列の引用符や括弧の閉じ忘れに注意しましょう。FAQ
Q: なぜ
A:
HAVING句でAVG(点数) >= 600と書くのですか？A:
HAVING句はグループ化後の集計結果に条件を付けるため、平均点のような集計関数を使った条件はHAVINGで指定します。Q:
A:
WHERE句で日付範囲を指定する理由は？A:
WHERE句は集計前の行を絞り込むため、対象年度のデータだけを抽出してから集計する必要があるからです。関連キーワード: SQL, 集計関数、HAVING句、GROUP BY, 日付範囲指定、平均点、データ抽出
\ せっかくなら /
データベーススペシャリストを
クイズ形式で学習しませんか？
クイズ画面へ遷移する→
すぐに利用可能！