SQL練習問題 – 問3


SQLの練習問題です。

筆者の環境は以下となっていますが、別のデータベースやツールでもほとんど同じように動作すると思います。

  • MariaDB 5.5.48
  • PhpMyAdmin 4.4.15.1

よくわからないという方は、XAMPをインストールすると簡単に筆者と同じ環境を構築できます。以下のサイトで紹介されています。
WINDOWS XAMPP にて LARAVEL をインストール (XAMPP V1.8.3インストール)

問題として使用するデータは以下からダウンロード可能です。phpMyAdminからデータベース(worldcup2014)を作成しインポートしておいてください。
worldcup2014.zip

各テーブルの説明は以下の通りです。
WS000000

WS000001

WS000006

WS000003


問題:各国の平均身長を高い方から順に表示してください。ただし、FROM句はcountriesテーブルとしてください。

WS000008

正解:

SELECT c.name AS 国名, AVG(p.height) AS 平均身長
FROM countries c
JOIN players p ON p.country_id = c.id
GROUP BY c.id, c.name
ORDER BY AVG(p.height) DESC

テーブル結合、グループ化、順序付けの問題です。
問題にFROM句の指定があるので、JOIN句のテーブルはplayersテーブルになります。テーブル定義書を見ると、playersテーブルのcountry_id列はcountriesテーブルの外部キーということなので、これを使ってテーブル結合を行います。
また、国ごとの平均身長を問われていますので、グループ化をcountriesテーブルのID列(プライマリーキー)で行っています。
ここで1点注意。問題文の出力例に国名が表示されていますので、SELECT句にはcountriesテーブルのname列を表示したいのですが、GROUP化をcountriesテーブルのid列のみで行っているとSELECT句でcountriesテーブルのname列を使用することはできません。
以下のSQLは誤りです。

SELECT c.name AS 国名, AVG(p.height) AS 平均身長
FROM countries c
JOIN players p ON p.country_id = c.id
GROUP BY c.id
ORDER BY AVG(p.height) DESC

実は、MySQLやMariaDBでは上記の誤答もエラーなく実行できてしまいます。しかし、SQL構文的には誤りとなりますので注意してください。

それでは、「GROUP BY句でc.idではなく、c.nameだけを使用すればいいのでは?」と考える方がいるかもしれませんが、テーブル定義的にはname列の値が重複していないという保証がありません(同じ国名があるケース)ので、一意が保証されているID列を使用する方がベターと考えます。

あとは、順序付けを忘れずにおこないましょう。

日本の平均身長って下から3番目だったんですね。。。

← SQL練習問題 一覧まとめに戻る

  • このエントリーをはてなブックマークに追加

PAGE TOP