The JOIN operation（SQLZoo第六部分）

-- 5.1 修改此SQL以列出 賽事編號matchid 和球員名 player ,該球員代表德國隊Germany入球的。要找出德國隊球員，要檢查: teamid = 'GER'
SELECT matchid,player FROM goal WHERE teamid = 'GER';

-- 5.2 由以上查詢，你可見Lars Bender's 於賽事 1012入球。.現在我們想知道此賽事的對賽隊伍是哪一隊。
--     留意在 goal 表格中的欄位 matchid ，是對應表格game的欄位id。我們可以在表格 game中找出賽事1012的資料。
--     只顯示賽事1012的 id, stadium, team1, team2
SELECT id,stadium,team1,team2 FROM game WHERE id = 1012;

-- 5.3 語句FROM 表示合拼兩個表格game 和 goal的數據。語句 ON 表示如何找出 game中每一列應該配對goal中的哪一列 -- goal的 id 必須配對game的 matchid 。 簡單來說，就是
--     ON (game.id=goal.matchid)
--     以下SQL列出每個入球的球員(來自goal表格)和場館名(來自game表格)
--     修改它來顯示每一個德國入球的球員名，隊伍名，場館和日期。
SELECT player, teamid, stadium, mdate FROM game a 
                                      join goal b on a.id=b.matchid
                                      WHERE teamid = 'GER';

-- 5.4 使用上題相同的 JOIN語句，
--     列出球員名字叫Mario (player LIKE 'Mario%')有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player
SELECT team1,team2,player FROM game a 
                          JOIN goal b ON a.id = b.matchid
                          WHERE b.player LIKE 'Mario%';

-- 5.5 列出每場球賽中首10分鐘gtime<=10有入球的球員 player, 隊伍teamid, 教練coach, 入球時間gtime
SELECT player,teamid,coach,gtime FROM goal a
                                 JOIN eteam b ON a.teamid = b.id
                                 WHERE gtime < 10;

-- 5.6 列出'Fernando Santos'作為隊伍1 team1 的教練的賽事日期，和隊伍名。
SELECT mdate,teamname FROM game a
                      JOIN eteam b ON a.team1 = b.id
                      WHERE coach = 'Fernando Santos';

-- 5..7 列出場館 'National Stadium, Warsaw'的入球球員。
SELECT player FROM goal a
              JOIN game b ON a.matchid = b.id
              WHERE stadium = 'National Stadium, Warsaw';  

-- 5.8 以下例子找出德國-希臘Germany-Greece 的八強賽事的入球
--     修改它，只列出全部賽事，射入德國龍門的球員名字
--     HINT
--     找非德國球員的入球，德國可以在賽事中作team1 隊伍１（主）或team2隊伍２（客）。
--     你可以用teamid!='GER' 來防止列出德國球員。 你可以用DISTINCT來防止球員出現兩次以上。
SELECT DISTINCT player FROM game a
                       JOIN goal b ON a.id = b.matchid
                       WHERE teamid != 'GER' AND (team1 = 'GER' OR team2 = 'GER');

-- 5.9 列出隊伍名稱 teamname 和該隊入球總數 
--     COUNT and GROUP BY
--     你應該在SELECT語句中使用COUNT(*)和使用GROUP BY teamname
SELECT teamname,COUNT(player) FROM goal a
                             JOIN eteam b ON a.teamid = b.id
                             GROUP BY teamname;

-- 5.10 列出場館名和在該場館的入球數字
SELECT stadium,COUNT(player) FROM game AS a 
                             JOIN goal AS b ON a.id = b.matchid
                             GROUP BY stadium; 

-- 5.11 每一場波蘭'POL'有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字。
SELECT matchid,mdate,COUNT(player) FROM game AS a
                                   JOIN goal AS b ON a.id = b.matchid
                                   WHERE team1 = 'POL' OR team2 = 'POL'
                                   GROUP BY matchid,mdate;

-- 5.12 每一場德國'GER'有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。
SELECT matchid,mdate,COUNT(player) FROM game a
                                   JOIN goal b ON a.id = b.matchid
                                   WHERE (team1 = 'GER' OR team2 = 'GER') AND b.teamid = 'GER'
                                   GROUP BY matchid,mdate;

-- 5.13 List every match with the goals scored by each team as shown.
--      This will use "CASE WHEN" which has not been explained in any previous exercises.
--      mdate	        team1	score1	team2	score2
--      1 July 2012	    ESP	    4	    ITA	    0
--      10 June 2012	ESP	    1	    ITA	    1
--      10 June 2012	IRL	    1	    CRO	    3
--      ...
--      Notice in the query given every goal is listed. 
--      If it was a team1 goal then a 1 appears in score1, otherwise there is a 0.
--      You could SUM this column to get a count of the goals scored by team1.
--      Sort your result by mdate, matchid, team1 and team2.
SELECT mdate,matchid,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 a
                    LEFT JOIN goal b ON a.id = b.matchid
                    GROUP BY mdate,matchid,team1,team2;