SQL 第六章简答题

最新推荐文章于 2023-04-22 08:14:53 发布

火火儿

最新推荐文章于 2023-04-22 08:14:53 发布

阅读量702

点赞数

本文链接：https://blog.csdn.net/weixin_36024620/article/details/56845356

版权

		简答三
BEGIN TRANSACTION
DECLARE @errorSum int
DECLARE @RID varchar(50)
DECLARE @BID varchar(50)
SELECT @RID=RID FROM Reader WHERE RName='张无忌'
SELECT @BID=BID FROM Book WHERE BName='深入。Net平台和c#编程'
SET @errorSum=0
INSERT INTO Borrow(RID,BID)VALUES (@RID,@BID)
SET @errorSum=@errorSum+@@error
UPDATE Book SET BCount=BCount-1 WHERE BName=' 深入。Net平台和c#编程'
SET @errorSum=@errorSum+@@error
UPDATE Reader SET LendNum=LendNum+1 WHERE RName='张无忌'
SET @errorSum=@errorSum+@@error
IF @errorSum<>0
		ROLLBACK TRANSACTION
	ELSE 
	COMMIT TRANSACTION
GO

			四
BEGIN TRANSACTION


DECLARE @errorSum int
DECLARE @RID varchar(50)
DECLARE @BID varchar(50)
SELECT @RID=RID FROM Reader WHERE RName='刘冰冰'
SELECT @BID=BID FROM Book WHERE BName='西游记'
SET @errorSum=0
INSERT INTO Penalty(RID,BID,PType,Amount) VALUES(@RID,@BID,'1',5.6)
SET @errorSum=@errorSum+@@error
UPDATE Borrow SET ReturnDate=getdate() WHERE BID=@BID
SET @errorSum=@errorSum+@@error
UPDATE Reader SET LendNum=LendNum-1 WHERE RName='刘冰冰'
SET @errorSum=@errorSum+@@error
UPDATE Book SET BCount=BCount+1 WHERE BID=@BID
SET @errorSum=@errorSum+@@error
IF @errorSum<>0
		ROLLBACK TRANSACTION
	ELSE 
	COMMIT TRANSACTION
GO

					五
USE Library
GO

--创建索引
IF EXISTS (SELECT * FROM sysindexes 
          WHERE name = 'IX_BOOK_BookName')
   DROP INDEX Book.IX_BOOK_BookName  --删除索引
/*--笔试列创建非聚集索引：填充因子为30％--*/
CREATE INDEX IX_BOOK_BookName
   ON BOOK(BName)
	   WITH FILLFACTOR = 30
GO

--创建管理员视图
IF EXISTS (SELECT  * FROM sysobjects  WHERE  NAME='view_borrow')
	Drop VIEW view_borrow
go
CREATE VIEW view_borrow 
	AS
		SELECT   图书名称=Book.BNAME,到期时间=Borrow.WillDate,读者姓名=Book.BNAME 
		from Borrow,book 
		with (index = IX_BOOK_BookName)
		WHERE  Book.BID=Borrow.BID 
		--order by Borrow.WillDate
GO

SELECT * FROM view_borrow


--创建读者视图
IF EXISTS(SELECT  *FROM sysobjects  WHERE  NAME='view_book')
	DROP VIEW view_book
	go
CREATE VIEW view_book
	As 
		SELECT  图书名称=BNAME,图书总量=BCount,可借阅量=BCount-(
		  SELECT COUNT(*) FROM Borrow WHERE returnDate is null AND Borrow.bid = Book.bid
		) FROM Book 
		with (index = IX_BOOK_BookName)
GO

SELECT * FROM view_book