学生ごとの平均点を求めるSQLはどう組み立てる？
基本情報技術者試験 令和元年度秋期 午前 問26 ／ データベース › データ操作
過去問にチャレンジ
"得点"表から，学生ごとに全科目の点数の平均を算出し，平均が80点以上の学生の学生番号とその平均点を求める。aに入れる適切な字句はどれか。ここで，実線の下線は主キーを表す。 得点（学生番号，科目，点数） 〔SQL文〕
SELECT 学生番号，AVG(点数) FROM 得点 GROUP BY a- ア
科目 HAVING AVG(点数) >= 80
- イ
科目 WHERE 点数 >= 80
- ウ
学生番号 HAVING AVG(点数) >= 80
- エ
学生番号 WHERE 点数 >= 80
会話形式の解説
講師まず、何を単位に平均を出すのかを確かめましょう。今回は学生ごとですね。
受験者それなら、GROUP BYには学生番号を入れます。科目でまとめると、科目ごとの平均になってしまいますね。
講師そのとおりです。AVGは、同じ学生番号でまとめた得点の平均を求めます。次に、80点以上という条件は何に対して判定しますか。
受験者個々の点数ではなく、計算した平均点に対して判定します。
講師はい。集計結果に条件を付けるHAVINGで、AVG(点数)が80以上のグループを残します。
受験者「科目 HAVING AVG(点数) >= 80」は、条件の掛け方は合っていても、まとめる単位が違うのですね。
講師そうです。「科目 WHERE 点数 >= 80」も科目でまとめてしまいます。加えて、この空欄に入れるとWHEREがGROUP BYの後に来るため、句の順序も誤ります。
受験者では、「学生番号 WHERE 点数 >= 80」はどうですか。学生ごとにはまとめられそうです。
講師それもWHEREの位置が誤りです。WHEREはグループ化前の各行を絞る句なので、点数を条件にしても全科目の平均が80以上かどうかの判定にはなりません。
受験者学生番号でまとめ、AVG(点数)の結果をHAVINGで判定する組合せが適切なのですね。
講師はい。GROUP BYでは平均を出す単位を決め、HAVINGでは出した平均に条件を付ける、と順に考えると整理できます。
まとめ
学生ごとに全科目の平均点を求めるには、学生番号でGROUP BYします。AVG(点数)で求めた平均が80点以上かどうかは、HAVINGで判定します。したがって、空欄には「学生番号 HAVING AVG(点数) >= 80」が入ります。WHEREはグループ化前の各行を絞る句であり、この空欄の位置にも置けません。
解答・解説
正解はウです
この問題のテーマ
学生ごとに全科目の平均点を求め、平均が80点以上の学生を選ぶSQLの組立てを問う問題です。
解き方の考え方
まず、平均を出す単位に合わせて、学生番号で行をまとめます。AVG(点数)で各学生の全科目の平均を求め、その集計結果に対してHAVING句で80点以上という条件を指定します。したがって、aに入るのは「学生番号 HAVING AVG(点数) >= 80」です。
選択肢ごとの解説
- ×ア 科目 HAVING AVG(点数) >= 80「科目 HAVING AVG(点数) >= 80」では、科目ごとに得点をまとめます。HAVING句で平均点を判定できても、求めたい学生ごとの平均にはなりません。
- ×イ 科目 WHERE 点数 >= 80「科目 WHERE 点数 >= 80」は、集計単位が科目になってしまいます。さらに、WHERE句をGROUP BY句の後に置くSQL文の順序も誤っています。
- ○ウ 学生番号 HAVING AVG(点数) >= 80「学生番号 HAVING AVG(点数) >= 80」なら、同じ学生番号の得点をまとめて平均を計算できます。その平均が80点以上のグループだけを残すので、設問の条件を満たします。
- ×エ 学生番号 WHERE 点数 >= 80「学生番号 WHERE 点数 >= 80」は、GROUP BY句の後にWHERE句を置いているため、SQL文の順序が誤っています。また、個々の点数を80点以上に絞ることは、全科目の平均が80点以上かどうかを判定することとは異なります。
POINT
- GROUP BYには、結果を求めたい集計単位の列を指定します。
- AVGは、各グループに含まれる行の値から平均を求めます。
- HAVINGは集計結果に対する条件を指定するときに使います。
- WHEREはグループ化より前に、個々の行を絞り込む句です。