第五章课后作业

1 假设有下面两个关系模式：
职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码
部门（部门号，名称，经理名，电话），其中部门号为主码
用SQl定义这两个关系模式，要求在模式中完成以下完整性约束条件的定义：
（1）定义每个模式的主码；
（2）定义参照完整性；
（3）定义职工年龄不得超过60岁

 
create table dept1
(
    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;

![在这里插入图片描述](https://img-blog.csdnimg.cn/20210423095938450.png

（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;