1、查询从来没有借过书的读者姓名。（分别使用 not in、not exists和连接查询三种写法实现，体会写法的不同之处）
SELECT readerName FROM Reader
WHERE readerNo not in (SELECT readerNo FROM Borrow );
SELECT readerName FROM Reader a
WHERE NOT EXISTS(SELECT * FROM Borrow b WHERE a.readerNo = b.readerNo);
SELECT a.readerName
FROM Reader a LEFT JOIN Borrow b
ON a.readerNo = b.readerNo
where b.readerNo IS null;

2.拿着类别编号匹配

SELECT a.readerNo,a.readerName,SUBSTRING(a.identifycard, 7, 8) AS birthday
FROM Reader a,Borrow b,Book c
WHERE a.readerNo = b.readerNo
AND b.bookNo = c.bookNo
AND c.classNo not in (
SELECT b.classNo FROM Book a LEFT JOIN BookClass b
ON a.classNo = b.classNo
WHERE b.className = '经济类')
GROUP BY a.readerNo,a.readerName,a.identifycard;
3、借阅过政治经济学图书有借阅过数据库系统概念图书的读者编号和读者姓名。
政治经济学','数据库系统概念')

1.查询借阅过政治经济学图书的读者编号和读者姓名

2.查询借阅过数据库系统概念图书的读者编号和读者姓名

SELECT b.readerNo,b.readerName
FROM Borrow a LEFT JOIN Reader b
ON a.readerNo = b.readerNo
WHERE a.readerNo IN(
SELECT readerNo FROM book a LEFT JOIN Borrow b
ON a.bookNo = b.bookNo
WHERE a.bookName = '政治经济学' OR a.bookName = '数据库系统概念')
GROUP BY b.readerNo,b.readerName;
4、查询既借过“政治经济学”图书又借过“数据库系统概论”图书的读者编号、读者姓名以及这两种图书的名称、借书日期和归还日期。(分别使用in和union两种方法实现，体会写法的不同)

第一种方法,使用使用in

SELECT a.readerNo,a.readerName,b.bookName,c.borrowDate,c.returnDate
FROM Reader a,Book b,Borrow c
WHERE a.readerNo = c.readerNo
AND b.bookNo = c.bookNo
AND b.bookName IN('政治经济学','数据库系统概念')
AND a.readerNo IN(
SELECT a.readerNo FROM Borrow a,Book b
WHERE a.bookNo = b.bookNo
AND b.bookName = '政治经济学'
AND a.readerNo IN(
SELECT a.readerNo FROM Borrow a,Book b
WHERE a.bookNo = b.bookNo
AND b.bookName = '数据库系统概念')
)

第二种方法,使用使用union

SELECT a.readerNo,a.readerName,b.bookName,c.borrowDate,c.returnDate
FROM Reader a,Book b,Borrow c
WHERE a.readerNo = c.readerNo
AND b.bookNo = c.bookNo
AND b.bookName = '政治经济学'
AND a.readerNo IN ( SELECT a.readerNo
FROM Borrow a,Book b
WHERE a.bookNo = b.bookNo
AND b.bookName = '数据库系统概念')
UNION
SELECT a.readerNo,a.readerName,b.bookName,c.borrowDate,c.returnDate
FROM Reader a,Book b,Borrow c
WHERE a.readerNo = c.readerNo
AND b.bookNo = c.bookNo
AND b.bookName = '数据库系统概念'
AND a.readerNo IN ( SELECT a.readerNo
FROM Borrow a,Book b
WHERE a.bookNo = b.bookNo
AND b.bookName = '政治经济学')

5、查询至少借阅过读者张小娟所借阅过的所有图书的读者编号、读者姓名和工作单位。
SELECT b.readerNo,b.readerName,b.workUnit
FROM Borrow a ,Reader b
WHERE bookNo IN (
SELECT bookNo
FROM Borrow
WHERE readerNo =(
SELECT readerNo
FROM Reader
WHERE readerName = '张小娟')
)
GROUP BY b.readerNo,b.readerName,b.workUnit;

6、查询至少有3本在借图书的读者编号、读者姓名以及在借图书的图书编号、图书名称，按读者编号升序、借阅日期降序排序输出。

SELECT a.readerNo,a.readerName,c.bookNo,c.bookName
FROM Reader a, Borrow b ,Book c
WHERE a.readerNo = b.readerNo
AND b.returnDate IS NULL
GROUP BY a.readerNo,a.readerName,c.bookNo,c.bookName
HAVING count(b.bookNo)>2
ORDER BY a.readerNo

如何解决按照借阅日期降序

7、查找价格高于数据库系统概念的图书清单。（使用连接查询和子查询两种写法，体会写法的不同之处）

第一种方法，使用连接查询

SELECT a.bookNo,a.classNo,a.bookName,a.authorName,a.price,a.publishingDate,a.shopDate,a.shopNum FROM Book AS a
INNER JOIN Book AS b
ON b.bookName = '数据库系统概念'
WHERE b.price < a.price

第二种方法，使用子查询

SELECT * FROM Book
WHERE price > (SELECT price FROM Book where bookName = '数据库系统概念')

8、查询从来没有接过书的单位名称。（分别使用 not in、not exists和连接查询三种写法实现，体会写法的不同之处）

第一种方法，使用 not in

SELECT a.workUnit FROM Reader a,Borrow b
WHERE a.readerNo not in (b.readerNo)
GROUP BY a.workUnit;

第二种方法，使用 not exists

SELECT a.workUnit FROM Reader a
WHERE not exists (SELECT a.workUnit FROM Reader a RIGHT OUTER JOIN Borrow b ON a.readerNo = b.readerNo GROUP BY a.workUnit )

第三种方法，使用 连接查询

SELECT workUnit
FROM Reader a LEFT OUTER JOIN Borrow b
ON a.readerNo = b.readerNo
GROUP BY a.workUnit;

9、查询借的最多的图书的图书编号和图书名称。
SELECT b.bookNo,b.bookName
FROM Book b
WHERE b.bookNo = (
SELECT bookNo FROM Borrow
GROUP BY bookNo HAVING COUNT(bookNo)=(
SELECT TOP 1 count(bookNo) FROM Borrow
GROUP BY bookNo
ORDER BY count(bookNo) DESC)
)

10、再图书表中计算每本书的价格与平均价格之差，结果显示图书编号、图书名称和平均差（价格-avg(价格)），并按照平均差降序排列。
SELECT bookNo,bookName,price-(
SELECT top 1 (SELECT SUM(price) FROM Book)/(SELECT count(price) FROM Book ) as subs FROM Book
) AS avgSub
FROM Book
GROUP BY bookNo,bookName,price
ORDER BY avgSub ASC

11、根据图书表查找每类图书的价格最高的图书，显示分类号、图书名称和价格，并按照分类号升序。（分别使用exists、not exists和派生表查询三种写法实现，体会写法的不同之处）

1. 使用exists查询

SELECT a.classNo,a.bookName,a.price
FROM Book a
WHERE EXISTS (
SELECT 1
FROM Book b
WHERE a.classNo = b.classNo
GROUP BY b.classNo
HAVING MAX(b.price) = a.price
)
GROUP BY classNo,bookName,price
ORDER BY a.classNo;

2. 使用not exists查询

SELECT a.classNo,a.bookName,a.price
FROM Book a
WHERE NOT EXISTS (
SELECT 1
FROM Book b
WHERE a.classNo = b.classNo
AND b.price > a.price
)
GROUP BY classNo,bookName,price
ORDER BY a.classNo ASC;

3. 使用派生表查询

SELECT classNo,bookName,price
FROM Book where price IN(
SELECT MAX(price) as maxPrice FROM Book GROUP BY classNo)
ORDER BY classNo ASC;

12、查询所有借的图书的图书编号、图书名称、借阅次数以及根据借阅次数的排名（借阅次数越大，排名就靠前，类似根据成绩排名），并按照借阅次数降序排列。
使用两种方法：方法1：使用DENSE_RANK()over(order by ）或者RANK()over(order by）（延申掌握ROW_NUMBER()，NTILE(数字)over(order by ），row_number，
方法2：使用借阅次数的比较法，提示：先求出图书编号、图书名称、借阅次数，再根据借阅次数做比较，如果想得到的排名为1、2、2、4就不添加去重，如果得到的排名为：1、2、2、3则需要添加distinct。

1.

SELECT a.bookNo,a.bookName,COUNT(bookName) as borrow_count ,
RANK() OVER (ORDER BY COUNT(*) DESC) AS rank
FROM Book a INNER JOIN Borrow b
ON a.bookNo = b.bookNo
GROUP BY a.bookNo,a.bookName
ORDER BY borrow_count

13、查询借阅量排名第三的图书名称和图书编号。
SELECT a.bookName,a.bookNo
FROM Book a INNER JOIN (
SELECT TOP 3 bookNo,COUNT(*) AS borrow_count
FROM Borrow
GROUP BY bookNo
ORDER BY borrow_count DESC
) AS b
ON a.bookNo = b.bookNo;