数据分析sql面试必会6题经典_数据分析师SQL面试必备50题[通俗易懂]

数据分析sql面试必会6题经典_数据分析师SQL面试必备50题[通俗易懂]以下是SQL面试必备的经典的50道题目,每道题都有博主本人的解题思路和对应的SQL语句。每道题的思路与答案均为博主本人主观理解,仅供参考。环境:MySQL8.0可视化工具:Navicat1、查询课程编号为01的课程比02的课程高的所有学生的学号和成绩解题思路:(1)先把课程为01的学号和成绩找出来as表a(2)再把课程为02的学号和成绩找出来as表b(3)用innerjoin将表a…

大家好,又见面了,我是你们的朋友全栈君。

以下是SQL面试必备的经典的50道题目,每道题都有博主本人的解题思路和对应的SQL语句。

每道题的思路与答案均为博主本人主观理解,仅供参考。

环境:MySQL8.0

可视化工具:Navicat

1、查询课程编号为01的课程比02的课程高的所有学生的学号和成绩

解题思路:

(1) 先把课程为01的学号和成绩找出来 as 表a

(2) 再把课程为02的学号和成绩找出来 as 表b

(3) 用inner join将表a和表b按照s_id连接起来

(4) 最后用where筛选表a成绩大于表b成绩的学生编号

select a.s_id

from

(select s_id,s_score from score where c_id=’01’) as a

inner join

(select s_id,s_score from score where c_id=’02’) as b on a.s_id=b.s_id

where a.s_score>b.s_score;

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

解题思路:

(1) 先用group by对s_id进行分组

(2) 再用having过滤平均分大于60

tips:group by里的东西必须是select里的东西,除非是统计函数(avg,max等)

select s_id,avg(s_score)

from score

group by s_id

having avg(s_score)>60;

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

解题思路:

(1) 姓名在student表,成绩在score表,因此需要连接两表;student表左连接score表,这样才能保证保留所有学生的信息

(2) 按s_id和s_score进行分组

(3) 选课数使用count(),总成绩使用sum(if(…))

select

a.s_id,

a.s_name,

count(b.c_id),

sum(if(b.s_score is null,0,b.s_score))

from student as a

left join score as b on a.s_id=b.s_id

group by a.s_id,a.s_name;

4、查询姓“猴”的老师的人数

解题思路:

(1) 使用like和%进行模糊查询

(2) 人数使用count()函数

select count(t_id)

from teacher

where t_name like ‘猴%’;

5、查询没学过“张三”老师课的学生的学号、姓名

解题思路:

(1) 先找出学过”张三”老师可的学生,这些学生以外的学生就是没学过”张三”老师课的

(2) 在teacher表中获取”张三”的t_id,在course表中获取所有老师t_id和课程c_id,在score表中获取学生S_id和课程c_id

(3) teacher表与course表按t_id内连接,course表与score表按s_id内连接,然后选出学生s_id

(4) 最后从student表中中过滤不在上面的学生s_id

select s_id,s_name

from student

where s_id not in(

select c.s_id

from

(select t_id from teacher where t_name=’张三’) as a

inner join

(select t_id, c_id from course) as b on a.t_id=b.t_id

inner join

(select s_id, c_id from score) as c on b.c_id=c.c_id

);

6、查询学过“张三”老师所教的所有课的同学的学号和姓名

解题思路:

(1) 首先查找”张三”教的所有课程c_id:将teahcer表和course表按t_id内连接

(2) 接着查找学过以上课程的学生:将student表和score表按s_id内连接

(3) 最后对s_id进行group by,过滤所选课程数量等于”张三”所教课程数量

select a.s_id,a.s_name

from student as a

join score as b on a.s_id=b.s_id

where b.c_id in ( — (2)接着查找学过以上课程的学生:将student表和score表按s_id内连接

— (1)首先查找”张三”教的所有课程c_id:将teahcer表和course表按t_id内连接

select b.c_id

from teacher as a

join course as b on a.t_id=b.t_id

where a.t_name=’张三’

)

— (3)最后对s_id进行group by,过滤所选课程数量等于”张三”所教课程数量

group by a.s_id

having count(b.c_id)=(

select count(b.c_id)

from teacher as a

join course as b on a.t_id=b.t_id

where a.t_name=’张三’

);

7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号和姓名

解题思路:

(1) 查出学过编号01课程学生s_id作为表a

(2) 查找学过编号02课程学生s_id作为表b

(3) 将表a和表b内连接,选出学生编号s_id

(4) 最后从student表筛选学生编号包含以上s_id的学生

select s_id,s_name

from student

where s_id in (

select a.s_id

from

(select s_id from score where c_id=’01’) as a

join

(select s_id from score where c_id=’02’) as b on a.s_id=b.s_id

);

8、查询课程编号为“02”的总成绩

select sum(s_score)

from score

group by c_id

having c_id=’02’;

9、查询所有课程成绩小于60分的学生的学号和姓名

解题思路:

(1) 将score表按s_id进行group by,过滤条件为课程最大成绩小于60(如果最大成绩都小于60,那就意味所有成绩都小于60)

(2) 从student表中选出学生编号包含以上s_id的学生

select s_id,s_name

from student

where s_id in (

select s_id

from score

group by s_id

having max(s_score)<60

);

10、查询没有学全所有课的学生的学号和姓名

解题思路:

(1) 首先从course表获取所有课程的总数

(2) 接着student表左连接score表并按student.s_id进行group by,过滤条件是每个学生的课程数量小于第一步的课程总数

(3) 最后从student表中筛选出学生编号包含以上s_id的学生

tips:可能存在一门课也没有学的学生

select a.s_id,a.s_name

from

student as a

left join score as b on a.s_id=b.s_id

group by a.s_id

having count(b.c_id)

select count(c_id) from course

);

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

解题思路:

(1) 查找出学号为”01″的学生所学课程编号c_id

(2) student表左连接score表,选出c_id有包含上面c_id的学生

(3) 接着对s_id进行group by,过滤s_id!=1,最后选择学生编号和姓名

select a.s_id,a.s_name

from

student as a

left join score as b on a.s_id=b.s_id

where b.c_id in (

select c_id from score where s_id=’01’

)

group by a.s_id

having a.s_id<>’01’;

12.查询和“01”号同学所学课程完全相同的其他同学的学号

解题思路:

(1) 对score表按s_id进行group by,对每个s_id的c_id进行group_concat转换,结果作为表a

(2) 查出”01″同学所学课程编号c_id,并进行group_concat转换,结果作为表b,将表a与表b按照group_concat的结果进行内连接

(3) 以上结果就是与”01″同学课程完全相同的同学(包括”01″),最后筛选学生编号不为”01″的

select a.s_id

from

(select s_id,group_concat(c_id order by c_id separator ‘,’) as c_id_str

from score

group by s_id) as a

inner join

(select group_concat(c_id order by c_id separator ‘,’) as c_id_str

from score

where s_id=’01’) as b on a.c_id_str=b.c_id_str

where a.s_id<>’01’;

13、查询没学过“张三”老师讲授的任一门课程的学生姓名

解题思路:

(1) 查出”张三”的教师编号t_id as表a

(2) 表a按t_id内连接course表,course表再按c_id内连接score表,便选出学过”张三”课的学生编号s_id

(3) 最后student表中选出不包含上面编号的学生

select s_name

from student

where s_id not in (

select distinct c.s_id

from

(select t_id from teacher where t_name=’张三’) as a

inner join

(select t_id,c_id from course) as b on a.t_id=b.t_id

inner join

(select s_id,c_id from score) as c on b.c_id=c.c_id

);

14、查询有两门及以上课程不及格的同学的学号、姓名和平均成绩

解题思路:

(1) 筛选score表中score小于60的记录,然后按s_id进行group by,过滤条件是课程数大于2,最后选出这些s_id

(2) 将上面结果作为表a内连接score表,score表按s_id内连接student表,如此一来即可获得有两门及其以上不及格课程同学的信息

(3) 对以上联合表按s_id进行group by,计算每个s_id的平均分

select a.s_id,c.s_name,avg(b.s_score)

from

(select s_id from score where s_score<60 group by s_id having count(c_id)>=2) as a

inner join

(select s_id,s_score from score) as b on a.s_id=b.s_id

inner join

(select s_id,s_name from student) as c on a.s_id=c.s_id

group by a.s_id;

15、检索“01”课程分数小于60、按分数降序排列的学生信息

解题思路:

(1) 从score表查找”01″课程分数小于60的学生编号s_id和”01″课程分数,并按按分数降序排序

(2)将上面结果作为表a与student表内连接,最后输出学生信息

select b.*,a.s_score

from

(select s_id,s_score

from score

where c_id=’01’ and s_score<60

order by s_score desc) as a

inner join student as b on a.s_id=b.s_id;

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

解题思路:

(1) 将student表与score表左连接,可以得到学生信息与各科成绩

(2) 因为要按照平均成绩来排序,所以将第一步的连接表按s_id进行group by

(3) 最终显示格式为学生编号、学生姓名、01成绩、02成绩、03成绩、平均成绩,因此需要使用到case when语句

select

a.s_id,

a.s_name,

— 如果c_id是01,那么这一列显示01课程对应的成绩,否则为空

max(case when b.c_id=’01’ then b.s_score else null end) as ’01’,

max(case when b.c_id=’02’ then b.s_score else null end) as ’02’,

max(case when b.c_id=’03’ then b.s_score else null end) as ’03’,

avg(b.s_score) as avg

from

(select s_id,s_name from student) as a

left join

(select s_id,c_id,s_score from score) as b on a.s_id=b.s_id

group by a.s_id

order by avg desc;

17、查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率(及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90)

解题思路:

(1) 显示列中有分数和课程名字,所以需要将score表与course表内连接

(2) 将上一步的连接表按课程编号c_id进行group by,分组后即可获取每个课程的最高、低分和平均分

(3) 计算及格率中等率等需要使用case when

select

a.c_id,

a.c_name,

max(b.s_score) as ‘最高分’,

min(b.s_score) as ‘最低分’,

avg(b.s_score) as ‘平均分’,

— 如果成绩大于等于60就标记为1,否则标记为0,最后计算平均值就得到对应的合格率

avg(case when b.s_score>=60 then 1 else 0 end) as ‘及格率’,

avg(case when b.s_score>=70 and b.s_score<80 then 1 else 0 end) as ‘中等率’,

avg(case when b.s_score>=80 and b.s_score<90 then 1 else 0 end) as ‘优良率’,

avg(case when b.s_score>=90 then 1 else 0 end) as ‘优秀率’

from

course as a

inner join score as b on a.c_id=b.c_id

group by a.c_id;

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

解题思路:

(1) 因为需要显示排名,因此这里需要使用row_number()函数,相似的还有rank()和dense_rank()

(2) 分别显示课程编号、学生编号、课程成绩、排名

select

c_id,

s_id,

s_score,

row_number() over (partition by c_id order by s_score desc) as ‘rank’

from

score;

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

解题思路:

(1) 将score表按学生编号s_id进行group by

(2) 使用row_number()对sum(s_score)进行排序

select

s_id,

sum(s_score) as sum_score,

row_number() over(order by sum(s_score) desc) as ‘rank’

from

score

group by

s_id;

20、查询不同老师所教不同课程的平均分并从高到低显示

解题思路:

(1) 不同老师不同课程的平均分:每个老师教的每一门课的平均分

(2) 需要的字段有老师编号、课程编号、分数,因此需要将course表左连接score表

(3) 接着对老师编号和课程编号进行group by

(4) 输出老师编号、课程编号、分数,按avg(score.s_score)降序排列

select

a.t_id,

a.c_id,

avg(b.s_score)

from

course as a

left join score as b on a.c_id=b.c_id

group by a.t_id,a.c_id

order by avg(b.s_score) desc;

21、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

解题思路:

(1) 需要有学生信息和课程成绩,因此需要连接student表和score表

(2) 使用row_number()函数按课程编号c_id分组并按成绩s_score降序排列

(3) 最后从上面的结果中筛选排名为第2名到第3名的记录

select *

from (

select

b.*,

a.c_id,

a.s_score,

row_number() over(partition by a.c_id order by a.s_score desc) as ‘rank’

from

score as a

left join student as b on a.s_id=b.s_id

) as info

where info.rank in (2,3);

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

解题思路:

(1) 需要的字段有课程名称和分数,因此需要连接course表和score表

(2) 统计各科成绩分数段人数,所以要先对课程编号进行group by

(3) 接着就要使用sum()函数和case when来进行分段

tips:也可以使用count(),但是count中else后面得是null不能是0

select

a.c_id,

a.c_name,

sum(case when b.s_score<=100 and b.s_score>=85 then 1 else 0 end) as ‘[100-85]’,

sum(case when b.s_score<85 and b.s_score>=70 then 1 else 0 end) as ‘(85-70]’,

sum(case when b.s_score<70 and b.s_score>=60 then 1 else 0 end) as ‘(70-60]’,

sum(case when b.s_score<60 then 1 else 0 end) as ‘(<60)’

from

course as a

left join score as b on a.c_id=b.c_id

group by a.c_id;

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

解题思路:

(1) 学生一定要包含所有学生,因此score中未包含所有学生,因此需要连接student表和score表

(2) 要查询每个学生的平均成绩,就需要对s_id进行group by

(3) 使用rank()函数对平均分排序和打名次

select

a.s_id,

avg(b.s_score) as avg_score,

rank() over(order by avg(b.s_score) desc) as ‘rank’

from

student as a

left join score as b on a.s_id=b.s_id

group by a.s_id;

24、查询各科成绩前三名的记录(不考虑成绩并列情况)

解题思路:

(1) 首先使用row_number()函数对score表中所有课程进行分组并对每门课程的所有学生分数进行排序

(2) 从上面的结果中筛选排序小于等于3的,则为各科成绩前三名

select *

from (

select

c_id,

s_id,

s_score,

row_number() over(partition by c_id order by s_score desc) as ‘row_number’

from score

) as info

where info.row_number<=3;

25、查询每门课程被选修的学生数

解题思路:

(1) 在score表中对c_id进行group by

(2) 用count()函数统计每个c_Id下的学生数

select c_id,count(s_id)

from score

group by c_id;

26、查询出只有两门课程的全部学生的学号和姓名

解题思路:

(1) 需要使用到的字段有课程和姓名,因此连接student表和score表

(2) 要计算每个学生的选课数,因此先对s_id进行group by

(3) 经过group by后,过滤条件为选课数量count(c_id)等于两门

select

a.s_id,a.s_name

from

student as a

left join score as b on a.s_id=b.s_id

group by a.s_id

having count(c_id)=2;

27、查询男生、女生人数

解题思路:

(1) 直接在student表中对性别s_sex进行group by

(2) 随后使用count()函数即可统计男生人数和女生人数

select

s_sex,count(s_id)

from

student

group by s_sex;

28、查询名字中含有”风”字的学生信息

解题思路:

(1) 在student表中操作即可

(2) 在筛选条件中使用模糊查询”%%”

select

*

from

student

where s_name like ‘%风%’;

29、查询1990年出生的学生名单

解题思路:

(1) 在student表中操作即可

(2) 筛选条件为出生日期s_birth的年份是1990,即可得到符合条件的学生

tips:这里会使用到year()函数,year(datetime)可以得到年份

select

*

from

student

where year(s_birth)=1990;

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

解题思路:

(1) 需要的字段包含有姓名和成绩,因此连接student表和score表

(2) 计算平均成绩需要先对学号进行group by

(3) 过滤条件为平均成绩avg(s_score)大于等于85分,这样借的到符合条件的成绩

select

a.s_id,a.s_name,avg(b.s_score) as avg

from

student as a

left join score as b on a.s_id=b.s_id

group by a.s_id

having avg(b.s_score)>=85;

31、查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同时,按课程号降序排列

解题思路:

(1) 在score表中操作即可

(2) 求每门课的平均成绩,需要先对课程编号进行group by

(3) 最后使用order by对平均成绩升序排序,平均成绩相同时按课程号降序排列

select

c_id,avg(s_score)

from

score

group by c_id

order by avg(s_score),c_id desc;

32、查询课程名称为”数学”,且分数低于60的学生姓名和分数

解题思路:

(1) 需要使用到的字段有课程名称、姓名和分数,因此要连接student、score和course这三表

(2) 筛选条件为课程名称c_name为”数学”,且分数s_score低于60

select

a.s_name,b.s_score

from

student as a

left join score as b on a.s_id=b.s_id

left join course as c on b.c_id=c.c_id

where c.c_name=’数学’ and b.s_score<60;

33、查询所有学生的课程及分数情况

解题思路:

(1) 题目中说的是所有学生,因此需要使用student表左连接score表(因为可能存在没有选课的学生)

(2) 需要的是每个学生的情况,因此先对学生编号进行group by

(3) 返回的字段格式为学生编号、语文成绩、数学成绩、英语成绩,因此要用到case when

select

a.s_id,

max(case when c.c_name=’语文’ then b.s_score else null end) as ‘语文’,– case when 当课程名字c_name是某门课时则得到这门课对应的成绩s_core

max(case when c.c_name=’数学’ then b.s_score else null end) as ‘数学’,

max(case when c.c_name=’英语’ then b.s_score else null end) as ‘英语’

— 因为group by要与select列一致,所以case when需要加修饰max

from

student as a

left join score as b on a.s_id=b.s_id

left join course as c on b.c_id=c.c_id

group by a.s_id;

34、查询任何一门课程成绩在70分以上的姓名、课程名称和分数

解题思路:

(1) 首先从score表中选出每个学生自己所选课程的成绩都在70分以上的学生编号

(2) 需要选出的字段有姓名、课程名称以及分数,因此连接student、score和course三张表

(3) 连接表的筛选条件为s_id含有第1步中的那些学生编号

select

a.s_name,

c.c_name,

b.s_score

from

student as a

left join score as b on a.s_id=b.s_id

left join course as c on b.c_id=c.c_id

where a.s_id in (

select

s_id

from

score

group by s_id

having min(s_score)>=70

);

35、查询学生不及格的课程并按课程号从大到小排列

解题思路:

(1) 在score表中操作即可

select

c_id,

s_id,

s_score

from

score

where s_score<60

order by c_id desc;

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/153427.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • 计算机信息计量单位中的1k代表多少字节,1k等于多少字节

    计算机信息计量单位中的1k代表多少字节,1k等于多少字节一、1K字节等于多少字节1KB=1024B,其中1024=2的10次方。字节(Byte/bait/n.)是计算机信息技术用于计量存储容量的一种计量单位,通常情况下一字节等于八位,也表示一些计算机编程语言中的数据类型和语言字符。国际单位制(SI)1KB=1024B;1MB=1024KB=1024*1024B。1B(byte,字节)=8bit;1KB(Kilobyte,千字节)=1000B…

  • javaee框架整合开发入门到实战源码_java底层框架

    javaee框架整合开发入门到实战源码_java底层框架kunJkunJ框架,是基于HK2框架的一个自实现注入框架,功能比较简单,重在探索依赖注入的实现原理。实现细节1.自定义3个注解,Access,Inject,Service2.在Servi

  • MySQL error 1396_Last

    MySQL error 1396_LastMySQL主备数据库切换报错1396处理方法,操作步骤如下:mysql>showslavestatus\G;***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:196.55.3.180Mas…

  • PHP artisan migrate 报错显示 could not find driver ,怎么办?

    PHP artisan migrate 报错显示 could not find driver ,怎么办?

  • 十大滤波算法总结

    十大滤波算法总结由于MPU6050的深入,我也学会了一些滤波算法,自己写了一些算法,收集了一些算法,供大家一起学习分享,我的代码都是经过反复试验,复制到Arduino中就能开跑的成品代码,移植到自己的程序中非常方便。而且都仔细研究了各个算法,把错误都修正了的,所以也算个小原创吧,在别人基础上的原创。1、限幅滤波法(又称程序判断滤波法)2、中位值滤波法3、算术平均滤波法4、递推平均滤波法(又称滑动平

  • eclipse环境配置

    eclipse环境配置eclipse环境配置

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号