最佳化M有School 第三章

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 '現有圖書在一萬本以上，需要管理員加強圖書管理'