数据库原理与应用77页第三章习题3.docx
《数据库原理与应用77页第三章习题3.docx》由会员分享，可在线阅读，更多相关《数据库原理与应用77页第三章习题3.docx（11页珍藏版）》请在冰豆网上搜索。
数据库原理与应用77页第三章习题3
完成第三章课后第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语句创建图书馆数据库。
C读者表tedatabase图书馆数据库；
（2）用SQL语句创建上述三个表。
use图书馆数据库
Go
c读者表tetable图书表
（图书号char（20）primarykey,
书名varchar（50）,
作者varchar（30）,
出版社varchar（30）,
单价float
）;
use图书馆数据库
go
c读者表tetable读者表
（读者号char（10）primarykey,
姓名char（8）notnull,
性别char
（2）notnull,
办公电话char（8）,
部门char（30）
）;
use图书馆数据库
go
createtable借阅表
（读者号char（10）notnull,
图书号char（20）notnull,
借出日期char（8）notnull,
归还日期char（8）,
foreignkey（读者号）references读者表（读者号）,
foreignkey（图书号）references图书表（图书号）
）;
（3）基于图书馆数据库的三个表，用SQL语言完成以下各项操作：
1）给图书表增加一列“ISBN”，数据类型为CHAR（10）
altertable图书表addISBNCHAR（10）;
2）为刚添加的ISBN列增加缺省值约束，约束名为ISBNDEF，缺省值为’7111085949’；
ALTERTABLE图书表
ADDCONSTRAINTISBNDEFDEFAULT'7111085949'FORISBN;
3）为读者表的’办公电话’列，添加一个CHECK约束，要求前五位’88320’，约束名为CHECKDEF。
ALTERTABLE读者表
ADDCONSTRAINTCHECKDEFCHECK（办公电话>='88320000'AND办公电话<='88320999'）;
4）删除图书表中ISBN列增加缺省值约束；
ALTERTABLE图书表
DROPISBNDEF;
5）删除读者表中“办公电话”列的CHECK约束；
ALTERTABLE读者表
DROPCHECKDEF;
6）删除图书表中新增的列ISBN；
ALTERTABLE图书表
DROPCOLUMNISBN;
（4）基于图书馆数据库的三个表，用SQL语言完成以下数据更新操作：
1）向读者表加入一个新读者，该读者的信息为：
（‘200197’,‘王小平’,‘男’,‘88320732’,’存中楼’）
INSERT
INTO读者表
VALUES（'200197','王小平','男','88320732','信息系'）;
2）向借阅表插入一个借阅记录，表示读者’王小平’借阅了一本书，图书号为’TP316/ZW6’，借出日期为当天的日期，归还日期为空值。
INSERT
INTO图书表（图书号）
VALUES（'TP316/ZW6'）;
INSERT
INTO借阅表
VALUES（'200197','TP316/ZW6',convert（CHAR（8）,GETDATE（）,112）,NULL）;
3）读者’王小平’在借出上述图书后10归还该书；
UPDATE借阅表
SET归还日期=借出日期+10
WHERE图书号='TP316/ZW6';
4）当读者’王小平’按期归还图书时，删除上述借阅记录；
DELETE
FROM借阅表
WHERE读者号=（select读者号
from读者表
where姓名='王小平'）
（5）针对以上三个表，用SQL语言完成以下各项查询：
1）查询全体图书的图书号,书名,作者,出版社,单价。
select图书号,书名,作者,出版社,单价
from图书表;
2）查询全体图书的信息,其中单价打8折，并且将该列设置别名为’打折价’。
select图书号,书名,作者,出版社,单价*0.8打折价
from图书表;
3）显示所有借阅者的读者号，并去掉重复行。
selectdistinct读者号
from借阅表
4）查询所有单价在20到30之间的图书信息。
select*
from图书表
where单价between20and30
5）查询所有单价不在20到30之间的图书信息。
select*
from图书表
where单价notbetween20and30
6）查询机械工业出版社、科学出版社、人民邮电出版社的图书信息
select*
from图书表
where出版社in（'机械工业出版社','科学出版社','人民邮电出版社'）
7）查询既不是机械工业出版社、也不是科学出版社出版的图书信息
select*
from图书表
where出版社notin（'机械工业出版社','科学出版社'）
8）查找姓名的第二个字符是’建’并且只有两三个字符的读者的读者号、姓名。
select读者号,姓名
from读者表
where姓名in（'_建‘，’_建_'）
9）查找姓名以’王’开头的所有读者的读者号、姓名。
select读者号,姓名
from读者表
where姓名like'王%'
10）查找姓名以’王’、’张’或’李’开头的所有读者的读者号、姓名。
select读者号,姓名
from读者表
where姓名like（'王%','张%','李%'）
11）查找姓名不是以’王’、’张’或’李’开头的所有读者的读者号、姓名。
select读者号,姓名
from读者表
where姓名notlike（'王%','张%','李%'）
12）查询无归还日期的借阅信息。
select*
from借阅表
where归还日期='null'
13）查询有归还日期的借阅信息。
select*
from借阅表
where归还日期!
='null'
14）查询单价在20元以上，30元以下的机械工业出版社出版的图书名，单价。
select书名,单价
from图书表
where（单价between20and30）and出版社='机械工业出版社'
15）查询机械工业出版社或科学出版社出版的图书名，出版社,单价。
select书名,出版社,单价
from图书表
where出版社='机械工业出版社'or出版社='科学出版社'
16）求读者的总人数
selectcount（*）总人数
from读者表
17）求借阅了图书的读者的总人数
selectcount（distinct读者号）借阅总人数
from借阅表
18）求机械工业出版社图书的平均价格、最高价、最低价。
selectAVG（单价）avg,MAX（单价）max,MIN（单价）min
from图书表
where出版社='机械工业出版社'
19）查询借阅图书本数超过2本的读者号、总本数。
并按借阅本数值从大到小排序。
select读者号,COUNT（图书号）总本数
from借阅表
Groupby读者号
havingCOUNT（图书号）>2
orderby（count（图书号））desc
（6）针对以上三个表，用SQL语言完成以下各项多表连接查询、子查询、组合查询
1）查询读者的基本信息以及他/她借阅的情况。
select读者表.*,借阅表.图书号,借阅表.归还日期,借阅表.借出日期
from读者表,借阅表
where读者表.读者号=借阅表.读者号
2）查询读者的读者号、姓名、借阅的图书名、借出日期、归还日期。
select借阅表.图书号,读者表.姓名,图书表.书名,借阅表.归还日期,借阅表.借出日期
from读者表,借阅表,图书表
where读者表.读者号=借阅表.读者号and图书表.图书号=借阅表.图书号
3）查询借阅了机械工业出版社出版,并且书名中包含’数据库’三个字的图书的读者，显示读者号、姓名、书名、出版社，借出日期、归还日期。
select读者表.读者号,读者表.姓名,图书表.书名,借阅表.归还日期,借阅表.借出日期
from读者表,借阅表,图书表
where图书表.书名like'%数据库%'and出版社='机械工业出版社'and读者表.读者号=借阅表.读者号and图书表.图书号=借阅表.图书号
4）查询至少借阅过1本机械工业出版社出版的书的读者的读者号、姓名、书名，借阅本数，并按借阅本数多少降序排列。
select借阅表.读者号,读者表.姓名,书名,COUNT（借阅表.图书号）本数
from图书表,读者表,借阅表
where读者表.读者号=借阅表.读者号and图书表.图书号=借阅表.图书号and
图书表.图书号='机械工业出版社'
Groupby借阅表.读者号,姓名,书名
havingCOUNT（书名）>=1
orderbyCOUNT（借阅表.图书号）desc
5）查询与’王平’的办公电话相同的读者的姓名。
selectr1.姓名
from读者表r1,读者表r2
wherer1.姓名='王平'andr1.办公电话=r2.办公电话
6）查询办公电话为’88320701’的所有读者的借阅情况，要求包括借阅了书籍的读者和没有借阅的读者，显示他们的读者号、姓名、书名、借阅日期。
select读者表.读者号,姓名,书名,借出日期
from图书表,读者表,借阅表
where办公电话='88320701'and读者表.读者号=借阅表.读者号
and图书表.图书号=借阅表.图书号
7）查询所有单价小于平均单价的图书号、书名、出版社
select图书号,书名,出版社
from图书表
where单价<（selectAVG（单价）
from图书表
）
8）查询’科学出版社’的图书中单价比’机械工业出版社’最高单价还高的的图书书名、单价。
select书名,单价
from图书表
where出版社='科学出版社'and单价>（selectMAX（单价）
rom图书表
where出版社='机械工业出版社'）
9）查询'科学出版社'的图书中单价比'机械工业出版社'最低单价高的的图书书名、单价。
select书名,单价
from图书表
where出版社='科学出版社'and单价>（selectMin（单价）
from图书表
where出版社='机械工业出版社'）
10）查询已被借阅过并已归还的图书信息。
select*
from图书表
where图书号in（select图书号
from借阅表
where归还日期isnotnull）
11）查询从未被借阅过的图书信息。
select*
from图书表
where图书号notin（select图书号
from借阅表
）
12）查询正在借阅的图书信息。
select*
from图书表
where图书号in（select图书号
from借阅表
where借出日期notlike'null'and归还日期likenull
）
13）查询借阅了机械工业出版社出版的书名中含有数'据库书'三个字的图书、或者借阅了科学出版社出版的书名中含有数'据库书'三个字的图书的读者姓名、书名。
select姓名,书名
from图书表,读者表,借阅表
where书名like'%数据库%'and读者表.读者号in（select借阅表.读者号
from借阅表,图书表
where出版社='机械工业出版社'or出版社='科学出版社'
）
14）查询借阅了机械工业出版社出版的书名中含有数'据库书'三个字的图书并且也借阅了科学出版社出版的书名中含有数'据库书'三个字的图书的读者姓名、书名。
select姓名,书名
from图书表,读者表,借阅表
where书名='%数据库%'and读者表.读者号in（select借阅表.读者号
from借阅表r1,借阅表r2,图书表
wherer1.读者号=r2.读者号and图书表.图书号=借阅表.图书号
and出版社='机械工业出版社'and出版社='科学出版社'
）
15）查询借阅了机械工业出版社出版的书名中含有数'据库书'三个字的图书但没有借阅了科学出版社出版的书名中含有数'据库书'三个字的图书的读者姓名、书名。
select姓名,书名
from图书表,读者表,借阅表
where书名like'%数据库%'and读者表.读者号in（select借阅表.读者号
from借阅表,图书表
where借阅表.图书号=图书表.图书号
and出版社='机械工业出版社'
and图书表.图书号in（select借阅表.读者号
from借阅表,图书表
where图书表.图书号=借阅表.图书号
and书名like'%数据库%'
and出版社='科学出版社'）
）
升级会员 