SQL第三次上机作业

1.查询与王利就读同一专业学生的借书证号和姓名

SELECT Lno,Rname
FROM Reader
WHERE Dept=(SELECT DeptFROM ReaderWHERE Rname='王利')

2.查询比希望出版社出版的所有图书价格都高的图书信息

SELECT *
FROM Book
WHERE Price>(SELECT MAX(Price)FROM BookWHERE Press='希望出版社')

3.显示所有借阅了图书的借书证号、姓名和专业

SELECT Lno,Rname,Spec
FROM Reader
WHERE  EXISTS(SELECT *FROM LendWHERE Lend.Lno=Reader.Lno)

括号里面是查询出已借阅的记录，然后使用 exists进行判断就是已查询的

4.查找已被借阅的书名中含有“SQL”字样的图书信息

SELECT *
FROM BOOK,Lend
WHERE BOOK.ISBN=Lend.ISBN AND Book.Bname LIKE '%SQL%'

5.查询没有借阅计算机类图书的读者借书证号、姓名和专业信息

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

6.查询已经归还全部结束的读者信息

SELECT *
FROM Reader
WHERE Lno NOT IN (
SELECT History.Lno
FROM History
WHERE History.Retdate IS NULL
)

这个我真的不知道咋回事

USE TSGL
GO
SELECT *
FROM Reader
WHERE NOT EXISTS(
SELECT Lend.Lno
FROM History FULL JOIN Lend ON History.Lno = Lend.Lno
WHERE History.Retdate IS NULL
)

我是这样写的但是查询出来没结果很奇怪，，坐等老师的正确答案

7.查询借阅了ISBN为“7-5051-1078-0”图书的计算机系读者的姓名

SELECT Rname
FROM Reader
WHERE Dept = '计算机系' 
AND Lno IN (
SELECT Lno 
FROM Lend
WHERE ISBN = '7-5051-1078-0'
)

8.查询同时借阅了ISBN为4-6081-1062-1和4-6076-1087-3图书的借书证号

SELECT Lno
FROM Lend
WHERE ISBN = '4-6081-1062-1'INTERSECTSELECT Lno
FROM Lend
WHERE ISBN = '4-6076-1087-3'

9.查询不是清华大学出版社的计算机类图书的书名、作者和出版社信息

SELECT Bname,Author,Press
FROM Book
WHERE Class='计算机类' AND Press !='清华大学出版社'

10.查找借阅了全部文学类图书的读者姓名

SELECT Rname
FROM Reader 
WHERE Lno IN(SELECT LnoFROM  LendWHERE ISBN IN(SELECT ISBNFROM BookWHERE Class='文学类'))

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

仅供参考~