S2第三章

--检查学生参加“oop”课最近一次考试是否有不及格（分及格）的学生。
--如有，每人加分，高于分的学生不再加分，直至所有学生这次考试成绩均及格
declare @subid int 
select @subid=SubjectId from Subject
where SubjectName='oop'

declare @time datetime
select @time=max(ExamDate) from Result
where @subid=SubjectId

declare @sum int 
select @sum=COUNT(StudentNo) from Result
where @subid=SubjectId and @time=ExamDate and StudentResult<70

while( @sum>0)
begin
update Result set StudentResult+=2
where @subid=SubjectId and ExamDate=@time and StudentResult<95
end
select * from Result
where @subid=SubjectId and  ExamDate=@time

---case
declare @bianhao int
select @bianhao=SubjectId from Subject
where SubjectName='oop'

declare @date datetime
select @date=max(ExamDate)from Result
where @bianhao=SubjectId

select 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   @bianhao=Result.SubjectId and   Result.ExamDate=@date 
                --作业
                --作业
-- 1. 打印直角三角形
    --两层while
declare @row int
set @row=1
declare @j int
set @j=1
declare @xing nvarchar(100)
set @xing=''
while(@row<=5)
begin
while(@j<=@row)
begin 
set @xing+='★'
set @j+=1
end
print @xing
set @row+=1
end
 --一层
 declare @hang int
 set @hang=1
 declare @xin nvarchar(32)
 set @xin='★'
 while(@hang<=5)
 begin
 print @xin
 set @xin+='★'
 set @hang+=1
 end
 

 



---2面试题
use MySchool
go
if exists(select * from sysobjects where name='tmp')
drop table tmp
create table tmp
(
rq varchar(10),
shengfu nvarchar(10)
)
select * from tmp
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-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(
case
when shengfu='胜' then 1
else 0
end 
) as 胜,
sum(
case 
when shengfu='负' then 1
else 0
end
) as 负
from tmp
group by rq

--1-100之间所有偶数和
declare @sum int
set @sum=0
declare @num int
set @num=1
while(@num<=100)
begin
if( @num%2=0)
begin
set @sum+=@num
end
set @num+=1
end
print  @sum