SQLZOO 练习题 6 JOIN

6 JOIN

1.Modify it to show the matchid and player name for all goals scored by Germany. To identify German players, check for: teamid = ‘GER’

修改它以显示德国队所有进球的比赛和球员名字。要识别德国球员，请检查:teamid = ‘GER’

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

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

在第1012场比赛中显示id，体育场，team1, team2

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.

修改它，显示德国的所有进球的player, teamid, stadium mdate。

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

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

在名为马里奥的玩家的每一个进球中显示团队1，团队2和玩家

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

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

显示在前10分钟内的所有进球的player, teamid, coach, gtime

select player,teamid,coach,gtime from goal join eteam on eteam.id = goal.teamid
where 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 eteam join game on eteam.id = game.team1
where 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 goal join game on id=matchid where stadium = 'National Stadium, Warsaw'

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

查询出与德国的比赛中取得进球的非德国球员

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

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

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

SELECT teamname,count(*) from eteam join goal
ON id=teamid group BY teamname

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

显示球场和每个球场的进球数量。

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

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

SELECT matchid,mdate,count(matchid)
  FROM game JOIN goal ON matchid = id
 WHERE (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(*) from game join goal on matchid = id
where (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. This will use “CASE WHEN” which has not been explained in any previous exercises.

Sort your result by mdate, matchid, team1 and team2.

列出每一场比赛中各队的进球数。这里将使用在以前的练习中没有解释过的CASE WHEN。
根据mdate, matchid, team1和team2对结果排序。

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 outer join goal
on id=matchid group by mdate,team1,team2