数据库系统概论,第五版,教程sql代码.doc

1.0create trigger sc_tafter update of grade on screferencign oldrow as oldtuplenewrow as newtuplefor each rowwhen(newtuple.grade=1.0*oldtuple.grade)insert into sc_u(sno,cno,oldgrade,newgrade)values(oldtuple.sno,o,oldtuple.grade,newtuple.grade)/* 触发器 */2.0begin transaction读取账户甲的金额 belance;balance=balance-amount;if(balance0)then打印金额不足,不能转账;rollback;else 读账户乙的余额BALANCE1;balance1=balance1+amount;写回 balance1;commit;3.0create database student;use studentcreate table stu(Sno char(11) Primary key, Sname char(20) unique, Ssex char(2), Sage SMALLINT, Sdept char(20) ) create table course (Cno char(4) PRIMARY KEY, Cname char(40) NOT NULL, Cpno CHAR(4), Ccredit SMALLINT, FOREIGN KEY(Cpno) references course(Cno) ) create table sc (Sno char(11), Cno char(4), Grade SMALLINT, PRIMARY KEY(Sno,Cno), FOREIGN KEY(Sno) references stu(Sno), FOREIGN KEY(Cno) references course(Cno) ) select *from course;4.0/* 6.0 建立下面二个关系模式 */create database work;use workcreate table worker( Wno char(10), Wname char(3), age smallint, job char(3), Wmoney SMALLINT, Wpub char(5), primary key(Wno), foreign key(Wpub) references club(Wpub), check(age=(select COUNT(fid) from female)+(select COUNT(mid) from male) ); 5.0create database homewark;use homewarkcreate table s( sno char(5) primary key, sname char(30), status smallint, city char(20) ); select * from s; create table p( pno char(5) primary key, pname char(10), color char(2), weight smallint ); select * from p; create table j( jno char(2) primary key, jname chAR(15), city char(10) ); select * from j; create table spj( sno char(2), pno char(2), jno char(2), qty smallint, primary key(sno,pno,jno) ); select * from spj; select distinct sno from spj where jno=j1; select sno from spj where pno=p1 and jno=j1; select sno from spj,p where spj.pno=p.pno and jno=j1 and color=红; select distinct sno from spj where pno=p1 and pno in ( select pno from p where color=红); select jno from spj,s,p where spj.pno=p.pno and spj.sno=s.sno and color=红 and city!=天津; select jno from spj where pno in ( select pno from p where color=红) and sno in ( select sno from s where city!=天津); select jno from spj where sno=s1; select pname,qty from p,spj where spj.pno=p.pno and spj.jno=j2; select distinct p.pno from s,spj,p where s.sno=spj.sno and spj.pno=p.pno and city=上海; create view pro1 as select sno,pno,qty from spj,j where spj.jno=j.jno and jname=三建; select distinct pno,qty from pro1; select * from pro1 where pno=p1;6.0select sname,snofrom stu;select*from stuorder by Sdept,Sage desc;select COUNT(distinct sno)from sc;select COUNT(*)from stu;select cno,COUNT(sno)from scgroup by Cno;select stu.*,sc.*from stu,scwhere stu.sno=sc.sno;select snamefrom stuwhere Sno in(select Sno from sc where Cno=2); select sname,sage from stu where Sageany(select Sage from stu where Sdept=cs) and Sdeptcs; select sno,sname,sdept from stu where not exists (select* from sc where Sno=stu.Sno and Cno=1); insert into stu(Sno,Sname,Ssex,Sdept,sage) values(201215126,张程,男,cs,18); select* from stu; insert into sc(Sno,Cno) values(201215125,1); select* from sc; UPDATE stuset Sage=22where Sno=201215121;select* from stu; update stu set Sage=Sage+1; select* from stu; delete from stu where Sno=201215126; select* from stu; create view is_stu as select sno,sname,sage from stu where Sdept=is; select* from is_stu; create view is_s1(sno,sname,grade)asselect stu.Sno,sname,gradefrom stu,scwhere Sdept=is and stu.Sno=sc.Sno and sc.Cno=1;select*from is_s1;7.0create table s( sno char(5) primary key, sname char(30), status smallint, city char(20) ); select * from s; create table p( pno char(5) primary key, pname char(10), color char(2), weight smallint ); select * from p; create table j( jno char(2) primary key, jname chAR(15), city char(10) ); select * from j; drop table spj; create table spj( sno char(2), pno char(2), jno char(2), qty smallint, primary key(sno,pno,jno) ); select * from spj;8.0select *from stu; -1.0select snamefrom stu s1where exists( select *from stu s2where s2.Sdept=s1.Sdept ands2.Sname=刘晨);-2.0select *from stuwhere Sdept=csunion select *from stuwhere Sage=19;-3.0select *from stuwhere Sdept=csintersect select *from stuwhere Sage=19;-4.0insert into stu (Sno,Sname,Ssex,Sdept,Sage)values(201215128,陈丹,男,is,18);select *from stu-5.0insert into sc(Sno,Cno)values(201215128,1);select *from sc;-6.0update stuset Sage=22where Sno=201215121;select *from stu;-7.0update stuset Sage=Sage+5;select *from stu;-8.0select *from stuwhere Sname is null or Ssex is nullor Sage is null or Sdept is null;-9.0create view df(sno,sname,grade)asselect stu.Sno,sname,gradefrom stu,scwhere Sdept=is andstu.Sno=sc.Sno andsc.Cno=1;select * from df;-10.0drop view df;create view df(sno,sname,grade)asselect stu.Sno,sname,gradefrom stu,scwhere Sdept=is andstu.Sno=sc.Sno andsc.Cno=1;select * from df;-11.0create view bt_s(sno,gave)asselect sno,AVG(grade)from scgroup by Sno;select sno,gavefrom bt_s;-12.0grant selecton table stuto u1;-13.0create table sss( sno char(9), cno char(4), grade smallint, primary key(sno,cno), foreign key(sno) references stu(sno) on delete cascade on update cascade, foreign