【SQLZOO答え】６．The JOIN operation

SQL入門を勉強するため、友人にSQLの練習問題ないかと聞いたら、
【SQL ZOO】というサイトを教えてもらいました。
ただし、問題を解いてるうちに、わからない問題に関して、クエリの答えがおらず、結果しか教えてくれないので、答えをアウトプットしようと思い、noteを始めました。　

０．SQLZOO練習問題

The JOIN operation

１．matchid と player 名をドイツ(Germany)チームの全ゴールについて表示する。

最初の例としてラストネームが Bender である選手のゴール記録が示されている。「*」でテーブルの全フィールド（カラム）を宣言する。これは matchid, teamid, player, gtime を短く書く方法である。
ドイツプレイヤーを識別するには、次を確認: teamid = 'GER'

select
　matchid, player
from
　goal 
where
　teamid = 'ger';

２．試合 1012 の id, stadium, team1, team2 を表示する。

上記のクエリ―では、Lars Benderのゴールは ゲーム 1012 で確認できる。さて、この試合でどのチーム達がプレイしていたか知りたい。
goalテーブルで試合番号は matchid だが、gameテーブルでは id となっていることに注意する。 試合 2012 の情報を知るには game の該当する行を参照する。

select
 id,stadium,team1,team2
from
 game
where
 id = 1012;

３．ドイツの全ゴールについて player, teamid ,stadium, mdate を表示するように修正する。

JOIN を利用して、２つのステップを組み合わせて単独のクエリ―にすることができる。gameとgoalの詳細を得るには、

SELECT *
FROM game JOIN goal ON (id=matchid)

FROM節はgoalテーブルとgameテーブルのデータを統合する。 ON で gameの行と goalの行を、goalテーブルのidをgameの matchidと一致させて対応させる。 (もっと明確に言うとすれば、こう言う。
ON (game.id=goal.matchid)
得点が有るたびに、goalテーブルから playerを表示して、stadium 名は game ゲーブルから表示する。

select
 player, teamid ,stadium, mdate
from
 game 
join
 goal 
  on (id=matchid)
where
 teamid = 'ger';

４．Marioという名前の選手のゴールについて、team1, team2 , player を表示する。

player LIKE 'Mario%'

select
 team1, team2 , player
from
 game 
join
 goal 
  on (id=matchid)
where
 player LIKE '%Mario%';

５．最初の１０分間でゴールしたという条件で、 player, teamid, coach, gtime を表示。gtime<=10

eteam には各参加国のコーチが記載されている。 JOIN で goal を eteam に結合する。 goal JOIN eteam on teamid=id

select
 player, teamid, coach, gtime
from
 goal
join
 eteam
  on teamid=id 
where
 gtime<=10;

６．team1のコーチcoachが 'Fernando Santos' となる試合日mdateとチーム名teamnameを表示。

game と eteam を結合するには、game JOIN eteam ON (team1=eteam.id)、または、game JOIN eteam ON (team2=eteam.id)
id は game と eteam で同じ名前なので、単にidと書く代わりにeteam.idと書かねばならないことに注意する。

select
 mdate, teamname
from
 game as g
join
 eteam as e
  on g.team1 =e.id
where
 coach = 'Fernando Santos';

７．'National Stadium, Warsaw' スタジアムで開催された試合でゴールした選手を表示する。

select
 player
from
 game gm
join
 goal go
  on gm.id=go.matchid
where
 stadium = 'National Stadium, Warsaw';

８．ドイツと対戦して、ゴールした選手の名前を全て表示する。

ドイツプレイヤーではないプレイヤーで、GER が team1 または team2 のidに現れるプレイヤーを選択する。
teamid!='GER' でドイツプレイヤーをリストアップするのを防ぐ。
DISTINCT でプレイヤーの名前が繰り返し登場するのを止める。

select
　distinct(player)
from
 game ga
join
 goal go
   on go.matchid = ga.id 
where
　(team1='ger' or team2='ger') 
　 and teamid<>'ger';

9．チーム名teamnameとゴール数の合計を表示する。

ドイツプレイヤーではないプレイヤーで、GER が team1 または team2 のidに現れるプレイヤーを選択する。
teamid!='GER' でドイツプレイヤーをリストアップするのを防ぐ。
DISTINCT でプレイヤーの名前が繰り返し登場するのを止める。

select
 teamname, count(teamid)
from
 goal g
join
 eteam e
  on g.teamid=e.id
group by
 teamname;

10．スタジアムstadiumの名前と、そのスタジアムでのゴール数を各スタジアムごとに表示する。

select
 stadium, count(gtime)
from
 game ga
join
 goal go
  on ga.id=go.matchid
group by
 stadium;

11．'ポーランド（POL）が参戦している全試合の matchid と 日程 とその試合のゴール数 を表示する。

select
 matchid, mdate, count(teamid)
from
 game 
join
 goal 
  on
   matchid = id 
where
 (team1 = 'POL' or team2 = 'POL')
group by
 matchid;

12．ドイツ'GER'が得点した試合の matchid と 日程 と ドイツの得点 を表示する。

select
 matchid, mdate, count(teamid)
from
 game 
join
 goal 
  on
   matchid = id 
where
 teamid = 'ger'
group by
 matchid;

13．ドイツ'GER'が得点した試合の matchid と 日程 と ドイツの得点 を表示する。

各試合ごとに各チームの得点を表示する。
注意） 全得点状況が、記録されている。もし、チーム名が goal に記録されていれば、 その時点でチームが１得点していることにななり、チーム名が記載されていなければ、得点は０点である。
チーム名有り →　１チーム名なし →　０
この、得点状況を １と０ に CASE WHEN で変換した結果をSUM で集計すれば、そのチームの得点を集計できる。
結果は、日程順で並べ替えて出力する(日程が同じなら、idの順番)。

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 join
 goal
  on id=matchid
group by
 mdate,team1,team2
order by
 mdate, matchid

その他の答へ　　　　　　　　

０．SELECT basics
１．SELECT name
２．SELECT from World
３．SELECT from Nobel
４．SELECT within SELECT
５．SUM and COUNT
６．JOIN
７．More JOIN operations
８．Using Null
８+ Numeric Examples
９．Self join
１０．Tutorial Quizzes
１１．Tutorial Student Records
１２．Tutorial DDL

※問題を攻略でき次第、随時更新いたします。