《数据库系统概论-第五版-教程sql代码》由会员分享，可在线阅读，更多相关《数据库系统概论-第五版-教程sql代码（10页珍藏版）》请在金锄头文库上搜索。
1、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=bal
2、ance-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) PRI
3、MARY 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) )授课：XXX select *from course;4.
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.0creat
5、e 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(
6、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;授课：XXX 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
7、=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 c
8、ity!=天津); select jno from spj where sno=s1; select pname,qty授课：XXX 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
9、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(
10、select Sno from sc where Cno=2); select sname,sage from stu where Sageany(select Sage 授课：XXX 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 s
