SQL面试50题

1、 本文结合了B站up“陆小亮”的SQL面试50题教学视频进行整理，根据知识点进行分类，将相似题放到一起，并提供一些解题思路方便启发思考，如有错误欢迎指摘~

2、 使用的软件是Navicat，sql版本是5.7。

3、数据来源：

常见的SQL面试题：经典50题 - 知乎

https://zhuanlan.zhihu.com/p/38354000

SQL面试必会50题 - 知乎

https://zhuanlan.zhihu.com/p/43289968

四张关系表联结图：

【Like】

-- 1.查询姓“猴”老师的个数
select count(t_id) from teacher where t_name like '%猴%' 

– %代表任意字符
– ‘猴’% 名字以猴开始的；‘%猴’ 名字以猴结束的

-- 拓展1：所有老师中有多少种名字，使用distinct
select count(distinct t_name) from teacher
where t_name like '张%'
-- 拓展2：找出名字第二字是云的学生
select count(s_id) from student like '_%云'
-- 拓展3：找出名字都是2个字的学生
select count(s_id) from student like '__'

-- 2. 查询名字中含有「风」字的学生信息【Like】
select * from student where s_name like '%风%'

【group by、聚合函数】
1、在使用group by时，在select中选用的字段是要在group by中使用到的字段或者是统计函数用过的字段；
2、where函数后不能直接用聚合函数
3、聚合函数：sum\avg\count\max\min，经常与 SELECT 语句的 GROUP BY 子句的HAVING一同使用。

-- 3. 查询平均成绩大于60分的学生的学号和平均成绩 类似的题目（重点）
select s_id, avg(s_score) from score
group by s_id having avg(s_score) > 60

-- 4. 查询男生、女生人数
select s_sex, count(*) from student group by s_sex;

-- 5. 查询所有学生的学号、姓名、选课数、总成绩
select a.s_id , a.s_name, count(b.c_id), sum(b.s_score) 
from student as a
left join score as b on a.s_id = b.s_id
group by s_id,a.s_name

-- 拓展1：使查询里不出现NULL值
select a.s_id , a.s_name, count(b.c_id),
sum(case when b.s_score is NULL then 0 else b.s_score end)
from student as a
left join score as b on a.s_id = b.s_id
group by s_id,a.s_name

-- 6. 求每门课程的学生人数 
select co.c_id , co.c_name, count(distinct co.c_id)
from score sc inner join course co on co.c_id = sc.c_id
group by co.c_id, co.c_name

-- 7、统计每门课程的学生选修人数（超过5人的课程才统计）。
-- 要求输出课程号和选修人数，查询结果按人数降序排列，
-- 若人数相同，按课程号升序排列
select c_id, count(DISTINCT S_id) AS CNT from score
group by c_id having cnt >5
ORDER BY CNT ASC, C_ID DESC

-- 8 检索至少选修两门课程的学生学号
select s_id, count(c_id) from score
group by s_id
having count(c_id)>=2

# 9. 查询课程编号为02的总成绩
select sum(s_score), avg(s_score), count(distinct s_id) from score
where s_id = '02'
拓展1：根据不同课程编号分组来写并且降序：
select c_id sum(s_score), avg(s_score),
count(distinct s_id from score
group by c_id desc
拓展2：用group by写
select c_id, sum(s_score), avg(s_score),
count(distinct s_id) from score
group by c_id having c_id = '02'

-- 10.查询选修了全部课程的学生信息(有问题？)
-- 思路：1.根据学生编号去做groupby后去做统计他选了几门课，
-- 2.选修课程=3
select s_id, count(c_id) AS cnt
from score 
group by s_id
having cnt = (select count(c_id) from course)  -- 利用子查询计算出课程=3

# 11.检索至少选修两门课程的学生学号（不重要）
select s_id, count(c_id)
from score
group by s_id
having count(c_id)>=2

# 12.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （重点）
# 思路：找出选了不止一门课，且不同课的成绩相同的，否则会出现只选一门课的同学成绩也被取出
# 条件1：学生选课总数>1
# 条件2：满足1，且该学生的每门课程分数相等。
select * from score    
where s_id in 
(select s_id from score 
group by s_id
having max(s_score) = min(s_score) and
count(c_id)>0)

# 13. 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列（不重要）
select sc.c_id, co.c_name, avg(sc.s_Score) as avg_score
from score as sc inner join course co on
sc.c_id = co.c_id 
group by c_id
order by avg_score ASC, c_id DESC

# 14.查询出只有两门课程的全部学生的学号和姓名(不重点)
select st.s_id , st.s_name , count(sc.c_id) from student as st
inner join score as sc on st.s_id = sc.s_id
group by st.s_id, st.s_name
having count(distinct sc.c_id)=2

-- 15. 查询每门课程被选修的学生数(不重点)
select c.c_id , c.c_name , count(