数据库系统原理与设计（万常选）第三版第3章第7章习题答案
3.1 查询1991年出⽣的读者姓名、⼯作单位和⾝份证号。
SELECT readerName,workUnit,identitycard
FROM Reader
WHERE SUBSTRING(identitycard,7,4) =‘1991’
3.2 查询图书名中含有“数据库”的图书的详细信息。
SELECT *
FROM Book
WHERE bookName LIKE ‘%数据库%’
3.3 查询在2015-2016年之间⼊库的图书编号、出版时间、⼊库时间和图书名称，并按⼊库时间降序排序输出。
SELECT bookNo,bookName,publishingDate,shopDate
FROM Book
WHERE YEAR(shopDate) BETWEEN 2015 AND 2016
ORDER BY shopDate DESC
3.4 查询读者“喻⾃强”借阅的图书编号、图书名称、借书⽇期和归还⽇期。
SELECT Book.bookNo,bookName,borrowDate,returnDate
控件下载不了怎么办
FROM Book,Borrow
WHERE Book.bookNo=Borrow.bookNo AND readerNo IN(dede什么意思
SELECT readerNo
FROM Reader
WHERE readerName=‘喻⾃强’ ）
3.5 查询借阅了清华⼤学出版社出版的图书的读者编号、读者姓名、图书名称、借书⽇期和归还⽇期。
aderNo,readerName,bookName,borrowDate,returnDate
FROM Reader,Borrow,Book,Publisher
aderNo AND Borrow.bookNo=Book.bookNo
AND Publisher. PublisherNo= Book. PublisherNo AND publisherName=‘清华⼤学出版社’
3.6 查询上海⽣物研究室没有归还所借图书的读者编号、读者姓名、图书名称、借书⽇期和应归还⽇期。
aderNo,readerName,bookName,borrowDate,shouldDate
FROM Reader,Borrow,Book
aderNo AND Borrow.bookNo=Book.bookNo
AND workUnit=‘上海⽣物研究室’ AND returnDate IS NULL
3.7 查询在2015-2016年之间借阅但没有归还图书的读者编号、读者姓名、读者⼯作单位以及这些借阅未归还图书的图书编号、图书名称和借书⽇期（分别使⽤IN⼦查询和存在量词⼦查询表达）。
aderNo,readerName,workUnit,c.bookNo,bookName, borrowDate
FROM Reader a,Borrow b,Book c
aderNo AND b.bookNo=c.bookNo AND returnDate IS NULL aderNO IN(
SELECT readerNo
FROM Borrow
WHERE YEAR(borrowDate) BETWEEN 2015 AND 2016 AND returnDate IS NULL )
3.8 查询每种类别的图书分类号、分类名称、最⾼价格和平均价格，并按最⾼价格的降序输出。
SELECT Book.classNo,BookClass.className,MAX(price) AS maxPrice,AVG(price) AS avgPrice
FROM Book,BookClass
WHERE Book.classNo=BookClass.classNo
GROUP BY Book.classNo,className
ORDER BY MAX(price) DESC
3.9 查询每个读者在借（即借阅未归还）的图书数量、读者编号、读者姓名和⼯作单位，并按借书数量的降序排序输出。
aderName,Reader.workUnit,COUNT() AS 未归还图书数量
FROM Reader,Borrow
aderNo urnDate IS NULL
GROUP aderName,Reader.workUnit
3.10查询每个出版社出版的每种类别的图书平均价格，要求显⽰出版社名称、图书类名称和平均价格。
SELECT Publisher.publisherName,BookClass.className,AVG(price) AS 平均价格
FROM Book,Publisher,BookClass
WHERE Book.classNo=BookClass.classNo AND Book.publisherNo=Publisher.publisherNo
WHERE Book.classNo=BookClass.classNo AND Book.publisherNo=Publisher.publisherNo
GROUP BY Book.publisherNo, Publisher.publisherName,BookClass.className
3.11 查询所借图书的总价不低于200元的读者编号、读者姓名和所借图书的总价。
aderNo,readerName,SUM(price) AS money
FROM Reader,Borrow,Book
aderNo AND Borrow.bookNo=Book.bookNo
AND returnDate IS NULL
GROUP aderNo,readerName
HAVING SUM(price)>200
3.12查询从来没有借过书的读者姓名和⼯作单位（分别使⽤IN⼦查询和存在量词⼦查询表达）
–use IN
SELECT readerName,workUnit FROM Reader
WHERE readerNo NOT IN(
SELECT readerNo FROM Borrow )
–use EXISTS
SELECT readerName,workUnit FROM Reader
WHERE NOT EXISTS(
SELECT * FROM Borrow aderNo)
3.13 查询没有借阅“经济类”图书的读者编号、读者姓名和出⽣⽇期（分别使⽤IN⼦查询和存在量词⼦查询表达）。
–use IN
递归函数如何计算的
aderNo,readerName,SUBSTRING(identitycard,7,8) AS birthday
FROM Reader,Borrow,Book
aderNo AND Borrow.bookNo=Book.bookNo AND returnDate IS NULL AND Book.classNo NOT IN(
SELECT classNo FROM BookClass WHERE className=‘经济类’ )
–use EXISTS
aderNo,readerName,SUBSTRING(identitycard,7,8) AS birthday
FROM Reader,Borrow,Book
aderNo AND Borrow.bookNo=Book.bookNo AND returnDate IS NULL AND NOT EXISTS (
SELECT * FROM BookClass WHERE className=‘经济类’ AND Book.classNo=BookClass.classNo )
3.14 查询既借阅了“政治经济学”图书⼜借阅了“数据库系统概念”两本图书的读者编号、读者姓名以及这两种图书的图书名称、借书⽇期和归还⽇期。
aderNo,readerName,bookName,borrowDate,returnDate
FROM Reader,Borrow,Book
aderNo AND Borrow.bookNo=Book.bookNo
AND (bookName=‘政治经济学’ OR bookName=‘数据库系统概念’)
aderNo IN(
SELECT readerNo FROM Borrow WHERE bookNo IN(
SELECT bookNo FROM Book WHERE bookName=‘政治经济学’ ) )
aderNo IN(
SELECT readerNo FROM Borrow WHERE bookNo IN(
SELECT bookNo FROM Book WHERE bookName=‘数据库系统概念’ ) )
或
aderNo,readerName,borrowDate,bookName
FROM Reader,Borrow,Book
aderNo AND Borrow.bookNo=Book.bookNo
AND (bookName=‘政治经济学’ OR bookName=‘数据库系统概念’)
aderNo IN
linux操作系统书籍(aderNo
FROM Reader,Borrow,Book
aderNo AND Borrow.bookNo=Book.bookNo
AND bookName=‘政治经济学’
aderNo IN (
aderNo FROM Borrow,Book
WHERE Borrow.bookNo=Book.bookNo AND bookName=‘数据库系统概念’))
WHERE Borrow.bookNo=Book.bookNo AND bookName=‘数据库系统概念’))
3.15 查询借阅过图书名称中包含“数据库”的所有图书的读者编号、读者姓名以及他们所借阅的这些图书的图书名称、借阅⽇期和归还⽇期。
aderName,Book.bookName,Borrow.urnDate
FROM Book,Reader,Borrow
WHERE Book.bookNo=Borrow.bookNo aderNo
AND returnDate IS NOT NULL AND bookName LIKE ‘%数据库%’
3.16 查询⾄少借阅过读者“张⼩娟”所借阅过的所有图书的读者编号、读者姓名和⼯作单位。
SELECT readerNo,readerName,workUnit
FROM Reader r
WHERE NOT EXISTS(
SELECT * FROM Borrow b1,Reader r1 aderNo AND readerName=‘张⼩娟’
AND NOT EXISTS(
SELECT * FROM Borrow b2
WHERE b2.bookNo=b1.bookNo aderNo ) )
3.17 查询⾄少有3本在借图书的读者编号、读者姓名以及在借图书的图书编号、图书名称，并按读者编号升序、借阅⽇期降序排序输出。aderNo,readerName,Book.bookNo,bookName
FROM Reader,Borrow,Book
aderNo AND Borrow.bookNo=Book.bookNo AND
returnDate IS NULL aderNo IN(
SELECT readerNo FROM Borrow
WHERE returnDate IS NULL GROUP BY readerNo HAVING COUNT()>=3 )
ORDER aderNo
3.18 查询所借阅的图书（包括已归还的图书）总价最⾼的读者编号、读者姓名和出⽣⽇期。
SELECT readerNo,readerName,SUBSTRING(identitycard,7,8) AS birthday
FROM Reader
WHERE readerNo IN(
SELECT readerNo FROM Borrow,Book
WHERE Borrow.bookNo=Book.bookNo
GROUP BY readerNo HAVING SUM(price) >=ALL(
SELECT SUM(price) FROM Borrow,Book
WHERE Borrow.bookNo=Book.bookNo
GROUP BY readerNo ))
第七章
7.2 在图书管理数据库BookDB中⽤SQL语句完成如下操作
（1） 将“经济管理”类图书的单价提⾼10%。
UPDATE Book
SET price=price1.1 WHERE classNo IN(
SELECT classNo FROM BookClass WHERE className=‘经济类’ )
（2） 将⼊库数量最多的图书单价下调5%。
UPDATE Book
SET price=price0.95 WHERE shopNum =windows10手机版下载
(SELECT MAX(SHOPNUM) FROM BOOK)
（3）删除读者“张⼩娟”的借书记录
DELETE FROM Borrow WHERE readerNo IN
(SELECT readerNo FROM Reader aderName=‘张⼩娟’)
（4）创建⼀个视图，该视图为所借图书的总价在60元以上的读者编号、读者姓名和所借图书的总价。
CREATE VIEW BookView1 AS
aderNo,readerName,SUM(price) AS money
FROM Reader,Borrow,Book
aderNo AND Borrow.bookNo=Book.bookNo
GROUP aderNo,readerName
HAVING SUM(price)>=60
（5）创建⼀个视图，该视图为年龄在25-35岁之间的读者，属性列包括读者编号、读者姓名、年龄、⼯作单位、所借图书名称和借书⽇期。
CREATE VIEW BookView2 AS
aderNo,readerName,SUBSTRING(identitycard,7,8) AS birthday,workUnit,bookName,borrowDate
FROM Reader,Borrow,Book
aderNo AND Borrow.bookNo=Book.bookNo
AND year(getdate())-CONVERT(int,SUBSTRING(identitycard,7,4)) BETWEEN 25 AND 35
（6）创建⼀个视图，该视图仅包含“清华⼤学出版社”在2015–2016年出版的“计算机类”的图书基本信息。
CREATE VIEW BookView3 AS
SELECT * FROM Book
WHERE publisherNo =
(SELECT publisherNo FROM Publisher WHERE publisherName= ‘清华⼤学出版社’)
AND YEAR(publishingDate) IN(2015,2016)
AND classNo=(SELECT classNo FROM BookClass WHERE className=‘计算机类’)
（7）对由题（6）所建⽴的视图进⾏插⼊、删除和更新操作。
网站建设的公司INSERT BookView3
VALUES(‘0000001’,‘003’,‘Linux⽹络技术’,‘王波’,‘P005’, 28,‘2014-5-1’, ‘2015-1-11’,80)
DELETE BookView3 WHERE bookName LIKE ‘Linux%’
UPDATE BookView3 SET shopNum=shopNum+10
INSERT BookView3
VALUES(‘0000003’,‘003’,‘Linux⽹络技术’,‘王波’,‘P002’, 28,‘2015-5-1’, ‘2016-1-11’,80)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。