SQLZOO (7)
The JOIN operation
1.显示德国队打进的所有进球的比赛号和球员名称。
SELECT matchid,player FROM goal
WHERE teamid = 'GER'
2.仅查询id是1012 ,显示id,球场,球队1,球队2
SELECT id,stadium,team1,team2
FROM game where id = 1012
3.显示球员,球队,球场和日期 为每一个德国队的进球。
SELECT player,teamid,stadium,mdate
FROM game JOIN goal ON game.id=goal.matchid where teamid = 'GER'
4.显示球队1,球队2和球员,每一个进球都是由一个叫马里奥的球员
select team1,team2,player
from game inner join goal
on game.id = goal.matchid
where player like 'Mario%'
5.显示前10分钟内所有进球的球员、队员、教练和进球time
select player,teamid,coach,gtime
from eteam inner join goal
on goal.teamid= eteam.id
where gtime<=10
6.列出比赛日期和“费尔南多·桑托斯”担任球队1教练的球队名称。
select mdate,teamname
from eteam inner join game
on eteam.id = game.team1
where coach= 'Fernando Santos'
7.列出在体育场为“华沙国家体育场”的比赛中每进一球的球员
select player
from goal inner join game
on goal.matchid = game.id
where stadium = 'National Stadium, Warsaw'
8.显示所有对德国队进球的球员的名字。
select distinct(player)
from game inner join goal
on game.id = goal.matchid
where (team1='GER' or team2 = 'GER') and teamid != 'GER'
9.显示球队名称和进球总数。
select teamname,count(*)
from eteam inner join goal
on eteam.id = goal.teamid
group by teamname
10.展示球场和每个球场的进球数。
select stadium,count(*)
from game inner join goal
on game.id = goal.matchid
group by stadium
11.对于每一场涉及“POL”的比赛,显示比赛号,日期和进球数。
select id,mdate,count(*)
from game inner join goal
on game.id = goal.matchid
where team1 = 'POL' or team2 = 'POL'
group by id,mdate
12.对于“GER”进球的每一场比赛,显示比赛号,比赛日期和“GER”的进球数
select id,mdate,count(*)
from game inner join goal
on game.id = goal.matchid
where teamid = 'GER' and(team1 = 'GER' or team2 = 'GER')
group by id,mdate
13.
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,team1,team2