-- Create table
create table S
(
SNO VARCHAR2(15) not null,
SNAME VARCHAR2(4)
)
INSERT into S values('1','ddd');
INSERT into S values('2','hwj');
INSERT into S values('3','jt');
-- Create table
create table C
(
CNO VARCHAR2(15) not null,
CNAME VARCHAR2(4),
CTEACHER VARCHAR2(4)
)
delete from C
INSERT into C values('1','语文','黎明');
INSERT into C values('2','数学','刘德');
INSERT into C values('3','体育','张学');
-- Create table
create table SC
(
SNO VARCHAR2(15) not null,
CNO VARCHAR2(4),
SCGRADE VARCHAR2(4)
)
INSERT into SC values('1','1','70');
INSERT into SC values('1','2','80');
INSERT into SC values('1','3','50');
INSERT into SC values('2','1','60');
INSERT into SC values('2','2','70');找出没选过“黎明”老师的所有学生姓名。
create table S
(
SNO VARCHAR2(15) not null,
SNAME VARCHAR2(4)
)
INSERT into S values('1','ddd');
INSERT into S values('2','hwj');
INSERT into S values('3','jt');
-- Create table
create table C
(
CNO VARCHAR2(15) not null,
CNAME VARCHAR2(4),
CTEACHER VARCHAR2(4)
)
delete from C
INSERT into C values('1','语文','黎明');
INSERT into C values('2','数学','刘德');
INSERT into C values('3','体育','张学');
-- Create table
create table SC
(
SNO VARCHAR2(15) not null,
CNO VARCHAR2(4),
SCGRADE VARCHAR2(4)
)
INSERT into SC values('1','1','70');
INSERT into SC values('1','2','80');
INSERT into SC values('1','3','50');
INSERT into SC values('2','1','60');
INSERT into SC values('2','2','70');找出没选过“黎明”老师的所有学生姓名。
SELECT s.sname FROM s WHERE s.sno NOT IN
(SELECT sc.sno FROM sc,c
WHERE sc.cno=c.cno AND c.cteacher='黎明');