数据库系统原理与设计(第2版)万常选版第3章SQL课后答案

1、一个图书管理数据库BookDB的模式如图3-31图3-34所示。请基于该数据库模式用SQL语句完成如下操作。属性含义属性名类型宽度小数位分类号classNo字符型3分类名称className字符型20图3-31 图书分类表BookClass的模式属性含义属性名类型宽度小数位图书编号bookNo字符型10分类号classNo字符型3图书名称BookName字符型40作者姓名authorName字符型8出版社名称publishingName字符型20出版号publishingNo字符型17单价price数值型72出版时间publishingDate日期型8入库时间shopDate日期型8入库数量s

2、hopNum数值型3图3-32 图书表Book的模式属性含义属性名类型宽度小数位读者编号readerNo字符型8姓名readerName字符型8性别sex字符型2身份证号identitycard字符型18工作单位workUnit字符型50图3-33 读者表Reader的模式属性含义属性名类型宽度小数位读者编号readerNo字符型8图书编号bookNo字符型10借阅日期borrowDate日期型8应归还日期shouldDate日期型8归还日期returnDate日期型8图3-34 借阅表Borrow的模式答案：SET NOCOUNT ONSET DATEFORMAT YMDUSE master

3、GO创建数据库BookDBIF EXISTS(SELECT * FROM sysdatabases WHERE name=BookDB)DROP DATABASE BookDBGOCREATE DATABASE BookDBGOUSE BookDBGO创建图书分类表BookClassCREATE TABLE BookClass(classNochar(3)PRIMARY KEY,/*分类号*/classNamechar(20)NOT NULL/*分类名称*/)GO创建图书表BookCREATE TABLE Book(bookNochar(10)PRIMARY KEY,/*图书编号*/class

4、Nochar(3)NOT NULL/*分类号*/FOREIGN KEY REFERENCES BookClass,bookNamevarchar(40)NOT NULL,/*图书名称*/authorNamechar(8)NOT NULL,/*作者姓名*/publishingName varchar(20) NOT NULL,/*出版社名称*/publishingNo char(17)NOT NULL,/*出版号*/pricenumeric(7,2) NOT NULL,/*单价*/publishingDate datetimeNOT NULL,/*出版时间*/shopDatedatetimeNO

5、T NULL,/*入库时间*/shopNumintNOT NULL/*入库数量*/)GO创建读者表ReaderCREATE TABLE Reader(readerNochar(8)PRIMARY KEY,/*读者编号*/readerNamechar(8)NOT NULL,/*读者姓名*/sexchar(2)NOT NULL,/*性别*/identitycard char(18)NOT NULL,/*身份证号*/workUnitvarchar(50)NULL/*工作单位*/)GO创建借阅表BorrowCREATE TABLE Borrow(readerNochar(8)NOT NULL,/*读者

6、编号*/bookNochar(10)NOT NULL,/*图书编号*/borrowDatedatetimeNOT NULL,/*借阅日期*/shouldDatedatetimeNOT NULL,/*应归还日期*/returnDatedatetimeNULL,/*归还日期*/FOREIGN KEY(readerNo) REFERENCES Reader,FOREIGN KEY(bookNo) REFERENCES Book,PRIMARY KEY(readerNo,bookNo,borrowDate)GO3.1 查询1991年出生的读者姓名、工作单位和身份证号。SELECT readerName

7、,workUnit,identitycardFROM ReaderWHERE CONVERT(int,SUBSTRING(identitycard,7,4)=19913.2 查询在信息管理学院工作的读者编号、姓名和性别。SELECT readerNo,readerName,sex=CASE sex WHEN M THEN 男 WHEN F THEN 女 ENDFROM ReaderWHERE workUnit=信息管理学院3.3 查询图书名中含有“数据库”的图书的详细信息。SELECT *FROM BookWHERE bookName LIKE %数据库%3.4 查询吴文君老师编写的单价不低于

8、40元的每种图书的图书编号、入库数量。SELECT bookNo,shopNumFROM BookWHERE authorName=吴文君AND price=403.5 查询在2005-2008年之间入库的图书编号、出版时间、入库时间和图书名称，并按入库时间排序输出。SELECT bookNo,bookName,publishingDate,shopDateFROM BookWHERE YEAR(shopDate) BETWEEN 2005 AND 2008ORDER BY shopDate3.6 查询借阅了图书编号001-000029图书的读者姓名、图书编号、借书日期。SELECT read

9、erName,bookNo,borrowDateFROM BorrowWHERE CONVERT(int, bookNo) BETWEEN 1 AND 293.7 查询读者马永强借阅的图书编号、图书名称、借书日期和归还日期。SELECT Book.bookNo,bookName,borrowDate,returnDateFROM Book,BorrowWHERE Book.bookNo=Borrow.bookNoAND readerNo IN(SELECT readerNoFROM ReaderWHERE readerName=马永强)3.8 查询会计学院没有归还所借图书的读者编号、读者姓名、

10、图书名称、借书日期和应归还日期。SELECT Reader.readerNo,readerName,bookName,borrowDate,shouldDateFROM Reader,Borrow,BookWHERE Reader.readerNo=Borrow.readerNoAND Borrow.bookNo=Book.bookNoAND workUnit=会计学院 AND returnDate IS NULL3.9 查询借阅了清华大学出版社出版的图书的读者编号、读者姓名、图书名称、借书日期和归还日期。SELECT Reader.readerNo,readerName,bookName,b

11、orrowDate,returnDateFROM Reader,Borrow,BookWHERE Reader.readerNo=Borrow.readerNoAND Borrow.bookNo=Book.bookNoAND publishingName=清华大学出版社3.10 查询借书时间在2007-2008年之间的读者编号、读者姓名、图书编号、图书名称。SELECT Reader.readerNo,readerName,Book.bookNo,bookNameFROM Reader,Borrow,BookWHERE Reader.readerNo=Borrow.readerNoAND Bo

12、rrow.bookNo=Book.bookNoAND YEAR(borrowDate) BETWEEN 2007 AND 20083.11 查询每种类别的图书分类号、最高价格和平均价格，并按最高价格的降序输出。SELECT classNo,MAX(price) AS maxPrice,AVG(price) AS avgPriceFROM BookGROUP BY classNoORDER BY MAX(price) DESC3.12查询图书分类号小于009号图书的入库数量。SELECT classNo,shopNumFROM BookWHERE shopNum=1503.14 查询没有借阅图书

13、的读者姓名和工作单位（分别使用IN子查询和存在量词子查询表达）。-use INSELECT readerName，workUnitFROM ReaderWHERE readerNo NOT IN(SELECT readerNoFROM BorrowWHERE returnDate IS NULL)-use EXISTSSELECT readerName，workUnitFROM ReaderWHERE NOT EXISTS(SELECT *FROM BorrowWHERE Reader.readerNo=Borrow.readerNoAND returnDate IS NULL)3.15 查询

14、借阅了图书编号为001-000029图书的读者编号、读者姓名、以及他们所借图书尚未归还的所有图书的图书名称、借书日期 （分别使用IN子查询和存在量词子查询表达）。-use INSELECT bookName,readerName,readerNo,borrowDateFROM Reader a,Borrow b WHERE a.readerNo=b.readerNoAND a.readerNo IN (SELECT readerName,bookNoFROM BorrowWHERE CONVERT(int, bookNo) BETWEEN 1 AND 29)-use EXISTS SELECT

15、 bookName,readerName,readerNo,borrowDateFROM Reader a,Borrow b WHERE EXISTS (SELECT *FROM BorrowWHERE a.readerNo=b.readerNo AND CONVERT(int, bookNo) BETWEEN 1 AND 29)3.16 查询没有借阅图书编号以001开头的图书的读者编号、姓名以及他们所借阅图书的图书名称、借书日期（分别使用IN子查询和存在量词子查询表达）。-use INSELECT readerNo,readerNameFROM ReaderWHERE readerNo NO

16、T IN(SELECT readerNoFROM BorrowWHERE bookNo LIKE 001%)-use EXISTS SELECT readerNo,readerNameFROM ReaderWHERE EXISTS(SELECT *FROM BorrowWHERE bookNo LIKE 001%)3.17 查询在2005-2008年之间借阅但没有归还图书的读者编号、读者姓名、读者工作单位以及他们所借阅过的所有图书的图书编号、图书名称和借书日期（分别使用IN子查询和存在量词子查询表达）。-use INSELECT readerNo,readerName,workUnitFROM

17、 ReaderWHERE readerNO IN(SELECT readerNoFROM BorrowWHERE YEAR(borrowDate) BETWEEN 2005 AND 2008AND returnDate IS NULL)-use EXISTS SELECT readerNo,readerName,workUnitFROM ReaderWHERE EXISTS (SELECT *FROM BorrowWHERE YEAR(borrowDate) BETWEEN 2005 AND 2008AND returnDate IS NULL)3.18 查询既借阅了“离散数学”图书又借阅了“

18、数据库系统概念”两本图书的读者编号、读者姓名、借书日期和图书名称。SELECT Reader.readerNo,readerName,borrowDate,bookNameFROM Reader,Borrow,BookWHERE Reader.readerNo=Borrow.readerNoAND Borrow.bookNo=Book.bookNoAND Reader.readerNo IN(SELECT readerNoFROM BorrowWHERE bookNo IN(SELECT bookNoFROM BookWHERE bookName=离散数学) AND Reader.reader

19、No IN(SELECT readerNoFROM BorrowWHERE bookNo IN(SELECT bookNoFROM BookWHERE bookName=数据库系统概念)3.19 查询没有借阅“经济管理”类图书的读者编号、读者姓名和出生日期（分别使用IN子查询和存在量词子查询表达）。-use INSELECT readerNo,readerName,SUBSTRING(identitycard,7,8) AS birthdayFROM ReaderWHERE readerNo NOT IN(SELECT readerNoFROM BorrowWHERE bookNo IN(SE

20、LECT bookNoFROM BookWHERE classNo IN(SELECT classNoFROM BookClassWHERE className=经济管理)-use EXISTSSELECT readerNo,readerName,SUBSTRING(identitycard,7,8) AS birthdayFROM ReaderWHERE NOT EXISTS(SELECT *FROM Borrow,BookWHERE Reader.readerNo=Borrow.readerNoAND Borrow.bookNo=Book.bookNoAND classNo=(SELECT

21、 classNoFROM BookClassWHERE className=经济管理)3.20 查询至少与读者“马永强”所借的图书一样的读者编号、读者姓名和工作单位。SELECT readerNo,readerName,workUnitFROM Reader rWHERE NOT EXISTS(SELECT *FROM Borrow b1WHERE readerNo IN(SELECT readerNoFROM ReaderWHERE readerName=马永强) AND returnDate IS NULLAND NOT EXISTS(SELECT *FROM Borrow b2WHERE

22、 b1.bookNo=b2.bookNoAND readerNo=r.readerNoAND returnDate IS NULL)3.21 查询借阅了图书类别为002号的所有图书的读者编号、读者姓名、以及他们所借阅过的所有图书的图书名称和借阅日期。SELECT Reader.readerNo,readerName,bookName,borrowDateFROM Reader,Borrow,BookWHERE Reader.readerNo=Borrow.readerNoAND Borrow.bookNo=Book.bookNoAND NOT EXISTS(SELECT *FROM BookW

23、HERE classNo=002AND NOT EXISTS(SELECT *FROM BorrowWHERE readerNo=Reader.readerNoAND bookNo=Book.bookNo)3.22 查询借阅了图书类别为002号的所有图书的读者编号、读者姓名、以及他们所借阅过的这些（002号）图书的图书名称和借阅日期。SELECT Reader.readerNo,readerName,bookName,borrowDateFROM Reader,Borrow,BookWHERE Reader.readerNo=Borrow.readerNoAND Borrow.bookNo=B

24、ook.bookNoAND NOT EXISTS(SELECT *FROM BookWHERE classNo=002AND NOT EXISTS(SELECT *FROM BorrowWHERE readerNo=Reader.readerNoAND bookNo=Book.bookNo)3.23 查询至少借阅了3本图书的读者编号、读者姓名、图书编号、图书名称，并按读者编号排序输出。SELECT Reader.readerNo,readerName,Book.bookNo,bookNameFROM Reader,Borrow,BookWHERE Reader.readerNo=Borrow.

25、readerNoAND Borrow.bookNo=Book.bookNoAND Reader.readerNo IN(SELECT readerNoFROM BorrowWHERE returnDate IS NULLGROUP BY readerNoHAVING COUNT(*)=3)ORDER BY Reader.readerNo3.24 查询所借阅的图书总价最高的读者编号、读者姓名和出生日期。SELECT readerNo,readerName,SUBSTRING(identitycard,7,8) AS birthdayFROM ReaderWHERE readerNo IN(SEL

26、ECT readerNoFROM Borrow,BookWHERE Borrow.bookNo=Book.bookNo AND returnDate IS NULLGROUP BY readerNoHAVING SUM(price) =ALL(SELECT SUM(price)FROM Borrow,BookWHERE Borrow.bookNo=Book.bookNoAND returnDate IS NULLGROUP BY readerNo) )3.25 将“经济管理”类图书的单价提高10%。UPDATE BookSET price=price*1.1WHERE classNo IN(S

27、ELECT classNoFROM BookClassWHERE className=经济管理)3.26 对于年龄在25-35之间的读者所借阅的应归还未归还的图书，将其归还日期修改为系统当天日期。UPDATE BorrowSET returnDate=GETDATE()WHERE readerNo IN(SELECT readerNoFROM ReaderWHERE CONVERT(int,SUBSTRING(identitycard,7,4) BETWEEN 25 AND 35)GO3.27 创建一个视图，该视图为所借图书的总价在150元以上的读者编号、读者姓名和所借图书的总价。CREATE VIEW BookView1ASSELECT Reader.readerNo,readerName,SUM(price) AS moneyFROM Reader,Borrow,BookWHERE Reader.readerNo=Borrow.readerNoAND B