基本情報技術者試験 平成31年度 春期 午前 問27

SQL 文、GROUP BY や ORDER BY の使い方についての問題。

--------------------------

基本情報技術者試験

平成31年度 春期 午前 問27

"中間テスト" 表からクラスごと、教科ごとの平均点を求め、クラス名、教科名の昇順に表示する SQL 文中の a に入れる字句はどれか。

中間テスト（クラス名、教科名、学生番号、名前、点数）

[ SQL文 ]

　SELECT クラス名、教科名、AVG（点数） AS 平均点

　FROM 中間テスト

　【  a  】

ア

GROUP BY クラス名、教科名 ORDER BY クラス名、AVG（点数）

イ

GROUP BY クラス名、教科名 ORDER BY クラス名、教科名

ウ

GROUP BY クラス名、教科名、学生番号 ORDER BY クラス名、教科名、平均点

エ

GROUP BY クラス名、平均点 ORDER BY クラス名、教科名

 --------------------------

解説

SQL 文に慣れていないととっつきにくいので、色々と例を出しながら解説してみる。

まずは中間テスト表をそのままに全て表示する、最も単純な SQL 文。

• SELECT * FROM 中間テスト

FROM の後に表を指定して、そこから表示したい列（属性）を SELECT で指定する。

" * " は全ての列を指定する意味になる。

次に GROUP BY 文を使ってみた SQL 文。

• SELECT クラス名、AVG（点数） FROM 中間テスト GROUP BY クラス名

FROM より後ろをまず見る。

GROUP BY にクラス名が指定されているので、中間テスト表にてクラス名が同じ値を持つ行が一つに纏められることになる。

ここで纏める時に、点数の様な数字データの平均値が欲しい場合は AVG(  ) を利用する。Average の略。

上の SQL 文だと表示される表は以下の様になる。

クラス名	AVG（点数）
2年1組	55
2年2組	54
2年3組	60
2年4組	57
2年5組	62
2年6組	70

AVG（点数）だと見た目がよくないなので、AS を使って列（属性）の名前を変えることができる。さっきの SQL 文に AS だけ追加。

• SELECT クラス名、AVG（点数） AS 平均点 FROM 中間テスト GROUP BY クラス名

この時に表示される表は以下の様になる。

クラス名	平均点
2年1組	55
2年2組	54
2年3組	60
2年4組	57
2年5組	62
2年6組	70

GROUP文は複数の列を指定することも可能。

• SELECT クラス名、教科名、AVG（点数） AS 平均点 FROM 中間テスト GROUP BY クラス名、教科名

GROUP BYで複数列を指定した場合、それらの組合せが全部一致する行で纏められる。

この時に表示される表は以下の様になる。

クラス名	教科名	平均点
2年1組	国語	55
2年1組	数学	54
2年6組	国語	60
2年6組	数学	57

次は表示を制御する。

上の例で書いている表は行の順番をそれっぽく並べているが、実際は不定（確実にこうなる！、とは言えない状態）となる。

この問題としてはクラス名、教科名の昇順に並べるとあるので、ORDER 文を使う。

• SELECT クラス名、教科名、AVG（点数） AS 平均点 FROM 中間テスト GROUP BY クラス名、教科名 ORDER BY クラス名、教科名

上の例の様に ORDER BY で指定した列が昇順になる様に表示される。

選択肢と見比べると、正解は選択肢イとなる。

他の選択肢では何が問題かも考えてみる。

選択肢アは ORDER BY の指定がクラス名、AVG（点数）であり、教科名ではなく平均点で並べてしまっている。指定方法も AS で定義した名称の平均点の方が良い。

選択肢ウは GROUP BY の指定に学生番号が入っている。クラス名と教科名と学生番号の組み合わせが一致する行は恐らく存在しないので、平均点どころではない。

選択肢エは GROUP BY に平均点が指定されている。GROUP BY 文でまとめた結果として平均点が出てくるはずなので、おかしい。

前後の問題はこちら。