本投稿の目的
- sql学習サイトsqlzooを用いたsqlの学習と理解
- 学習のアウトプット
- 学習メモとして、かつ公式の解答ページがなかったので作成した。
0 SELECT basics
1 SELECT name
2 SELECT from World
3 SELECT from Nobel
4 SELECT within SELECT
5 SUM and COUNT
6 JOIN←今ここ
7 More JOIN operations
8 Using Null
8+ Numeric Examples
9 Self join
6 JOIN
問題1
ドイツが得点したすべてのゴールのマッチIDとプレーヤー名を表示します。
ドイツのプレイヤーを識別するには、teamid = 'GER'を確認します
SELECT matchid, player -- 試合のid, 選手
FROM goal -- 試合
WHERE teamid = "GER" -- ドイツチーム
問題2
前のクエリから、ラーズベンダー氏がゲーム (matchid = 1012)でゴールを決めたことがわかります。
次に、その試合でどのチームがプレーしていたかを表示する。
SELECT id,stadium,team1,team2 -- 試合id、会場、チーム1、チーム2
FROM game -- 試合
WHERE id = 1012 -- 試合番号1012
問題3
JOINを使用して、2つのステップを単一のクエリに結合できます。
FROM句は、GOALテーブルのデータとGAMEテーブルのデータを併合する意。
ONは、ゲーム内のどの行がゴール内のどの行に行くかを判断する方法を示す。
-ゴールのmatchidはゲームのidと一致する必要がある。
※より明確/具体的にしたい場合は ON（game.id = goal.matchid）
-- 解答例1（丁寧）
SELECT player, teamid, stadium, mdate -- 選手、チームid、会場、試合日
FROM game -- 試合
JOIN goal -- ゴールを結合
ON game.id = goal.matchid -- 試合id＝ゴールした試合id
WHERE goal.teamid = 'GER' -- ゴールしたチームはドイツ
-- 解答例2
SELECT player, teamid, stadium, mdate
FROM game
JOIN goal
ON id = matchid
WHERE goal.teamid = 'GER'
問題4
team1、team2、およびプレーヤーを表示。
マリオと呼ばれる選手を表示
■表示する値：
- チーム1
- チーム2
- 選手の名前（マリオと呼ばれる選手) ※ヒント(player LIKE 'Mario%')
■条件
- マリオ選手がゴールした試合
→where句で範囲を限定
SELECT team1, team2, player -- チーム1, チーム2, 選手の名前
FROM game -- 試合
JOIN goal -- ゴール
ON game.id = goal.matchid -- 結合（試合idと得点がある試合id）
WHERE player LIKE 'Mario%' -- マリオと呼ばれる選手
■↑ポイント
- 試合と選手の関係
→得点したことを示すフラグはどうする？
→matcheにgoalカラムを持たせず別テーブルとしてGOALテーブルを作成
問題5
テーブルeteamには、コーチを含むすべてのナショナルチームの詳細が記載されています。
teamid = idでフレーズgoal JOIN eteamを使用して、eteamにゴールを参加できます。
【問題】
最初の10分間に得点された
すべてのゴールのプレーヤー、チームID、コーチ、ゴールした時間を表示
■条件
- 最初の10分間
→10分以内
→（※gtime <= 10）
SELECT player, teamid, coach, gtime
FROM goal -- ゴール
JOIN eteam -- チーム詳細
ON goal.teamid = eteam.id -- 結合（ゴールしたチームid ＝ チーム詳細）
WHERE gtime<=10 -- 10分以内
問題6
試合の日付と「フェルナンドサントス」がチーム1のコーチの時のチーム名を表示。
SELECT mdate, teamname -- 試合日、チーム名
FROM game -- 試合
JOIN eteam ON (team1 = eteam.id) -- 結合（チーム1 ＝ チーム詳細）
WHERE coach = 'Fernando Santos' -- コーチが'Fernando Santos'
問題7
スタジアムが「ナショナルスタジアム、ワルシャワ」であったゲームで記録されたすべてのゴールのプレーヤーをリストにする
SELECT player -- 選手
FROM game -- 試合
JOIN goal ON ( goal.matchid = game.id) -- ゴールした試合と試合のidを結合
WHERE stadium = 'National Stadium, Warsaw' -- ワルシャワで
問題8
例には、ドイツとギリシャの準々決勝で得点されたすべての目標が示されている。
【問題】
ドイツに対してゴールを決めたすべてのプレーヤーの名前を表示する。
■表示・条件
- 選手の名前
- ゴールがある試合
- ドイツとの試合
-- example
-- SELECT player, gtime
-- FROM game
-- JOIN goal ON matchid = id
-- WHERE (team1='GER' AND team2='GRE')
SELECT DISTINCT player -- 選手（）
FROM game -- 試合から
JOIN goal ON (game.id = goal.matchid) -- 結合(試合id = ゴールした試合id)
WHERE (team1 = 'GER' OR team2 = 'GER') -- チーム1か2がドイツの場合
AND goal.teamid <> 'GER' -- ドイツ以外の選手
-- （〜と等しくない） 「!=」でもできるが標準sqlではないため注意！！
-- これも可能。しかし問題の意図に沿っていない。
-- SELECT player -- 選手
-- FROM game -- 試合から
-- JOIN goal ON (game.id = goal.matchid) -- 結合(試合id = ゴールした試合id)
-- WHERE (team1 = 'GER' OR team2 = 'GER') -- チーム1か2がドイツの場合
-- AND goal.teamid <> 'GER' -- ドイツ以外の選手
-- GROUP BY player 各選手
■↑解説:
- 組み合わせの問題。
→重複を弾く必要がある
→DISTINCTを使用する。
- ドイツに対してのゴール
→常識的に考えてドイツの選手は弾く（オウンゴールのため）
→WHERE句の後にANDで条件を追記
→AND goal.teamid <> 'GER'
問題9
チーム名と得点の合計数を表示。
※ヒント：SELECT行とGROUP BYチーム名にCOUNT（*）が必要。
SELECT teamname, COUNT(teamid)
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) -- 結合（試合id、ゴールした試合id）
GROUP BY game.stadium -- 各スタジアム
問題11
POLに関連するすべての試合について
試合ID、日付、および得点数を表示。
SELECT matchid, mdate, COUNT(*) -- 試合ID、日付、および得点数
FROM game -- 試合
JOIN goal ON (game.id = goal.matchid) -- 結合（試合idとゴールした試合id）
WHERE team1 = 'POL' OR team2 = 'POL' -- チーム1.id または チーム2.idがポーランド
GROUP BY goal.matchid, mdate -- 各ゴールした試合id
-- ミス、自分の書いた答え
-- SELECT matchid, mdate, COUNT(*)
-- FROM game
-- JOIN eteam ON ( game.team1 OR game.team2 =eteam.id)
-- WHERE id = 'POL'
-- これも正解。しかし記述量が多いためNG
-- SELECT matchid, mdate, COUNT(*) -- 試合ID、日付、および得点数
-- FROM game -- 試合
-- JOIN goal ON (game.id = goal.matchid) -- 結合（試合idとゴールした試合id）
-- WHERE team1 = 'POL' OR team2 = 'POL' -- チーム1.id または チーム2.idがポーランド
-- GROUP BY game.mdate -- 各ゴールした試合id
-- ORDER BY goal.matchid -- ゴールした試合idで並び替え
■↑解説:
- 組み合わせの問題。
【雑談】
DISTINCTとGROUP BYについて
DISTINCTとGROUP BYはどちらも重複を排除するという点で同じ動作をする。
他にも
- NULLをひとまとめにする点
- 複数列の結果を表す場合の結果も全く同じ
- 実行速度もほぼ同じ
Q.どう使い分けるべきか？
A.そもそもSELECT分の意味が要件と合致しているかを考えるべき、
疑問自体が本末転倒。
・英語によく似た構文を持ち、
・人にも意味がわかりやすいメリットを持っているのだから
その長所を殺してしまうような使い方はもったいない。
結論
-
「選択結果から重複を排除したい」
→ DISTINCT -
「集約した結果を求めたい」
→ GROUP BY
問題12
GERが得点したすべての試合について、
matchid、試合日、および「GER」が得点したゴール数を表示。
-- 自分が書いたsql(ミス) 200211 16:07:50
-- SELECT matchid, mdate, COUNT(*) -- 試合ID、日付、および得点数
-- FROM goal -- ゴール
-- JOIN game ON (game.id = goal.matchid)
-- WHERE team1 = 'GER' OR team2 = 'GER'
-- GROUP BY game.mdate -- 各ゴールした試合id
SELECT matchid, mdate, COUNT(*)-- 試合ID、日付、および得点数
FROM game -- 試合
JOIN goal ON (id = matchid)
WHERE teamid = 'GER' -- ドイツ
GROUP BY matchid -- 各試合
問題13
すべての試合を、各チームが決めたゴールを図のようにリストアップします。ここでは、これまでの演習では説明されていなかった「CASE WHEN」を使用します。
| mdate | team1 | score1 | team2 | score2 |
|---|---|---|---|---|
| 1 July 2012 | ESP | 4 | ITA | 0 |
| 10 July 2012 | ESP | 1 | ITA | 1 |
| 10 July 2012 | IRL | 1 | CR | 3 |
このクエリでは、すべてのゴールがリストアップされています。チーム1のゴールであればscore1に1が、そうでなければ0が表示されます。この列をSUMすることで、チーム1が決めたゴールの数を得ることができます。結果をmdate、matchid、team1、team2でソートしてみましょう。
解答1
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 (id = matchid)
GROUP BY id, game.mdate, goal.matchid, game.team1, game.team2
ORDER BY mdate, matchid, team1, team2
解答2
SELECT mdate,
team1,
SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END)score1,
team2,
SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END)score2
FROM game
LEFT OUTER JOIN goal ON matchid = id
GROUP BY mdate, matchid, team1, team2;