id(編號) | mdate(日期) | stadium(場館) | team1(隊伍1) | team2(隊伍2) |
---|---|---|---|---|
1001 | 8 June 2012 | National Stadium, Warsaw | POL | GRE |
1002 | 8 June 2012 | Stadion Miejski (Wroclaw) | RUS | CZE |
1003 | 12 June 2012 | Stadion Miejski (Wroclaw) | GRE | CZE |
1004 | 12 June 2012 | National Stadium, Warsaw | POL | RUS |
... |
matchid(賽事編號) | teamid(隊伍編號) | player(入球球員) | gtime(入球時間) | |
---|---|---|---|---|
1001 | POL | Robert Lewandowski | 17 | |
1001 | GRE | Dimitris Salpingidis | 51 | |
1002 | RUS | Alan Dzagoev | 15 | |
1001 | RUS | Roman Pavlyuchenko | 82 | |
... |
id(編號) | teamname(隊名) | coach(教練) | ||
---|---|---|---|---|
POL | Poland | Franciszek Smuda | ||
RUS | Russia | Dick Advocaat | ||
CZE | Czech Republic | Michal Bilek | ||
GRE | Greece | Fernando Santos | ||
... |
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 s.player,s.teamid,t.stadium,t.mdate
FROM game t JOIN goal s ON (t.id=s.matchid) where s.teamid = 'GER'
4.列出球員名字叫Mario (player LIKE 'Mario%'
)有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player
select t.team1,t.team2,s.player from game t join goal s on (t.id=s.matchid)
where s.player LIKE 'Mario%'
5.列出每場球賽中首10分鐘gtime<=10
有入球的球員 player
, 隊伍teamid
, 教練coach
, 入球時間gtime
SELECT t.player, t.teamid, s.coach,t.gtime
FROM goal t join eteam s on t.teamid=s.id
WHERE t.gtime<=10
6.列出'Fernando Santos'作為隊伍1 team1 的教練的賽事日期,和隊伍名。
select t.mdate,s.teamname from game t join eteam s on t.team1= s.id
where s.coach='Fernando Santos'
7.列出場館 'National Stadium, Warsaw'的入球球員
select s.player from game t join goal s on t.id=s.matchid
where t.stadium='National Stadium, Warsaw'
8. 修改它,只列出全部賽事,射入德國龍門的球員名字。
select distinct(player) from game t join goal s on t.id=s.matchid
where (t.team1='GER'or t.team2='GER')
and s.teamid!='GER'
#select distinct(player) from game t join goal s on t.id=s.matchid
where (t.team1=s.teamid and t.team2='GER') or (t.team2=s.teamid and t.team1='GER')
9.列出隊伍名稱 teamname 和該隊入球總數
select t.teamname, count(s.teamid)
from eteam t join goal s on t.id=s.teamid
group by teamname
10.列出場館名和在該場館的入球數字。
select t.stadium, count(s.teamid)
from game t join goal s on t.id=s.matchid
group by stadium
11.每一場波蘭'POL'有參與的賽事中,列出賽事編號 matchid, 日期date 和入球數字。
SELECT s.matchid,t.mdate,count(s.teamid)
FROM game t JOIN goal s ON s.matchid = t.id
WHERE (t.team1 = 'POL' OR t.team2 = 'POL')
group by s.matchid,t.mdate
12.每一場德國'GER'有參與的賽事中,列出賽事編號 matchid, 日期date 和德國的入球數字。
SELECT s.matchid,t.mdate,count(s.teamid)
FROM game t JOIN goal s ON s.matchid = t.id
WHERE (t.team1 = 'GER' OR t.team2 = 'GER') and s.teamid='GER'
group by s.matchid,t.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.
SELECT distinct 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 t left JOIN goal s ON t.id=s.matchid
group by t.mdate,s.matchid,t.team1,team2