优化MySchool数据库 第三章上机 简答

--上机一declare @low intset @low=1;declare @row intset @row=1;declare @xing nvarchar(8)set @xing='☆'while(@low<=5)beginwhile(@row<=@low)beginprint @xingset @xing+='☆'set @row+=1endprint ''set @low+=1end--上机二declare @age intselect @age=FLOOR(DATEDIFF(DY,Birthday,GETDATE())/365) from Student where StudentNo='23268'select @age as 年龄 ,studentName as 姓名 from Student where StudentNo='23268'--上机三DECLARE @name nvarchar(50)      --姓名DECLARE @score decimal(5,2)       --分数SELECT TOP 1  @score=StudentResult,@name=stu.StudentName    FROM Result r   INNER JOIN Student stu ON r.StudentNo=stu.StudentNo   INNER JOIN Subject sub ON r.SubjectNo=sub.SubjectNo   WHERE r.StudentNo='20012' AND sub.SubjectName='Java Logic'   ORDER BY ExamDate DESCPRINT '学生姓名:' + @nameIF (@score > 85)  PRINT '考试等级:' + '优秀'ELSE IF (@score > 70)  PRINT '考试等级:' + '良好'ELSE IF (@score > 60)  PRINT '考试等级:' + '中等'ELSE  PRINT '考试等级:' + '差'--上级四DECLARE @date datetime  --考试时间DECLARE @subNO int      --课程编号SELECT @subNO=SubjectNo FROM SubjectWHERE SubjectName='C# OOP'SELECT  @date=max(ExamDate) FROM Result WHERE SubjectNo=@subNOPRINT '加分前学生的考试成绩如下：'SELECT 学号=StudentNo,成绩等级=CASE   WHEN StudentResult BETWEEN 0 AND 59 THEN '你要努力了！！！' WHEN StudentResult BETWEEN 60 AND 69 THEN '★' WHEN StudentResult BETWEEN 70 AND 79 THEN '★★' WHEN StudentResult BETWEEN 80 AND 89 THEN '★★★' ElSE '★★★★'                                END         FROM Result        WHERE SubjectNo=@subNO AND ExamDate=@date DECLARE @n intWHILE(1=1) --条件永远成立   BEGIN    SELECT @n=COUNT(*) FROM Result     WHERE SubjectNo=@subNO AND ExamDate=@date AND StudentResult<60 --统计不及格人数    IF (@n>0)       UPDATE Result SET StudentResult=StudentResult+2 FROM Result       WHERE SubjectNo=@subNO AND ExamDate=@date AND StudentResult<=98  --每人加2分    ELSE       BREAK  --退出循环  END--上级五USE MySchool  GOIF  EXISTS(SELECT * FROM  sysobjects  WHERE  name ='Admin')DROP TABLE AdminGOCREATE TABLE Admin(  --创建表[LoginId]  [nvarchar](50) NOT NULL,[LoginPwd] [nvarchar](50) NOT NULL)ALTER TABLE Admin    --添加主健约束ADD CONSTRAINT PK_Admin PRIMARY KEY (LoginId)GOINSERT INTO Admin([LoginId],[LoginPwd]) VALUES('TEST1','123')    --插入数据INSERT INTO Admin([LoginId],[LoginPwd]) VALUES('TEST2','123456') --插入数据GOUPDATE Admin SET [LoginPwd]='1234567' WHERE [LoginId]='TEST2' --更新数据GO--简答题--简答二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  --简答三  Print '罚款记录表情况如下：'Select RID ,BID ,PDate,Amount, 罚款类型=case                  When PType=1 then '损坏'                  When Ptype=2 then '延期'                  When Ptype=3 then  '丢失'End from Penalty--简答四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--简答五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 '现有图书在一万本以上，需要管理员加强图书管理'