【SQL】sqlzoo练习题The JOIN operation

原地址：https://sqlzoo.net/wiki/The_JOIN_operation/zh

上一篇：sqlzoo练习题The nobel table can be used to practice more SUM and COUNT functions.

1.第一個例子列出球員姓氏為’Bender’的入球數據。 * 表示列出表格的全部欄位，簡化了寫matchid, teamid, player, gtime語句。
修改此SQL以列出 賽事編號matchid 和球員名 player ,該球員代表德國隊Germany入球的。要找出德國隊球員，要檢查: teamid = ‘GER’

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

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

3.語句FROM 表示合拼兩個表格game 和 goal的數據。語句 ON 表示如何找出 game中每一列應該配對goal中的哪一列 – goal的 id 必須配對game的 matchid 。 簡單來說，就是
ON (game.id=goal.matchid)
以下SQL列出每個入球的球員(來自goal表格)和場館名(來自game表格)
修改它來顯示每一個德國入球的球員名，隊伍id，場館和日期。

select player,teamid,stadium,mdate from game join goal on id=matchid
 where teamid = 'GER'

4.使用上題相同的 JOIN語句，
列出球員名字叫Mario (player LIKE ‘Mario%’)有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player

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

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

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

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

select mdate,teamname from game join eteam on 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 (team1 = 'GER' or team2 = 'GER') and teamid != 'GER'

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

select teamname,count(player) as 进球次数 from eteam join goal on teamid = id
 group by teamname

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

select stadium,count(player) as 进球次数 from game join goal on id = matchid
 group by stadium

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

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

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

select matchid,mdate,count(player) from game join goal on matchid = id
 where teamid = 'GER' and (team1 = 'GER' or team2 = 'GER')
  group by matchid,mdate
--解题思路：关键点要查德国的入球数字，不管德国在team1还是team2,只要德国进球都算上。注意在or的操作中一定要括起来，因为这是一个条件。最后按照日期和赛事编号分组即可用count()求出进球数。

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 left join goal on matchid = id
  group by mdate,matchid,team1,team2
--解题思路：如上图所示，要查日期、team1进球的次数，team2的进球次数。可以用case when判断是否进球，如果进球了返回1，最后用sum()计算总和。最后分组要按照日期先分组，比赛场次再分组，最后两个分别是两个队伍的分组。

下一篇：sqlzoo练习题Music Tutorial