【数据库】第五章课后题（以及储存过程的课后题）

第五章

create table 部门(部门号 char(9) primary key ,
				  名称 char(9),
				  经理名 char(9),
				  电话 char(11));
create table 职工(
					职员号 char(9) primary key,
					姓名   char(9) ,
					年龄   smallint check (年龄<=60),
					职务   char(9),
					工资   smallint,
					部门号 char (9),
					foreign key(部门号) references 部门(部门号));

存储过程

统计数据分布情况

create procedure Proc_GetMathStatus
@Cname char(9)
as
begin transaction trans
	declare 
	@less60 int,
	@more60less70 int ,
	@more70less80 int,
	@more80less90 int,
	@more90 int,
	@Cno char(4),
	@cnt int
	select @Cno=Cno from Course where Cname=@Cname
	select @cnt=count(*) from sc where Cno=@Cno;
	if @cnt=0
	begin
		print '这个课没有学生或者没有这个课'
		rollback transaction trans;
		return ;
	end

	select @less60 = count(*) 
					 from sc
					 where sc.Cno=@Cno AND sc.Grade<60;
	select @more60less70 = count(*) 
					 from sc
					 where sc.Cno=@Cno AND sc.Grade>=60 AND sc.Grade<70;
	select @more70less80 = count(*) 
					 from sc
					 where sc.Cno=@Cno AND sc.Grade>=70 AND sc.Grade<80;
	select @more80less90 = count(*) 
					 from sc
					 where sc.Cno=@Cno AND sc.Grade>=80 AND sc.Grade<90;
	select @more90 = count(*) 
					 from sc
					 where sc.Cno=@Cno AND sc.Grade>=90;
	print'[0,60):'
	print @less60
	print'[60,70):'
	print @more60less70
	print'[70,80):'
	print @more70less80
	print'[80,90):'
	print @more80less90
	print'[90,100]:'
	print @more90
	commit transaction trans;

统计一门课的平均成绩

create procedure Proc_GetCourseAvgGrade
@Cname char(9)
as
begin transaction trans
	declare 
	@Cno char(4),
	@StuNum smallint,
	@Avg smallint
	select @Cno=Cno from Course where Cname=@Cname;
	if @Cno is null 
	begin
		print '这门课不存在'
		rollback ;
		return;
	end;
	select @StuNum= count(*) from sc where Cno=@Cno;
	select @Avg =avg(Grade) from sc where Cno= @Cno;
	print @Cname+'共有'+convert(varchar,@StuNum)+'个学生，平均分：'+convert(varchar,@Avg)
	commit;
	return ;

将学生成绩从百分制改成等级制
首先添加了一行level来存等级

alter table SC
add level char(1)

create procedure Proc_GetLevel
as
	update SC set level = 'A' where Grade >= 80;
	update SC set level = 'B' where Grade < 40 and Grade >= 20;
	update SC set level = 'C' where Grade < 60 and Grade >= 40;
	update SC set level = 'D' where Grade < 80 and Grade >= 60;
	update SC set level = 'E' where Grade < 20;

本篇完。