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语句创建图书馆数据库。 Create database 图书馆数据库； （2）用SQL语句创建上述三个表。 use 图书馆数据库 Go
create table book
( bnum char (20) primary key, bname varchar (50), bwri varchar (30), bpub varchar (30),
bpri float );
use 图书馆数据库 go
create table rea
use 图书馆数据库 go
create table br
( rnum char (10) not null, bnum char (20) not null, brdate char (8) not null, brback char (8),
foreign key (rnum) references rea(rnum), foreign key (bnum) references book(bnum) ( rnum char (10) primary key, rname char (8) not null, rsex char (2) not null, rphone char (8), rpar char(30) );
);
（3）基于图书馆数据库的三个表，用SQL语言完成以下各项操作： 1）给图书表增加一列“ISBN”，数据类型为CHAR(10)
alter table book add ISBN CHAR(10);
2）为刚添加的ISBN列增加缺省值约束，约束名为ISBNDEF，缺省值为’7111085949’；
ALTER TABLE BOOK
ADD CONSTRAINT ISBNDEF DEFAULT '7111085949' FOR ISBN;
3）为读者表的’办公电话’列，添加一个CHECK约束，要求前五位’88320’，约束名为CHECKDEF。
ALTER TABLE REA
ADD CONSTRAINT CHECKDEF CHECK (RPHONE >='88320000' AND RPHONE <='88320999');
4）删除图书表中ISBN列增加缺省值约束； ALTER TABLE BOOK DROP ISBNDEF;
5）删除读者表中“办公电话”列的CHECK约束；
ALTER TABLE REA DROP CHECKDEF;
6）删除图书表中新增的列ISBN；
ALTER TABLE BOOK DROP COLUMN ISBN;
（4）基于图书馆数据库的三个表，用SQL语言完成以下数据更新操作： 1）向读者表加入一个新读者，该读者的信息为：
(‘200197’, ‘王小平’, ‘男’ , ‘88320732’,’存中楼’)
INSERT INTO REA
VALUES ('200197','王小平','男','88320732','信息系');
2）向借阅表插入一个借阅记录，表示读者’王小平’借阅了一本书，图书号为’TP316/ZW6’，借出日期为当天的日期，归还日期为空值。
INSERT
INTO BOOK(BNUM) VALUES ('TP316/ZW6'); INSERT INTO BR VALUES
('200197','TP316/ZW6',convert( CHAR(8) ,GETDATE(),112),NULL);
3）读者’王小平’在借出上述图书后10 归还该书；
UPDATE BR
SET BRBACK= BRDATE+10 (10什么时候开应该加引号，什么是不加)
WHERE BNUM = 'TP316/ZW6';
4）当读者’王小平’按期归还图书时，删除上述借阅记录； DELETE FROM BR
WHERE rnum=(select rnum
from rea
where rname = '王小平')
（5）针对以上三个表，用SQL语言完成以下各项查询： 1）查询全体图书的图书号,书名,作者,出版社,单价。
select bnum,bname,bwri,bpub,bpri from book;
2）查询全体图书的信息,其中单价打8折，并且将该列设置别名为’打折价’ 。
select bnum,bname,bwri,bpub, bpri*0.8 打折价 from book;
3）显示所有借阅者的读者号，并去掉重复行。
select distinct rnum from br
4）查询所有单价在20到30之间的图书信息。
select * from book
where bpri between 20 and 30
5）查询所有单价不在20到30之间的图书信息。
select * from book
where bpri not between 20 and 30
6）查询机械工业出版社、科学出版社、人民邮电出版社的图书信息
select * from book
where bpub in ('机械工业出版社','科学出版社','人民邮电出版社')
7）查询既不是机械工业出版社、也不是科学出版社出版的图书信息
select * from book
where bpub not in ('机械工业出版社','科学出版社')
8）查找姓名的第二个字符是’建’并且只有两三个字符的读者的读者号、姓名。
select rnum ,rname from rea
where rname in ('_建‘，’_建_')
9）查找姓名以’王’开头的所有读者的读者号、姓名。
select rnum ,rname from rea
where rname like '王%'
10）查找姓名以’王’、’张’或’李’开头的所有读者的读者号、姓名。
select rnum ,rname from rea
where rname like ('王%','张%','李%')
11）查找姓名不是以’王’、’张’或’李’开头的所有读者的读者号、姓名。
select rnum ,rname from rea
where rname not like ('王%','张%','李%')
12）查询无归还日期的借阅信息。
select * from br
where brback = 'null'
13）查询有归还日期的借阅信息。
select * from br
where brback != 'null'
14）查询单价在20元以上，30元以下的机械工业出版社出版的图书名，单价。
select bname,bpri from book
where (bpri between 20 and 30) and bpub = '机械工业出版社'
15）查询机械工业出版社或科学出版社出版的图书名，出版社,单价。
select bname,bpub,bpri from book
where bpub = '机械工业出版社' or bpub = '科学出版社'
16）求读者的总人数
select count (*) 总人数 from rea
17）求借阅了图书的读者的总人数
select count (distinct rnum ) 借阅总人数 from br
18）求机械工业出版社图书的平均价格、最高价、最低价。
select AVG (bpri) avg , MAX (bpri) max , MIN (bpri) min from book
where bpub = '机械工业出版社'
19）查询借阅图书本数超过2本的读者号、总本数。并按借阅本数值从大到小排序。
select rnum,COUNT (bnum) 总本数 from br Group by rnum
having COUNT(bnum) >2 order by (count (bnum)) desc
（6）针对以上三个表，用SQL语言完成以下各项多表连接查询、子查询、组
合查询
1）查询读者的基本信息以及他/她借阅的情况。
select rea.* ,br.bnum ,br.brback ,br.brdate from rea,br
where rea.rnum = br.rnum
2）查询读者的读者号、姓名、借阅的图书名、借出日期、归还日期。
select br.bnum ,rea.rname,book.bname,br.brback ,br.brdate from rea,br,book
where rea.rnum = br.rnum and book.bnum=br.bnum
3）查询借阅了机械工业出版社出版,并且书名中包含’数据库’三个字的图书的读者，显示读者号、姓名、书名、出版社，借出日期、归还日期。
select rea.rnum,rea.rname,book.bname,br.brback ,br.brdate from rea,br,book
where book.bname like '%数据库%' and bpub = '机械工业出版社' and rea.rnum = br.rnum and book.bnum=br.bnum
4）查询至少借阅过1本机械工业出版社出版的书的读者的读者号、姓名、书名，借阅本数，并按借阅本数多少降序排列。
select br.rnum,rea.rname,bname,COUNT(br.bnum )本数 from book,rea,br
where rea.rnum = br.rnum and book.bnum = br.bnum and book.bnum = ' 机械工业出版社' Group by br.rnum ,rname,bname
having COUNT(bname) >=1 order by COUNT(br.bnum) desc
5）查询与’王平’的办公电话相同的读者的姓名。
select r1.rname from rea r1,rea r2
where r1.rname = '王平' and r1.rphone = r2.rphone
6）查询办公电话为’88320701’的所有读者的借阅情况，要求包括借阅了书籍的读者和没有借阅的读者，显示他们的读者号、姓名、书名、借阅日期。
select rea.rnum,rname,bname,brdate from book,rea,br
where rphone = '88320701' and rea.rnum = br.rnum
and book.bnum = br.bnum
7）查询所有单价小于平均单价的图书号、书名、出版社
select bnum,bname,bpub from book
where bpri < (select AVG(bpri)
from book )
8）查询’科学出版社’的图书中单价比’机械工业出版社’最高单价还高的的图书书名、单价。
select bname ,bpri from book
where bpub = '科学出版社' and bpri > (select MAX (bpri)
9）查询'科学出版社'的图书中单价比'机械工业出版社'最低单价高的的图书书名、单价。
rom book
where bpub = '机械工业出版社')
select bname ,bpri from book
where bpub = '科学出版社' and bpri > (select Min (bpri)
from book
where bpub = '机械工业出版社')
10）查询已被借阅过并已归还的图书信息。
select * from book
where bnum in ( select bnum
from br
where brback is not null)
11）查询从未被借阅过的图书信息。
select * from book
where bnum not in ( select bnum
from br
)
12）查询正在借阅的图书信息。
select * from book
where bnum in ( select bnum
from br
where brdate not like 'null' and brback like null )
13）查询借阅了机械工业出版社出版的书名中含有数'据库书'三个字的图书、或者借阅了科学出版社出版的书名中含有数'据库书'三个字的图书的读者姓名、书名。
select rname,bname from book,rea,br
where bname like '%数据库%' and rea.rnum in ( select br.rnum
from br,book
where bpub = '机械工业出版社' or bpub = '科学出版社' )
14）查询借阅了机械工业出版社出版的书名中含有数'据库书'三个字的图书并且也借阅了科学出版社出版的书名中含有数'据库书'三个字的图书的读者姓名、书名。
select rname,bname from book,rea,br
where bname = '%数据库%' and rea.rnum in ( select br.rnum
from br r1,br r2,book
where r1.rnum = r2.rnum and book.bnum =br.bnum and bpub = '机械工业出版社' and bpub = '科学出版社' )
15）查询借阅了机械工业出版社出版的书名中含有数'据库书'三个字的图书
但没有借阅了科学出版社出版的书名中含有数'据库书'三个字的图书的读者姓名、书名。
select rname,bname from book,rea,br
where bname like '%数据库%' and rea.rnum in ( select br.rnum
from br ,book
where br.bnum = book.bnum
and bpub = '机械工业出版社' and book.bnum in (select br.rnum
from br,book
where book.bnum = br.bnum and bname like '%数据库%' and bpub = '科学出版社')
)