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 readerNo,bookNo,borrowDate
FROM Borrow
WHERE CONVERT(int, bookNo) BETWEEN 1 AND 29
--3.7 查询没有借阅图书编号以001开头的读者编号和姓名。
SELECT readerNo,readerName
FROM Reader
WHERE readerNo NOT IN(
SELECT readerNo
FROM Borrow
WHERE bo
okNo LIKE '001%'
)
--3.8 查询读者马永强借阅的图书编号、图书名称、借书日期和归还日期。
SELECT Book.bookNo,bookName,borrowDate,returnDate
FROM Book,Borrow
WHERE Book.bookNo=Borrow.bookNo
AND readerNo IN(
SELECT readerNo
FROM Reader
WHERE readerName='马永强'
)
--3.9 查询会计学院没有归还图书的读者编号、读者姓名、图书名称、借书日期和应归还日期。
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.10 查询借阅了清华大学出版社出版的图书的读者编号、读者姓名、图书名称、借书日期和归还日期。
SELECT Reader.readerNo,readerName,bookName,borrowDate,returnDate
FROM Reader,Borrow,Book
WHERE Reader.readerNo=Borrow.readerNo
AND Borrow.bookNo=Book.bookNo
AND publishingName='清华大学出版社'
--3.11 查询借书时间在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.12 查询在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
)
--3.13 查询没有借书的读者姓名（分别使用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 Reader.readerNo=Borrow.readerNo
AND returnDate IS NULL
)
--3.14 查询既借阅了“离散数学”图书又借阅了“数据库系统概念”两本书的读者编号、读者姓名、借书日期和图书名称。
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.15 查询没有借阅图书的读者编号、读者姓名和工作单位（分别使用IN子查询和存在量词子查询表达）。
--与3.13重复
--3.16 查询没有借阅“经济管理”类图书的读者编号、读者姓名和出生日期（分别使用IN子查询和存在量词子查询表达）。
--use IN
SELECT readerNo,readerName,SUBSTRING(identity
card,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.17 查询至少与读者“马永强”所借的图书一样的读者编号、读者姓名和工作单位。
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.18 查询每种类别的图书分类号、最高价格和平均价格，并按最高价格的降序输出。
SELECT classNo,MAX(price) AS maxPrice,AVG(price) AS avgPrice
FROM Book
GROUP BY classNo
ORDER BY MAX(price) DESC
--3.19 查询图书分类号为001号图书的入库数量。
SELECT SUM(shopNum) AS sumShopNum
FROM Book
GROUP BY classNo
HAVING classNo='001'
--3.20 查询借阅了图书类别为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
WHERE readerNo=Reader.readerNo
AND bookNo=Book.bookNo
)
)
--3.21 查询至少借阅了3本图书的读者编号、读者姓名、图书编号、图书名称，并按读者编号排序输出。
SELECT Reader.readerNo,readerName,Book.bookNo,bookName
FROM Reader,Borrow,Book
WHERE Reader.readerNo=Borrow.readerNo
AND Borrow.bookNo=Book.bookNo
AND Reader.readerNo IN(
SELECT readerNo
FROM Borrow
WHERE returnDate IS NULL
GROUP BY readerNo
HAVING COUNT(*)>=3
)
ORDER BY Reader.readerNo
--3.22 查询所借图书的总价在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.23 查询所借阅的图书总价最高的读者编号、读者姓名和出生日期。
SELECT readerNo,readerName,SUBSTRING(identitycard,7,8) AS birthday
FROM Reader
WHERE readerNo IN(
SELECT readerNo
FROM Borrow,Book
WHERE Borrow.bookNo=Book.bookNo
AND returnDate IS NULL
GROUP BY readerNo
HAVI
NG SUM(price) >=ALL(
SELECT SUM(price)
FROM Borrow,Book
WHERE Borrow.bookNo=Book.bookNo
AND returnDate IS NULL
GROUP BY readerNo
)
)
--3.24 将“经济管理”类图书的单价提高10%。
UPDATE Book
SET price=price*1.1
WHERE classNo IN(
SELECT classNo
FROM BookClass
WHERE className='经济管理'
)
--3.25 对于年龄在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
--3.26 创建一个视图，该视图为所借图书的总价在150元以上的读者编号、读者姓名和所借图书的总价。
CREATE VIEW BookView1
AS
SELECT Reader.readerNo,readerName,SUM(price) AS money
FROM Reader,Borrow,Book
WHERE Reader.readerNo=Borrow.readerNo
AND Borrow.bookNo=Book.bookNo
GROUP BY Reader.readerNo,readerName
HAVING SUM(price)>=150
GO
--3.27 创建一个视图，该视图为年龄在25~35岁之间的读者，属性列包括读者编号、读者姓名、年龄、工作单位、所借图书名称和借书日期。
CREATE VIEW BookView2
AS
SELECT Reader.readerNo,readerName,workUnit,bookName,borrowDate
FROM Reader,Borrow,Book
WHERE Reader.readerNo=Borrow.readerNo
AND Borrow.bookNo=Book.bookNo
AND CONVERT(int,SUBSTRING(identitycard,7,4)) BETWEEN 25 AND 35
GO
--3.28 创建一个视图，该视图仅包含“清华大学出版社”在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
--3.29 对由题3.28所建立的视图进行插入、删除和更新操作。
INSERT BookView3 VALUES('0000001','001','Linux网络技术','王波','机械工业出版社','9787111216063',28,'20070701','20070901',80)
DELETE BookView3 WHERE bookName LIKE 'Linux%'
UPDATE BookView3 SET shopNum=shopNum-10
--3.30 将入库数量最多的图书单价下调5%。
UPDATE Book
SET price=price*0.95
WHERE shopNum=(
SELECT MAX(shopNum)
FROM Book
)