+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 01 | 赵雷 | 1990-01-01 | 男 |
| 02 | 钱电 | 1990-12-21 | 男 |
| 03 | 孙风 | 1990-05-20 | 男 |
| 04 | 李云 | 1990-08-06 | 男 |
| 05 | 周梅 | 1991-12-01 | 女 |
| 07 | 郑竹 | 1989-07-01 | 女 |
+------+--------+------------+-------+
# *8、查询没学过"张三"老师授课的同学的信息
-- 张三老师教的课
select c.* from course c left join teacher t on t.t_id=c.t_id where
t.t_name="张三"
-- 有张三老师课成绩的 st.s_id
select sc.s_id from score sc where sc.c_id in (select c.c_id from course
c left join teacher t on t.t_id=c.t_id where t.t_name="张三")
-- 不在上面查到的 st.s_id 的学生信息,即没学过张三老师授课的同学信息
select st.* from student st where st.s_id not in(
select sc.s_id from score sc where sc.c_id in (select c.c_id from
course c left join teacher t on t.t_id=c.t_id where t.t_name="张三")
)
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 06 | 吴兰 | 1992-03-01 | 女 |
| 08 | 王菊 | 1990-01-20 | 女 |
+------+--------+------------+-------+
# *9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select st.* from student st
inner join score sc on sc.s_id = st.s_id
inner join course c on c.c_id=sc.c_id and c.c_id="01"
where st.s_id in (
select st2.s_id from student st2
inner join score sc2 on sc2.s_id = st2.s_id
inner join course c2 on c2.c_id=sc2.c_id and c2.c_id="02"
)
或
select st.*
from student st
inner join score s on st.s_id = s.s_id and s.c_id = '01'
inner join score s2 on st.s_id=s2.s_id and s2.c_id='02';