SQL Zoo:The JOIN operation

PART-1:The JOIN operation

1. 第一個例子列出球員姓氏為’Bender’的入球數據。修改此SQL以列出 賽事編號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 like ‘GER’

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

select a.team1,a.team2,b.player
from game a join goal b on(a.id=b.matchid)
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 a join eteam b on (a.team1=b.id)
where b.coach=‘Fernando Santos’

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

select b.player
from game a join goal b on(a.id=b.matchid)
where a.stadium= ‘National Stadium, Warsaw’