资源描述：
《数据库系统概论第五版教程sql代码.doc》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、1.0createtriggersc_tafterupdateofgradeonscreferencignoldrowasoldtuplenewrowasnewtupleforeachrowwhen(newtuple.grade>=1.0*oldtuple.grade)insertintosc_u(sno,cno,oldgrade,newgrade)values(oldtuple.sno,oldtuple.cno,oldtuple.grade,newtuple.grade)/*触发器*/2.0begintransacti
2、on读取账户甲的金额belance;balance=balance-amount;if(balance<0)then{打印'金额不足,不能转账';rollback;}else{读账户乙的余额BALANCE1;balance1=balance1+amount;写回balance1;commit;}3.0createdatabasestudent;usestudentcreatetablestu(Snochar(11)Primarykey,Snamechar(20)unique,Ssexchar(2),SageSMALLIN
3、T,Sdeptchar(20))createtablecourse(Cnochar(4)PRIMARYKEY,Cnamechar(40)NOTNULL,CpnoCHAR(4),CcreditSMALLINT,FOREIGNKEY(Cpno)referencescourse(Cno))createtablesc(Snochar(11),Cnochar(4),GradeSMALLINT,PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)referencesstu(Sno),FOREIGNKEY(Cno)r
4、eferencescourse(Cno))select*fromcourse;4.0/*6.0建立下面二个关系模式*/createdatabasework;useworkcreatetableworker(Wnochar(10),Wnamechar(3),agesmallint,jobchar(3),WmoneySMALLINT,Wpubchar(5),primarykey(Wno),foreignkey(Wpub)referencesclub(Wpub),check(age<60));createtableclub(W
5、pubchar(5)primarykey,Cnamechar(3)notnull,massegerchar(2),phonechar(10)unique);/*8.0*/createtablefemale(fidintprimarykey,fnamechar(3)notnull,fageint,phonechar(10)notnull);createtablemale(midint,mnamechar(3),magesmallint,phonechar(10),primarykey(mid));selectCOUNT(f
6、id)fromfemale;selectCOUNT(mid)frommale;createassertionfmcheck(50>=(selectCOUNT(fid)fromfemale)+(selectCOUNT(mid)frommale)));5.0createdatabasehomewark;usehomewarkcreatetables(snochar(5)primarykey,snamechar(30),statussmallint,citychar(20));select*froms;createtablep
7、(pnochar(5)primarykey,pnamechar(10),colorchar(2),weightsmallint);select*fromp;createtablej(jnochar(2)primarykey,jnamechAR(15),citychar(10));select*fromj;createtablespj(snochar(2),pnochar(2),jnochar(2),qtysmallint,primarykey(sno,pno,jno));select*fromspj;selectdist
8、inctsnofromspjwherejno='j1';selectsnofromspjwherepno='p1'andjno='j1';selectsnofromspj,pwherespj.pno=p.pnoandjno='j1'andcolor='红';selectdistinctsnofromspjwherep
