sqlzoo练习--JION

这里涉及三个表格，表名分别是：game;goal;eteam.
其中表中含有的列分别为：game（id,mdate,stadium,team1,team2);
goal(matchid,teamid,player,gtime);
eteam(id,teamname,coach);
1.Modify it to show the matchid and player name for all goals scored by Germany. To identify German players, check for: teamid = ‘GER’

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

2.Show id, stadium, team1, team2 for just game 1012

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

3.Modify it to show the player, teamid, stadium and mdate for every German goal.

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

4.Show the team1, team2 and player for every goal scored by a player called Mario player LIKE ‘Mario%’

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

5.Show player, teamid, coach, gtime for all goals scored in the first 10 minutes gtime<=10

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

6.List the the dates of the matches and the name of the team in which ‘Fernando Santos’ was the team1 coach.

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

7.List the player for every goal scored in a game where the stadium was ‘National Stadium, Warsaw’

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

8.Instead show the name of all players who scored a goal against Germany.Show teamname and the total number of goals scored.

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

9.Show teamname and the total number of goals scored.

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

10.Show the stadium and the number of goals scored in each stadium.

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

11.For every match involving ‘POL’, show the matchid, date and the number of goals scored.

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

12.For every match where ‘GER’ scored, show matchid, match date and the number of goals scored by ‘GER’

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

13.Sort your result by mdate, matchid, team1 and team2.(该题花了一点点时间，涉及外联结和CASE表达式这两个知识点)

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