優化MySchool 第三章作業


作業2    

九九乘法表


DECLARE @i int
DECLARE @j int
DECLARE @str varchar(110)
SET @i=1

WHILE @i<10
BEGIN
SET @j=1
SET @str=''
WHILE @j<[email protected]
BEGIN
SET @str= @str+CAST(@i AS varchar(1))+' x '+CAST(@j AS varchar(1))+'='+CAST(@[email protected] AS varchar(2))+' '
SET @[email protected]+1
END
print @str
SET @[email protected]+1

END




作業3

  查詢罰款
/*數據庫表見:建庫建表.sql*/
Print '罰款記錄表情況如下:'
Select RID ,BID ,PDate,Amount, 罰款類型=case
When PType=1 then '損壞'
When Ptype=2 then '延期'
When Ptype=3 then '丟失'
End
from Penalty






作業4

   借書記錄
/*數據庫表見:建庫建表.sql*/
declare @count int
declare @TDay datetime
set @TDay=DateAdd(dd,1,getDate()) --獲取明天的日期
select @count=count(*) from Borrow where willDate<@TDay
IF(@count=0)
BEGIN
print '明天沒有應歸還的圖書'
End
Else
IF(@count<10)
BEGIN
Update Borrow set WillDate=DateAdd(dd,2,WillDate) WHERE willDate<@TDay
END
Else
BEGIN
print '還書總數量:'
SELECT book.BName, reader.RName, borrow.LendDate FROM Book as book,Reader as reader,Borrow as borrow WHERE book.BID=borrow.BID and reader.RID=borrow.RID and borrow.willDate<@TDay
print @count
End




作業5

     查詢圖書總額

/*數據庫表見:建庫建表.sql*/
DECLARE @count int,@totalMoney money
SELECT @count=sum(BCount) FROM book
SELECT @totalMoney=sum(BCount*Price) FROM book
print '現存數量'+convert(varchar(10),@count)
print '總金額'+convert(varchar(10),@totalMoney)

IF @count<10000
print '現有圖書不足一萬本,還需要繼續購置書籍'
ELSE
print '現有圖書在一萬本以上,需要管理員加強圖書管理'


                                
                                                                
                           
                            

0 個評論

要回覆文章請先登錄註冊