sqlzoo练习--The JOIN operation

The JOIN operation

use world;
select * from goal
#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.
#From the previous query you can see that Lars Bender's scored a goal in game 1012.
#Now we want to know what teams were playing in that match.
#Notice in the that the column matchid in the goal table corresponds to the id column in the game table.
#We can look up information about game 1012 by finding that row in the game table.
#Show id, stadium, team1, team2 for just game 1012
select distinct id,stadium,team1,team2 from game as ga
join goal as go on ga.id=go.matchid
where go.matchid=1012

#3.
#You can combine the two steps into a single query with a JOIN.
SELECT *
  FROM game JOIN goal ON (id=matchid)
#The FROM clause says to merge data from the goal table with that from the game table.
#The ON says how to figure out which rows in game go with which rows in goal - the id from goal must match matchid from game.
#(If we wanted to be more clear/specific we could say
#ON (game.id=goal.matchid)
#The code below shows the player (from the goal) and stadium name (from the game table) for every goal scored.

#Modify it to show the player, teamid, stadium and mdate for every German goal.
select go.player,go.teamid,ga.stadium,ga.mdate from game as ga
join goal as go
on ga.id=go.matchid
where go.teamid='GER'

#4.
#Use the same JOIN as in the previous question.
#Show the team1, team2 and player for every goal scored by a player called Mario player LIKE 'Mario%'
SELECT team1,team2,player FROM game as ga
join goal as go
on ga.id=go.matchid
where go.player like 'Mario%'

#5.
#The table eteam gives details of every national team including the coach.
#You can JOIN goal to eteam using the phrase goal JOIN eteam on teamid=id
#Show player, teamid, coach, gtime for all goals scored in the first 10 minutes gtime<=10
select player,teamid,coach,gtime from goal as go
join eteam as e
on go.teamid=e.id
where go.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 ga.mdate,e.teamname from game as ga
join eteam as e
on ga.team1=e.id
where e.coach='Fernando Santos'


#7.
#List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw'
select go.player from goal as go
join game as ga
on go.matchid=ga.id
where ga.stadium='National Stadium, Warsaw'

#8.
The example query shows all goals scored in the Germany-Greece quarterfinal.
Instead show the name of all players who scored a goal against Germany.

select DISTINCT player from goal as go
join game as ga
on go.matchid=ga.id
where go.teamid != 'GER' and (ga.team1 ='GER' or ga.team2 ='GER')

#9.
Show teamname and the total number of goals scored.
COUNT and GROUP BY

select teamname,count(teamid) from eteam as e
join goal as go
on go.teamid=e.id
group by teamname

#10.
Show the stadium and the number of goals scored in each stadium.
select stadium,count(matchid) from game as ga
join goal as go
on go.matchid=ga.id group by stadium

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

select matchid,mdate,COUNT(teamid) from game as ga
join goal as go
on go.matchid=ga.id
where (ga.team1='POL'OR ga.team2='POL')
group by go.matchid,mdate

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

select matchid,mdate,count(teamid) from game as ga
join goal as go on go.matchid=ga.id
where go.teamid='GER'
group by matchid,mdate

#13.
#Sort your result by mdate, matchid, team1 and 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 as ga
JOIN goal as go ON go.matchid = ga.id

group by team1,team2,mdate,teamid