oracle中，关联表中查询出选了此老师的所有课的学员的小问题，在线等

[zgycsmb]

oracle中有四个表，
create table student (
sno varchar2(10) primary key not null,
sname varchar2(20),
sage number(2),
ssex varchar2(5)
) ;
insert into student values ('s01','刘红',18,'女');
insert into student values ('s02','刘亮',19,'女');
insert into student values ('s03','李先',17,'男');
insert into student values ('s04','明上',20,'女');
/*教师表*/
create table teacher (
tno varchar2(10) primary key,
tname varchar2(20)
) ;
insert into teacher values ('t01','s老师');
insert into teacher values ('t02','胡老师');
insert into teacher values ('t03','刘老师');

/*课程表*/
create table course (
cno varchar2(10),
cname varchar2(20),
tno varchar2(20)
) ;
insert into course values ('c01','计算机原理','t01');
insert into course values ('c02','算法','t01');
insert into course values ('c03','数据结构','t02');
insert into course values ('c04','英语','t03');
/*成绩表*/
create table sc2 (
sno varchar2(10),
cno varchar2(10),
score number(4,2)
) ;
insert into sc2 values ('s01','c01',89);
insert into sc2 values ('s01','c02',90);
insert into sc2 values ('s01','c03',92);
insert into sc2 values ('s02','c01',93);
insert into sc2 values ('s02','c02',91);
insert into sc2 values ('s02','c03',90);
insert into sc2 values ('s03','c01',99);
insert into sc2 values ('s03','c02',98);
insert into sc2 values ('s03','c03',96);
insert into sc2 values ('s04','c01',98);
insert into sc2 values ('s04','c03',96);


1.写一条sql查询学过 s老师所教的所有课的同学的学号、姓名；

目前写的sql会把s04号学员也会查出来，其实s04学员，只选了s老师的其中一门课，其实要查出来的结果应该是
s01 s02 s03 学员。
请大家帮助想下。

[nayi_224]

select t1.sno,
       t4.sname
  from sc2 t1, 
       course t2,
       teacher t3,
       student t4
 where t1.cno = t2.cno
   and t2.tno = t3.tno
   and t1.sno = t4.sno
 group by t1.sno,
       t4.sname
 having count(decode(t3.tname, 's老师', 1)) 
 = (select count(1) from course v1, teacher v2 where v1.tno = v2.tno and v2.tname = 's老师')
;