sql第二次上机作业

1查找借阅了ISBN为“4-6045-1023-4”的借书证号，读者姓名，专业名和借书时间

use tsgl
go
select Reader.Lno,Rname,Spec,Lend.Bordate
FROM Reader,Lend
WHERE Reader.Lno=Lend.Lno AND ISBN = '4-6045-1023-4'
1
2
3
4
5

2查找借阅了《数据库原理》一书的借阅信息，结果显示借书证号，读者姓名，专业名，借书时间（要求用WHERE方法实现）

use tsgl
go
select Reader.Lno,Rname,Spec,Lend.Bordate
FROM Reader,Lend,Book
WHERE Reader.Lno=Lend.Lno AND Lend.ISBN = Book.ISBN AND Bname = '数据库原理'
1
2
3
4
5

3查询读者的借阅信息，显示借书证号、姓名、专业名、ISBN、书名、借书时间，并按借阅时间降序排序（要求用JOIN方法实现）

USE tsgl
GO
SELECT Reader.Lno, Rname, Spec, Lend.ISBN, Bname, Lend.Bordate
FROM Reader 
INNER JOIN Lend ON Reader.Lno = Lend.Lno 
INNER JOIN Book ON Lend.ISBN = Book.ISBN 
ORDER BY Lend.Bordate DESC
1
2
3
4
5
6
7

4从Lend表中查询至少被两名同学借阅的图书，显示ISBN号、书名、和借阅人数。

USE tsgl
GO
SELECT Lend.ISBN, Bname, count(Lend.Lno) AS 借阅人数
FROM Lend JOIN Book
	ON Lend.ISBN=Book.ISBN
GROUP BY Lend.ISBN ,Book.Bname Having count(Lend.Lno)>=2
1
2
3
4
5
6

5 查询与“王朋”同一专业的读者信息
子查询：

USE tsgl
GO
SELECT *
FROM Reader
WHERE Spec = (
	SELECT Spec
	FROM Reader
	WHERE Rname = '王朋'
)
1
2
3
4
5
6
7
8
9

如果已知王朋是哪个专业

USE tsgl
GO
SELECT *
FROM Reader
WHERE Spec = '网络工程'
1
2
3
4
5

法二：
自连接

USE tsgl
GO
SELECT c1.*
from Reader c1,Reader c2
where c1.Spec = c2.Spec and c2.Rname='王朋'and c1.Rname<>'王朋'
1
2
3
4
5

结果是一样的
6 查询从来没有借阅过图书的学生信息

USE tsgl
GO
SELECT *
FROM Reader
WHERE Lno NOT IN(
	SELECT DISTINCT Lno
	FROM Lend
	WHERE Lno IS NOT NULL
)And Lno not in
(select Lno 
from History
where Lno is not null)
1
2
3
4
5
6
7
8
9
10
11
12

一开始我写的没有让Lend里的Lno不等于NULL时查询出来的是空我很纳闷

USE tsgl
GO
SELECT *
FROM Reader
WHERE Lno NOT IN(
	SELECT DISTINCT Lno
	FROM Lend
)
1
2
3
4
5
6
7
8

然后问gpt说可能我的Lno 存在空值：如果 Reader 表或 Lend 表中的 Lno 列中存在 NULL 值，则导致该查询无法正确匹配，结果集为空。您可以使用 IS NOT NULL 运算符或 INNER JOIN 语句的方式规避这个问题。
于是我查了一下Lend的Lno发现真的存在NULL值我也不知道为什么！！！

法二：

USE tsgl
GO
SELECT Reader.*
from Reader left outer join History on Reader.Lno=History.Lno
where History.ISBN is null and Bornum=0
1
2
3
4
5

7 查询没有借书未还的读者信息（利用Lend和Reader表的外连接实现查询）

USE tsgl
GO
SELECT Reader.*
from Reader left join Lend
on Reader.Lno = Lend.Lno
where ISBN is NULL
1
2
3
4
5
6

第七题有点儿不懂可能写的不对hhh
8 查询曾借阅了同一种图书的所有读者对

USE tsgl
GO
SELECT distinct a.Lno,b.Lno
from Lend a,Lend b
where a.Lno < b.Lno and a.ISBN = b.ISBN
1
2
3
4
5

USE tsgl
GO
SELECT L1.Lno, L2.Lno
FROM Lend L1
INNER JOIN Lend L2 ON L1.ISBN = L2.ISBN AND L1.Lno < L2.Lno
WHERE L1.ISBN IN (
    SELECT ISBN
    FROM Lend
    GROUP BY ISBN
    HAVING COUNT(*) > 1
)
1
2
3
4
5
6
7
8
9
10
11

感觉这个题有点儿坑，因为三个人借了同一本书有三个读者对，两个人借了同一本书只有一个读者对，只有一个人借了同一本书就没有读者对，也不知道我理解的对不对~
9从lend表和Reader表中查询各专业读者的人数，平均借阅册数，结果显示专业名、专业人数和人均节约图书册数、统计借阅册数不要直接使用Reader表中的Bornum属性值来实现。

USE tsgl
GO
SELECT Reader.Spec AS 专业名, COUNT(DISTINCT Reader.Lno) AS 专业人数, 
    ROUND(COUNT(Lend.ISBN) / COUNT(DISTINCT Reader.Lno),2) AS 人均借阅册数
FROM Reader
LEFT JOIN Lend 
ON Reader.Lno = Lend.Lno
GROUP BY Reader.Spec;
1
2
3
4
5
6
7
8

人数是没错的，但人均借阅册数我不知道对不对
参考1：

USE tsgl
GO
SELECT Spec 专业名,count(distinct Reader.Lno) 专业人数,count(case when ISBN is null then 0 else 1 end)/count(distinct Reader.Lno)as 人均借阅图书册数
from Reader left join Lend on Reader.Lno=Lend.Lno
group by Spec
1
2
3
4
5

参考二：

USE tsgl
GO
SELECT Spec 专业名,count(distinct Reader.Lno) 专业人数,cast(count(case when ISBN is null then 0 else 1 end)as float)/count(distinct Reader.Lno)as 人均借阅图书册数
from Reader left join Lend on Reader.Lno=Lend.Lno
group by Spec
1
2
3
4
5

参考1和参考二两个查询语句的主要区别在于第一个查询语句在计算“人均借阅图书册数”时使用了 CAST 函数将分子转换为浮点数，从而避免了整数除法的问题。通常情况下，整数除法会向零舍入，而不会四舍五入，因此它可能会导致一些不准确的结果。通过使用 CAST 函数或者其他方法将分子转换为浮点数，可以避免这个问题。

总之，第一个查询语句比第二个查询语句更准确，因为它避免了整数除法的问题。

10 列出有超期还书记录的读者的借书证号、姓名及书名

USE tsgl
GO
SELECT L.Lno, R.Rname, B.Bname
FROM Lend L
JOIN Reader R ON L.Lno = R.Lno
JOIN Book B ON L.ISBN = B.ISBN
WHERE DATEDIFF(day, L.Bordate, GETDATE()) > 60
1
2
3
4
5
6
7

USE tsgl GO SELECT Reader.Lno,Rname,Bname from Reader,History,Book where Reader.Lno = History.Lno And History.ISBN = Book.ISBN And Retdate>DATEADD(D,60,Bordate)

仅供个人参考后面几题我也不知道对不对