专栏首页猴子聊数据分析图解面试题:经典50题
原创

图解面试题:经典50题

已知有如下4张表:

学生表:student(学号,学生姓名,出生年月,性别)

成绩表:score(学号,课程号,成绩)

课程表:course(课程号,课程名称,教师号)

教师表:teacher(教师号,教师姓名)

1.汇总分析

-查询学生的总成绩并进行排名

/*【知识点】分组查询分析思路select 查询结果 [总成绩:sum(成绩), 学号]from 从哪张表中查找数据 [成绩表score]where 查询条件 [没有]group by 分组 [学生的总成绩:按照每个学生学号进行分组]order by 排序 [按照总成绩进行排序:sum(成绩)];/*select 学号 ,sum(成绩) from score group by 学号order by sum(成绩) ;

-查询平均成绩大于60分的学生的学号和平均成绩

/*【知识点】分组+条件分析思路select 查询结果 [学号, 平均成绩: avg(成绩)]from 从哪张表中查找数据 [成绩表score]where 查询条件 [没有]group by 分组 [学号]having 分组条件 [平均成绩大于60分:avg(成绩 ) >60]order by 排序 [没有];/*select 学号 ,avg(成绩) from score group by 学号  having avg(成绩 ) >60

2.复杂查询

-查询各学生的年龄(精确到月份)

/*【知识点】时间格式转化*/select 学号 ,timestampdiff(month ,出生日期 ,now())/12 from student ;

-查询本月过生日的学生

select * from student where month (出生日期 ) = month(now())+2;

3.多表查询

-检索"0001"课程分数小于60,按分数降序排列的学生信息

思路如图:

select a.*,b.成绩 from student as a inner join score as b on a.学号 =b.学号 where b.成绩 <60 and b.课程号 =01order by b.成绩 desc;

-查询不同老师所教不同课程平均分从高到低显示

【知识点】分组+条件+排序+多表连接,思路如图

select a.教师号,a.教师姓名,avg(c.成绩) from  teacher as a inner join course as b on a.教师号= b.教师号inner join score  c on b.课程号= c.课程号group by a.教师姓名order by avg(c.成绩) desc;

-查询课程名称为"数学",且分数低于60的学生姓名和分数

【知识点】多表连接,思路如图

select a.姓名,b.成绩 from student as a inner join score as b on a.学号 =b.学号 inner join course c on b.课程号 =c.课程号 where b.成绩  <60 and c.课程名称 ='数学';

-查询任何一门课程成绩在70分以上的姓名、课程名称和分数(与上题类似)

select a.姓名,c.课程名称 ,b.成绩 from student as a inner join score as b on a.学号=b.学号inner join course c on b.课程号 =c.课程号 where b.成绩 >70;

 -查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩

【知识点】分组+条件+多表连接

翻译成大白话:计算每个学号不及格分数个数,筛选出大于2个的学号并找出姓名,平均成绩,思路如图:

select b.姓名,avg(a.成绩),a.学号  from score as ainner join student as b on a.学号 =b.学号 where a.成绩 <60group by a.学号 having count(a.学号 ) >=2;

-查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select distinct a.学号 ,a.成绩 ,a.课程号 from score as a inner join score as b on a.学号 =b.学号 where a.成绩 =b.成绩 and a.课程号 != b.课程号 ;

-查询课程编号为“0001”的课程比“0002”的课程成绩高的所有学生的学号

【知识点】多表连接+条件,思路如图

select a.学号  from (select 学号 ,成绩 from score where 课程号=01) as ainner join (select 学号 ,成绩 from score where 课程号=02) as bon a.学号 =b.学号 inner join student c on c.学号 =a.学号 where a.成绩 >b.成绩 ;

-查询学过编号为“0001”的课程并且也学过编号为“0002”的课程的学生的学号、姓名

思路如图

select a.学号  from (select 学号 ,成绩 from score where 课程号=01) as ainner join (select 学号 ,成绩 from score where 课程号=02) as bon a.学号 =b.学号 inner join student c on c.学号 =a.学号 where a.成绩 >b.成绩 ;

-查询学过“孟扎扎”老师所教的所有课的同学的学号、姓名

思路如图

select s.学号 ,s.姓名,a.学号 ,b.课程号,c.教师号 ,c.教师姓名from student as s  inner join score as a  on s.学号 =a.学号 inner join  course  b on a.课程号 =b.课程号inner join  teacher c  on b.教师号 = c.教师号where c.教师姓名 ='孟扎扎';

-查询没学过"孟扎扎"老师讲授的任一门课程的学生姓名 (与上题类似,"没学过"用not in来实现)

select 姓名 ,学号 from student where 学号 not in (select a.学号 from student as a inner join score as bon a.学号 =b.学号 inner join course as c on b.课程号 =c.课程号 inner join teacher as d on c.教师号 =d.教师号 where d.教师姓名 ='孟扎扎');

-查询没学过“孟扎扎”老师课的学生的学号、姓名(与上题类似)

select 学号, 姓名 from student where 学号 not in (select 学号 from score where 课程号=(select 课程号 from course  where 教师号 = (select 教师号 from teacher where 教师姓名 ='孟扎扎')));

-查询选修“孟扎扎”老师所授课程的学生中成绩最高的学生姓名及其成绩(与上题类似,用成绩排名,用 limit 1得出最高一个)

select a.姓名,b.成绩 from student as a inner join score as b on a.学号=b.学号inner join course as c on b.课程号 =c.课程号 inner join teacher as d on c.教师号 = d.教师号 where d.教师姓名 = '孟扎扎'order by b.成绩 desc limit 1;

-查询至少有一门课与学号为“0001”的学生所学课程相同的学生的学号和姓名

select 学号 ,姓名 from student where 学号 in(select distinct(学号) from score where 课程号 in (select 课程号 from score where 学号=0001)) and 学号 !=0001;

-按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

【知识点】多表连接 新建字段 ,思路如图

select a.学号,avg(a.成绩 ),max(case when b.课程名称  = '数学' then a.成绩 else null end ) as '数学',max(case when b.课程名称  = '语文' then a.成绩 else null end ) as '语文',max(case when b.课程名称  = '英语' then a.成绩 else null end ) as '英语'from score as ainner join course as b on a.课程号 =b.课程号 group by a.学号 ;

4.SQL高级功能

-查询学生平均成绩及其名次

【知识点】窗口函数排名,思路如图

select 学号 ,avg(成绩),row_number () over( order by avg(成绩) desc)from scoregroup by 学号  ;

-按各科成绩进行排序,并显示排名

select 课程号 ,row_number () over(partition by 课程号 order by 成绩 )from score ;

-查询每门功成绩最好的前两名学生姓名

【知识点】窗口函数排名+多表连接+条件

select a.课程号 ,b.姓名 ,a.成绩,a.ranking from (select 课程号 ,学号 ,成绩 ,row_number () over(partition by 课程号 order by 成绩 desc) as rankingfrom  score) as a inner join student as b on a.学号 =b.学号 where a.ranking <3 ;

-查询所有课程的成绩第2名到第3名的学生信息及该课程成绩(与上一题相似)

select b.姓名 ,a.课程号 ,a.成绩 from (select 课程号 ,学号 ,成绩 ,row_number () over( partition by 课程号 order by 成绩 desc) as rankingfrom  score ) as a inner join student as b on a.学号 =b.学号 where a.ranking in( 2,3) ;

-查询各科成绩前三名的记录(不考虑成绩并列情况)(与上一题相似)

select b.姓名 ,a.课程号 ,a.成绩 from (select 课程号 ,学号 ,成绩 ,row_number () over( partition by 课程号 order by 成绩 desc) as 'ranking'from  score ) as a inner join student as b on a.学号 =b.学号 where a.ranking <4 ;

这些题要融会贯通,以后碰到类似的面试题都可以找到对应场景的使用案例。

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Java经典编程50题(面试笔试机试)

    https://blog.csdn.net/alias_fa/article/details/52985112

    林万程
  • 经典面试题解析

    如果你认为输出的是6,那么恭喜你答错了。正确答案是10。首先分析一下这段代码的具体执行过程。

    Chor
  • 【收藏】SQL经典面试50题 | 附答案

    今天给大家分享一份星友对SQL经典面试50题的刷题记录,稍作了一些修改,以下是星友自述正文:

    木东居士
  • 经典面试题

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明...

    cwl_java
  • 经典面试题

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明...

    cwl_java
  • 经典面试题

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明...

    cwl_java
  • 经典面试题

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明...

    cwl_java
  • 50道Java集合经典面试题(收藏版)

    Collection.sort是对list进行排序,Arrays.sort是对数组进行排序。

    捡田螺的小男孩
  • MySQL练习(一)——MySQL之经典面试50题(上)

      我们通过前面的六篇文章给大家介绍了MySQL中的基本操作,包括增删改查等基本的操作,另外的MySQL的性能优化,我们之前通过十篇文章进行讲解,截止目前,我们...

    stefan666

扫码关注云+社区

领取腾讯云代金券