SQLZOO习题详解(07)——The JOIN operation

水深无声 2023-01-19 14:50 106阅读 0赞

--------------------

*  本文原创作者：谷哥的小弟
 *  作者博客地址：http://blog.csdn.net/lfdfhl

--------------------

# 表及其关系 #

game表如下：  
![在这里插入图片描述][20210407233801548.png]  
goal表如下：  
![在这里插入图片描述][20210407233833202.png]  
eteam表如下：  
![在这里插入图片描述][20210407233905240.png]  
表的关系如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xmZGZobA_size_16_color_FFFFFF_t_70]

# 链接地址 #

[https://napier.sqlzoo.net/wiki/The\_JOIN\_operation][https_napier.sqlzoo.net_wiki_The_JOIN_operation]

# SQL练习 #

01、请查询出德国GER队进球球员的名字以及对应比赛的matchid

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

02、请查询id为1012的比赛的id,stadium,team1,team2

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

03、请查询德国队GER进球球员的球员名，队伍名，比赛场馆，比赛日期

SELECT player,teamid,stadium,mdate FROM game INNER JOIN goal ON (id=matchid) WHERE teamid='GER'

SELECT player,teamid,stadium,mdate FROM game INNER JOIN goal ON (game.id=goal.matchid) WHERE goal.teamid='GER'

04、请查询进球球员名字以Mario开头的相关信息：比赛双方的球队名以及球员名

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

05、请查询比赛中前10分钟有进球的比赛的相关信息：进球球员名字player，球队id即teamid，教练名字coach，进球时间gtime

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

06、请查询Fernando Santos作为队伍team1的教练的赛事日期和球队名

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

07、请查询在National Stadium, Warsaw球馆进球的球员的名字

SELECT player
    FROM game INNER JOIN goal ON (game.id = goal.matchid)
    WHERE stadium = 'National Stadium, Warsaw'

08、查询其他球队在于德国队的比赛中进球的球员名字

SELECT DISTINCT(player)
    FROM game INNER JOIN goal ON (game.id = goal.matchid)
    WHERE teamid!='GER' AND (team1 = 'GER' OR team2 = 'GER')

09、请查询各球队的名称及其进球总数

SELECT teamname, COUNT(player)
    FROM eteam INNER JOIN goal ON eteam.id = goal.teamid
    GROUP BY teamname

10、请查询各体育场的名称以及在该体育场的进球数

SELECT stadium, COUNT(*)
    FROM game JOIN goal ON game.id=goal.matchid
    GROUP BY stadium

11、请查询波兰POL参与的每场比赛的比赛日期和进球数

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

12、查询德国GER队参与的比赛的相关信息：比赛编号matchid，比赛日期mdate，以及德国队的进球数

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

13、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. 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 
    LEFT JOIN goal 
    ON matchid = id
    GROUP BY mdate, matchid, team1, team2

[20210407233801548.png]: /images/20221021/b4dd496ee81d42b0bd90f920a68bed8b.png
[20210407233833202.png]: /images/20221021/f95725c97e054fa5abd4c69bd6d62935.png
[20210407233905240.png]: /images/20221021/71a6c35777cf40ec87be83afc52c43b6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xmZGZobA_size_16_color_FFFFFF_t_70]: /images/20221021/f6674447090d4818a881c9666096e941.png
[https_napier.sqlzoo.net_wiki_The_JOIN_operation]: https://napier.sqlzoo.net/wiki/The_JOIN_operation