SQL面试50题

注：此笔记是观看B站陆小亮老师的SQL面试50题的视频后记录，部分内容不完整

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

select a.s_id ,c.s_name, a.s_score "01",b.s_score "02" from 
(
select s_id,c_id, score from score where c_id='01'
)as a
inner join
(
select s_id,c_id, score from score where c_id='02'
) as b on a.s_id=b.s_id
inner join student as st on c.s_id = st.s_id
where a.s_score > b.s_score

 

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

select s_id, avg(s_score)
from score
group by s_id having avg(s_score)>60

 

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

select a.s_id, a.s_name, count(b.c_id),
sum(case when b.score is NULL then 0 else b.score end)
from student as a
left join score as b a.s_id = b.s_id
group by s_id, a.s_name,

 

注：
存在null值时使用case when；
select 后最好是接group by 后的字段，避免不同数据库使用时出现问题；
问：
什么是左连接与其他连接的区别？

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

select count(t_id)
from teacher 
where t_name like '张%'
select count(distinct t_name)
from teacher 
where t_name like '张%'

 

注:
主要是考察like和%的用法；
注意有的场合使用distinct；

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

selet s_id, s_name from student
where s_id not in (
select s_id from score
where c_id = (
select c_id from course
where t_id=(
select  t_id from teacher
where t_name = '张三 '
)))


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

 

注：
实际使用注意嵌套不要太多

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

select st.s_id st.s_name
from student as st
inner join score as s on s.s_id = st.s_id
inner join course as c on s.c_id = c.c_id
inner join teacher as t on t.t_id = c.t_id
where t.t_name ='张三'order by st.s_id

 

注：
最好先使用过滤条件尤其是在大表间关联；

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

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

 

注：
尽量不使用子查询（实际场景可能使用临时表来代替子查询）
null取长度依然是null

8、查询课程编号为“02”的总成绩（不是重点主要考察sum、avg、count）

select sum(s_score), avg(s_score), count(s_score), count(distinct s_id) from score
where c_id='02'
select sum(s_score), avg(s_score), count(s_score), count(distinct s_id) from score
group by c_id having c_id='02'

 

9、查询所有成绩小于60分的学生的学号姓名（参考题目二）

思：

1. 得出同学课程成绩小于60 分的课程数
2. 统计同学总共学了几门课

select a.sid, t.s_name
from 
(
select s_id, count(c_id) as cnt
from score 
where s_score<60group by s_id
)as a
inner join
(
select s_id, count(c_id) as cnt from score
group by s_sid
)as b on a.s_id = b.s_id
inner join student as t on a.s_id = t.s_id
where a.cnt = b.cnt

 

10、查询没有学全所有课的学生的学号、姓名（重点）

select s_id, s_name from student
where s_id in(
select s_id from score
group by s_sid having count(distinct c_id)<(select count(distinct c_id) from course)
)
此方法不完整（漏了一门课都没选的学生）
select st.*, sc.*
from student as st
left join score as sc on st.s_id = sc.s_id
group by st.s_id having count(distinct sc.c_id) < (select count(distinct c_id) from course)

 

注：
主要是having后面子句的书写要注意

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

select s_id, s_name from student
where s_id in(
select distinct s_id, from score
where c_id in(
select c_id from score
where s_id = '01'
) and s_id != '01'
)
select a.s_id, a.s_name from student as a
inner join(
select distinct s_id, from score
where c_id in(
select c_id from score
where s_id = '01'
) and s_id != '01'
) as b on a.s_id = b.s_id

 

注：
in的速度会相对慢一些，可以使用连接

12、查询和“01”号同学所学课程完全相同的其他同学的学号（重点）

思：
1、选出学了01 or 02 or 03课程的同学的成绩记录
2、计算同学的选课数目是否为3

select s_id from score
where s_id in(
select s_id from sore 
where c_id in(
select c_id from score where s_id = '01'
) and s_id!='01'group by s_id having count (distinct c_id) = (select count(distinct c_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')

-- 不合适
select * from student
where s_id in (
select s_id from score 
group by s_sid count（distinct c_id）= (select count(distinct c_id) from score where s_id = '01')
)
and s_id not in(
select distinct s_id from score 
where c_id not in (
select s_id from score
where s_id = '01'
)
)

 

注：

15、查询两门及其以上不合格的同学的学号姓名及其平均成绩

select a.s_id, a.s_name, avg(s_score)
from student as a
inner join score as b on a.s_id = b.s_id
where s_id in （
select s_id from score
where s_score < 60group by s_id having count(distinct c_id) >= 2
）
group by a.s_sid, a.s_name

-- 没选课的呢？

 

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

select t.* from student as t
inner join score as s on t.s_id = s.s_id
where s.c_id = '01' and s.s_score < 60 
order by s.s_score desc

 

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩（重点）

select s.s_id, s.c_id, s.s_score,b.avg_s_sore 
from score as s
inner join
(
select s_id, avg(s_score) from score
group by s_id
) as b on s.s_id = b.s_id
order by avg(s_score) desc

-- 结果不美观
select s_id "学号",
MAX(case when c_id='01' then s_score else null end) "语文", 
MAX(case when c_id='02' then s_score else null end) "数学",
MAX(case when c_id='03' then s_score else null end) "英语", 
avg(s_score) "平均成绩"from score 
group by s_id
order by avg(s_score) desc

 

注：
max、case、avg、group by 、order by

18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID,课程name,最高分，最低分，平均分，及格率，中等率，优良率，优秀率（及格60，中等70-80，优良80-90，优秀90）（重点）

select s.c_id, 
c.c_name,
max(s.s_score),
min(s.s_score),
avg(s.s_score),
sum(case when s.s_score >= 60 then 1 else 0 end)/count(s_id) "及格率"，
sum(case when s.s_score >= 70 and s.s_score <= 80 then 1 else 0 end)/count(s_id) "中等率"，
sum(case when s.s_score >= 80 and s.s_score <= 90 then 1 else 0 end)/count(s_id) "优良率"，
sum(case when s.s_score >= 90 then 1 else 0 end)/count(s_id) "优秀率"from score as s
innner join course as c on s.c_id = c.c_id
group by c_id 

 

19、按各科成绩进行排序，并显示排名（重点row_number）

rank()
dense_rank()
row_number()
https://yq.aliyun.com/articles/593698

 

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

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

 

21、查询不同老师所教的不同课程平均分从高到低显示(重点，视频材料不太好，自己课后练习)

1. 以课程为主体求平均分
2. 以老师为主体求平均分

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

 

22、查询所有课程的成绩第二名到第三名的学生信息及该课程信息

select * 
from (
select st.s_id, st.s_name, st.s_birth,st.s_sex,c_id,s_core,row_number () over(partition by c_id order by s_core desc) m
from score sc inner join student st on sc.s_id = st.s_id) a
where m in (2,3)

 

23、使用分段100-85，85-70，70-60，<60来统计各科的成绩，分别统计各分数段人数，课程ID和课程名称（重点）

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

 

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

select s_id, avg(s_score),row_number () over (order by avg(s_score) desc)
from score
group by s_id

 

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

select c.c_id, c.c_name, count(distinct sc.s_id)
from score  as sc
inner join course as  c on sc.c_id=c.c_id
group by c.c_id, c.c_name

 

注：
count不会计算null

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

select s.s_id, s.s_name
from student as s 
inner join score as sc on s.s_id = sc.s_id
group by s.s_id,s.s_name having count(distinct sc.c_id)=2
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
)

 

28、查询男生女生人数

select s_sex,count(s_sex) from student 
group by s_sex                                                        
select 
sum(case when s_sex='男' then 1 else 0 end) "男生个数"，
sum(case when s_sex='女' then 1 else 0 end) "女生个数"from student

-- 要是用count时候else后要使用null

 

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

select * 
from student 
where s_name like "%风%"

 

问：
通配符共有有哪些

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

select* 
from student
where year(s_birth)='1990'

select MONTH('2019-09-27')

select CURDATE()

 

注：
主要考察时间的使用具体使用有哪些

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

-- 自己答案:select sc.s_id, st.s_name, avg(sc.s_score) as avg_score,
from score as sc
inner join student as st on sc.s_id = st.s_id
group by sc.s_id having avg_score>=85

-- 视频答案:select a.s_id, a.s_name,b.avg_score from student as a
inner join 
(
select s_id, avg(s_score) as avg_score from score
group by s_id having avg_score >=85
)as b on a.s_id = b.s_id

 

注：
having的使用方法以及其与where的区别

33、查询每门课程的平均成绩、结果按平均成绩升序排序、平均成绩相同，按课程号降序排序

select s.c_id, c.c_name,avg(s.s_score) as avg_score
from score as s
inner join cource as c on s.c_id = c.c_id
group by s.c_id
order by avg_score asc, s.c_id desc

 

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

-- 自己的答案select s.s_id, s.s_name, t.c_score
from student as s
inner join
(
select s_id, s_score from score
where c_id = (select c_id from course where c_name="数学")
and s_score<60
) as t on s.s_id = t.s_id

-- 视频答案

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

 

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

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

 

问：
max的用法？
此处不用max,而将下方的group by换成order by是否可以？
注：
用case when常和统计函数一起，此出如果没有max,会和下方的group by存在问题

36、查询课程成绩在70分以上课程名称，分数和学生姓名

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

 

37、查询不及格的课程并按照课程号的大小排列

-- 自己的答案select  sc.c_id, sc.s_score, c.c_name
from score as sc
inner join course as c on sc.c_id = c.c_id
where sc.s_score < 60

-- 视屏的答案

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

 

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

-- 自己的答案select st.s_id, st.s_name, sc.s_score, c.c_name 
from student as st
inner score as sc on st.s_id = sc.s_id
inner course as c on sc.c_id = c.c_id
where sc.c_id = '03' and sc.s_score > 80order by sc.s_score

 

39、求每门课程的学生人数

-- 自己的答案select c_id, count(distinct s_id) from score 
group by c_id


-- 视屏答案select c.c_id, c.c_name, count(distinct s.s_id) from score as s
inner join course as c on c.c_id = s.c_id
group by c.c_id, c.c_name

 

40、查询选修张三老师所受课程的学生中的成绩最高的学生姓名及其成绩

-- 自己的答案select st.s_id, st.s_name, max(sc.c_score) as max_score, c.c_name, t.t_name
from student as st
inner join score as sc on st.s_id = sc.s_id
inner join course as c on sc.c_id = c.c_id
inner join teacher as t on  c.t_id = t.t_id
where t.t_name = "张三"group by c.c_id


-- 视屏
没用max。最后用了limit过滤第一条记录，未考虑一个老师教授多门课程的情况
select st.s_id, st.s_name, sc.c_score as max_score, c.c_name, t.t_name
from student as st
inner join score as sc on st.s_id = sc.s_id
inner join course as c on sc.c_id = c.c_id
inner join teacher as t on  c.t_id = t.t_id
where t.t_name = "张三"order by s.s_score desc limit 0,1

 

问：
limit的用法？

41、查询不同的课程成绩相同的学生的学生编号、课程编号和学生成绩（重点）

select s_id from
(
select from score
group by s_id,s_score
)as a
group by s_id having count(s_id)=1

select s_id from
(
select b.s_id, b.s_score from score as b
inner join
(select s_id fron score 
group by s_id having count(distinct c_id)>1
) as c on b.s_id = c.s_id
group by b.s_id,b.s_score
) as a
group by s_id having count(s_id)=1

select b.s_i,
from score as b
inner join 
(
select s_id from score 
group by s_id having count(distinct c_id)>1
) as c on b.s_id = c.s_id

 

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

select c_id count(distinct s_id) as cnt from score
group by c_id having cnt>5order by cnt desc, c_id asc

 

注：
group后用having

44、检索至少选修两门课程的学生学号

select s_id, count(distinct c_id) as cnt
from score 
group by s_id  having cnt>=2

 

45、查询选修了全部课程的学生信息

select  * 
from student as st
inner join score as sc on st.s_id = sc.s_id
where count(distinct c_id) = (select count(c_id) from course)
gorup by sc.s_id



select s_id, count(distinct c_id) as cnt from score
group by s_id having cnt = (select count(c_id) from course)

 

46、查询各学生年龄

select s_id, s_birth, round(DATEDIFF('2019-11-24', s_birth)/365） from student

 

注：
关于时间处理函数以及取整函数的使用

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

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

 

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

select week('2020-01-01',1)

select now()
select date(now())

week(出生日期，1)=week(现在的日期，1)+1

select* from student where week(s_birth,1)=week(date(now()),1+1)

select* from student where week(concat('2020-',substring(s_birth,6,5)),1)=week('2019-05-15',1)+1

不太准确

 

49、查询本月过生日的人

select * from student
where month(s_borth) = month(now())

-- 注意month这个函数的使用

 

50、下个月过生日的同学

 

select * from student where month(s_birth) = month(date(now()))+1

-- 12月存在问题待修改