SQLZOOのJOINを解いてみた
さいきんSQLを勉強していまして、Gunosyの「非エンジニアが最速でSQLをマスターする方法」に書いてある『10年戦えるデータ分析入門』→SQLZOO (https://sqlzoo.net/wiki/SQL_Tutorial) という順番で学習を進めています。
SQLZOOは出力が合っているのか違っているのかは教えてくれるのですが、どんなSQL文を書けば正解できるのか教えてくれません。すでに日本語でSQLZOOの解答を公開してくださっている方もおられますが、私も自分の勉強のアウトプットとして、解答例を公開したいと思います。
SQLZOOは問題文が英語なので、あまり自信はありませんが問題文の和訳も用意しました。意訳なので正確さに欠けるかもしれませんが……
- SQLZOOのSUM and COUNTの解答例はこちら → https://rnsr0371.boy.jp/2020/12/18/aqlzoosum_and_count/
参考にさせていただいたサイト
- 【SQLZOO答え】６．The JOIN operation https://note.com/shincz88/n/nc1812c6e9ce3
- 【SQL ZOO 解答集】(6 JOIN) https://qiita.com/m_masashi/items/d93f54915312a93f98f6
1.
最初の例はBenderというラストネームのプレイヤーによるゴールを表示することです。*はテーブルのすべてのカラムを列挙します。手短に言えばmatchid, teamid, player, gtimeです。ドイツの全ゴールのmatchidとplayerの名前を表示するように、書き換えてください。ドイツのプレイヤーを特定するためにはteamid = ‘GER’としてください。
SELECT
matchid
, player
FROM goal
WHERE teamid = 'GER’;2.
前のクエリでは、geme 1012のLars Benderによるゴールを見ることができました。今度はその試合でプレーしたチームを知りたいです。gameテーブルのmatchidカラムがgameテーブルのidカラムに対応しているのを見てください。gameテーブルのgame 1012の行を見つけることで、情報を見ることができます。game 1012のid, stadium, team1, team2を表示してください。
SELECT
id
, stadium
, team1
, team2
FROM game
WHERE id = 1012;3.
2ステップでJOINを使った単一のクエリを組み合わせることができます。FROM節はgoalテーブルをgameテーブルのデータを結合するように書いてあります。ONはgameのどの行がgoalの行に一致するかを書いてあります。具体的には、goalのmacthidがgameのidと一致するようにということです。（もしより明確に/具体的にしたいならON(game.id = goal.matchidと書くこともできます）下で表示されているコードは、各ゴールについてのgoalテーブルのplayerとgameテーブルのスタジアムの名前を表示します。これを、ドイツの各ゴールについて、player, teamid, stadium, mdateを表示するように修正してください。
SELECT
player
, teamid
, stadium
, mdate
FROM game
JOIN goal ON game.id = goal.matchid
WHERE teamid = 'GER’;4.
以前の問題と同じくJOINを使います。Marioと呼ばれているプレイヤー（player LIKE ‘Mario%’）によるすべてのゴールについて、team1, team2, playerを表示してください。
SELECT
team1
, team2
, player
FROM game
JOIN goal ON game.id = goal.matchid
WHERE goal.player LIKE 'Mario%';5.
eteamテーブルにはコーチを含めた各国代表チームの詳細が記録されています。goalとeteamをgoal JOIN eteam ON teamid = idでジョインすることができます。gtime <= 10で最初の10分でのゴールを決めたplayer, teamid, coach, gtimeを表示してください。
SELECT
player
, teamid
, coach
, gtime
FROM goal
JOIN eteam ON goal.teamid = eteam.id
WHERE goal.gtime <= 10;6.
gameとeteamをジョインするために、game JOIN eteam ON (team1=eteam.id) または game JOIN eteam ON (team2=eteam.id)が使えます。gameとeteamの両方にidカラムがあるので、単にidとするのではなく、eteam.idとどのidなのかはっきりさせる必要がある点に注意してください。team1のコーチがFernando Santos’のチームの試合日とチームの名前を列挙してください。
SELECT
mdate
, teamname
FROM game
JOIN eteam ON game.team1 = eteam.id
WHERE eteam.coach = 'Fernando Santos’;7.
National Stadium, Warsawというスタジアムでのゲームでゴールを決めたプレイヤーを全員列挙してください。
SELECT
player
FROM game
JOIN goal ON game.id = goal.matchid
WHERE game.stadium = 'National Stadium, Warsaw’;More difficult questions
8.
サンプルクエリはドイツ対ギリシャの準々決勝の全ゴールを表示します。ドイツと戦い、ゴールを決めた全選手の名前を表示してください。
SELECT
DISTINCT(player)
FROM goal
JOIN game ON goal.matchid = game.id
WHERE (game.team1 ='GER' OR game.team2 = 'GER')
AND goal.teamid <> 'GER’;9.
チーム名とゴールの総数を表示してください。
SELECT
teamname
, COUNT(*)
FROM eteam
JOIN goal ON eteam.id = goal.teamid
GROUP BY eteam.teamname;10.
スタジアム名と各スタジアムで決められたゴール数を表示してください。
SELECT
stadium
, COUNT(*)
FROM game
JOIN goal ON game.id = goal.matchid
GROUP BY game.stadium;11.
ポーランドが参加したすべてのゲームの、matchid, 日付, 決めたゴールの数を表示してください。
SELECT
matchid
, mdate
, COUNT(*)
FROM game
JOIN goal ON game.id = goal.matchid
WHERE game.team1 = 'POL' OR game.team2 = 'POL'
GROUP BY matchid, mdate;12.
ドイツが得点した全試合について、matchid, 日付, ドイツが記録したゴールの数を表示してください。
SELECT
matchid
, mdate
, COUNT(*)
FROM game
JOIN goal ON game.id = goal.matchid
WHERE goal.teamid = 'GER'
GROUP BY goal.matchid, game.mdate;13.
すべての試合について、表示された各チームによって記録されたゴールを列挙してください。これをするためには、これまでの練習問題で説明されていないCASE WHENを使う必要があるでしょう。例示したクエリはすべてのゴールを列挙することに注目してください。もしチーム1がゴールを決めたらscore 1に1が表示され、その他の場合は0になります。チーム1によって記録されたゴールの総数を得るために、このカラムにSUMを使うと良いでしょう。結果はmdate, matchid, team1, team2でソートしてください。
SELECT
mdate
, team1
, SUM(CASE WHEN teamid = team1 THEN 1 ELSE 0 END) AS score1
, team2
, SUM(CASE WHEN teamid = team2 THEN 1 ELSE 0 END) AS score2
FROM game
LEFT JOIN goal ON game.id = goal.matchid
GROUP BY mdate, team1, team2
ORDER BY mdate, matchid, team1, team2;(補足)この問題はJOINではなく、LEFT JOINを使う必要があるようです。
参考
Follow me!
