SQLZOO The_JOIN_operation 答案 UEFA EURO数据库

题目网址：https://sqlzoo.net/wiki/The_JOIN_operation/zh

SELECT matchid,player
FROM goal
WHERE teamid = 'GER'

SELECT id,stadium,team1,team2
FROM game
WHERE id='1012'

SELECT player,teamid,stadium,mdate
FROM game JOIN goal ON (game.id=goal.matchid)
WHERE teamid='GER'

SELECT team1,team2,player
FROM game JOIN goal ON (id=matchid)
WHERE player LIKE 'Mario%'

SELECT player, teamid, coach, gtime
FROM goal join eteam on teamid=id
WHERE gtime<=10

select mdate,teamname
from game join eteam on team1=eteam.id
where coach='Fernando Santos'

select player
from game join goal on id=matchid
where stadium='National Stadium, Warsaw'

SELECT distinct player
FROM game JOIN goal ON matchid = id 
WHERE teamid!='GER' and (team1='GER' or team2='GER')

SELECT teamname, count(teamid)
FROM eteam JOIN goal ON id=teamid
group BY teamname

select stadium,count(matchid)
from game join goal on id=matchid
group by stadium

SELECT matchid,mdate, count(gtime)
FROM game JOIN goal ON matchid = id 
WHERE (team1 = 'POL' OR team2 = 'POL')
group by matchid

select matchid,mdate,count(teamid)
from game join goal on id=matchid
where teamid='GER' and (team1='GER' OR team2='GER')
group by matchid

SELECT mdate,team1,
  sum(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) as score1,
  team2,
  sum(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) as score2
FROM game left outer JOIN goal ON matchid = id
group by mdate, matchid, team1, team2