数据库系统概论第五版教程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_usno,cno,oldgrade,newgrade values,,, /* 触发器 */ begin transaction 读取账户甲的金额 belance; balancebalance-amount; ifbalance0then {打印金额不足,不能转账; rollback;} else {读账户乙的余额BALANCE1; balance1balance1amount; 写回 balance1; commit;} create database student; use student create table stu Sno char11 Primary key, Sname char20 unique, Ssex char2, Sage SMALLINT, Sdept char20 create table course Cno char4 PRIMARY KEY, Cname char40 NOT NULL, Cpno CHAR4, Ccredit SMALLINT, FOREIGN KEYCpno references courseCno create table sc Sno char11, Cno char4, Grade SMALLINT, PRIMARY KEYSno,Cno, FOREIGN KEYSno references stuSno, FOREIGN KEYCno references courseCno select * from course; /* 建立下面二个关系模式 */ create database work; use work create table worker Wno char10, Wname char3, age smallint, job char3, Wmoney SMALLINT, Wpub char5, primary keyWno, foreign keyWpub references clubWpub, checkage60 ; create table club Wpub char5 primary key, Cname char3 not null, masseger char2, phone char10 unique ; /* */ create table female fid int primary key, fname char3 not null, fage int, phone char10 not null ; create table male mid int, mname char3, mage smallint, phone char10, primary keymid ; select COUNTfid from female; select COUNTmid from male; create assertion fm check50select COUNTfid from femaleselect COUNTmid from male ; create database homewark; use homewark create table s sno char5 primary key, sname char30, status smallint, city char20 ; select * from s; create table p pno char5 primary key, pname char10, color char2, weight smallint ; select * from p; create table j jno char2 primary key, jname chAR15, city char10 ; select * from j; create table spj sno char2, pno char2, jno char2, qty smallint, primary keysno,pno,jno ; select * from spj; select distinct sno from spj where jnoj1; select sno from spj where pnop1 and jnoj1; select sno from spj,p where and jnoj1 and color红; select distinct sno from spj where pnop1 and pno in select pno from p where color红;