sql50题自练习持续更新（参考知乎）

常见的SQL面试题：经典50题

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

SQL面试必会50题

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

【数据分析】- SQL面试50题 - 跟我一起打怪升级 一起成为数据科学家

https://www.bilibili.com/video/av55971363?p=3

全文题目及答案均参考知乎以上链接，还有b站

---

学生表：
Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
课程表：
Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
教师表：
Teacher(t_id,t_name) –教师编号,教师姓名
成绩表：
Score(s_id,c_id,s_s_score) –学生编号,课程编号,分数

测试数据：

---

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

从score表中将01和02课程的成绩分别提取出来成为两张子表，这俩张表再通过s_id进行join

s_id  01课程的成绩 02课程的成绩

01课程的成绩：select * from Score where c_id = '01' 

select a.s_id "s_name",a.s_score "01 score",b.s_score "02 score" from
(select * from Score where c_id = '01') as a
JOIN
(select * from Score where c_id = '02') as b
ON a.s_id = b.s_id
where a.s_score > b.s_score

---

2-1、查询平均成绩大于60分的学生的学号和平均成绩（简单，第二道重点）

-- 2、查询平均成绩大于60分的学生的学号和平均成绩（简单，第二道重点）
select s_id,avg(s_score)
from Score
GROUP BY s_id
HAVING avg(s_score)>60;

---

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

-- 3、查询平均成绩小于60分的学生的学号和平均成绩
select s_id,avg(s_score)
from Score sco
group by s_id
 HAVING avg(s_score)<60

---

 3、查询所有学生的学号、姓名、选课数、总成绩（不重要）

-- 3、查询所有学生的学号、姓名、选课数、总成绩（不重要）
select stu.s_id,stu.s_name,count(DISTINCT s.c_id),sum(s.s_score)
from Student as stu
JOIN Score as s
on stu.s_id = s.s_id
group by stu.s_id

 

---

 4、查询姓“猴”的老师的个数（不重要）

-- 4、查询姓“张”的老师的个数（不重要）
select count(DISTINCT t_name)
from Teacher 
where t_name like '张%'

---

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

找到“张三”老师对应的t_id,拿t_id去Course表里找这个t_id教过的课程号c_id,从成绩表中去排除没有考过这门课c_id的学生的s_id，再到student表中拿s_name

通过以上拿到了张三老师教的课程的课程号，当一个学生考过的所有课程的课程号里面没有这个c_id = 2时，说明该学生没有上过张三老师的课。

思路1：需要用过集合运算中的并集运算：某学生考过的课程号集合 与 02 求交集，若交集为空，则为所求学生。

思路2：在score表中将考过c_id 为02的课程的学生s_id取出来，然后学生s_id没在这个集合里就是没考过02课程的，也就是没有上过张三老师课的。（不考虑有同学上了张三老师的课，但是没有去考试）

--  5、查询没学过“张三”老师课的学生的学号、姓名（重点）
SELECT s_id,s_name FROM Student
where s_id not IN
(
-- 查出考过张三老师所带课程的学生的s_id
SELECT s_id from Score WHERE c_id =
	(
		SELECT c_id from Course where t_id =
		(
			SELECT t_id from Teacher where t_name = '张三'
		)
	)
-- 
)

---

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

(1)查出张三老师对应的老师编号t_id

(2)查出这个t_id所代课程的课程号c_id

(3)查出考过这门课程c_id的学生号s_id

SELECT s_id,s_name from Student WHERE s_id 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 = '张三'
			)
	)
)

---

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

拼接表：s_id   01成绩  02成绩 (在Score表的内部进行)

select s_id,s_name from Student where s_id in
(
SELECT a.s_id FROM(
(SELECT *  from Score where c_id = '01')  a
JOIN 
(SELECT * from Score where c_id = '02')  b
on a.s_id = b.s_id)
)

 

---

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

---

 9、查询所有课程平均成绩小于60分的学生的学号、姓名

！！！但是以上写法漏掉了总成绩为0的王菊同学 

所以要把>=60分的同学s_id找出来，然后取not  in

---

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

思路：找出学了所有课程的学生s_id，再取 not in

如何找出学了所有课程的学生呢？

思路：要清楚课程都有哪些？查出所有的c_id，然后用每个c_id去join其他c_id的子表

思路：求出c_id的个数，然后把Student表与Score表进行join，并计算join后每个学生所考试的课程门数，门数小于总课程数即为所求。

！！！由于用的s_score来求count是不对的，因为03号学生三科成绩都为80分，所以求出来的count=1，就误纳入了结果集，注意应该有用客观的字段，比如c_id 

---

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

老是想到集合，但我不熟练集合操作..

思路：查出01学生所学的所有课程；

          某学生学过的课要是在01学生学过的课的集合中，就是所求。

---

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

 

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

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