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

1.0createtrigger sc_tafterupdateof grade on screferencignoldrow as oldtuplenewrow as newtuplefor eachrowwhen(newtuple.grade>=1.0*oldtuple.grade)insertinto sc_u(sno,cno,oldgrade,newgrade)values(oldtuple.sno,o,oldtuple.grade,newtuple.grade) /* 触发器 */2.0begintransaction读取账户甲的金额belance;balance=balance-amount;if(balance<0)then{打印'金额不足,不能转账';rollback;}else{读账户乙的余额BALANCE1;balance1=balance1+amount;写回balance1;commit;}3.0createdatabase student;use studentcreatetable stu(Sno char(11)Primarykey,Sname char(20)unique,Ssex char(2),Sage SMALLINT,Sdept char(20))createtable course(Cno char(4)PRIMARYKEY,Cname char(40)NOTNULL,Cpno CHAR(4),Ccredit SMALLINT,FOREIGNKEY(Cpno)references course(Cno))createtable sc(Sno char(11),Cno char(4),Grade SMALLINT,PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)references stu(Sno),FOREIGNKEY(Cno)references course(Cno))select*from course;4.0/*6.0 建立下面二个关系模式 */ createdatabase work;use workcreatetable worker(Wno char(10),Wname char(3),age smallint,job char(3),Wmoney SMALLINT,Wpub char(5),primarykey(Wno),foreignkey(Wpub)references club(Wpub), check(age<60));createtable club(Wpub char(5)primarykey,Cname char(3)notnull,masseger char(2),phone char(10)unique);/* 8.0 */createtable female(fid intprimarykey,fname char(3)notnull,fage int,phone char(10)notnull);createtable male(mid int,mname char(3),mage smallint,phone char(10),primarykey(mid));select COUNT(fid)from female;select COUNT(mid)from male;create assertionfmcheck(50>=(select COUNT(fid) from female)+(select COUNT(mid) from male)));5.0createdatabase homewark;use homewarkcreatetable s(sno char(5)primarykey,sname char(30),statussmallint,city char(20));select*from s;createtable p(pno char(5)primarykey,pname char(10),color char(2),weightsmallint);select*from p;createtable j(jno char(2)primarykey,jname chAR(15),city char(10));select*from j;createtable spj(sno char(2),pno char(2),jno char(2),qty smallint,primarykey(sno,pno,jno));select*from spj;selectdistinct snofrom spjwhere jno='j1';select snofrom spjwhere pno='p1'and jno='j1';select snofrom spj,pwhere spj.pno=p.pnoand jno='j1'and color='红'; selectdistinct snofrom spjwhere pno='p1'and pno in(select pnofrom pwhere color='红');select jnofrom spj,s,pwhere spj.pno=p.pnoand spj.sno=s.snoand color='红'and city!='天津';select jnofrom spjwhere pno in(select pno from pwhere color='红')and sno in(select snofrom swhere city!='天津');select jnofrom spjwhere sno='s1';select pname,qtyfrom p,spjwhere spj.pno=p.pnoand spj.jno='j2'; selectdistinct p.pnofrom s,spj,pwhere s.sno=spj.snoand spj.pno=p.pnoand city='';createview pro1asselect sno,pno,qtyfrom spj,jwhere spj.jno=j.jnoand jname='';selectdistinct pno,qty from pro1;select*from pro1where pno='p1';6.0select sname,snofrom stu;select*from stuorderby Sdept,Sage desc; select COUNT(distinct sno) from sc;select COUNT(*)from stu;select cno,COUNT(sno)from scgroupby Cno;select stu.*,sc.*from stu,scwhere stu.sno=sc.sno; select snamefrom stuwhere Sno in(select Snofrom scwhere Cno='2');select sname,sagefrom stuwhere Sage<any(select Sage from stuwhere Sdept='cs')and Sdept<>'cs';select sno,sname,sdeptfrom stuwhere notexists(select*from scwhere Sno=stu.Sno and Cno='1');insertinto stu(Sno,Sname,Ssex,Sdept,sage) values('201215126','程','男','cs','18');select*from stu;insertinto sc(Sno,Cno)values('201215125','1');select*from sc;UPDATE stuset Sage=22where Sno='201215121';select*from stu;update stuset Sage=Sage+1;select*from stu;deletefrom stuwhere Sno='201215126';select*from stu;createview is_stuasselect sno,sname,sagefrom stuwhere Sdept='is';select*from is_stu;createview is_s1(sno,sname,grade) asselect stu.Sno,sname,gradefrom stu,scwhere Sdept='is'andstu.Sno=sc.Sno ando='1';select*from is_s1;7.0createtable s(sno char(5)primarykey,sname char(30),statussmallint,city char(20));select*from s;createtable p(pno char(5)primarykey,pname char(10),color char(2),weightsmallint);select*from p;createtable j(jno char(2)primarykey,jname chAR(15),city char(10));select*from j;droptable spj;createtable spj(sno char(2),pno char(2),jno char(2),qty smallint,primarykey(sno,pno,jno));select*from spj;8.0select*from stu;--1.0select snamefrom stus1where exists(select*from stus2where s2.Sdept=s1.Sdept ands2.Sname='晨');--2.0select*from stuwhere Sdept='cs'unionselect*from stuwhere Sage<=19;--3.0select*from stuwhere Sdept='cs'intersectselect*from stuwhere Sage<=19;--4.0insertinto stu(Sno,Sname,Ssex,Sdept,Sage) values('201215128','丹','男','is',18); select*from stu--5.0insertinto 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 isnullor Ssex isnullor Sage isnullor Sdept isnull;--9.0createview df(sno,sname,grade)asselect stu.Sno,sname,gradefrom stu,scwhere Sdept='is'andstu.Sno=sc.Sno ando='1';select*from df;--10.0dropview df;createview df(sno,sname,grade)asselect stu.Sno,sname,gradefrom stu,scwhere Sdept='is'andstu.Sno=sc.Sno ando='1';select*from df;--11.0createview bt_s(sno,gave)asselect sno,AVG(grade)from scgroupby Sno;select sno,gavefrom bt_s;--12.0grantselectontable stuto u1;--13.0createtable sss(sno char(9),cno char(4),grade smallint,primarykey(sno,cno),foreignkey(sno)references stu(sno) ondeletecascadeonupdatecascade,foreignkey(cno)references course(cno) ondeletenoactiononupdatecascade);--14.0createtable stud(sno char(9),sname char(8)notnull,ssex char(2),sage smallint,primarykey(sno),check(ssex='女'or sname notlike'ms.%') );15.0createview succeedselect Gradefrom sc;。