create table Student(
Sno VARCHAR(15) PRIMARY KEY,
Sname VARCHAR(8),
Sage INT,
Ssex CHAR(2),
Sdept CHAR(20));

create table course(
cno varchar(10) primary key,
cname varchar(10),
cpno varchar(10),
ccredit int,
foreign key(cpno)references course(Cno));

create table sc(
Sno VARCHAR(15),
Cno varchar(10),
Grade int,
primary key(Sno,Cno),
foreign key(Sno) references Student(Sno),
foreign key(Cno) references course(Cno));

alter table student ADD BloodType char(2);
alter table student MODIFY (Sdept varchar2(40));
alter table student add CONSTRAINT ck_sage check(sage>15 and sage<30);
alter table student DROP CONSTRAINT ck_sage;
alter table student DROP (BloodType);

create unique index stusnam0352 on student(Sname);
create unique index i_sc0352 on sc(sno ASC,cno DESC);

drop index stusnam0352;
drop index i_sc0352;

Insert Into Student(Sno,Sname,Ssex,Sage,Sdept)
Values(‘200215121’,‘李勇’,‘男’,20,‘CS’);
Insert Into Student(Sno,Sname,Ssex,Sage,Sdept)
Values(‘200215122’,‘刘晨’,‘女’,19,‘CS’);
Insert Into Student(Sno,Sname,Ssex,Sage,Sdept)
Values(‘200215123’,‘王敏’,‘女’,18,‘MA’);
Insert Into Student(Sno,Sname,Ssex,Sage,Sdept)
Values(‘200215125’,‘张立’,‘男’,22,‘IS’);

insert into Course(Cno,Cname,Cpno,Ccredit) values(‘2’,‘数字’,null,‘2’);
insert into Course(Cno,Cname,Cpno,Ccredit) values(‘6’,‘数据处理’,null,‘2’);
insert into Course(Cno,Cname,Cpno,Ccredit) values(‘7’,‘PASCAL语言’,‘6’,‘4’);
insert into Course(Cno,Cname,Cpno,Ccredit) values(‘4’,‘操作系统’,‘6’,‘3’);
insert into Course(Cno,Cname,Cpno,Ccredit) values(‘5’,‘数据结构’,‘7’,‘4’);
insert into Course(Cno,Cname,Cpno,Ccredit) values(‘1’,‘数据库’,‘5’,‘4’);
insert into Course(Cno,Cname,Cpno,Ccredit) values(‘3’,‘信息系统’,‘1’,‘4’);

insert into SC(Sno,Cno,Grade) values(‘200215121’,‘1’,‘92’);
insert into SC(Sno,Cno,Grade) values(‘200215121’,‘2’,‘85’);
insert into SC(Sno,Cno,Grade) values(‘200215121’,‘3’,‘88’);
insert into SC(Sno,Cno,Grade) values(‘200215122’,‘4’,‘90’);
insert into SC(Sno,Cno,Grade) values(‘200215122’,‘3’,‘80’);

update student set sage=20 where SNAME=‘王敏’;
update student set sage=sage+1;
update sc set Grade=0 where Sno in(select sno from student where sdept=‘CS’);

delete from SC where Sno in(select Sno from Student where Sdept=‘CS’);
delete from student where sdept in(select sdept from student where Sname=‘刘晨’);

drop table sc;
drop table student;
drop table course;

commit work;