SQL第三次上机作业

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

USE TSGL
GO
SELECT Lno,Rname
FROM Reader
WHERE Dept=(SELECT Dept
            FROM Reader
            WHERE Rname='王利') and Rname != '王利'
1
2
3
4
5
6
7

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

SELECT *
FROM Book
WHERE Price>(SELECT MAX(Price)
             FROM Book
             WHERE Press='希望出版社')
1
2
3
4
5

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

SELECT Lno,Rname,Spec
FROM Reader
WHERE  EXISTS
     (SELECT *
     FROM Lend
     WHERE Lend.Lno=Reader.Lno)
1
2
3
4
5
6

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

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

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

修改

USE TSGL
GO
select *
from book
where ISBN in (select ISBN from lend) and Bname like '%SQL%'
1
2
3
4
5

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 != '计算机类'
1
2
3
4
5

修改：

select lno,rname,spec
from reader
where lno not in (select lno from lend,book where lend.isbn=book.isbn and type='计算机类')
1
2
3

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

SELECT *
FROM Reader
WHERE Lno NOT IN (
SELECT History.Lno
FROM History
WHERE History.Retdate IS NULL
)
1
2
3
4
5
6
7

这个我真的不知道咋回事

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
)
1
2
3
4
5
6
7
8
9

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

select *
from Reader
where Lno <> ALL(select Lno from Lend) and Lno in (select Lno from History)
1
2
3

老师这样写的也没查询结果

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'
)
1
2
3
4
5
6
7
8

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

SELECT Lno
FROM Lend
WHERE ISBN = '4-6081-1062-1'

INTERSECT

SELECT Lno
FROM Lend
WHERE ISBN = '4-6076-1087-3'
1
2
3
4
5
6
7
8
9

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

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

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

SELECT Rname
FROM Reader 
WHERE Lno IN(SELECT Lno
           FROM  Lend
           WHERE ISBN IN(SELECT ISBN
                       FROM Book
                       WHERE Class='文学类'))
1
2
3
4
5
6
7

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

仅供参考~