create table c:\s2(sn c(5),sname c(10),age i,sex c(4))
insert into s2(sn,sname,age,sex) values ('95001','李勇',20,'男')
insert into s2(sn,sname,age,sex) values ('95002','刘晨',19,'女')
insert into s2(sn,sname,age,sex) values ('95003','王敏',18,'女')
insert into s2(sn,sname,age,sex) values ('95004','李立',19,'男')
create table c:\c2(cn i,cname c(20),teacher c(10))
insert into c2(cn,cname,teacher) values (1,'数据库','李红军')
insert into c2(cn,cname,teacher) values (2,'数学','刘任仁')
insert into c2(cn,cname,teacher) values (3,'信息系统','何丽')
insert into c2(cn,cname,teacher) values (4,'操作系统','赵军强')
insert into c2(cn,cname,teacher) values (5,'数据结构','贾振江')
insert into c2(cn,cname,teacher) values (6,'数据处理','张毛毛')
insert into c2(cn,cname,teacher) values (7,'C语言','李超')
insert into c2(cn,cname,teacher) values (8,'VC++','宝宝')
create table c:\sc2(sn c(5),cn i,grade n(5,2))
insert into sc2(sn,cn,grade) values ('95001',1,96)
insert into sc2(sn,cn,grade) values ('95001',2,85)
insert into sc2(sn,cn,grade) values ('95001',3,56)
insert into sc2(sn,cn,grade) values ('95002',2,94)
insert into sc2(sn,cn,grade) values ('95002',3,88)
insert into sc2(sn,cn,grade) values ('95002',1,60)
insert into sc2(sn,cn,grade) values ('95001',4,58)
insert into sc2(sn,cn,grade) values ('95001',5,79)
insert into sc2(sn,cn,grade) values ('95002',6,66)
insert into sc2(sn,cn,grade) values ('95003',1,88)
insert into sc2(sn,cn,grade) values ('95003',3,59)
insert into sc2(sn,cn,grade) values ('95003',6,63)
insert into sc2(sn,cn,grade) values ('95004',3,93)
insert into sc2(sn,cn,grade) values ('95004',6,89)
*!* 题目要求是把选修刘老师课程的女同学选课元组全部删去
*!* 要删除合符的记录，你就要会查出那些些记录符合，有2种常见写法
*!* 方法1
SELECT sc2.* FROM sc2,(SELECT cn FROM c2 WHERE teacher='刘任仁') t1,(SELECT sn FROM s2 WHERE sex='女') t2 WHERE AND sc2.sn=t2.sn
*!* 方法2
SELECT sc2.* FROM sc2 ;
INNER JOIN (SELECT cn FROM c2 WHERE teacher='刘任仁')t1 ON ;
INNER JOIN (SELECT sn FROM s2 WHERE sex='女')t2 ON sc2.sn=t2.sn
*!* 删除1
DELETE sc2 from sc2,(SELECT cn FROM c2 WHERE teacher='刘任仁') t1,(SELECT sn FROM s2 WHERE sex='女') t2 WHERE AND sc2.sn=t2.sn
*!* 删除2
DELETE sc2 from sc2;
INNER JOIN (SELECT cn FROM c2 WHERE teacher='刘任仁')t1 ON ;
INNER JOIN (SELECT sn FROM s2 WHERE sex='女')t2 ON sc2.sn=t2.sn
SELECT sc2
BROWSE
