SQLZOO JOIN答案

链接：https://sqlzoo.net/wiki/The_JOIN_operation

1.

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

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

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

4.

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

SELECT goal.player, goal.teamid, eteam.coach, goal.gtime
FROM goal 
JOIN eteam ON goal.teamid=eteam.id
WHERE gtime<=10

SELECT game.mdate, eteam.teamname
FROM game
JOIN eteam ON game.team1=eteam.id
WHERE eteam.coach='Fernando Santos'

SELECT goal.player
FROM goal
JOIN game ON goal.matchid=game.id
WHERE game.stadium='National Stadium, Warsaw'

SELECT DISTINCT goal.player
FROM goal JOIN game ON goal.matchid=game.id 
WHERE (game.team1='GER' OR game.team2='GER' )AND goal.teamid!='GER'

SELECT eteam.teamname, COUNT(goal.player)
FROM eteam JOIN goal ON eteam.id=goal.teamid
GROUP BY(eteam.teamname)
ORDER BY eteam.teamname

SELECT game.stadium, COUNT(goal.player)
FROM game 
JOIN goal ON game.id = goal.matchid
GROUP BY(game.stadium)

SELECT goal.matchid, game.mdate, COUNT(goal.matchid) AS count
FROM game JOIN goal ON matchid = id 
WHERE (team1 = 'POL' OR team2 = 'POL')
GROUP BY goal.matchid, game.mdate

SELECT game.id, game.mdate, COUNT(game.id)
FROM game JOIN goal ON game.id=goal.matchid
WHERE goal.teamid='GER'
GROUP BY game.id, game.mdate

SELECT game.mdate, 
  game.team1, SUM(CASE WHEN goal.teamid=game.team1 THEN 1 ELSE 0 END) AS score1, 
  game.team2, SUM(CASE WHEN goal.teamid=game.team2 THEN 1 ELSE 0 END) AS score2
FROM game LEFT JOIN goal ON game.id=goal.matchid
GROUP BY game.mdate, game.id, game.team1,game.team2
ORDER BY game.mdate, game.id, game.team1, game.team2