– 第6关 The JOIN operation - SQLZOO


-- 练习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'
-- 练习 where 

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

-- 2.Show id, stadium, team1, team2 for just game 1012
-- 练习 where 

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.
-- 练习join on 

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

-- 4.Show the team1, team2 and player for every goal scored by a player called Mario player LIKE 'Mario%'
-- 练习 join on 
select team1, team2, player 
from game join goal
on id = matchid 
where player like 'Mario%'

-- 5. Show player, teamid, coach, gtime for all goals scored in the first 10 minutes gtime<=10
-- 练习 join on 
select player, teamid, coach, gtime
from goal join eteam
on teamid = id 
where gtime <= 10

-- 6. List the dates of the matches and the name of the team in which 'Fernando Santos' was the team1 coach.
-- 练习 join on 
select mdate, teamname
from game join eteam 
on team1 = eteam.id 
where coach = 'Fernando Santos'

-- 7. List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw'
-- 练习 join on 
select player 
from game join goal 
on id = matchid 
where stadium = 'National Stadium, Warsaw'

-- 8. Instead show the name of all players who scored a goal against Germany.
-- 练习join on 
-- 注意如何排除掉Germany队员。
select distinct player 
from game join goal
on matchid = id 
where (team1='GER' or team2 = 'GER')
and teamid !='GER'