数据库系统概论,第五版,教程sql代码.doc
文本预览下载声明
. . .
1.0
create trigger sc_t
after update of grade on sc
referencign
oldrow as oldtuple
newrow as newtuple
for each row
when(newtuple.grade=1.0*oldtuple.grade)
insert into sc_u(sno,cno,oldgrade,newgrade)
values(oldtuple.sno,oldtuple.cno,oldtuple.grade,newtuple.grade)
/* 触发器 */
2.0
begin transaction
读取账户甲的金额 belance;
balance=balance-amount;
if(balance0)then
{打印金额不足,不能转账;
rollback;}
else
{读账户乙的余额BALANCE1;
balance1=balance1+amount;
写回 balance1;
commit;}
3.0
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;
4.0
/*
6.0 建立下面二个关系模式 */
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(age60)
);
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 fm
check(50=(select COUNT(fid)
from female)+(select COUNT(mid)
显示全部