SQL 50面试题2021春招准备

视频链接：https://www.bilibili.com/video/BV1q4411G7Lw?p=35
题目链接：https://zhuanlan.zhihu.com/p/43289968
表的关系链接：https://zhuanlan.zhihu.com/p/38354000

总结：select后有函数的话，一定记得加group by
子查询时，外面的查询要的东西一定是在子查询的select 后跟着的。

1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号

select a.s_id, c.s_name, a.c_id, a.s_score as '01score', b.s_score as '02score'
from (select * FROM interview.score where c_id= '01') as a
inner join
(
SELECT * FROM interview.score where c_id= '02') as b
on a.s_id=b.s_id
inner join interview.student as c
on c.s_id=a.s_id
where a.s_score> b.s_score;

思路：如果一个表内含有这三列，

学号	课程号01的成绩	课程号02的成绩

那么代码就简单了

select * from table
where 课程号01的成绩 > 课程号01的成绩;

那如何合成出这个表？

select a.s_id, a.s_score as '01score', b.s_score as '02score'
from (select * FROM interview.score where c_id= '01') as a
inner join
(
SELECT * FROM interview.score where c_id= '02') as b
on a.s_id=b.s_id;

因为想知道学号对应的人名，所以也inner join了student表。
考核：inner join的使用

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

select a.s_id, student.s_name, a.avgScore
from 
(select s_id,AVG(s_score) as 'avgScore'
from score
group by s_id
having AVG(s_score) > 60) as a
inner join
student
on student.s_id=a.s_id;

不知道为啥having 后面写 avgScore 或 ‘avgScore’ 都会报错
只能写 AVG(s_score)

考核：AVG，group by，having

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

select a.s_id,s_name,count(c_id), sum(s_score)
from score a
inner join student s
on a.s_id=s.s_id
group by s_id;

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

SELECT count(t_id) 
FROM interview.teacher
where t_name like '猴%';

count(t_id) 会比 count (distinct t_name) 要好，因为系统运算会更快。

5、查询没学过“张三”老师课的学生的学号、姓名（重点：子查询）

正确答案：

select *
from student
where s_id not in
(SELECT st.s_id from score s
inner join course c
on s.c_id=c.c_id
inner join student st
on s.s_id=st.s_id
inner join teacher t
on c.t_id=t.t_id
where t_name= '张三') ;

注意1：where A (NOT) IN (Select A from table); select 后面的A 要跟 where后的A 保持一致的，不能说一个A，一个B的；而且where A 了，不能select 多个，如不能select A,B,C,D。只能select A。

注意2：此处不能用select s_id from table where t_name !='张三'； 因为会出现同时修了张三和李四课的A同学。系统剔除A同学上张三课程的那行数据后，A同学还有上李四课程的数据，那么最终结果还是会出现A同学的学号。所以错的。

6、查询学过“张三”老师所教的所有课的同学的学号、姓名（重点：子查询）

`

跟上一题差不多，把not in 改成 in

select *
from student
where s_id in
(SELECT st.s_id from score s
inner join course c
on s.c_id=c.c_id
inner join student st
on s.s_id=st.s_id
inner join teacher t
on c.t_id=t.t_id
where t_name='张三') ;

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

方法一：先让student和score两个表合并，这样就有学号，姓名和课程号了。合并两次，一次是c_id = 01，一次是 c_id=02。然后再合并。

select a.s_id, a.s_name, a.c_id,b.c_id from
(SELECT st.s_id, s_name, c_id FROM student st
inner join score s
on st.s_id=s.s_id
where c_id=01) as a
inner join 
(SELECT st.s_id, s_name, c_id FROM student st
inner join score s
on st.s_id=s.s_id
where c_id=02) as b
on a.s_id=b.s_id
;

易错点：
1、inner join 后忘记了 on a.s_id=b.s_id，导致合并后的表是出现两列s_id，并且两列不相等。
2、 where c_id = 01 and 02 错误的，因为不可能一个cell中既有01 也有02的。一个cell，只有一个值。

方法二：不把student与score两表合并，只是把score的01和02课程合并，然后再把student中的s_id和score中的s_id 相匹配。

select * from student
where s_id in 
(select a.s_id from 
(select * from score where c_id=01)as a
inner join 
(select * from score where c_id=02)as b
on a.s_id=b.s_id);

易错点：where A in (select A from table)

8、查询课程编号为“02”的总成绩（不重点）

select c_id,sum(s_score)
from score
where c_id=02;

易错点：当需要计算各个课程的总成绩，才需要group by。尽量思考用最优解。

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

正确答案：

select s_id,s_name
from student st
where s_id not in
(select s.s_id
from score s
where s_score>=60) ;

错误答案：（没有包含无成绩的学生，即没有包含s_score 是null的情况）

select s_id,s_name
from student st
where s_id in
(select s.s_id
from score s
where s_score<60) ;

10.查询没有学全所有课的学生的学号、姓名(重点)

SELECT st.s_id,s_name, count(c_id) FROM student st
left join score s
on s.s_id=st.s_id
group by st.s_id
having count(distinct c_id)< (select count(distinct c_id) from course)
;

易错点：1、可能存在某一门课程所有同学都没有上，所以不能看score里面的课，要看course里面的课。
2、可能存在有同学没有上过任何一门课，所以要用left join，不能用inner join。
3、having 后的聚合函数，最好跟其要对比的select后的聚合函数保持一致。

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

select distinct st.s_id, s_name from student st
inner join (
select c_id, s_id from score
where c_id in
(select c_id from score
where s_id=01)
And s_id !=01) as a
on a.s_id=st.s_id;

总结：1、and 可以外用 2、这个属于一对多 3、where A in 可等于or

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

若用where A in 会出现这种错误：答案包含A和B同学的情况。
因为where A in 表示or的，只要有一个相同都会被选出来。

01同学修的课	A同学修的课	B同学修的课
1，2，3	1，2，4	1，2

所以，课程完全相同=课程数量相同+没有不一样的课程

课程数量相同：用count
不一样的课程：用where A NOT IN

由于原数据中没有"上表"展示的A同学修1，2，4的情况，那我给07插入04课程的数据。

01同学选了01，02，03 课程
第一步：选出所学的课不在（01，02，03）的同学，然后排除；

select distinct s_id from score
where s_id not in(
select s_id from score
where c_id not in (
select c_id from score
where s_id=01));

第二步：剩下的同学必定选择了（01，02，03）门中的一门或两门或三门，所以要判断是否所选的课程数为3；

select distinct s_id from score
group by s_id
having count(distinct c_id)=
(select count(distinct c_id) from score
where s_id=01);

第三步：剔除01学生本身

select distinct s_id from score
where s_id !=01

把第一和第二步合起来时，用AND；
最终答案：

select distinct s_id from score
where s_id !=01
group by s_id
having count(distinct c_id)=
(select count(distinct c_id) from score
where s_id=01) and
 s_id not in(
select s_id from score
where c_id not in (
select c_id from score
where s_id=01));

13、查询没学过"张三"老师讲授的任一门课程的学生姓名 和47题一样（重点，能做出来）

select st.s_id,s_name
from student st
where st.s_id not in (
select s.s_id from score s
inner join course c on s.c_id=c.c_id
inner join teacher t on c.t_id=t.t_id
where t_name ='张三' );

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点） **

select s.s_id,s_name, count(s_score),avg(s_score)
from score s inner join student st on s.s_id=st.s_id
where s_score<'60'
group by s_id
having count(distinct c_id)>1;

16、检索"01"课程分数小于60，按分数降序排列的学生信息（和34题重复，不重点）**

select st.*,s.c_id,s.s_score from student st
inner join score s 
on st.s_id=s.s_id
where c_id=01 and s_score <60
order by s_score desc;

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点“行转列”)**

select s_id "学号",
MAX(case when c_id='01' then s_score else NULL END) as "语文",
MAX(case when c_id='02' then s_score else NULL END) AS "数学",
MAX(case when c_id='03' then s_score else NULL END) AS "英文",
avg(s_score) "平均成绩"
FROM interview.score
group by s_id
order by avg(s_score) DESC;

注：MAX(case when c_id='01' then s_score else NULL END) as "语文"
意思为，当课程号为01时，显示对应的成绩，没有成绩就显示NULL。因为它是属于一个属性，所以放在select后不是放在表名后。而且由于有group by在后面，所以一定要加个MAX, SUM, AVG 等。但是这里用MAX 更合适。

如果想要下图这种效果的，就好像BISM7206那个作业

其对应的代码为

Select s.s_id, s.c_id,s.s_score,b.avg_s_score from score as s
inner join
(select s_id, avg(s_score) as avg_s_score from score
group by s_id
) as b
on s.s_id=b.s_id
order by b.avg_s_score DESC;

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 (超级重点)**

select c.c_id "课程ID", c_name "课程名", max(s_score) "最高分" ,min(s_score) "最低分", avg(s_score) "平均分",
sum(case when s_score>=60 then 1 else 0 END) /count(s_id) "及格",
sum(case when s_score>=70 and s_score<80 then 1 else 0 END) / count(s_id) "中等",
sum(case when s_score>=80 and s_score<90 then 1 else 0 END) / count(s_id) "优良",
sum(case when s_score>=90 then 1 else 0 END) /count(s_id) "优秀"
FROM interview.score s
inner join course c
on s.c_id=c.c_id
group by c.c_id
;

易错点：我总忘了写 case when 里面的END
总结：如果遇到需要弄多个带条件的新属性，请记得用case when
该题中的sum(case when s_score>=60 then 1 else 0 END) 指：当满足大于60时，累计加一，否则加零。

19、按各科成绩进行排序，并显示排名(重点rank() )**

根据课程分组，对分数进行排序

rank() over(partition by subject order by score desc) as "排名" from table 1

其他两个函数的公式也一样的。

dense_rank() over(partition by subject order by score desc) as "排`名" from table 1

row_number() over(partition by subject order by score desc) as "排名" from table 1

select s_id, c_id, s_score, rank () over(partition by c_id order by s_score desc) as "排名"
from score;

20、查询学生的总成绩并进行排名（不重点）**

方法一：

select s_id "学号", sum(s_score) "总成绩", rank() over(order by sum(s_score)  desc) as "排名"
from score
group by s_id;

方法二：

select s_id "学号", sum(s_score) "总成绩"
from score
group by s_id
order by "总成绩" desc;

21 、查询不同老师所教不同课程平均分从高到低显示(不重点)**

角度1：以课程为主体，求平均分

select c.c_id,c.c_name,avg(s_score)
from course c
inner join score s
on s.c_id=c.c_id
group by s.c_id
order by avg(s_score) desc;

角度2：以老师为主体，算平均分

select t.t_id,t_name,avg(s_score)
from course c
inner join score s
on s.c_id=c.c_id
inner join teacher t
on t.t_id=c.t_id
group by t.t_id
order by avg(s_score) desc;

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（重要子查询）**

select *
from (select st.*, c_id, s_score, row_number() over(partition by c_id order by s_score desc) as pm
from score s
inner join student st
on s.s_id=st.s_id) a
where pm in (2,3);

最终的结果

23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称(重点“打标签”，连着18题一起复习）**

select c.c_id, c_name,
sum(case when s_score<60 then 1 else 0 end) as "[<60]",
sum(case when s_score<70 and s_score>=60 then 1 else 0 end) as "[70-60]",
sum(case when s_score<85 and s_score>=70 then 1 else 0 end) as "[85-70]",
sum(case when s_score>=85 then 1 else 0 end) as "[100-85]"
from score s
inner join course c
on s.c_id=c.c_id
group by c.c_id;

24、查询学生平均成绩及其名次（同19题，重点）**

select s_id, avg(s_score), rank() over(order by avg(s_score) DESC) as pm
from score
group by s_id
;

rank里的order by 后可以接函数，如这里的avg（）。

25、查询各科成绩前三名的记录（不考虑成绩并列情况）（重点 与22题类似）**

Select c_id, 
max(case when pm=1 then s_score else null end) as "第一名",
max(case when pm=2 then s_score else null end) as "第二名",
max(case when pm=3 then s_score else null end) as "第三名"
from (Select c_id,s_score, row_number() over(partition by c_id order by s_score desc) as pm
from score) a
group by c_id;

先子查询，再case when。

26、查询每门课程被选修的学生数(不重点)**

select s.c_id, c_name, count(s_id)
from score s
left join course c
on s.c_id=c.c_id
group by c_id;

or

select s.c_id, count(s_id)
from score s
group by c_id;

27、 查询出只有两门课程的全部学生的学号和姓名(不重点)**

select s_id, s_name
from student
where s_id in
(select s_id
from score
group by s_id
having count(distinct c_id)=2 );

or

select s.s_id, st.s_name 
from score s
inner join student st
on s.s_id=st.s_id
group by s.s_id
having count(distinct c_id)=2;

方案1：用where in+子查询
方案2：inner join

28、查询男生、女生人数(不重点)**

select s_sex, count(s_id)
from student
group by s_sex;

or
用case when来打标签

29 查询名字中含有"风"字的学生信息（不重点）**

select s_name
from student
where s_name like '%风%';

**30题无

31、查询1990年出生的学生名单（重点year）**

select *
from student
where YEAR(s_birth)=1990;

or

select *
from student
where s_birth between '1990-01-01' and '1990-12-31';

时间函数的链接：https://www.w3school.com.cn/sql/sql_dates.asp

32、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩（不重要）

select s.s_id,s_name,avg(s_score) as "平均分"
from score s
inner join student st
on s.s_id=st.s_id
group by s.s_id
having avg(s_score) >=85;

33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列（不重要）

select c_id,avg(s_score) as "平均分"
from score
group by c_id
order by "平均分" DESC;

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数（不重点）

select s.s_id,s_name,c_name,s_score
from score s
inner join student st
on s.s_id=st.s_id
inner join course c
on c.c_id=s.c_id
where s_score<60 and c_name="数学" ;

35、查询所有学生的课程及分数情况（重点）

select s_id,
max(case when c_name="语文" then s_score else null end) as "语文",
max(case when c_name="数学" then s_score else null end) as "数学",
max(case when c_name="英语" then s_score else null end) as "英语"
from score s
inner join course c
on s.c_id=c.c_id
group by s_id;

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数（重点）

select s.s_id,s_name, c_name, s_score
from student st
inner join score s
on st.s_id=s.s_id
inner join course c
on c.c_id=s.c_id
where s_score > 70;

37、查询不及格的课程并按课程号从大到小排列(不重点)

select s.s_id,s_name,s.c_id,c_name,s_score
from score s
inner join student st
on s.s_id=st.s_id
inner join course c
on c.c_id=s.c_id
where s_score<60
order by s.c_id DESC;

38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名（不重要）

select s.s_id, s_name, c_id,s_score
from score s
inner join student st
on s.s_id=st.s_id
where c_id=3 and s_score>80;

39、求每门课程的学生人数（不重要）

select c_id, count(distinct s_id)
from score
group by c_id;

40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩（重要top）无答案，自己写的

select *
from(SELECT st.s_id,s_name,s_score, rank()over(order by s_score DESC) as pm
from teacher t
inner join course c on t.t_id=c.t_id
inner join score s  on c.c_id=s.c_id
inner join student st on s.s_id=st.s_id
where t_name="张三" 
order by s_score DESC) a
where pm=1;

注意：不能用max() 和 limit ，因为当有并列第一时，它只会显示一条数据。
limit 默认第一行为0

41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （重点，无答案）

个人对这道题目的理解是，只要是成绩相同的学生都被提取出来。

SELECT distinct a.*
FROM score a
inner join score b
on a.c_id !=b.c_id and a.s_score=b.s_score
;

42、查询每门课成绩最好的前两名

SELECT *
FROM (select c_id,s_score,row_number() over(partition by c_id order by s_score DESC) AS pm
from score
order by c_id) a
where pm in (1,2);

43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列（不重要）

select c_id, count(s_id) as rs
from score
group by c_id
having count(s_id)>5
order by rs DESC,c_id;

44、检索至少选修两门课程的学生学号（不重要）

select s_id,count(c_id)
from score
group by s_id
having count(c_id)>=2;

45、 查询选修了全部课程的学生信息（重点划红线地方）

SELECT st.s_id, s_name,count(distinct s.c_id) as a
FROM student st
inner join score s
on st.s_id=s.s_id
group by st.s_id
having a=(select count(distinct c.c_id)
from course c);

46、查询各学生的年龄（精确到月份）

备注：ditediff (结束日期，开始日期) 函数返回两个日期之间的天数；
round() 四舍五入。 如 round(3.9) = 4
floor（) 向下取整数。如 floor(3.9) = 3

select s_id, s_birth, floor(datediff('2020-12-29',s_birth)/365)
from student;

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

正确的code

select distinct st.s_id, s_name
from student st
where st.s_id not in (select s.s_id 
from score s
inner join course c
on c.c_id=s.c_id
inner join teacher t
on t.t_id=c.t_id 
where t_name="张三") ;

48、查询下周过生日的学生

49、查询本月过生日的学生（无法使用week、date(now()）

50、查询下一个月过生日的学生