sqlzoo习题答案--The join operation

8.show the name of all players who scored a goal against Germany.

HINT

Select goals scored only by non-German players in matches where GER was the id of either team1 or team2.

You can use teamid!='GER' to prevent listing German players.

You can use DISTINCT to stop players being listed twice.

SELECT distinct player
   from goal join game
   on(game.id=goal.matchid)
   where teamid!='GER' and (team1='GER' or team2='GER')

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

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