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

create trigger sc_t after update of grade on sc referen cig n oldrow as oldtuple newrow as newtuple for each row when=* insert into sc_u(sno, eno, oldgrade ,newgrade) values ,,, /*触发器*/ beg in tran sacti on 读取账户甲的金额 belanee ; balanee =balanee - amount; if (balanee 0)then {打印’金额不足，不能转账’; rollback ;} else {读账户乙的余额 BALANCE) bala ncel =bala ncel +amou nt; 写回 balancel ; commit;} create database stude nt 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) PRIMARYKEY Cnamechar ( 40) NOTNULL, Cpno CHAR4), Ccredit SMALLINT, course (Cnc)stu (Sno),course (Cnc) course (Cnc) stu (Sno), course (Cnc) ) create table sc (Sno char (11), Cno char (4), Grade SMALLINT PRIMARYKEY Sno, Cno), FOREIGNKEYSno) references FOREIGNKEYCno) references ) select * from course ; /* 建立下面二个关系模式 */ create database work; use work create table worker ( Wno char (10), Wname char ( 3), age smalli nt , job char (3), Wmoney SMALLINT Wpub char (5), primary key(Wn°, foreign key (Wpub references club ( Wpub, check (age60) ); create table club ( Wpub char (5) primary key, Cname char ( 3) not null, masseger char (2), phone char (10) unique ); /* */ 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 COUNTfid ) from female ; select COUNTmid) from male; create asserti on fm check (50=( select COUN(Tfid ) from female )+( select COUN(Tmid) from male)) ); create database homewark; use homewark create table s ( sno char ( 5) primary key, sname char ( 30), status smalli nt city char (20) )； select * from s; create table p( pno char (5) primary key, pname char (10), color char (2), weight smalli nt ); 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 ch