SQLZOO 6.The-JOIN-operationid mdate stadium team1 team2 1001

game

id	mdate	stadium	team1	team2
1001	8 June 2012	National Stadium, Warsaw	POL	GRE
1002	8 June 2012	Stadion Miejski (Wroclaw)	RUS	CZE
1003	12 June 2012	Stadion Miejski (Wroclaw)	GRE	CZE
1004	12 June 2012	National Stadium, Warsaw	POL	RUS
...

goal

matchid	teamid	player	gtime
1001	POL	Robert Lewandowski	17
1001	GRE	Dimitris Salpingidis	51
1002	RUS	Alan Dzagoev	15
1002	RUS	Roman Pavlyuchenko	82
...

eteam

id	teamname	coach
POL	Poland	Franciszek Smuda
RUS	Russia	Dick Advocaat
CZE	Czech Republic	Michal Bilek
GRE	Greece	Fernando Santos
...

1.

列出德国得分的matchid和名称。

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

Correct answer

matchid	player
1008	Mario Gómez
1010	Mario Gómez
1010	Mario Gómez
1012	Lukas Podolski
1012	Lars Bender
1026	Philipp Lahm
1026	Sami Khedira
1026	Miroslav Klose
1026	Marco Reus
1030	Mesut Özil

2.

列出比赛编号为1012d的id, stadium, team1, team2

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

Correct answer

id	stadium	team1	team2
1012	Arena Lviv	DEN	GER

4.

列出所有以Mario开头的得分球员，按team1,team2,player顺序。

SELECT team1,team2,player
FROM game a 
JOIN goal b ON ( a.id = b.matchid ) WHERE b.player LIKE 'Mario%'

Correct answer

team1	team2	player
GER	POR	Mario Gómez
NED	GER	Mario Gómez
NED	GER	Mario Gómez
IRL	CRO	Mario Mandžukic
IRL	CRO	Mario Mandžukic
ITA	CRO	Mario Mandžukic
ITA	IRL	Mario Balotelli
GER	ITA	Mario Balotelli
GER	ITA	Mario Balotelli

5.

列出所有在前十分钟得分的player,teamid,coach,gtime。

SELECT player, teamid, coach, gtime
  FROM goal a JOIN eteam b ON a.teamid=b.id
 WHERE gtime<=10

Correct answer

player	teamid	coach	gtime
Petr Jirácek	CZE	Michal Bílek	3
Václav Pilar	CZE	Michal Bílek	6
Mario Mandžukic	CRO	Slaven Bilic	3
Fernando Torres	ESP	Vicente del Bosque	4

6.

列出比赛为team1，球队教练是'Fernando Santos'的球队比赛时间以及队伍名称。

SELECT mdate, teamname
FROM eteam a JOIN game b ON a.id=b.team1 
WHERE a.coach ='Fernando Santos'

Correct answer

mdate	teamname
12 June 2012	Greece
16 June 2012	Greece

7.

列出所有在'National Stadium, Warsaw'场馆得分的球员名称。

SELECT player
FROM game a
JOIN goal b ON a.id=b.matchid
WHERE a.stadium = 'National Stadium, Warsaw'

Correct answer

player
Robert Lewandowski
Dimitris Salpingidis
Alan Dzagoev
Jakub Blaszczykowski
Giorgos Karagounis
Cristiano Ronaldo
Mario Balotelli
Mario Balotelli
Mesut Özil

8.

列出对战德国队进球的球员名称。

SELECT DISTINCT(player)
  FROM game JOIN goal ON matchid = id 
    WHERE (team1='GER' or team2='GER' ) AND teamid <> 'GER'

Correct answer

player
Robin van Persie
Michael Krohn-Dehli
Georgios Samaras
Dimitris Salpingidis
Mario Balotelli

9.

列出各个球队的进球总数。

SELECT teamname, count(teamid)
  FROM eteam JOIN goal ON id=teamid
GROUP BY teamname

Correct answer

teamname	count(teamid)
Croatia	4
Czech Republic	4
Denmark	4
England	5
France	3
Germany	10
Greece	5
Italy	6
Netherlands	2
Poland	2
Portugal	6
Republic of Ireland	1
Russia	5
Spain	12
Sweden	5
Ukraine	2

10.

列出各个场馆的进球总数。

SELECT stadium, count(teamid)
  FROM game JOIN goal ON id=matchid
GROUP BY stadium

Correct answer

stadium	count(teamid)
Arena Lviv	9
Donbass Arena	7
Metalist Stadium	7
National Stadium, Warsaw	9
Olimpiyskiy National Sports Complex	14
PGE Arena Gdansk	13
Stadion Miejski (Poznan)	8
Stadion Miejski (Wroclaw)	9

11.

列出包含波兰的每场比赛，展示matchid, date,以及每场比赛的进球总数。

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

Correct answer

matchid	mdate	count(id)
1001	8 June 2012	2
1004	12 June 2012	2
1005	16 June 2012	1

12.

列出包含波兰的每场比赛，展示matchid, date,以及每场比赛德国的的进球总数。

SELECT matchid,mdate, count(id)
  FROM game JOIN goal ON matchid = id 
 WHERE (team1 = 'GER' OR team2 = 'GER') AND teamid = 'GER'
GROUP BY matchid, mdate

Correct answer

matchid	mdate	count(id)
1008	9 June 2012	1
1010	13 June 2012	2
1012	17 June 2012	2
1026	22 June 2012	4
1030	28 June 2012	1

13.

列出每场比赛的比分板，mdate,team1,score1,team2,score2，以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 id,mdate,team1,team2
ORDER BY mdate,matchid,team1,team2

Correct answer

mdate	team1	score1	team2	score2
1 July 2012	ESP	4	ITA	0
10 June 2012	ESP	1	ITA	1
10 June 2012	IRL	1	CRO	3
11 June 2012	FRA	1	ENG	1
11 June 2012	UKR	2	SWE	1
12 June 2012	GRE	1	CZE	2
12 June 2012	POL	1	RUS	1
13 June 2012	DEN	2	POR	3
13 June 2012	NED	1	GER	2
14 June 2012	ITA	1	CRO	1
14 June 2012	ESP	4	IRL	0
15 June 2012	UKR	0	FRA	2
15 June 2012	SWE	2	ENG	3
16 June 2012	CZE	1	POL	0
16 June 2012	GRE	1	RUS	0
17 June 2012	POR	2	NED	1
17 June 2012	DEN	1	GER	2
18 June 2012	CRO	0	ESP	1
18 June 2012	ITA	2	IRL	0
19 June 2012	ENG	1	UKR	0
19 June 2012	SWE	2	FRA	0
21 June 2012	CZE	0	POR	1
22 June 2012	GER	4	GRE	2
23 June 2012	ESP	2	FRA	0
24 June 2012	ENG	0	ITA	0
27 June 2012	POR	0	ESP	0
28 June 2012	GER	1	ITA	2
8 June 2012	POL	1	GRE	1
8 June 2012	RUS	4	CZE	1
9 June 2012	NED	0	DEN	1
9 June 2012	GER	1	POR	0