优化MYSchool数据库设计第三章

--****************** 7.经典while循环加分题目***************************************--检查学生参加“oop”课最近一次考试是否有不及格（分及格）的学生。--如有，每人加2分，高于95分的学生不再加分，直至所有学生这次考试成绩均及格--oop课程declare @subid intselect @subid =subjectid from subject where subjectname='oop'--最近一次考试declare @time datetimeselect @time=MAX(examdate)from Resultwhere subjectid=@subid--是否有不及格的人declare @people intselect @people =COUNT(Studentno) from Resultwhere subjectid=@subid and  examdate=@timeand studentresult<70--加分while(@people>0)begin   --每人+2分   update result set studentresult+=2   where subjectid=@subid and  examdate=@time    and studentresult<95        select @people=COUNT(Studentno) from Resultwhere subjectid=@subid and  examdate=@timeand studentresult<70end--CASE AND 经典练习--采用美国ABCDE五级打分制显示学生oop课程最近一次考试成绩(姓名等级)--A级 : 90分以上--B级 : 80分以上--C级 : 70分以上--D级 : 60分以上--E级 : 60分以下--*******************************************************--声明变量存储oop科目编号declare @bainhao intselect @bainhao=subjectid From Subjectwhere SubjectName ='oop'--声明变量存储最近一次考试的时间declare @time2 datetimeselect @time2=MAX(ExamDate) from Resultwhere SubjectId=@bainhaoselect Studentname ,等级=case      when  studentresult>90   then'A'     when   studentresult>80  then'B'     when    studentresult>70 then'C'     when studentresult>60    then'D'    else 'E'    END    From student,result    where student.studentNo=result.studentNo    and subjectid=@bainhao                    ---100内的偶数和    declare @num int    set @num =0        declare @sum int     set @sum =0    while(@num<=100)beginif(@num%2=0)beginset @sum+=@numendset @num=@num+1endprint @sum    --正三角形    declare @sum1 intset @sum1=1declare @num1 int set @num1=1declare @str nvarchar(32)set @str=''while(@sum1<=4)  begin    while (@num1<=@sum1)      begin      set @str+='#'      set @num1=@num1+1      end  print  @str  set @sum1=@sum1+1  end    --正三角形   --胜负 use MySchoolgoif exists(select * from sysobjects where name='tmp')drop table tmpcreate table tmp(rq varchar(10),shengfu nvarchar(10))select * from tmpinsert into tmp values('2005-05-09','胜')insert into tmp values('2005-05-09','胜')insert into tmp values('2005-05-09','负')insert into tmp values('2005-05-09','负')insert into tmp values('2005-05-10','胜')insert into tmp values('2005-05-09','负')insert into tmp values('2005-05-10','胜')insert into tmp values('2005-05-10','负')insert into tmp values('2005-05-10','负')--日期 胜 负select rq as 日期 ,sum(casewhen shengfu='胜' then 1else 0end ) as 胜,sum(case when shengfu='负' then 1else 0end) as 负from tmpgroup by rq--1-100之间所有偶数和declare @sum intset @sum=0declare @num intset @num=1while(@num<=100)beginif( @num%2=0)beginset @sum+=@numendset @num+=1endprint  @sum