SQLZOO练习笔记-the join operation

题目网址：THE JOIN OPERATION

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

SELECT matchid, player FROM goal
WHERE teamid = ‘GER’

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

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.

SELECT player, teamid, stadium, mdate
FROM game JOIN goal ON id = matchid and teamid = ‘GER’

这里头一次尝试用join on语句时，在on之后接只和表二有关的判断语句，没想到也可以用欸。
4. Show the team1, team2 and player for every goal scored by a player called Mario player LIKE ‘Mario%’

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

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

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

6. List the the dates of the matches and the name of the team in which ‘Fernando Santos’ was the team1 coach.

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

7.List the player for every goal scored in a game where the stadium was ‘National Stadium, Warsaw’

select player from goal
join game on matchid = id 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 id = matchid and teamid <> ‘GER’
WHERE team1=‘GER’ or team2=‘GER’

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

SELECT teamname, count(teamname) 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(1) from game
group by stadium

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

select matchid, mdate, count(mdate) from
(SELECT matchid, mdate FROM game
JOIN goal ON matchid = id
WHERE (team1 = ‘POL’ OR team2 = ‘POL’)) c
group by mdate

12. For every match where ‘GER’ scored, show matchid, match date and the number of goals scored by ‘GER’

select id, mdate, count(id) from
(select id, mdate from game
join goal on matchid = id and teamid = ‘GER’) c
group by id

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

select mdate, team1, sum(s1) as score1, team2, sum(s2) as score2 from
(select case team2
when team2 = teamid then 1 else 0
end as s2, s1, mdate, matchid, team1, team2
from (select case team1
when team1 = teamid then 1 else 0
end as s1, mdate, matchid, team1, team2, teamid
FROM game JOIN goal ON matchid = id) c) d
group by mdate, team1

第13题这里需要说明一下，不知道为何算出来和答案相比少了两行……具体数据缺失如下，上图是我输出的表格，下图是答案给的表格：


然后还有一点相信大家也看出来了，就是我的数据和答案数据正好相反…不过我检查了一下代码，如果对表格的理解没有错的话我的代码逻辑应该是没错的。鉴于之前也有标准答案对不上题目的经历，所以也就不管啦。
如果有大神知道为什么我的代码少了24 June 2012, 27 June 2012的数据，还请多指教~另外个人觉得第十三题答案有些冗长了，应该可以更精简些，也欢迎大家多留言交流！