第三章解答题

九九乘法表
DECLARE @i int
DECLARE @j int
DECLARE @str varchar(110)
SET  @i=1

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

  END

罚款记录
Print '罚款记录表情况如下：'
Select RID ,BID ,PDate,Amount, 罚款类型=case
                  When PType=1 then '损坏'
                  When Ptype=2 then '延期'
                  When Ptype=3 then  '丢失'
End 
from Penalty

借书记录
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

查询图书总额
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 '现有图书在一万本以上，需要管理员加强图书管理'