发布时间 : 星期六 文章SQL Sever 2005 习题与答案更新完毕开始阅读ee28118681c758f5f71f676c
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