SQL Sever 2005 习题与答案

create default stu_d_df as '信息学院'

sp_bindefault stu_d_df,'student_info.院系' sp_unbindefault 'student_info.院系' drop default stu_d_df /* 4.5 */

create table stu_con (学号char(4), 姓名char(8), 性别char(2),

出生日期datetime,

constraint pk_sid primary key (学号), constraint uk_name unique (姓名),

constraint ck_bday check (出生日期>'1988-1-1') )

alter table stu_con

add constraint df_sex default '男' for 性别 /* 4.6 */

alter table grade

add constraint fk_sid foreign key (学号) references student_info(学号)

五

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 @a float,@b float,@c float

select @a=max(分数),@b=min(分数),@c=avg(分数)

from grade a inner join curriculum b on a.课程编号=b.课程编号 where 课程名称='高等数学' select @a,@b,@c

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

if @sex='男' set @n1=@n1+1 else

set @n2=@n2+1

fetch next from cur_stu into @sex end

select @n1 as 男,@n2 as 女 close cur_stu

deallocate cur_stu

六

declare @a float,@b float,@c float

select @a=max(分数),@b=min(分数),@c=avg(分数)

from grade a inner join curriculum b on a.课程编号=b.课程编号 where 课程名称='高等数学' select @a,@b,@c

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