sqlzoo-join

6. JOIN 合并两个表格

1. 列出 賽事編號**matchid 和球員名player **,該球員代表德國隊Germany入球的。要找出德國隊球員，要檢查: teamid = 'GER'

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

2. 只顯示賽事1012的 id, stadium, team1, team2

SELECT id,stadium,team1,team2 
FROM game 
where id = 1012;

3. 顯示每一個德國入球的球員名，隊伍名，場館和日期。

SELECT player, teamid, stadium, mdate 
FROM game JOIN goal ON (id=matchid) 
where teamid = 'GER';

4. 列出球員名字叫Mario (player LIKE 'Mario%')有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player

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

5. 列出每場球賽中首10分鐘**gtime<=10有入球的球員player, 隊伍teamid, 教練coach, 入球時間gtime**

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

6. 列出’Fernando Santos’作為隊伍1 team1 的教練的賽事日期，和隊伍名。

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

7. 列出場館 'National Stadium, Warsaw’的入球球員。

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

8. 列出全部賽事，射入德國龍門的球員名字。

select distinct(player)
  from game JOIN goal ON matchid = id 
    where(teamid=team1 and team2 = 'GER')or (teamid=team2 and team1 = 'GER');

9. 列出隊伍名稱teamname 和該隊入球總數

select teamname, count(*)
from eteam join goal on id=teamid 
group by teamname
order by teamname

10. 列出場館名和在該場館的入球數字。

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

11. 每一場波蘭’POL’有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字。

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

12. 每一場德國’GER’有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。

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

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.
    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, 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 ga LEFT OUTER JOIN goal go ON (ga.id= go.matchid)
GROUP BY mdate, team1, team2