sqlzoo练习10-join

sqlzoo练习10-Join opreation

Join opetation指的是不同的表之间通过某个相同的字段进行关联，从而进行查询操作。

通过下面的两张图中，可以得出3个结论

game和goal两张表，是通过id和matchid来进行联结
game和eteam两张表，是通过id字段进行联结
goal和eteam两张表，通过matchid和id字段进行联结

练习

show the matchid and player name for all goals scored by Germany. To identify German players, check for: teamid = 'GER'

1
2
3

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

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
join goal on game.id=goal.matchid
where goal.matchid=1012;

show the player, teamid, stadium and mdate for every German goal.

找出每个德国队的球

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

Show the team1, team2 and player for every goal scored by a player called Mario player LIKE 'Mario%'

找出一个名叫Mario的球员进的球

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

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
where gtime <= 10;   -- 找出10分钟之内进的球

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

找出team1的较量是FS的球队情况

select mdate, teamname
from game
join eteam on team1=eteam.id
where coach='Fernando Santos';

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';

show the name of all players who scored a goal against Germany.

找出和德国对抗的进球的球员信息

select distinct(player)
from goal
join game on id=matchid
where (team1='GER' or team2='GER')   -- 两个队中有一个是德国队
and teamid != 'GER'   -- 保证对手不是德国队

Show teamname and the total number of goals scored.

找出球队名称和总进球数

select teamname, count(player) as total_scoer   -- 进球总数通过球员总数来求得
from eteam
join goal on id=matchid
group by teamname;

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

显示每个球场和每个球场中的总得分数

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

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

找出每场合POL相关的比较的情况

select matchid, mdate, count(teamid)    -- 进球总数通过球队数来统计
from game 
join goal on matchid=id
where (team1='POL' or team2='POL')
group by matchid, mdate

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

找出每场GER进球的比赛和GER进球总数

select matchid, mdate, count(teamid)   -- 统计进球数
from goal
join game on id=matchid 
where teamid='GER'
group by matchid, mdate;

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.

Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1.

select mdate,
team1,sum(case when teamid=team1 then 1 else 0 end) scoer1,
team2,sum(case when teamid=team1 then 1 else 0 end) scoer2
from game
join goal on matchid=id
group by mdate, team1, team2