SQL-查询没有学全所有课的同学，显示（学号、姓名）

表信息

SELECT * FROM student s;
SELECT * FROM teacher t;
SELECT * FROM course c;
SELECT * FROM student_core sc;

--解法1：（WITH子句）

----a.得到每个同学学过的课程数量
with s_no_sum as
 (select s.student_no s_no, count(sc.course_no) s_sum
    from student s, student_core sc
   where s.student_no = sc.student_no(+)
   group by s.student_no)
----b.得出没有学全课程的学生
select s.student_no, s.student_name
  from student s, s_no_sum
 where s.student_no = s_no_sum.s_no
   and s_no_sum.s_sum < (select count(*) from course)

--解法二：（having子句）

SELECT s.student_no,
       s.student_name
  FROM student  s,
       student_core sc
 WHERE s.student_no = sc.student_no(+)
 GROUP BY s.student_no,
          s.student_name
HAVING COUNT(sc.course_no) < (SELECT COUNT(*) FROM course c);