SQL面试题:多表查询》练习题答案

96
猴子聊人物
0.9 2019.05.23 09:52* 字数 2434

复仇者联盟中多个人物之间有着关系,同样的,表和表之间也会有关系,这种关系在数据库里叫做联结(join),多表查找也是通过联结来实现的。

这是《从零学会sql》系列课程第5节课《多表查询》的练习题,也是常考常考的面试题。

一、练习题

查询所有学生的学号、姓名、选课数、总成绩

selecta.学号,a.姓名,count(b.课程号)as选课数,sum(b.成绩)as总成绩fromstudentasa leftjoinscoreasbona.学号 = b.学号groupbya.学号;

查询平均成绩大于85的所有学生的学号、姓名和平均成绩

selecta.学号,a.姓名,avg(b.成绩)as平均成绩fromstudentasaleftjoinscoreasbona.学号 = b.学号groupbya.学号havingavg(b.成绩)>85;

查询学生的选课情况:学号,姓名,课程号,课程名称

selecta.学号, a.姓名, c.课程号,c.课程名称fromstudent ainnerjoinscore bona.学号=b.学号innerjoincourse conb.课程号=c.课程号;

查询出每门课程的及格人数和不及格人数

- 考察case表达式select课程号,sum(casewhen成绩>=60then1else0end)as及格人数,sum(casewhen成绩 <60then1else0end)as不及格人数fromscoregroup by 课程号;

使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩,分别统计:各分数段人数,课程号和课程名称

-- 考察case表达式selecta.课程号,b.课程名称,sum(casewhen成绩between85and100then1else0end)as'[100-85]',sum(casewhen成绩 >=70and成绩<85then1else0end)as'[85-70]',sum(casewhen成绩>=60and成绩<70then1else0end)as'[70-60]',sum(casewhen成绩<60then1else0end)as'[<60]'fromscoreasarightjoincourseasbona.课程号=b.课程号groupbya.课程号,b.课程名称;

二、题目来自sqlzoo的多表查询题目

网址:

https://sqlzoo.net/wiki/The_JOIN_operation/zh

这部分题目使用的是‘2012年欧洲足球锦标赛’数据库里的表。

1.比赛信息表game

id比赛编号,mdate比赛日期, stadium比赛地点, team1对战双方(球队编号1), team2对战双方(球队编号2)

2.进球信息表goal

matchid比赛编号,teamid球队编号, player进球球员的姓名,gtime多长时间进的球(从开始比赛到进球多长时间,单位:分钟)

比赛信息表game(id比赛编号)和进球信息表goal(matchid比赛编号)通过‘比赛编号’联结

3.球队信息表eteam 

id球队编号(联结2) teamname 球队名称 coach教练

进球信息表goal(teamid球队编号)球队信息表eteam(球队编号id)通过‘球队编号’联结

如何做题?

练习题答案

先自己在sqlzoo平台里(网址:

https://sqlzoo.net/wiki/The_JOIN_operation/zh)

输入sql,查看运行结果,然后再和下面的答案对比哪里写的不一样

1. 在进球表(goal)中查找德国球队(teamid = 'GER')进球的比赛编号(matchid),进球球员姓名(player)

selectmatchid, playerfromgoalwhereteamid ='GER';

2. 在比赛信息表(game)查找比赛编号1012的信息

selectid,stadium,team1,team2fromgamewhereid=1012;

3. 查找德国队进球球员姓名,球队编号(在进球信息表goal), 比赛地点,比赛日期(在比赛信息表game)

问题分析:

进球球员姓名,球队编号(在进球信息表goal)

比赛地点,比赛日期(在比赛信息表game)

两个表如何联结?通过比赛编号

哪一种联结?需要两个表中共同部分,内联结

selectb.player, b.teamid, a.stadium, a.mdatefromgameasainnerjoingoalasbona.id=b.matchidwhereb.teamid ='GER';

4. 查找姓名中以Mario开头的进球球员,符合条件球员参加比赛的对战双方

问题分析:

1)查找出进球球员姓名(进球信息表goal:进球球员姓名player),

比赛的对战双方(比赛信息表game:对战双方team1,team2)

两个表如何联结?通过比赛编号

哪一种联结?需要两个表中共同部分,内联结

2)加入条件:进球球员姓名中以Mario开头,模糊查询:like 'Mario%'

selecta.team1, a.team2, b.playerfromgameasainnerjoingoalasbona.id=b.matchidwhereb.playerlike'Mario%';

5. 查找进球球员的姓名、球队编号、教练、多长时间进球。要求多长时间进球<=10分钟

问题分析:

1)查找出进球球员的姓名、球队编号、多长时间进球(在进球信息表goal)

教练(在球队信息表eteam)

两个表如何联结?通过球队编号(进球信息表goal:teamid球队编码 = 球队信息表eteam:id球队编码)

哪一种联结?需要两个表中共同部分,内联结

2)加入条件:多长时间进球<=10分钟

selectb.player, b.teamid, c.coach, b.gtimefromgoalasbinnerjoineteamasconb.teamid=c.idwhere b.gtime<=10;

6. 'Fernando Santos'作为教练的比赛日期,球队编号有哪些?

问题分析:

1)查找出

教练(在球队信息表eteam)

比赛日期,球队编码(在比赛信息表game)

两个表如何联结?通过球队编号(比赛信息表game:team1对战双方的球队编码(team1或者team2的球队编码) = 球队信息表eteam:id球队编码)

哪一种联结?需要两个表中共同部分,内联结

2)加入条件:教练是'Fernando Santos'

selecta.mdate, c.teamnamefromgameasainnerjoineteamascona.team1=c.idwherec.coach ='Fernando Santos';

7. 在比赛地点'National Stadium, Warsaw'有哪些进球球员?

问题分析:

1)查找

比赛地点(在比赛信息表game),进球球员(在进球信息表goal)

2)加入条件:比赛地点是'National Stadium, Warsaw'

selectb.playerfromgameasainnerjoingoalasbona.id=b.matchidwherea.stadium ='National Stadium, Warsaw';

8.射入德国球门的球员姓名

问题分析:

1)查找进球球员姓名,对战双方

2)条件:有一个球队是德国(球队编号'GER'),被射入了球

selectdistinctb.playerfromgameasainnerjoingoalasbona.id = b.matchidwhere(b.teamid = a.team1anda.team2 ='GER')or(b.teamid = a.team2anda.team1 ='GER');

1)解释下where子句中的条件:

主队是德国或者客队是德国,比如

德国 和 A对比赛(德国是主队),进球的是A队

A队和德国比赛(德国是客队),进球的是A队

所以条件是(b.teamid = a.team1 and a.team2 = 'GER') or (b.teamid = a.team2 and a.team1 = 'GER')

2)内联结的结果中入门球员有重复值,用distinct去掉重复值

9. 查找出球队名称,和每个球队进球人数

问题分析:

球队名称(球队信息表eteam),和每个球队进球人数(进球信息表goal:按球队编号分组统计count(teamid))

selectc.teamname,count(b.teamid)frometeamascinnerjoingoalasbonc.id=b.teamidgroupbyb.teamid;

上面这么写是不对的,因为查下结果中要查出球队名称(teamname)。如果按球队编码(teamid)来分组,结果中没法显示球队名称。所以这里我们用球队名称(teamname)来分组。正确的sql是:

selectc.teamname,count(c.teamname)frometeamascinnerjoingoalasbonc.id=b.teamidgroupbyc.teamname,;

10. 查找出所有比赛地点,每个比赛地点的进球数

问题分析:

比赛地点(比赛信息表game),和每个比赛地点的进球数(进球信息表goal:按比赛地点分组,对进球人员进行汇总count(player))

联结方式是什么?要查出比赛信息表game中全部比赛地点,所以是左联结

selecta.stadium,count(b.player)fromgameasaleftjoingoalasbona.id=b.matchidgroupbya.stadium;

11. 查找出有波兰球队'POL'参加的比赛编号,比赛日期,对应这场比赛的进球数

selecta.id,a.mdate,count(b.player)fromgameasainnerjoingoalasbona.id = b.matchidwhere(team1 ='POL'ORteam2 ='POL')groupbya.id;

上面sql运行后会报错,是因为分组结果中没有比赛日期(mdate),而查询结果要求有比赛日期(mdate)。

为了让查询结果中有比赛日期(mdate),我们在分组group by子句中再加上分组比赛日期(mdate)。

这时候group by a.id,a.mdate 有两个分组列,表示这两个列的值都相同时才算一组。因为比赛编号(id),比赛日期(mdate)都是一对一的,不会改变之前的分组结果,但有可以让查询结果中有分组中的列。正确的sql:

selecta.id,a.mdate,count(b.player)fromgameasainnerjoingoalasbona.id = b.matchidwhere(team1 ='POL'orteam2 ='POL')groupbya.id,a.mdate;

12. 对于德国队'GER'得分的每场比赛,显示比赛编号,比赛日期和'GER'得分的进球数

问题分析:

比赛编号,比赛日期(在比赛信息表game)和'GER'得分的进球数(进球信息表goal:按比赛地点分组,对进球人员进行汇总count(player))

selecta.id,a.mdate,count(player)fromgameasainnerjoingoalasbona.id=b.matchidwhereb.teamid='GER'groupbya.id,a.mdate;

13. 查找出所有比赛的日期,每场比赛中对战双方各自的进球数(也就是team1进球数,team2进球数)

问题分析:

当比赛信息表game中的球队编号(team1)也出现在进球信息表(goal)中时,表示这个球队进球了,使用case语句来统计

selecta.mdate,a.team1,sum(casewhena.team1=b.teamidthen1else0end)asscore1,a.team2,sum(casewhena.team2=b.teamidthen1else0end) score2fromgameasaleftjoingoalasbona.id = b.matchidgroupbya.id,a.mdate,a.team1,a.team2;

推荐:如何用最短的时间找到初级数据分析师工作?

猴子聊人物