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

3.13 查询所借图书的总价在150元以上的读者编号、读者姓名和所借图书的总价。 SELECT Reader.readerNo,readerName,SUM(price) AS money FROM Reader,Borrow,Book

WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo AND returnDate IS NULL

GROUP BY Reader.readerNo,readerName HAVING SUM(price)>=150

3.14 查询没有借阅图书的读者姓名和工作单位(分别使用IN子查询和存在量词子查询表达)。 --use IN

SELECT readerName,workUnit FROM Reader

WHERE readerNo NOT IN( SELECT readerNo FROM Borrow

WHERE returnDate IS NULL )

--use EXISTS

SELECT readerName,workUnit FROM Reader

WHERE NOT EXISTS( SELECT * FROM Borrow

WHERE Reader.readerNo=Borrow.readerNo AND returnDate IS NULL )

3.15 查询借阅了图书编号为001-000029图书的读者编号、读者姓名、以及他们所借图书尚未归还的所有图书的图书名称、借书日期 (分别使用IN子查询和存在量词子查询表达)。 --use IN

SELECT bookName,readerName,readerNo,borrowDate FROM Reader a,Borrow b

WHERE a.readerNo=b.readerNo

AND a.readerNo IN (SELECT readerName,bookNo

FROM Borrow

WHERE CONVERT(int, bookNo) BETWEEN 1 AND 29)

--use EXISTS

SELECT bookName,readerName,readerNo,borrowDate FROM Reader a,Borrow b

WHERE EXISTS (SELECT *

FROM Borrow

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

联系客服:779662525#qq.com(#替换为@)