declare @sum smallint, @i smallint, @nums smallint
set @sum = 0
set @i = 1
set @nums = 0
while (@i <= 100)
  begin
  if (@i % 3 = 0)
    begin
    set @sum = @sum + @i
    set @nums = @nums + 1
    end
  set @i = @i + 1
  end
print '个数是：' + ltrim(str(@nums))
print '总和是：' + ltrim(str(@sum))go

declare @factorial float, @sum float, @i smallint
set @i = 1
set @factorial = 1
set @sum = 0
while(@i <=100)
  begin
  set @factorial = @factorial * @i
  set @sum = @sum + @factorial
  set @i = @i +1
  end
print @factorial
print @sumgo

select SNO 学号, SN 姓名,
       性别 = case SEX
               when '男' then 'M'
               when '女' then 'F'
             end
From S
go

select SN 姓名, CN 课程名,
       成绩 = case
               when SCORE is null then '未考'
               when SCORE < 60 then '不及格'
	       when SCORE >= 60 and SCORE <70 then '及格'
	       when SCORE >= 70 and SCORE <80 then '中'
	       when SCORE >= 80 and SCORE <90 then '良好'
               when SCORE >= 90 then '优'
	     end
from SC, S, C
where S.SNO = SC.SNO and C.CNO = SC.CNO
order by S.SNO asc, C.CNO, SCORE desc
go

update T
set SAL = SAL + case
                  when T.TNO in (select TC.TNO
				 from T, TC
				 where T.TNO = TC.TNO and (SAL+COMM) >= 4000
				 group by TC.TNO having count(*) >= 2) then 300
		  when T.TNO in (select TC.TNO
				 from T, TC
				 where T.TNO = TC.TNO and (SAL+COMM) >= 3000 and (SAL+COMM) < 4000
				 group by TC.TNO having count(*) >= 2) then 200
		  when T.TNO in (select TC.TNO
				 from T, TC
				 where T.TNO = TC.TNO and (SAL+COMM) < 3000
				 group by TC.TNO having count(*) >= 2) then 100
		  when T.TNO in (select TC.TNO
				 from T, TC
				 where T.TNO = TC.TNO
				 group by TC.TNO having count(*) = 1) then 50
		  else 0
		end
go

declare @SNO char(6), @SNAME char(8), @SEX char(2), @AGE tinyint, @DEPT char(10)
declare Cursor_Female cursor
  for select Sno, Sname, Sex, Age, Dept from Student
open Cursor_Female
fetch next from Cursor_Female
  into @SNO, @SNAME, @SEX, @AGE, @DEPT
while @@fetch_status = 0
  begin
  if @SNAME = '牛莉'
    begin
    print '找到牛莉的信息如下:'
    print @SNO+' '+@SNAME+' '+@SEX+' '+convert(char(2),@AGE)+' '+@DEPT
    break
    end
  fetch next from Cursor_Female
    into @SNO, @SNAME, @SEX, @AGE, @DEPT
  end
if @@fetch_status != 0
  print '很抱歉,没有找到牛莉的信息!'
close Cursor_Female
deallocate Cursor_Femalego

create procedure Pro_C
  @C_cursor cursor VARying output
as
set @C_cursor = cursor
  for
  select Sname, Score from Student, SC, Course
  where Student.Sno = SC.Sno and SC.Cno = Course.Cno
        and Course.Cname = '数据库'
open @C_cursor
go

declare @MyCursor cursor
declare @Name char(10)
declare @Name_in char(10)
declare @Score int
select @Name_in = '王一山'
exec Pro_C @C_cursor = @MyCursor output
fetch next from @MyCursor
  into @Name, @Score
while (@@fetch_status = 0)
  begin
  if @Name = @Name_in
    begin
    print rtrim(@Name)+'选修了数据库课程,成绩是:'+convert(char(2),@Score)
    break
    end
  fetch next from @MyCursor
    into @Name, @Score
  end
if (@@fetch_status != 0)
  print rtrim(@Name_in)+'没有选修数据库课程。'
close @MyCursor
deallocate @MyCursor
go