SQLZOO（The JOIN operation）Writeup

转载自SQLZOO（The JOIN operation）Writeup

1.

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

2.

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

3.

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

4.

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

5.

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

6.

SELECT game.mdate,eteam.teamname 
    FROM game join eteam on eteam.id=game.team1 
        and eteam.coach='Fernando Santos'

或者是
SELECT game.mdate,eteam.teamname
FROM game inner join eteam on eteam.id=game.team1
where eteam.coach='Fernando Santos'


7.

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

8.

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

感觉有点丑

9.

SELECT teamname,count(teamid) FROM eteam inner join goal on goal.teamid=eteam.id group by teamname

不太懂这题目的意思，网上搜来的答案

10.

SELECT stadium,count(1) FROM game inner join goal on goal.matchid=game.id group by stadium

11.

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

12.

select matchid,mdate,count(teamid) FROM game inner join goal on game.id=goal.matchid and teamid='GER' group by matchid

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, matchid, team1,team2