显示各个表信息
SELECT * FROM student s;
SELECT * FROM teacher t;
SELECT * FROM course c;
SELECT * FROM student_core sc;
--1. 查询没学过“xxx”老师课的同学,显示(学号、姓名)
----a.查看学生学过的课以及该门课的授课老师(使用with子句)
with s_t as(
select sc.student_no stu_no, t.teacher_name t_name
from student_core sc, course c,teacher t
where sc.course_no = c.course_no
and c.teacher_no = t.teacher_no)
----b.连接上学生表并筛选出学过该老师课程的学生 学号
/*
select s.student_no s_no from student s ,s_t where s.student_no=s_t.stu_no and s_t.t_name='xxx' group by s.student_no
*/
----c.从学生表中除去学过该老师课程的学生并得到最终结果
select s.student_no, s.student_name
from student s
where s.student_no not in (select s.student_no s_no
from student s, s_t
where s.student_no = s_t.stu_no
and s_t.t_name = 'xxx'
group by s.student_no);