数据库系统原理与设计课后习题答案

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

查询1991年出生的读者姓名、工作单位和身份证号。

SELECT readerName,workUnit,identitycard

FROM Reader

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

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

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

WHERE workUnit='信息管理学院'

查询图书名中含有“数据库”的图书的详细信息。

SELECT *

FROM Book

WHERE bookName LIKE '%数据库%'

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

SELECT bookNo,shopNum

FROM Book

WHERE authorName='吴文君'

AND price>=40

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

SELECT bookNo,bookName,publishingDate,shopDate

FROM Book

WHERE YEAR(shopDate) BETWEEN 2005 AND 2008

ORDER BY shopDate

查询借阅了001~000029图书编号的读者编号、图书编号、借书日期。

SELECT readerNo,bookNo,borrowDate

FROM Borrow

WHERE CONVERT(int, bookNo) BETWEEN 1 AND 29

查询没有借阅图书编号以001开头的读者编号和姓名。

SELECT readerNo,readerName

FROM Reader

WHERE readerNo NOT IN(

SELECT readerNo

FROM Borrow

WHERE bookNo LIKE '001%'

)

查询读者马永强借阅的图书编号、图书名称、借书日期和归还日期。

SELECT ,bookName,borrowDate,returnDate

FROM Book,Borrow

WHERE =

AND readerNo IN(

SELECT readerNo

FROM Reader

WHERE readerName='马永强'

)

查询会计学院没有归还图书的读者编号、读者姓名、图书名称、借书日期和应归还日期。SELECT ,readerName,bookName,borrowDate,shouldDate

FROM Reader,Borrow,Book

WHERE =

AND =

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

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

SELECT ,readerName,bookName,borrowDate,returnDate

FROM Reader,Borrow,Book

WHERE =

AND =

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

查询借书时间在2007——2008年之间的读者编号、姓名、图书编号、图书名称。SELECT ,readerName,,bookName

FROM Reader,Borrow,Book

WHERE =

AND =

AND YEAR(borrowDate) BETWEEN 2007 AND 2008

查询在2005-2008年之间没有归还图书的读者编号、读者姓名、读者工作单位。SELECT readerNo,readerName,workUnit

FROM Reader

WHERE readerNO IN(

SELECT readerNo

FROM Borrow

WHERE YEAR(borrowDate) BETWEEN 2005 AND 2008

AND returnDate IS NULL

)

查询没有借书的读者姓名（分别使用IN子查询和存在量词子查询表达）。

--use IN

SELECT readerName

FROM Reader

WHERE readerNo NOT IN(

SELECT readerNo

FROM Borrow

WHERE returnDate IS NULL

)

--use EXISTS

SELECT readerName

FROM Reader

WHERE NOT EXISTS(

SELECT *

FROM Borrow

WHERE =

AND returnDate IS NULL

)

查询既借阅了“离散数学”图书又借阅了“数据库系统概念”两本书的读者编号、读者姓名、借书日期和图书名称。

SELECT ,readerName,borrowDate,bookName

FROM Reader,Borrow,Book

WHERE =

AND =

AND IN(

SELECT readerNo

FROM Borrow

WHERE bookNo IN(

SELECT bookNo

FROM Book

WHERE bookName='离散数学'

)

) AND IN(

SELECT readerNo

FROM Borrow

WHERE bookNo IN(

SELECT bookNo

FROM Book

WHERE bookName='数据库系统概念'

)

)

查询没有借阅图书的读者编号、读者姓名和工作单位（分别使用IN子查询和存在量词子查询表达）。

--与重复

查询没有借阅“经济管理”类图书的读者编号、读者姓名和出生日期（分别使用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 =

AND =

AND classNo=(

SELECT classNo

FROM BookClass

WHERE className='经济管理'

)

)

查询至少与读者“马永强”所借的图书一样的读者编号、读者姓名和工作单位。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 =

AND readerNo=

AND returnDate IS NULL

)

)

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

FROM Book

GROUP BY classNo

ORDER BY MAX(price) DESC

查询图书分类号为001号图书的入库数量。

SELECT SUM(shopNum) AS sumShopNum

FROM Book

GROUP BY classNo

HAVING classNo='001'

查询借阅了图书类别为002号的所有图书的读者编号、读者姓名、图书名称和借阅日期。SELECT ,readerName,bookName,borrowDate

FROM Reader,Borrow,Book

WHERE =

AND =

AND NOT EXISTS(

SELECT *

FROM Book

WHERE classNo='002'

AND NOT EXISTS(

SELECT *

FROM Borrow

WHERE readerNo=

AND bookNo=

)

)

查询至少借阅了3本图书的读者编号、读者姓名、图书编号、图书名称，并按读者编号排序输出。

SELECT ,readerName,,bookName

FROM Reader,Borrow,Book

WHERE =

AND =

AND IN(

SELECT readerNo

FROM Borrow

WHERE returnDate IS NULL

GROUP BY readerNo

HAVING COUNT(*)>=3

)

ORDER BY

查询所借图书的总价在150元以上的读者编号、读者姓名和所借图书的总价。

SELECT ,readerName,SUM(price) AS money

FROM Reader,Borrow,Book

WHERE =

AND =

AND returnDate IS NULL

GROUP BY ,readerName

HAVING SUM(price)>=150

查询所借阅的图书总价最高的读者编号、读者姓名和出生日期。

SELECT readerNo,readerName,SUBSTRING(identitycard,7,8) AS birthday

FROM Reader

WHERE readerNo IN(

SELECT readerNo

FROM Borrow,Book

WHERE =

AND returnDate IS NULL

GROUP BY readerNo

HAVING SUM(price) >=ALL(

SELECT SUM(price)

FROM Borrow,Book

WHERE =

AND returnDate IS NULL

GROUP BY readerNo

)

)

将“经济管理”类图书的单价提高10%。

UPDATE Book

SET price=price*

WHERE classNo IN(

SELECT classNo

FROM BookClass

WHERE className='经济管理'

)

对于年龄在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

创建一个视图，该视图为所借图书的总价在150元以上的读者编号、读者姓名和所借图书的总价。

CREATE VIEW BookView1

AS

SELECT ,readerName,SUM(price) AS money

FROM Reader,Borrow,Book

WHERE =

AND =

GROUP BY ,readerName

HAVING SUM(price)>=150

GO

创建一个视图，该视图为年龄在25~35岁之间的读者，属性列包括读者编号、读者姓名、年龄、工作单位、所借图书名称和借书日期。

CREATE VIEW BookView2

AS

SELECT ,readerName,workUnit,bookName,borrowDate

FROM Reader,Borrow,Book

WHERE =

AND =

AND CONVERT(int,SUBSTRING(identitycard,7,4)) BETWEEN 25 AND 35

GO

创建一个视图，该视图仅包含“清华大学出版社”在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

对由题所建立的视图进行插入、删除和更新操作。

INSERT BookView3 VALUES('0000001','001','Linux网络技术','王波','机械工业出版社','16063',28,'','',80)

DELETE BookView3 WHERE bookName LIKE 'Linux%'

UPDATE BookView3 SET shopNum=shopNum-10

将入库数量最多的图书单价下调5%。

UPDATE Book

SET price=price*

WHERE shopNum=(

SELECT MAX(shopNum)

FROM Book

)