SQL——数据库实验一

create table student ( sno number primary key, sname varchar2(8) not null unique, sage int, ssex varchar2(8), sdept varchar2(10));create table course(cno number primary key,cname varchar2(10),cpno number,ccredit int);create table sc(sno number,cno number,primary key(sno,cno),grade smallint,foreign key(sno) REFERENCES student(sno),foreign key(cno) REFERENCES course(cno));alter table student add bloodtype varchar2(2);alter table student modify sdept varchar2(40);alter table student add constraint yueshu1 check(sage>15 and sage<30);alter table student drop constraint yueshu1;alter table student drop column bloodtype;create unique index stusnam4099 on student (sname);create index i_sc4099 on sc(sno,cno desc);drop index stusnam4099;drop index i_sc4099;insert into student(sno,sname,sage,ssex,sdept)values(200215121,'liyong',20,'m','cs');insert into student(sno,sname,sage,ssex,sdept)values(200215122,'liuchen',19,'f','cs');insert into student(sno,sname,sage,ssex,sdept)values(200215123,'wangmin',18,'f','ma');insert into student(sno,sname,sage,ssex,sdept)values(200215125,'zhangli',19,'m','is');insert into course(cno,cname,cpno,ccredit)values(1,'shujuku',5,4);insert into course(cno,cname,cpno,ccredit)values(2,'shuxue',null,2);insert into course(cno,cname,cpno,ccredit)values(3,'信息系统',1,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(6,'shujuchuli',null,2);insert into course(cno,cname,cpno,ccredit)values(7,'pascal',6,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='wangmin';update student set sage=sage+1 ;update sc set grade = 0 where sno in(select sno from student where sdept='cs');delete from student where sdept = (select sdept from student where sname='liuchen'); delete from sc where sno in (select sno from student where sdept = 'cs');