数据库第五章课后作业

1、假设有下面两个关系模式：

    职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码

    部门（部门号，名称，经理名，电话），其中部门号为主码

    用SQl定义这两个关系模式，要求在模式中完成以下完整性约束条件的定义：

 （1）定义每个模式的主码；

 （2）定义参照完整性；

 （3）定义职工年龄不得超过60岁

create table Dept
(
    Dno char(11) primary key,
    Dname char(20),
    Dman char(10),
    Maphone char(11)
);

create table Staff 
(
    Empno char(11) primary key,
    Empname char(10),
    Empage smallint(2),
    constraint C1 check(Empage <= 60),
    Post char(20),
    Wages char(20),
    Dno char(11),
    foreien key(Dno) reference Dept(Dno)
);

2、对学生—课程数据库编写储存过程，完成下述功能：

（1）统计离散数学的成绩分布情况，即按照各分数段统计人数

建一个表来记录各个分数段学生人数：

create table GradeLevel(
	score char(10),
	num int
);

insert into GradeLevel
values('[0,60)',0),('[60,70)',0),('[70,80)',0),('[80,90)',0),('[90,100]',0);

select * from GradeLevel;

接着建立存储过程：

if (exists (select * from sys.objects where name = 'Proc_GRADELEVEL'))
    drop procedure Proc_GRADELEVEL
go
create procedure Proc_GRADELEVEL
as
begin  
   	declare	/*定义变量*/
	@Cno char(4),
	@less60 int, -- [0,60)
	@more60less70 int,-- [60,70)
	@more70less80 int, -- [70,80)
	@more80less90 int, -- [80,90)
	@more90less100 int -- [90,100]
	select @Cno = Cno from Course where Cname = '离散数学';

	select @less60 = count(*) from SC where Grade < 60 and Cno = @Cno;
	update GradeLevel set num = @less60 where score = '[0,60)';

	select @more60less70 = count(*) from SC where Grade >= 60 and Grade < 70 and Cno = @Cno;
	update GradeLevel set num = @more60less70 where score = '[60,70)';
	
	select @more70less80 = count(*) from SC where Grade >= 70 and Grade < 80 and Cno = @Cno;
	update GradeLevel set num = @more70less80 where score = '[70,80)';
	
	select @more80less90 = count(*) from SC where Grade >= 80 and Grade < 90 and Cno = @Cno;
	update GradeLevel set num = @more80less90 where score = '[80,90)';
	
	select @more90less100 = count(*) from SC where Grade >= 90 and Grade < 100 and Cno = @Cno;
	update GradeLevel set num = @more90less100 where score = '[90,100]';

end;

执行过程并显示结果：

exec Proc_GRADELEVEL; -- 执行

select * from SC where Cno = 8;
select * from GradeLevel;

（2）统计任意一门课的平均成绩

建立一个记录平均成绩的表，计算离散数学的平均成绩：

create table AvgGrade (
	Cname char(15),  -- 课程名
	AvgScore float  -- 平均分
);

insert into AvgGrade
values('离散数学', 0);

建立存储过程并执行结果：

if(exists (select * from sys.objects where name = 'Proc_AVGSCORE'))
drop procedure Proc_AVGSCORE;
go
create procedure Proc_AVGSCORE
as
begin 
	declare	
	@AvgDiscretemath float;
	
	select @AvgDiscretemath = avg(Grade) from SC where Cno = '8';
	update AvgGrade set AvgScore = @AvgDiscretemath where Cname = '离散数学';
end;

exec Proc_AVGSCORE;
select * from AvgGrade;

（3）将学生选课成绩从百分制改为等级制（即A、B、C、D、E）

在SC表中添加一行记录等级：

alter table SC
add GradeLevel char(2);

select * from SC;

建立存储过程：

if(exists(select * from sys.objects where name = 'Proc_LEVEL'))
	drop procedure Proc_LEVEL;
go
create procedure Proc_LEVEL
as
begin
	update SC set GradeLevel = 'A' where Grade >= 90 and Grade <= 100;
	update SC set GradeLevel = 'B' where Grade >= 80 and Grade < 90;
	update SC set GradeLevel = 'C' where Grade >= 70 and Grade < 80;
	update SC set GradeLevel = 'D' where Grade >= 60 and Grade < 70;
	update SC set GradeLevel = 'E' where Grade >= 0 and Grade < 60;
end;

exec Proc_LEVEL; -- 执行

select * from SC;