SQLZOO (7)

The JOIN operation

1.显示德国队打进的所有进球的比赛号和球员名称。

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

2.仅查询id是1012 ，显示id，球场，球队1，球队2

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

3.显示球员，球队，球场和日期 为每一个德国队的进球。

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

4.显示球队1，球队2和球员，每一个进球都是由一个叫马里奥的球员

select team1,team2,player 
     from game inner join goal 
      on game.id = goal.matchid 
      where player like 'Mario%'

5.显示前10分钟内所有进球的球员、队员、教练和进球time

select player,teamid,coach,gtime 
   from eteam inner join goal 
    on goal.teamid= eteam.id 
    where gtime<=10

6.列出比赛日期和“费尔南多·桑托斯”担任球队1教练的球队名称。

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

7.列出在体育场为“华沙国家体育场”的比赛中每进一球的球员

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

8.显示所有对德国队进球的球员的名字。

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

9.显示球队名称和进球总数。

select teamname,count(*) 
      from eteam inner join goal 
      on eteam.id = goal.teamid 
       group by teamname

10.展示球场和每个球场的进球数。

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

11.对于每一场涉及“POL”的比赛，显示比赛号，日期和进球数。

select id,mdate,count(*) 
    from game inner join goal 
     on game.id = goal.matchid  
     where team1 = 'POL' or team2 = 'POL' 
     group by id,mdate

12.对于“GER”进球的每一场比赛，显示比赛号，比赛日期和“GER”的进球数

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

13.

SELECT mdate,
  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 left JOIN goal ON matchid = id group by mdate,team1,team2