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

create trigger sc_tafter update of grade on screferencignoldrow 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(balance<0)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<60));create table club(Wpub char(5)primary key,Cname char(3)not null,masseger char(2),phone char(10)unique);/* 8.0 */create table female(fid int primary key,fname char(3)not null,fage int,phone char(10)not null);create table male(mid int,mname char(3),mage smallint,phone char(10),primary key(mid));select COUNT(fid)from female;select COUNT(mid)from male;create assertion fmcheck(50>=(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 snofrom spjwhere jno='j1';select snofrom spjwhere pno='p1'and jno='j1';select snofrom spj,pwhere spj.pno=p.pnoand jno='j1'and color='红'; select distinct 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';select distinct p.pnofrom s,spj,pwhere s.sno=spj.snoand spj.pno=p.pnoand city='上海';create view pro1asselect sno,pno,qtyfrom spj,jwhere spj.jno=j.jnoand jname='三建';select distinct pno,qty from pro1;select*from pro1where 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 Snofrom scwhere Cno='2');select sname,sagefrom stuwhere Sage<any(select Sagefrom stuwhere Sdept='cs')and Sdept<>'cs';select sno,sname,sdeptfrom stuwhere not exists(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;create view is_stuasselect sno,sname,sagefrom stuwhere Sdept='is';select*from is_stu;create view 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.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='cs'union select*from stuwhere Sage<=19;--3.0select*from stuwhere Sdept='cs'intersect 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 ando='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 ando='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 cascadeon update cascade,foreign key(cno)references course(cno) on delete no actionon update cascade);--14.0create table stud(sno char(9),sname char(8)not null,ssex char(2),sage smallint,primary key(sno),check(ssex='女'or sname not like'ms.%') );15.0create view succeedselect Gradefrom sc;。