数据库系统概论第五版教程sql代码
create trigger sc_t after update of grade on sc referencign oldrow as oldtuple newrow as newtuple for each row when>=* insert into sc_u(sno,cno,oldgrade,newgrade) values,,, /* 触发器 */ begin transaction 读取账户甲的金额 belance; balance=balance-amount; if(balance<0)then {打印 金额不足,不能转账 ; rollback;} else {读账户乙的余额BALANCE1; balance1=balance1+amount; 写回 balance1; commit;} create database student; use student create 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; /* 建立下面二个关系模式 */ create database work; use work create 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)) ); create database homewark; use homewark create 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 = and jno= j1 and color= 红 ; select distinct sno from spj where pno= p1 and pno in ( select pno from p where color= 红 );