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