作业2
九九乘法表
DECLARE @i intDECLARE @j intDECLARE @str varchar(110)SET @i=1WHILE @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
作业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 intdeclare @TDay datetimeset @TDay=DateAdd(dd,1,getDate()) --获取明天的日期select @count=count(*) from Borrow where willDate<@TDayIF(@count=0) BEGIN print '明天没有应归还的图书' EndElseIF(@count<10)BEGIN Update Borrow set WillDate=DateAdd(dd,2,WillDate) WHERE willDate<@TDayENDElse 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<@TDayprint @countEnd
作业5
查询图书总额
/*数据库表见:建库建表.sql*/DECLARE @count int,@totalMoney moneySELECT @count=sum(BCount) FROM bookSELECT @totalMoney=sum(BCount*Price) FROM bookprint '现存数量'+convert(varchar(10),@count)print '总金额'+convert(varchar(10),@totalMoney)IF @count<10000 print '现有图书不足一万本,还需要继续购置书籍' ELSE print '现有图书在一万本以上,需要管理员加强图书管理'
0 0