select 学号,分数,等级= case
when 分数>=90 then 'A'
when 分数>=80 and 分数<90 then 'B' when 分数>=70 and 分数<80 then 'C' when 分数>=60 and 分数<70 then 'D' else 'E' end
from grade
declare @n int,@s int set @s=0 set @n=1 while @n<=10 begin
set @s=@s+@n set @n=@n+1 end
print '1+2+3+...+10='+str(@s,2)
declare @i int,@n int,@s int set @i=1 set @n=0 set @s=0
while @i<=100 begin
if @i%7=0 begin
set @n=@n+1 set @s=@s+@i end
set @i=@i+1 end
select @n as 个数,@s as 总和
declare @sex char(2),@n1 int,@n2 int set @n1=0 set @n2=0
declare cur_stu cursor for select 性别from student_info open cur_stu
while @@fetch_status=0 begin
fetch next from cur_stu into @sex if @sex='男'
set @n1=@n1+1 else
set @n2=@n2+1 end
select @n1 as 男,@n2 as 女 close cur_stu
deallocate cur_stu
create procedure stu_grade as select 姓名,课程名称,分数
from student_info s inner join grade g on s.学号=g.学号 inner join curriculum c on g.课程编号=c.课程编号 where s.学号='0001'
execute stu_grade
sp_rename 'stu_grade','stu_g'
create procedure stu_p_g @stu_name char(8) as select 姓名,课程名称,分数
from student_info s inner join grade g on s.学号=g.学号 inner join curriculum c on g.课程编号=c.课程编号 where s.姓名like @stu_name
execute stu_p_g '刘卫平'
sp_helptext stu_p_g
create procedure stu_g_r @stu_no char(4),@stu_avg float output as
select @stu_avg=avg(分数) from grade
where 学号=@stu_no
declare @avg1 float
execute stu_g_r '0002',@avg1 select @avg1