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