数据库第五章作业

创建部门

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 table math
(
   rankGrade char(20), 
	num int




create procedure SepGrade(
@name char(10))
as
declare
 @less60 int,
 @f60t69 int,
 @f70t79 int,
 @f80t89 int,
 @f90t100 int,
 @cpo char(10); 
 begin
 select @cpo=Cno from Course
 where Cname =@name; 
 if @cpo is null
 begin 
 print '未录入'+@name+'这门课课程'
 rollback ;
 return 
 end;
 select @less60=count(*)  from SC
 where Grade < 60 and Cno =@cpo;
 
  select @f60t69 =count(*)  from SC
 where Grade <= 69 and Grade >=60 and Cno =@cpo;
 
  select @f70t79=count(*)  from SC
 where Grade <=79 and  Grade >=70 and Cno =@cpo;
 
  select @f80t89=count(*)  from SC
  where Grade <=89 and  Grade >=80 and Cno =@cpo;
 
  select @f90t100=count(*)  from SC
  where Grade <=100 and  Grade >=90 and Cno =@cpo;
  end;
 update math set num = @less60 where rankGrade = '[0,60)';
update math set num = @f60t69 where rankGrade = '[60,69]';
update math set num = @f70t79 where rankGrade = '[70,79]';
update math set num = @f80t89 where rankGrade = '[80,89]';
update math set num = @f90t100 where rankGrade = '[90,100]';

统计任意一门课的平均成绩。

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 ;

将学生成绩从百分制改成等级制

ALTER TABLE SC
ADD LEVEL CHAR(1)

CREATE PROCEDURE Proc_Level
AS 
UPDATE SC SET Level='A' WHERE Grade>=80 AND Grade<=100;
UPDATE SC SET Level='B' WHERE Grade>=60 AND Grade<80;
UPDATE SC SET Level='C' WHERE Grade>=40 AND Grade<60;
UPDATE SC SET Level='D' WHERE Grade<20 

好难好难。。。。。。。。不会哎