数据库原理与应用庞国莉pg77no2题目+答案.docx
《数据库原理与应用庞国莉pg77no2题目+答案.docx》由会员分享,可在线阅读,更多相关《数据库原理与应用庞国莉pg77no2题目+答案.docx(13页珍藏版)》请在冰豆网上搜索。
数据库原理与应用庞国莉pg77no2题目+答案
完成第三章课后第2、3、4题,写相关SQL程序,提交数据库备份文件及SQL程序代码,(如不提交附件)可直接粘贴代码提交
1、设有一图书馆数据库,包括三个表:
图书表、读者表、借阅表。
三个表的结构如表3-3、表3-4和表3-5所示。
完成以下习题。
表3-3 图书表结构
列名
说明
数据类型
约束
图书号
图书唯一的图书号
定长字符串,长度为20
主键
书名
图书的书名
变长字符串,长度为50
空值
作者
图书的编著者名
变长字符串,长度为30
空值
出版社
图书的出版社
变长字符串,长度为30
空值
单价
出版社确定的图书的单价
浮点型,FLOAT
空值
表3-4 读者表结构
列名
说明
数据类型
约束说明
读者号
读者唯一编号
定长字符串,长度为10
主键
姓名
读者姓名
定长字符串,长度为8
非空值
性别
读者性别
定长字符串,长度为2
非空值
办公电话
读者办公电话
定长字符串,长度为8
空值
部门
读者所在部门
变长字符串,长度为30
空值
表3-5 借阅表结构
列名
说明
数据类型
约束说明
读者号
读者的唯一编号
定长字符串,长度为10
外码,引用读者表的主键
图书号
图书的唯一编号
定长字符串,长度为20
外码,引用图书表的主键
借出日期
图书借出的日期
定长字符串,长度为8,为’yymmdd’
非空值
归还日期
图书归还的日期
定长字符串,长度为8,为’yymmdd’
空值
主键为:
(读者号,图书号)
(1)用SQL语句创建图书馆数据库。
Createdatabase图书馆数据库;
(2)用SQL语句创建上述三个表。
use图书馆数据库
Go
createtablebook
(bnumchar(20)primarykey,
bnamevarchar(50),
bwrivarchar(30),
bpubvarchar(30),
bprifloat
);
use图书馆数据库
go
createtablerea
(rnumchar(10)primarykey,
rnamechar(8)notnull,
rsexchar
(2)notnull,
rphonechar(8),
rparchar(30)
);
use图书馆数据库
go
createtablebr
(rnumchar(10)notnull,
bnumchar(20)notnull,
brdatechar(8)notnull,
brbackchar(8),
foreignkey(rnum)referencesrea(rnum),
foreignkey(bnum)referencesbook(bnum)
);
(3)基于图书馆数据库的三个表,用SQL语言完成以下各项操作:
1)给图书表增加一列“ISBN”,数据类型为CHAR(10)
altertablebookaddISBNCHAR(10);
2)为刚添加的ISBN列增加缺省值约束,约束名为ISBNDEF,缺省值为’49’;
ALTERTABLEBOOK
ADDCONSTRAINTISBNDEFDEFAULT'49'FORISBN;
3)为读者表的’办公电话’列,添加一个CHECK约束,要求前五位’88320’,约束名为CHECKDEF。
ALTERTABLEREA
ADDCONSTRAINTCHECKDEFCHECK(RPHONE>=''ANDRPHONE<='');
4)删除图书表中ISBN列增加缺省值约束;
ALTERTABLEBOOK
DROPISBNDEF;
5)删除读者表中“办公电话”列的CHECK约束;
ALTERTABLEREA
DROPCHECKDEF;
6)删除图书表中新增的列ISBN;
ALTERTABLEBOOK
DROPCOLUMNISBN;
(4)基于图书馆数据库的三个表,用SQL语言完成以下数据更新操作:
1)向读者表加入一个新读者,该读者的信息为:
(‘200197’,‘王小平’,‘男’,‘’,’存中楼’)
INSERT
INTOREA
VALUES('200197','王小平','男','','信息系');
2)向借阅表插入一个借阅记录,表示读者’王小平’借阅了一本书,图书号为’TP316/ZW6’,借出日期为当天的日期,归还日期为空值。
INSERT
INTOBOOK(BNUM)
VALUES('TP316/ZW6');
INSERT
INTOBR
VALUES('200197','TP316/ZW6',convert(CHAR(8),GETDATE(),112),NULL);
3)读者’王小平’在借出上述图书后10归还该书;
UPDATEBR
SETBRBACK=BRDATE+10(10什么时候开应该加引号,什么是不加)
WHEREBNUM='TP316/ZW6';
4)当读者’王小平’按期归还图书时,删除上述借阅记录;
DELETE
FROMBR
WHERErnum=(selectrnum
fromrea
wherername='王小平')
(5)针对以上三个表,用SQL语言完成以下各项查询:
1)查询全体图书的图书号,书名,作者,出版社,单价。
selectbnum,bname,bwri,bpub,bpri
frombook;
2)查询全体图书的信息,其中单价打8折,并且将该列设置别名为’打折价’。
selectbnum,bname,bwri,bpub,bpri*打折价
frombook;
3)显示所有借阅者的读者号,并去掉重复行。
selectdistinctrnum
frombr
4)查询所有单价在20到30之间的图书信息。
select*
frombook
wherebpribetween20and30
5)查询所有单价不在20到30之间的图书信息。
select*
frombook
wherebprinotbetween20and30
6)查询机械工业出版社、科学出版社、人民邮电出版社的图书信息
select*
frombook
wherebpubin('机械工业出版社','科学出版社','人民邮电出版社')
7)查询既不是机械工业出版社、也不是科学出版社出版的图书信息
select*
frombook
wherebpubnotin('机械工业出版社','科学出版社')
8)查找姓名的第二个字符是’建’并且只有两三个字符的读者的读者号、姓名。
selectrnum,rname
fromrea
wherernamein('_建‘,’_建_')
9)查找姓名以’王’开头的所有读者的读者号、姓名。
selectrnum,rname
fromrea
wherernamelike'王%'
10)查找姓名以’王’、’张’或’李’开头的所有读者的读者号、姓名。
selectrnum,rname
fromrea
wherernamelike('王%','张%','李%')
11)查找姓名不是以’王’、’张’或’李’开头的所有读者的读者号、姓名。
selectrnum,rname
fromrea
wherernamenotlike('王%','张%','李%')
12)查询无归还日期的借阅信息。
select*
frombr
wherebrback='null'
13)查询有归还日期的借阅信息。
select*
frombr
wherebrback!
='null'
14)查询单价在20元以上,30元以下的机械工业出版社出版的图书名,单价。
selectbname,bpri
frombook
where(bpribetween20and30)andbpub='机械工业出版社'
15)查询机械工业出版社或科学出版社出版的图书名,出版社,单价。
selectbname,bpub,bpri
frombook
wherebpub='机械工业出版社'orbpub='科学出版社'
16)求读者的总人数
selectcount(*)总人数
fromrea
17)求借阅了图书的读者的总人数
selectcount(distinctrnum)借阅总人数
frombr
18)求机械工业出版社图书的平均价格、最高价、最低价。
selectAVG(bpri)avg,MAX(bpri)max,MIN(bpri)min
frombook
wherebpub='机械工业出版社'
19)查询借阅图书本数超过2本的读者号、总本数。
并按借阅本数值从大到小排序。
selectrnum,COUNT(bnum)总本数
frombr
Groupbyrnum
havingCOUNT(bnum)>2
orderby(count(bnum))desc
(6)针对以上三个表,用SQL语言完成以下各项多表连接查询、子查询、组合查询
1)查询读者的基本信息以及他/她借阅的情况。
selectrea.*,,,
fromrea,br
where=
2)查询读者的读者号、姓名、借阅的图书名、借出日期、归还日期。
select,,,,
fromrea,br,book
where=and=
3)查询借阅了机械工业出版社出版,并且书名中包含’数据库’三个字的图书的读者,显示读者号、姓名、书名、出版社,借出日期、归还日期。
select,,,,
fromrea,br,book
wherelike'%数据库%'andbpub='机械工业出版社'and=and=
4)查询至少借阅过1本机械工业出版社出版的书的读者的读者号、姓名、书名,借阅本数,并按借阅本数多少降序排列。
select,,bname,COUNT)本数
frombook,rea,br
where=and=and
='机械工业出版社'
Groupby,rname,bname
havingCOUNT(bname)>=1
orderbyCOUNTdesc
5)查询与’王平’的办公电话相同的读者的姓名。
select
fromrear1,rear2
where='王平'and=
6)查询办公电话为’’的所有读者的借阅情况,要求包括借阅了书籍的读者和没有借阅的读者,显示他们的读者号、姓名、书名、借阅日期。
select,rname,bname,brdate
frombook,rea,br
whererphone=''and=
and=
7)查询所有单价小于平均单价的图书号、书名、出版社
selectbnum,bname,bpub
frombook
wherebpri<(selectAVG(bpri)
frombook
)
8)查询’科学出版社’的图书中单价比’机械工业出版社’最高单价还高的的图书书名、单价。
selectbname,bpri
frombook
wherebpub='科学出版社'andbpri>(selectMAX(bpri)
rombook
wherebpub='机械工业出版社')
9)查询'科学出版社'的图书中单价比'机械工业出版社'最低单价高的的图书书名、单价。
selectbname,bpri
frombook
wherebpub='科学出版社'andbpri>(selectMin(bpri)
frombook
wherebpub='机械工业出版社')
10)查询已被借阅过并已归还的图书信息。
select*
frombook
wherebnumin(selectbnum
frombr
wherebrbackisnotnull)
11)查询从未被借阅过的图书信息。
select*
frombook
wherebnumnotin(selectbnum
frombr
)
12)查询正在借阅的图书信息。
select*
frombook
wherebnumin(selectbnum
frombr
wherebrdatenotlike'null'andbrbacklikenull
)
13)查询借阅了机械工业出版社出版的书名中含有数'据库书'三个字的图书、或者借阅了科学出版社出版的书名中含有数'据库书'三个字的图书的读者姓名、书名。
selectrname,bname
frombook,rea,br
wherebnamelike'%数据库%'andin(select
frombr,book
wherebpub='机械工业出版社'orbpub='科学出版社'
)
14)查询借阅了机械工业出版社出版的书名中含有数'据库书'三个字的图书并且也借阅了科学出版社出版的书名中含有数'据库书'三个字的图书的读者姓名、书名。
selectrname,bname
frombook,rea,br
wherebname='%数据库%'andin(select
frombrr1,brr2,book
where=and=
andbpub='机械工业出版社'andbpub='科学出版社'
)
15)查询借阅了机械工业出版社出版的书名中含有数'据库书'三个字的图书但没有借阅了科学出版社出版的书名中含有数'据库书'三个字的图书的读者姓名、书名。
selectrname,bname
frombook,rea,br
wherebnamelike'%数据库%'andin(select
frombr,book
where=
andbpub='机械工业出版社'
andin(select
frombr,book
where=
andbnamelike'%数据库%'
andbpub='科学出版社')
)