基本情報技術者試験 令和元年度 秋季 午前 問26
SQL 文の問題。HAVING と WHERE の使い分け。
--------------------------
令和元年度 秋期 午前 問26
"得点" 表から、学生ごとに全科目の点数の平均を算出し、平均が 80 点以上の学生の学生番号とその平均点を求める。a に入れる適切な字句はどれか。ここで、実線の下線は主キーを表す。
得点（学生番号、科目、点数）
[ SQL 文 ]
SELECT 学生番号、AVG（点数）
FROM 得点
GROUP BY 【 a 】
ア 科目 HAVING AVG（点数）>= 80
イ 科目 WHERE 点数 >= 80
ウ 学生番号 HAVING AVG（点数）>= 80
エ 学生番号 WHERE 点数 >= 80
--------------------------
解説
問題となっている表は "得点" で、その形は以下のようになっている。
- 得点（学生番号、科目、点数）
この表から、学生ごとに全科目の点数の平均を算出する必要がある。
SQL 文の問題で上のように平均（AVG）がほしい場合には GROUP BY 句が使われる。
GROUP BY 句は指定した要素ごとに、他の要素の平均（AVG）、合計（SUM）、最大（MAX）、最小（MIN）、個数（COUNT）を計算することができる。
回答の選択肢は 2 パターンに分けられて、"GROUP BY 科目" か "GROUP BY 学生番号" のどちらかを用いている。
| 学生番号 | 科目 | 点数 |
| 1001 | 国語 | 60 |
| 1002 | 国語 | 80 |
| 1001 | 数学 | 70 |
| 1002 | 数学 | 90 |
得点表が上のようなケースであった場合、"GROUP BY 科目" とした場合は以下のような表が作られているイメージになる（実際に表が作られている訳ではない。はず。）。
| 科目 | AVG（点数） | MAX（点数） | MIN（点数） | SUM（点数） | COUNT(*) |
| 国語 | 70 | 80 | 60 | 140 | 2 |
| 数学 | 80 | 90 | 70 | 160 | 2 |
"科目 = 国語" の行で集計された結果と、"科目 = 数学" の行で集計された結果がわかる。
各科目ごとの平均点や最高得点などはわかるけれど、学生番号ごとの区別は集計をする際に消えてしまっていることがわかるので、問題が聞いている "学生番号ごと" の平均点はわからなくなってしまう。
もう一方の "GROUP BY 学生番号" とした場合は以下のような表が作られているイメージになる（略）。
| 学生番号 | AVG（点数） | MAX（点数） | MIN（点数） | SUM（点数） | COUNT(*) |
| 1001 | 65 | 70 | 60 | 130 | 2 |
| 1002 | 85 | 90 | 80 | 170 | 2 |
このときの AVG（点数）は国語と数学の平均点（＝全科目の点数の平均）になるので、問題が聞いている "学生ごとの全科目の点数の平均" が計算できていることになる。
ということで、"GROUP BY 学生番号" にする必要があるため、正解の選択肢はウかエになる。
- ウ 学生番号 HAVING AVG（点数）>= 80
- エ 学生番号 WHERE 点数 >= 80
異なる点は HAVING 句か WHERE 句を使っているかであるが、"平均が 80 点以上" という条件なので、 "AVG（点数）>= 80" を指定していなければおかしい。
なので、問題の正解は選択肢ウの "学生番号 HAVING AVG（点数）>= 80" になる。
選択肢エは "点数 >= 80" なので、平均ではなく全ての科目の点数に対して 80 点以上かを確認してしまっている。
HAVING 句と WHERE 句はどちらも条件を満たす行を抽出するために使われるが、明確な使い分けが必要になる。
誤りを恐れずざっくり書くと以下のような使い分けをすれば良い。
- HAVING 句は GROUP BY で集計した後の表にでてくる要素 "AVG（点数）" や "MAX（点数）" などを条件に指定する際に用いる。
- WHERE 句は GROUP BY で集計する前の、もともとの表にある要素 "科目" や "点数" などを条件に指定する際に用いる。
厳密には HAVING 句でもともとの表にある要素を指定することができたりもするけれど。気にしない。
前後の問題はこちら。
