【SQL】11.SQLZOO练习6--The JOIN ope

题目链接：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