题目链接：https://sqlzoo.net/wiki/The_JOIN_operation
题目
表
1
select matchid,player from goal
where teamid='GER';
2
SELECT id,stadium,team1,team2
FROM game
where id='1012';
3
select a.player,a.teamid,b.stadium,b.mdate from goal a
join game b on a.matchid=b.id
where a.teamid='GER';
4
select a.team1,a.team2,b.player
from game a join goal b on
b.player like 'Mario%' and a.id= b.matchid;
5
SELECT a.player, a.teamid,b.coach,a.gtime
FROM goal a join eteam b on a.teamid=b.id
WHERE gtime<=10;
6
select a.mdate,b.teamname from game a
join eteam b on a.team1=b.id
where b.coach='Fernando Santos';
7
select a.player from goal a
join game b on (a.matchid=b.id)
where b.stadium= 'National Stadium, Warsaw'
8
SELECT DISTINCT player
FROM game JOIN goal ON matchid = id
WHERE (teamid!='GER' AND (team1='GER' OR team2='GER'))
9
SELECT a.teamname, COUNT(teamid)
FROM eteam a JOIN goal b ON a.id=b.teamid
GROUP BY teamname
10
select a.stadium,count(teamid) from game a
join goal b on a.id=b.matchid
group by stadium;
11
SELECT matchid,mdate,COUNT(matchid) goals
FROM game a JOIN goal b ON a.id=b.matchid
WHERE (a.team1= 'POL' OR a.team2='POL')
GROUP BY matchid,mdate
12
SELECT matchid,mdate,COUNT(teamid)
FROM game a JOIN goal b ON b.matchid=a.id
WHERE (b.teamid='GER' )
GROUP BY matchid,mdate
13
SELECT a.mdate, a.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 a LEFT JOIN goal b ON b.matchid = a.id GROUP BY mdate, matchid, team1, team2
