【SQL-SQLZOO】Tutorials-6.JOIN(合并表格)

The JOIN operation/zh

1.列出 賽事編號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.顯示每一個德國入球的球員名，隊伍名，場館和日期。
SELECT player,teamid,stadium,mdate
  FROM goal JOIN game ON (goal.matchid=game.id) where teamid = 'GER'

4.列出球員名字叫Mario (player LIKE 'Mario%')有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player
select team1,team2,player from game join goal on (game.id=goal.matchid)
where player like 'Mario%'

5.列出每場球賽中首10分鐘gtime<=10有入球的球員 player, 隊伍teamid, 教練coach, 入球時間gtime
select player,teamid,coach,gtime from goal join eteam on (goal.teamid=eteam.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 game join goal on (game.id=goal.matchid)
where stadium = 'National Stadium, Warsaw'

More Difficult

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)
from eteam JOIN goal ON id=teamid
group by teamname

10.列出場館名和在該場館的入球數字。
select stadium, count(player)
from game JOIN goal ON id=matchid
group by stadium

11.每一場波蘭'POL'有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字。
select matchid,mdate,count(player)
from game join goal on id = matchid
where team1 = 'POL' or team2 = 'POL'
group by matchid,mdate

12.每一場德國'GER'有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。
select matchid,mdate,count(player)
from game join goal on id = matchid
where (team1 = 'GER' or team2 = 'GER') and teamid = 'GER'
group by matchid,mdate

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

case与之前条件排序的用法原理相似，会得到一个结果列表(每行都有一个结果，整体是0和1组成)，分组后对其进行聚合操作(SUM)

Music Tutorial/zh

album(asin, title, artist, price, release, label, rank)
大碟(asin, 碟名, 歌手, 售價, 推出, 標籤, 排名)

track(album, dsk, posn, song)
曲目(大碟, 碟號, 軌號, 歌名)

1.找出收錄歌曲song 'Alison'　碟名title和歌手artist
SELECT title,artist
  FROM album JOIN track
         ON (album.asin=track.album)
 WHERE song = 'Alison'

2.哪一歌手artist 錄了歌曲 song 'Exodus'?
select artist from album join track on (asin=album)
where song = 'Exodus'

3.為大碟album 'Blur'， 顯示每一首歌的歌名 song 。
select song from album join track on (asin=album)
where title = 'Blur'

4.為每一大碟album顯示歌名title和每大碟的歌曲track數量。
SELECT title,count(song) FROM album JOIN track ON (asin=album)
 GROUP BY title

5.為每一大碟album列出碟名title 歌名中有'Heart'一詞的歌曲數量。 (沒有這些歌的大碟不用列出).
select title,count(song) from album join track on (asin=album)
where song like '%Heart%'
group by title

6.主題歌曲是歌名 song 和大碟名字 title相同。找出主題歌曲。
select song from album join track on (asin=album)
where song = title

7.同名大碟是指大碟和歌手名字相同。 (例如大碟'Blur' 是由樂隊 'Blur'主唱)。 找出同名大碟。
select title from album
where artist = title

8.找出歌曲收錄在２隻以上的大碟中。列出收錄次數。
select song,count(distinct album) from track
group by song
having count(distinct album) > 2

9.好價大碟是指大碟中每一首歌曲的價格是少於5角。 找出好價大碟，列出大碟名字，售價和歌曲數量。
select title,price,count(song) from track join album a on (a.asin=album)
where 0.5 > (select price/count(song) from track join album b on (b.asin=album) where a.asin = b.asin group by price)
group by title,price

10.歌手Wagner的大碟 Ring cycle 有173首歌曲, 歌手Bing Crosby有一大碟 收錄了 101首歌曲。
按歌曲量(多至少)列出每一大碟的碟名和歌曲數量。
select title,count(song) from album join track on (asin=album)
group by title
order by count(song) desc

Quiz

1.You want to find the stadium where player ‘Dimitris Salpingidis’ scored. Select the JOIN condition to use:
Answer：

game JOIN goal ON (id=matchid)

2.You JOIN the tables goal and eteam in an SQL statement. Indicate the list of column names that may be used in the SELECT line:
Answer：

matchid, teamid, player, gtime, id, teamname, coach

3.Select the code which shows players, their team and the amount of goals they scored against Greece(GRE).
Answer：

SELECT player, teamid, COUNT(*)
  FROM game JOIN goal ON matchid = id
 WHERE (team1 = "GRE" OR team2 = "GRE")
   AND teamid != 'GRE'
 GROUP BY player, teamid

4.Select the result that would be obtained from this code:

SELECT DISTINCT teamid, mdate
  FROM goal JOIN game on (matchid=id)
 WHERE mdate = '9 June 2012'

Answer：

5.Select the code which would show the player and their team for those who have scored against Poland(POL) in National Stadium, Warsaw.
Answer：

 SELECT DISTINCT player, teamid 
  FROM game JOIN goal ON matchid = id 
 WHERE stadium = 'National Stadium, Warsaw' 
AND (team1 = 'POL' OR team2 = 'POL')
  AND teamid != 'POL'

6.Select the code which shows the player, their team and the time they scored, for players who have played in Stadion Miejski (Wroclaw) but not against Italy(ITA).
Answer：

SELECT DISTINCT player, teamid, gtime
  FROM game JOIN goal ON matchid = id
 WHERE stadium = 'Stadion Miejski (Wroclaw)'
   AND (( teamid = team2 AND team1 != 'ITA') OR ( teamid = team1 AND team2 != 'ITA'))

7.Select the result that would be obtained from this code:

SELECT teamname, COUNT(*)
  FROM eteam JOIN goal ON teamid = id
 GROUP BY teamname
HAVING COUNT(*) < 3

Answer：

Summary

• 图源自菜鸟教程
  
• The JOIN operation/zh中的T13之所以用left join的原因是即使goal表中没有记录，也就是这场比赛双方都没有得分，数据仍然需要列出来，所以game left join goal
• 注意区分PK和FK