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

一个图书管理数据库BookDB的模式如图3-31—图3-34所示。请基于该数据库模式用SQL语句完成如下操作。 属性含义 分类号 分类名称 属性名 classNo className 类型 字符型 字符型 宽度 3 20 小数位 图3-31 图书分类表BookClass的模式 属性含义 图书编号 分类号 图书名称 作者姓名 出版社名称 出版号 单价 出版时间 入库时间 入库数量 属性名 bookNo classNo BookName authorName publishingName publishingNo price publishingDate shopDate shopNum 类型 字符型 字符型 字符型 字符型 字符型 字符型 数值型 日期型 日期型 数值型 宽度 10 3 40 8 20 17 7 8 8 3 小数位 2 图3-32 图书表Book的模式

属性含义 读者编号 姓名 性别 身份证号 工作单位 属性名 readerNo readerName sex identitycard workUnit 类型 字符型 字符型 字符型 字符型 字符型 宽度 8 8 2 18 50 小数位 图3-33 读者表Reader的模式

属性含义 读者编号 图书编号 借阅日期 应归还日期 归还日期 属性名 readerNo bookNo borrowDate shouldDate returnDate 类型 字符型 字符型 日期型 日期型 日期型 宽度 8 10 8 8 8 小数位 图3-34 借阅表Borrow的模式

答案：

SET NOCOUNT ON SET DATEFORMAT YMD USE master GO

创建数据库BookDB

IF EXISTS(SELECT * FROM sysdatabases WHERE name='BookDB') DROP DATABASE BookDB

GO

CREATE DATABASE BookDB GO

USE BookDB GO

创建图书分类表BookClass CREATE TABLE BookClass(

classNo char(3) PRIMARY KEY, /*分类号*/ className char(20) NOT NULL /*分类名称*/ ) GO

创建图书表Book CREATE TABLE Book(

bookNo char(10) PRIMARY KEY, /*图书编号*/ classNo char(3) NOT NULL /*分类号*/

FOREIGN KEY REFERENCES BookClass, bookName varchar(40) NOT NULL, /*图书名称*/ authorName char(8) NOT NULL, /*作者姓名*/ publishingName varchar(20) NOT NULL, /*出版社名称*/ publishingNo char(17) NOT NULL, /*出版号*/ price numeric(7,2) NOT NULL, /*单价*/ publishingDate datetime NOT NULL, /*出版时间*/ shopDate datetime NOT NULL, /*入库时间*/ shopNum int NOT NULL /*入库数量*/ ) GO

创建读者表Reader CREATE TABLE Reader(

readerNo char(8) PRIMARY KEY, /*读者编号*/ readerName char(8) NOT NULL, /*读者姓名*/ sex char(2) NOT NULL, /*性别*/ identitycard char(18) NOT NULL, /*身份证号*/ workUnit varchar(50) NULL /*工作单位*/ ) GO

创建借阅表Borrow CREATE TABLE Borrow(

readerNo char(8) NOT NULL, /*读者编号*/ bookNo char(10) NOT NULL, /*图书编号*/ borrowDate datetime NOT NULL, /*借阅日期*/

shouldDate datetime NOT NULL, /*应归还日期*/ returnDate datetime NULL, /*归还日期*/ FOREIGN KEY(readerNo) REFERENCES Reader, FOREIGN KEY(bookNo) REFERENCES Book, PRIMARY KEY(readerNo,bookNo,borrowDate) ) GO

3.1 查询1991年出生的读者姓名、工作单位和身份证号。 SELECT readerName,workUnit,identitycard FROM Reader

WHERE CONVERT(int,SUBSTRING(identitycard,7,4))=1991

3.2 查询在信息管理学院工作的读者编号、姓名和性别。

SELECT readerNo,readerName,sex=CASE sex WHEN 'M' THEN '男' WHEN 'F' THEN '女' END FROM Reader

WHERE workUnit='信息管理学院'

3.3 查询图书名中含有“数据库”的图书的详细信息。 SELECT * FROM Book

WHERE bookName LIKE '%数据库%'

3.4 查询吴文君老师编写的单价不低于40元的每种图书的图书编号、入库数量。 SELECT bookNo,shopNum FROM Book

WHERE authorName='吴文君' AND price>=40

3.5 查询在2005-2008年之间入库的图书编号、出版时间、入库时间和图书名称，并按入库时间排序输出。

SELECT bookNo,bookName,publishingDate,shopDate FROM Book

WHERE YEAR(shopDate) BETWEEN 2005 AND 2008 ORDER BY shopDate

3.6 查询借阅了图书编号001-000029图书的读者姓名、图书编号、借书日期。 SELECT readerName,bookNo,borrowDate FROM Borrow

WHERE CONVERT(int, bookNo) BETWEEN 1 AND 29

3.7 查询读者马永强借阅的图书编号、图书名称、借书日期和归还日期。 SELECT Book.bookNo,bookName,borrowDate,returnDate FROM Book,Borrow

WHERE Book.bookNo=Borrow.bookNo AND readerNo IN( SELECT readerNo FROM Reader

WHERE readerName='马永强' )

3.8 查询会计学院没有归还所借图书的读者编号、读者姓名、图书名称、借书日期和应归还日期。

SELECT Reader.readerNo,readerName,bookName,borrowDate,shouldDate FROM Reader,Borrow,Book

WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo

AND workUnit='会计学院' AND returnDate IS NULL

3.9 查询借阅了清华大学出版社出版的图书的读者编号、读者姓名、图书名称、借书日期和归还日期。

SELECT Reader.readerNo,readerName,bookName,borrowDate,returnDate FROM Reader,Borrow,Book

WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo

AND publishingName='清华大学出版社'

3.10 查询借书时间在2007-2008年之间的读者编号、读者姓名、图书编号、图书名称。 SELECT Reader.readerNo,readerName,Book.bookNo,bookName FROM Reader,Borrow,Book

WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo

AND YEAR(borrowDate) BETWEEN 2007 AND 2008

3.11 查询每种类别的图书分类号、最高价格和平均价格，并按最高价格的降序输出。 SELECT classNo,MAX(price) AS maxPrice,AVG(price) AS avgPrice FROM Book

GROUP BY classNo

ORDER BY MAX(price) DESC

3.12查询图书分类号小于009号图书的入库数量。 SELECT classNo,shopNum FROM Book

WHERE shopNum

(SELECT classNo,shopNum FROM Book

WHERE classNo=’009’)

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

本文来源：https://www.bwwdw.com/article/53gv.html