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

)

WHERE readerNo=Reader.readerNo AND bookNo=Book.bookNo )

3.22 查询借阅了图书类别为002号的所有图书的读者编号、读者姓名、以及他们所借阅过的这些（002号）图书的图书名称和借阅日期。

SELECT Reader.readerNo,readerName,bookName,borrowDate FROM Reader,Borrow,Book

WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo AND NOT EXISTS( SELECT * FROM Book

WHERE classNo='002' AND NOT EXISTS( SELECT * FROM Borrow

WHERE readerNo=Reader.readerNo AND bookNo=Book.bookNo ) )

3.23 查询至少借阅了3本图书的读者编号、读者姓名、图书编号、图书名称，并按读者编号排序输出。

SELECT Reader.readerNo,readerName,Book.bookNo,bookName FROM Reader,Borrow,Book

WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo AND Reader.readerNo IN( SELECT readerNo FROM Borrow

WHERE returnDate IS NULL GROUP BY readerNo HAVING COUNT(*)>=3 )

ORDER BY Reader.readerNo

3.24 查询所借阅的图书总价最高的读者编号、读者姓名和出生日期。

SELECT readerNo,readerName,SUBSTRING(identitycard,7,8) AS birthday FROM Reader

WHERE readerNo IN( SELECT readerNo

FROM Borrow,Book

WHERE Borrow.bookNo=Book.bookNo AND returnDate IS NULL GROUP BY readerNo

HAVING SUM(price) >=ALL( SELECT SUM(price) FROM Borrow,Book

WHERE Borrow.bookNo=Book.bookNo AND returnDate IS NULL GROUP BY readerNo ) )

3.25 将“经济管理”类图书的单价提高10%。 UPDATE Book

SET price=price*1.1 WHERE classNo IN( SELECT classNo FROM BookClass

WHERE className='经济管理' )

3.26 对于年龄在25-35之间的读者所借阅的应归还未归还的图书，将其归还日期修改为系统当天日期。 UPDATE Borrow

SET returnDate=GETDATE() WHERE readerNo IN( SELECT readerNo FROM Reader

WHERE CONVERT(int,SUBSTRING(identitycard,7,4)) BETWEEN 25 AND 35 ) GO

3.27 创建一个视图，该视图为所借图书的总价在150元以上的读者编号、读者姓名和所借图书的总价。

CREATE VIEW BookView1 AS

SELECT Reader.readerNo,readerName,SUM(price) AS money FROM Reader,Borrow,Book

WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo GROUP BY Reader.readerNo,readerName HAVING SUM(price)>=150 GO

3.28 创建一个视图，该视图为年龄在25-35岁之间的读者，属性列包括读者编号、读者姓名、年龄、工作单位、所借图书名称和借书日期。 CREATE VIEW BookView2 AS

SELECT Reader.readerNo,readerName,workUnit,bookName,borrowDate FROM Reader,Borrow,Book

WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo

AND CONVERT(int,SUBSTRING(identitycard,7,4)) BETWEEN 25 AND 35 GO

3.29 创建一个视图，该视图仅包含“清华大学出版社”在2008-2009年出版的“计算机类”的图书基本信息。 CREATE VIEW BookView3 AS

SELECT * FROM Book

WHERE publishingName='清华大学出版社'

AND YEAR(publishingDate) IN(2008,2009) AND classNo=(

SELECT classNo FROM BookClass

WHERE className='计算机类' ) GO

3.30 对由题3.29所建立的视图进行插入、删除和更新操作。

INSERT BookView3 VALUES('0000001','001','Linux网络技术','王波','机械工业出版社','9787111216063',28,'20070701','20070901',80) DELETE BookView3 WHERE bookName LIKE 'Linux%' UPDATE BookView3 SET shopNum=shopNum-10

3.31 将入库数量最多的图书单价下调5%。 UPDATE Book

SET price=price*0.95 WHERE shopNum=(

SELECT MAX(shopNum) FROM Book )