SQL--查询没学过“xxx”老师课的同学，显示（学号、姓名）(使用with子句)

显示各个表信息

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);