SQLZOO练习-- The JOIN operation（含题目翻译）

知识点

join on连接、join连接+group by+count、join连接+case

 

数据表

表的连接关系

• game.id = goal.matchid
• goal.teamid=eteam.id

说明

1. game：比赛记录表：比赛id、比赛日期、比赛地点、队伍1、队伍2
2. goal：得分记录表：比赛id、队伍id、选手名称、比赛用时？
3. eteam：队伍记录表：队伍id、队伍名称、教练名称

 

题目内容

1.Modify it to show the matchid and player name for all goals scored by Germany. To identify German players, check for：teamid=‘GER’.（查询德国队伍的比赛id、选手名称，德国=‘GER’）

select matchid,player from goal 
where teamid = 'GER'

 

 

2.Show id, stadium, team1, team2 for just game 1012.（查询比赛id为1012的比赛id、比赛地点、队伍1、队伍2）

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.（查询德国队伍的选手名称、队伍id、比赛地点、比赛日期）

select player,teamid,stadium,mdate 
from game join goal on game.id = goal.matchid
and teamid = 'GER'

 

 

4.Show the team1, team2 and player for every goal scored by a player called Mario.（查询以‘Mario’开头的选手参加的队伍1、队伍2、选手名称）

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

 

 

5.Show player，teamid，coach，gtime for all goals scored in the first 10 minutes  gtime<=10.（查询gtime<=10的选手名称、队伍id、教练名称、gtime）

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

 

 

6.List the dates of the matches and the name of the team in which 'Fernando Santos' was the team1 coach.（查询教练为‘Fernando Santos’的比赛日期、队伍名称）

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

 

 

7.List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw'.（查询比赛地点为'National Stadium, Warsaw'的选手名称）

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

 

 

8.Instead show the name of all players who scored a goal against Germany.（查询和德国对战的选手名称）

select distinct(player)
from game join goal on game.id = goal.matchid
and 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

 

 

10.Show the stadium and the number of goals scored in each stadium.（查询比赛地点以及每个地点的进球次数）

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

 

 

11.For every match involving 'POL', show the matchid, date and the number of goals scored.（查询每个波兰队参加的比赛id、比赛日期、和进球次数）

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

 

 

12.For every match where 'GER' scored, show matchid, match date and the number of goals scored by 'GER'.（查询每个德国队参加的比赛id、比赛日期、德国的进球次数）

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

 

 

13.【难点】List every match with the goals scored by each team as shown. Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1. Sort your result by mdate, matchid, team1 and team2.

select 
mdate,
team1,   
sum(case  when teamid=team1 then 1
    else 0 end)  as score1,
team2,   
sum(case  when teamid = team2 then 1
    else 0 end)  as score2
from game left join goal on matchid = id 
group by mdate, matchid, team1,team2