Tips

SQL練習問題 – 問16

SQL練習問題 – 問16

SQLの練習問題です。

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

  • MariaDB 5.5.48
  • MySQL Workbench 6.3 ← 現在、お試し期間中です。

よくわからないという方は、XAMPをインストールすると簡単に筆者と同じ環境を構築できます。以下のページで紹介されています。
【PHP入門】環境構築

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

※データの誤りがありましたので、既にインポートされた方も改めて再インポートをお願いします。※ 2016/07/15


問題:グループCの各対戦毎にゴール数を表示してください。

ゴール数がゼロの場合も表示してください。問15の副問合せを用いたSQLにカラムを付けくわえる形で作成してください。

表示するカラム
・キックオフ日時
・自国名
・対戦相手国名
・自国FIFAランク
・対戦相手国FIFAランク
・自国のゴール数
・対戦国のゴール数(※追加!)

ソート順
・キックオフ日時
・自国FIFAランク

WS000012

正解:

SELECT p1.kickoff, c1.name AS my_country, c2.name AS enemy_country, 
    c1.ranking AS my_ranking, c2.ranking AS enemy_ranking,
	(SELECT COUNT(g1.id) FROM goals g1 WHERE p1.id = g1.pairing_id) AS my_goals,
    (
		SELECT COUNT(g2.id) 
		FROM goals g2 
		LEFT JOIN pairings p2 ON p2.id = g2.pairing_id
		WHERE p2.my_country_id = p1.enemy_country_id AND p2.enemy_country_id = p1.my_country_id
    ) AS enemy_goals
FROM pairings p1
LEFT JOIN countries c1 ON c1.id = p1.my_country_id
LEFT JOIN countries c2 ON c2.id = p1.enemy_country_id
WHERE c1.group_name = 'C' AND c2.group_name = 'C'
ORDER BY p1.kickoff, c1.ranking

初の難易度★★★となりました。
対戦国のゴール数を追加するためにはmy_countryとenemy_countryを入れ替える必要があります。
そのため副問合せを用いてSQLが複雑になることを極力抑えています。
8行目のWHERE条件がポイントです。my_country_idとenemy_country_idを入れ替えて条件付けしています。

   

連載目次リンク

SQL練習問題 一覧まとめ

関連する連載リンク

SQL基礎 連載目次

WEBアプリケーション関連 人気連載リンク

データベースの基礎が学べるSQL基礎講座
SQL基礎 連載

より実践的なWEBアプリ開発講座!Bootstrap活用を学ぶなら・・
魁!小野の塾 連載

Recent News

Recent Tips

Tag Search