表信息
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);