WHERE a.readerNo=b.readerNo
AND CONVERT(int, bookNo) BETWEEN 1 AND 29)
3.16 查询没有借阅图书编号以001开头的图书的读者编号、姓名以及他们所借阅图书的图书名称、借书日期（分别使用IN子查询和存在量词子查询表达）。 --use IN
SELECT readerNo,readerName FROM Reader
WHERE readerNo NOT IN( SELECT readerNo FROM Borrow
WHERE bookNo LIKE '001%' )
--use EXISTS
SELECT readerNo,readerName FROM Reader WHERE EXISTS( SELECT * FROM Borrow
WHERE bookNo LIKE '001%')
3.17 查询在2005-2008年之间借阅但没有归还图书的读者编号、读者姓名、读者工作单位以及他们所借阅过的所有图书的图书编号、图书名称和借书日期（分别使用IN子查询和存在量词子查询表达）。 --use IN
SELECT readerNo,readerName,workUnit FROM Reader
WHERE readerNO IN( SELECT readerNo FROM Borrow
WHERE YEAR(borrowDate) BETWEEN 2005 AND 2008 AND returnDate IS NULL )
--use EXISTS
SELECT readerNo,readerName,workUnit FROM Reader WHERE EXISTS ( SELECT * FROM Borrow
WHERE YEAR(borrowDate) BETWEEN 2005 AND 2008 AND returnDate IS NULL )
3.18 查询既借阅了“离散数学”图书又借阅了“数据库系统概念”两本图书的读者编号、读者姓名、借书日期和图书名称。
SELECT Reader.readerNo,readerName,borrowDate,bookName FROM Reader,Borrow,Book
WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo AND Reader.readerNo IN( SELECT readerNo FROM Borrow
WHERE bookNo IN( SELECT bookNo FROM Book
WHERE bookName='离散数学' )
) AND Reader.readerNo IN( SELECT readerNo FROM Borrow
WHERE bookNo IN( SELECT bookNo FROM Book
WHERE bookName='数据库系统概念' ) )
3.19 查询没有借阅“经济管理”类图书的读者编号、读者姓名和出生日期（分别使用IN子查询和存在量词子查询表达）。 --use IN
SELECT readerNo,readerName,SUBSTRING(identitycard,7,8) AS birthday FROM Reader
WHERE readerNo NOT IN( SELECT readerNo FROM Borrow
WHERE bookNo IN( SELECT bookNo FROM Book
WHERE classNo IN( SELECT classNo FROM BookClass
WHERE className='经济管理' ) ) )
--use EXISTS
SELECT readerNo,readerName,SUBSTRING(identitycard,7,8) AS birthday FROM Reader
WHERE NOT EXISTS(
SELECT *
FROM Borrow,Book
WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo AND classNo=(
SELECT classNo FROM BookClass
WHERE className='经济管理' ) )
3.20 查询至少与读者“马永强”所借的图书一样的读者编号、读者姓名和工作单位。 SELECT readerNo,readerName,workUnit FROM Reader r WHERE NOT EXISTS( SELECT *
FROM Borrow b1 WHERE readerNo IN( SELECT readerNo FROM Reader
WHERE readerName='马永强' ) AND returnDate IS NULL AND NOT EXISTS( SELECT *
FROM Borrow b2
WHERE b1.bookNo=b2.bookNo AND readerNo=r.readerNo AND returnDate IS NULL ) )
3.21 查询借阅了图书类别为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.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 )