SQL第五次上机实验

1.向图书表（Book）插入以下记录

USE TSGL
GO
INSERT INTO Book
VALUES('7-5402-1800-3','文学类','边城','沈从文','燕山出版社','10','5','5')

2.向借阅表插入以下两条记录

USE TSGL
GO
INSERT INTO Lend
VALUES('201207034201','7-5402-1800-3','00366240','2013-04-22'),('201207034201','4-3042-1056-4','00366260','2013-04-22')

3.创建一个借阅统计表，将按系部和专业统计的读者数量和在借图书册数的信息放入借阅统计表中

USE TSGL
GO
CREATE TABLE JIEYUETONGJIBIAO
(Dept char(12),
Spec char(12),
R_num real,
B_num real
)
INSERT INTO JIEYUETONGJIBIAO(Dept,Spec,R_num,B_num)
(SELECT Dept,Spec,COUNT(Lno) R_num,SUM(BorNum) B_num
FROM Reader
GROUP BY Dept,Spec
)

4.将读者表（Reader）中借书证号为201207034201的借阅数量改为2

USE TSGL
GO
UPDATE Reader
SET BorNum='2'
WHERE Lno='201207034201'

5.将希望出版社出版的图书单价修改为原价的8折

USE TSGL
GO
UPDATE Book
SET Price=Price*0.8
WHERE Press='希望出版社'

6.将借阅了《乔布斯传》一书的借阅记录中的借阅时间推后一个月

USE TSGL
GO
UPDATE Lend
SET Bordate = DATEADD(MONTH,1,Bordate)
WHERE ISBN='7-54-425399-4'

发现Bordate的格式发生了改变
7.从历史借阅表（History）中，删除所有关于文学类图书的借阅记录

USE TSGL
GO
DELETE
FROM History
WHERE ISBN IN (
SELECT ISBN
FROM Book
WHERE Class='文学类')

我猜这个也可以

USE TSGL
GO
DELETE
FROM History
WHERE '文学类' = (
SELECT Class
FROM Book
WHERE History.ISBN=Book.ISBN)

8.删除历史借阅表中的所有记录

USE TSGL
GO
DELETE
FROM History

或TRUNCATE TABLE History


9.由于王朋退学，在Reader表中删除该读者记录

USE TSGL
GO
DELETE 
FROM Reader
WHERE Rname='王朋'

10.借书证号为201207034201的读者来归还ISBN为4-6045-1023-4的图书，请对Lend和History两表进行相关信息

ALTER TABLE History
DROP COLUMN F6

不知道History咋突然多了一列F6我先给它删了

USE TSGL
GO
DELETE
FROM Lend
WHERE Lno='201207034201' AND ISBN='4-6045-1023-4'

INSERT INTO History
VALUES('201207034201','4-6045-1023-4','00366341','2013-03-12','2023-11-09')

DELETE FROM Lend
WHERE Lno='NULL';