1.查询与王利就读同一专业学生的借书证号和姓名
USE TSGL
GO
SELECT Lno,Rname
FROM Reader
WHERE Dept=(SELECT DeptFROM ReaderWHERE Rname='王利') and 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%'
修改
USE TSGL
GO
select *
from book
where ISBN in (select ISBN from lend) and 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 != '计算机类'
修改:
select lno,rname,spec
from reader
where lno not in (select lno from lend,book where lend.isbn=book.isbn and type='计算机类')
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
)
我是这样写的但是查询出来没结果很奇怪,,坐等老师的正确答案
select *
from Reader
where Lno <> ALL(select Lno from Lend) and Lno in (select Lno from History)
老师这样写的也没查询结果
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 = '文学类'
仅供参考~