【数据分析】- SQL面试50题第五、六题

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

SELECT 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 = "张三"
			)
		)
	)
;

解题思路：先将学过张三老师课程的同学查询出来，再用NOT IN进行排除。

1、先在teacher表中找出张三老师的教师编号；

2、再去course表里找张三老师的课程编号；

3、再去score表里找考过张三老师课程的同学有哪些；

4、之后排除。

---

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

SELECT st.s_id, st.s_name, c.c_name, t.t_name,s.s_score
FROM student AS st
INNER JOIN score AS s ON s.s_id = st.s_id
INNER JOIN course AS c ON c.c_id = s.c_id
INNER JOIN teacher AS t ON t.t_id = c.t_id
WHERE t.t_name = '张三'
ORDER BY st.s_id;

解题思路：

将过滤条件先写上，再通过关联表进行慢慢查询子结果。