第三章 练习 知识点 课后作业

--练习：--统计并显示2013-08-09 的oop考试平均分--如果平均分在70分以上，显示“考试成绩优秀”，并显示前三名学生的考试信息--如果在以下，显示“考试成绩较差”，并显示后三名学生的考试信息 select * from result order by examdate  --00.求出oop课程对应的课程编号 declare @subid int  select @subid=SubjectId from Subject where subjectname='oop'   --01.查询平均分 declare @avg int  select @avg=avg(Studentresult) from result where examdate>='2013-08-09' and examdate <'2013-08-10' and subjectid=@subid  if(@avg>=70) begin   print '成绩优秀'   -- *打印前三名的成绩   select  top 3 from result    where  examdate>='2013-08-09' and examdate <'2013-08-10' and subjectid=@subid   order by studentresult desc    end else begin print '成绩较差' select from Result   where  examdate>='2013-08-09' and examdate <'2013-08-10' and subjectid=@subid   order by studentresult  end********************************************************一。case    when   then  when    thenend二。while循环   循环结构-- 一定不能出现 while(true)--100内偶数和 declare @sum int  declare @num int  set @sum=0 set @num=1 while(@num<=100) begin   if(@num%2=0)     begin   set @sum=@num+@sum end set @num=@num+1 end  print @sum三。GO指令四。--***********************************************************************--检查学生参加“oop”课最近一次考试是否有不及格（分及格）的学生。--如有，每人加分，高于分的学生不再加分，直至所有学生这次考试成绩均及格--code a little ,debug a little 编写一点点，调试一点点--01.找一个变量存储oop科目编号declare @subid int select @subid=subjectid from subjectwhere subjectname='oop'--02.找到一个变量，存储最近一次考试时间declare @maxdate datetime select @maxdate= max(examdate) from resultwhere subjectid=@subidselect * from resultwhere subjectid=@subid and  examdate=@maxdate--03.投影出符合条件的人数declare @sum intselect @sum=COUNT(Studentno) from Resultwhere subjectid=@subid and  examdate=@maxdateand studentresult<70while(@sum>0)begin   --每人+2分   update result set studentresult+=2   where subjectid=@subid and  examdate=@maxdate    and studentresult<95        select @sum=COUNT(Studentno) from Resultwhere subjectid=@subid and  examdate=@maxdateand studentresult<70end一，打印三角形01，一重循环 declare @i int declare @j int declare @str nvarchar(100) set @i=1 set @j=1 set @str='' while(@i<=5) begin   while(@j<=@i)   begin     set @str+='*'       set @j+=1   end   print @str   set @i+=1 end02，二重循环declare @strr  varchar(32)set @strr  ='*'declare @row intset @row=1 declare @col int        set @col=1    while(@row<=5)    begin       while(@col<=@row)begin         PRINT @strr  SET @strr  =@strr  +'*'SET @col=@col+1end SET @row=@row+1end二。面试题作业  行转列 --创建 tmp 表  并添加数据 create table tmp(riqi varchar(32),shengfu nchar(1)) select *  from tmp insert into tmp values('2015-05-09','胜') insert into tmp values('2015-05-09','胜') insert into tmp values('2015-05-09','负') insert into tmp values('2015-05-09','负') insert into tmp values('2015-05-10','胜') insert into tmp values('2015-05-10','负') insert into tmp values('2015-05-10','负') --行转列 select *from tmpselect riqi as 日期,  SUM(   case     when shengfu='胜' then 1  else 0  end   ) as 胜,  SUM(   case     when shengfu='负' then 1  else 0  end   ) as 负   from  tmp   group by riqi