数据库系统概论第五版教程sql代码.docx
《数据库系统概论第五版教程sql代码.docx》由会员分享,可在线阅读,更多相关《数据库系统概论第五版教程sql代码.docx(17页珍藏版)》请在冰点文库上搜索。
数据库系统概论第五版教程sql代码
1.0
createtriggersc_t
afterupdateofgradeonsc
referencign
oldrowasoldtuple
newrowasnewtuple
foreachrow
when(newtuple.grade>=1.0*oldtuple.grade)
insertintosc_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
createdatabasestudent;
usestudent
createtablestu
(Snochar(11)Primarykey,
Snamechar(20)unique,
Ssexchar
(2),
SageSMALLINT,
Sdeptchar(20)
)
createtablecourse
(Cnochar(4)PRIMARYKEY,
Cnamechar(40)NOTNULL,
CpnoCHAR(4),
CcreditSMALLINT,
FOREIGNKEY(Cpno)referencescourse(Cno)
)
createtablesc
(Snochar(11),
Cnochar(4),
GradeSMALLINT,
PRIMARYKEY(Sno,Cno),
FOREIGNKEY(Sno)referencesstu(Sno),
FOREIGNKEY(Cno)referencescourse(Cno)
)
select*
fromcourse;
4.0
/*
6.0建立下面二个关系模式*/
createdatabasework;
usework
createtableworker
(
Wnochar(10),
Wnamechar(3),
agesmallint,
jobchar(3),
WmoneySMALLINT,
Wpubchar(5),
primarykey(Wno),
foreignkey(Wpub)referencesclub(Wpub),
check(age<60)
);
createtableclub
(
Wpubchar(5)primarykey,
Cnamechar(3)notnull,
massegerchar
(2),
phonechar(10)unique
);
/*8.0*/
createtablefemale
(
fidintprimarykey,
fnamechar(3)notnull,
fageint,
phonechar(10)notnull
);
createtablemale
(
midint,
mnamechar(3),
magesmallint,
phonechar(10),
primarykey(mid)
);
selectCOUNT(fid)
fromfemale;
selectCOUNT(mid)
frommale;
createassertionfm
check(50>=(selectCOUNT(fid)
fromfemale)+(selectCOUNT(mid)
frommale))
);
5.0
createdatabasehomewark;
usehomewark
createtables(
snochar(5)primarykey,
snamechar(30),
statussmallint,
citychar(20)
);
select*froms;
createtablep(
pnochar(5)primarykey,
pnamechar(10),
colorchar
(2),
weightsmallint
);
select*fromp;
createtablej(
jnochar
(2)primarykey,
jnamechAR(15),
citychar(10)
);
select*fromj;
createtablespj(
snochar
(2),
pnochar
(2),
jnochar
(2),
qtysmallint,
primarykey(sno,pno,jno)
);
select*fromspj;
selectdistinctsno
fromspj
wherejno='j1';
selectsno
fromspj
wherepno='p1'
andjno='j1';
selectsno
fromspj,p
wherespj.pno=p.pno
andjno='j1'andcolor='红';
selectdistinctsno
fromspj
wherepno='p1'
andpnoin
(selectpno
fromp
wherecolor='红');
selectjno
fromspj,s,p
wherespj.pno=p.pno
andspj.sno=s.sno
andcolor='红'
andcity!
='天津';
selectjno
fromspj
wherepnoin
(
selectpnofromp
wherecolor='红')
andsnoin
(
selectsno
froms
wherecity!
='天津');
selectjno
fromspj
wheresno='s1';
selectpname,qty
fromp,spj
wherespj.pno=p.pno
andspj.jno='j2';
selectdistinctp.pno
froms,spj,p
wheres.sno=spj.sno
andspj.pno=p.pno
andcity='';
createviewpro1
as
selectsno,pno,qty
fromspj,j
wherespj.jno=j.jno
andjname='';
selectdistinctpno,qty
frompro1;
select*
frompro1
wherepno='p1';
6.0
selectsname,sno
fromstu;
select*
fromstu
orderbySdept,Sagedesc;
selectCOUNT(distinctsno)
fromsc;
selectCOUNT(*)
fromstu;
selectcno,COUNT(sno)
fromsc
groupbyCno;
selectstu.*,sc.*
fromstu,sc
wherestu.sno=sc.sno;
selectsname
fromstu
whereSnoin
(selectSno
fromsc
whereCno='2');
selectsname,sage
fromstu
whereSagefromstu
whereSdept='cs')
andSdept<>'cs';
selectsno,sname,sdept
fromstu
wherenotexists
(select*
fromsc
whereSno=stu.SnoandCno='1');
insert
intostu(Sno,Sname,Ssex,Sdept,sage)
values('201215126','程','男','cs','18');
select*
fromstu;
insert
intosc(Sno,Cno)
values('201215125','1');
select*
fromsc;
UPDATEstu
setSage=22
whereSno='201215121';
select*
fromstu;
updatestu
setSage=Sage+1;
select*
fromstu;
delete
fromstu
whereSno='201215126';
select*
fromstu;
createviewis_stu
as
selectsno,sname,sage
fromstu
whereSdept='is';
select*
fromis_stu;
createviewis_s1(sno,sname,grade)
as
selectstu.Sno,sname,grade
fromstu,sc
whereSdept='is'and
stu.Sno=sc.Snoand
sc.Cno='1';
select*
fromis_s1;
7.0
createtables(
snochar(5)primarykey,
snamechar(30),
statussmallint,
citychar(20)
);
select*froms;
createtablep(
pnochar(5)primarykey,
pnamechar(10),
colorchar
(2),
weightsmallint
);
select*fromp;
createtablej(
jnochar
(2)primarykey,
jnamechAR(15),
citychar(10)
);
select*fromj;
droptablespj;
createtablespj(
snochar
(2),
pnochar
(2),
jnochar
(2),
qtysmallint,
primarykey(sno,pno,jno)
);
select*fromspj;
8.0
select*
fromstu;
--1.0
selectsname
fromstus1
whereexists
(select*
fromstus2
wheres2.Sdept=s1.Sdeptand
s2.Sname='晨');
--2.0
select*
fromstu
whereSdept='cs'
unionselect*
fromstu
whereSage<=19;
--3.0
select*
fromstu
whereSdept='cs'
intersectselect*
fromstu
whereSage<=19;
--4.0
insertintostu(Sno,Sname,Ssex,Sdept,Sage)
values('201215128','丹','男','is',18);
select*
fromstu
--5.0
insertintosc(Sno,Cno)
values('201215128','1');
select*
fromsc;
--6.0
updatestu
setSage=22
whereSno='201215121';
select*
fromstu;
--7.0
updatestu
setSage=Sage+5;
select*
fromstu;
--8.0
select*
fromstu
whereSnameisnullorSsexisnull
orSageisnullorSdeptisnull;
--9.0
createviewdf(sno,sname,grade)
as
selectstu.Sno,sname,grade
fromstu,sc
whereSdept='is'and
stu.Sno=sc.Snoand
sc.Cno='1';
select*fromdf;
--10.0
dropviewdf;
createviewdf(sno,sname,grade)
as
selectstu.Sno,sname,grade
fromstu,sc
whereSdept='is'and
stu.Sno=sc.Snoand
sc.Cno='1';
select*fromdf;
--11.0
createviewbt_s(sno,gave)
as
selectsno,AVG(grade)
fromsc
groupbySno;
selectsno,gave
frombt_s;
--12.0
grantselect
ontablestu
tou1;
--13.0
createtablesss
(
snochar(9),
cnochar(4),
gradesmallint,
primarykey(sno,cno),
foreignkey(sno)referencesstu(sno)
ondeletecascade
onupdatecascade,
foreignkey(cno)referencescourse(cno)
ondeletenoaction
onupdatecascade
);
--14.0
createtablestud
(snochar(9),
snamechar(8)notnull,
ssexchar
(2),
sagesmallint,
primarykey(sno),
check(ssex='女'orsnamenotlike'ms.%')
);
15.0
createviewsucceed
selectGrade
fromsc;