数据库系统概论第五版教程sql代码
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
1.0
createtrigger sc_t
afterupdateof grade on sc
referencign
oldrow as oldtuple
newrow as newtuple
for eachrow
when(newtuple.grade>=1.0*oldtuple.grade)
insertinto sc_u(sno,cno,oldgrade,newgrade)
values(oldtuple.sno,o,oldtuple.grade,newtuple.grade) /* 触发器 */
2.0
begintransaction
读取账户甲的金额belance;
balance=balance-amount;
if(balance<0)then
{打印'金额不足,不能转账';
rollback;}
else
{读账户乙的余额BALANCE1;
balance1=balance1+amount;
写回balance1;
commit;}
3.0
createdatabase student;
use student
createtable stu
(Sno char(11)Primarykey,
Sname char(20)unique,
Ssex char(2),
Sage SMALLINT,
Sdept char(20)
)
createtable course
(Cno char(4)PRIMARYKEY,
Cname char(40)NOTNULL,
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGNKEY(Cpno)references course(Cno)
)
createtable sc
(Sno char(11),
Cno char(4),
Grade SMALLINT,
PRIMARYKEY(Sno,Cno),
FOREIGNKEY(Sno)references stu(Sno),
FOREIGNKEY(Cno)references course(Cno)
)
select*
from course;
4.0
/*
6.0 建立下面二个关系模式 */ createdatabase work;
use work
createtable worker
(
Wno char(10),
Wname char(3),
age smallint,
job char(3),
Wmoney SMALLINT,
Wpub char(5),
primarykey(Wno),
foreignkey(Wpub)references club(Wpub), check(age<60)
);
createtable club
(
Wpub char(5)primarykey,
Cname char(3)notnull,
masseger char(2),
phone char(10)unique
);
/* 8.0 */
createtable female
(
fid intprimarykey,
fname char(3)notnull,
fage int,
phone char(10)notnull
);
createtable male
(
mid int,
mname char(3),
mage smallint,
phone char(10),
primarykey(mid)
);
select COUNT(fid)
from female;
select COUNT(mid)
from male;
create assertionfm
check(50>=(select COUNT(fid) from female)+(select COUNT(mid) from male))
);
5.0
createdatabase homewark;
use homewark
createtable s(
sno char(5)primarykey,
sname char(30),
statussmallint,
city char(20)
);
select*from s;
createtable p(
pno char(5)primarykey,
pname char(10),
color char(2),
weightsmallint
);
select*from p;
createtable j(
jno char(2)primarykey,
jname chAR(15),
city char(10)
);
select*from j;
createtable spj(
sno char(2),
pno char(2),
jno char(2),
qty smallint,
primarykey(sno,pno,jno)
);
select*from spj;