sql统计各科成绩大于平均分的人_数据分析师SQL面试必备50题

以下是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) 最后从stude