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 学员。
请大家帮助想下。
