一个图书管理数据库BookDB的模式如图3-31—图3-34所示。请基于该数据库模式用SQL语句完成如下操作。
属性含义
属性名
类型
宽度
小数位
分类号
classNo
字符型
3
分类名称
className
字符型
20
图3-31 图书分类表BookClass的模式
属性含义
属性名
类型
宽度
小数位
图书编号
bookNo
字符型
10
分类号
classNo
字符型
3
图书名称
BookName
字符型
40
作者姓名
authorName
字符型
8
出版社名称
publishingName
字符型
20
出版号
publishingNo
字符型
17
单价
price
数值型
7
2
出版时间
publishingDate
日期型
8
入库时间
shopDate
日期型
8
入库数量
shopNum
数值型
3
图3-32 图书表Book的模式
属性含义
属性名
类型
宽度
小数位
读者编号
readerNo
字符型
8
姓名
readerName
字符型
8
性别
sex
字符型
2
身份证号
identitycard
字符型
18
工作单位
workUnit
字符型
50
图3-33 读者表Reader的模式
属性含义
属性名
类型
宽度
小数位
读者编号
readerNo
字符型
8
exists子查询图书编号
bookNo
字符型
10
借阅日期
borrowDate
日期型
8
应归还日期
shouldDate
日期型
8
归还日期
returnDate
日期型
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 查询会计学院没有归还所借图书的读者编号、读者姓名、图书名称、借书日期和应归还日期。
aderNo,readerName,bookName,borrowDate,shouldDate
FROM Reader,Borrow,Book
aderNo
    AND Borrow.bookNo=Book.bookNo
    AND workUnit='会计学院' AND returnDate IS NULL
3.9 查询借阅了清华大学出版社出版的图书的读者编号、读者姓名、图书名称、借书日期和归还日期。
aderNo,readerName,bookName,borrowDate,returnDate
FROM Reader,Borrow,Book
aderNo
    AND Borrow.bookNo=Book.bookNo
    AND publishingName='清华大学出版社'
3.10 查询借书时间在2007-2008年之间的读者编号、读者姓名、图书编号、图书名称。
aderNo,readerName,Book.bookNo,bookName
FROM Reader,Borrow,Book
aderNo
    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<ANY
(SELECT classNo,shopNum
FROM Book
WHERE classNo=’009’)
3.13 查询所借图书的总价在150元以上的读者编号、读者姓名和所借图书的总价。
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)>=150
3.14 查询没有借阅图书的读者姓名和工作单位（分别使用IN子查询和存在量词子查询表达）。
--use IN
SELECT readerName，workUnit
FROM Reader
WHERE readerNo NOT IN(
    SELECT readerNo
    FROM Borrow