SQL第四次上机实验

第四次上机实验"/>

SQL第四次上机实验

1.查询借阅了计算机类或者文学类图书的读者的借书证号

USE TSGL
GO
SELECT DISTINCT Reader.Lno
FROM Book,Lend,Reader
WHERE Book.ISBN=Lend.ISBN AND Lend.Lno=Reader.Lno AND Class = '计算机类' OR Class = '文学类'

2.查询同时借阅了计算机类和文学类图书的读者的借书证号

USE TSGL
GO
SELECT DISTINCT Reader.Lno
FROM Book,Lend,Reader
WHERE Book.ISBN=Lend.ISBN AND Lend.Lno=Reader.Lno AND Class = '计算机类' 
INTERSECT
SELECT DISTINCT Reader.Lno
FROM Book,Lend,Reader
WHERE Book.ISBN=Lend.ISBN AND Lend.Lno=Reader.Lno AND Class = '文学类'

USE TSGL
GO
SELECT DISTINCT Reader.Lno
FROM Book,Lend,Reader
WHERE Book.ISBN=Lend.ISBN AND Lend.Lno=Reader.Lno AND Class = '计算机类' AND Class = '文学类'

但不知道为什么我用and连接的话就查询不到

因为一本书不能既是计算机类又是文学类所以and不行，可以用子查询

USE TSGL
GO
SELECT DISTINCT Reader.Lno
FROM Book,Lend,Reader
WHERE Book.ISBN=Lend.ISBN AND Lend.Lno=Reader.Lno AND Class = '计算机类' AND Reader.Lno IN (SELECT Lend.LnoFROM Book,Lend,ReaderWHERE Book.ISBN=Lend.ISBN AND Lend.Lno=Reader.Lno AND Book.Class = '文学类'
)

3.查询只借阅了计算机类图书，而没有借阅文学类图书的读者借书证号、ISBN和借书日期

USE TSGL
GO
SELECT DISTINCT Reader.Lno,Lend.ISBN,Lend.Bordate
FROM Book,Lend,Reader
WHERE Book.ISBN=Lend.ISBN AND Lend.Lno=Reader.Lno AND Class = '计算机类' 
EXCEPT
SELECT DISTINCT Reader.Lno,Lend.ISBN,Lend.Bordate
FROM Book,Lend,Reader
WHERE Book.ISBN=Lend.ISBN AND Lend.Lno=Reader.Lno AND Class = '文学类'

4.查询没有被借阅的图书的ISBN和书名

USE TSGL
GO
SELECT Book.ISBN,Book.Bname
FROM Book
WHERE ISBN in
(SELECT ISBN
FROM BookEXCEPT
SELECT DISTINCT ISBN
FROM Lend)

5.查询没有借阅计算机类图书的读者借书证号、ISBN和借书日期

USE TSGL
GO
SELECT Book.ISBN
FROM Lend,Book
WHERE Book.ISBN=Lend.ISBN AND Book.ISBN NOT IN (SELECT Book.ISBN
FROM Book,Lend
WHERE Book.ISBN=Lend.ISBN AND Class = '计算机类')

上面这个不太对
改良版

USE TSGL
GO
SELECT Lend.Lno,Lend.ISBN,Lend.Bordate
FROM Lend
WHERE Lend.ISBN NOT IN (SELECT Book.ISBN
FROM Book
WHERE Class = '计算机类')

我又不知道为什么下面这个为什么查询不出来

USE TSGL
GO
SELECT Book.ISBN
FROM Lend,Book
WHERE NOT EXISTS (SELECT Book.ISBN
FROM Book,Lend
WHERE Book.ISBN=Lend.ISBN AND Class = '计算机类')

并且子查询是没有问题的

因为要考虑这两个是如何做的，not exict是说有相同的ISBN存在返回false，不存在返回True
改良后这个查询结果和前面是一样的但是多了一个NULL值行又不知道为什么

USE TSGL
GO
SELECT DISTINCT Lend.Lno,Lend.ISBN,Lend.Bordate
FROM Lend
WHERE NOT EXISTS (SELECT *FROM BookWHERE Lend.ISBN = Book.ISBN AND Book.Class = '计算机类'
)

6.查询借阅了没有与借书证号为201207035102的读者所借全部图书相同的读者的借书证号

USE TSGL
GO
SELECT Reader.Lno
FROM Reader,Lend
WHERE Reader.Lno=Lend.Lno AND Lend.ISBN NOT IN (SELECT Lend.ISBN
FROM Reader,Lend
WHERE Reader.Lno=Lend.Lno AND  Reader.Lno= '201207035102')