发布时间 : 星期一 文章数据库系统原理与设计（第2版） 万常选版 第3章 SQL 课后答案更新完毕开始阅读ed0d4d29192e45361066f533
)
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 )