【数据库系统原理作业】十三、第五章作业题

第五章课后作业

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

这里就把之前第三章中创建的SC、Student、Course三张表直接拿来用了,不过Course中没有离散数学这门课，还是先为数据库添加一点相关数据，通过查询结果来判断功能是否完成。

--向Course中添加离散数学
insert into Course values('8','离散数学',NULL,4)
--添加几名选了离散数学的学生
insert into SC values('201215121','8',45);
insert into SC values('201215122','8',65);
insert into SC values('201215123','8',78);
insert into SC values('201215125','8',82);
insert into SC values('201215129','8',98);
insert into SC values('201215130','8',92);
--查看添加结果
select * from Course;
select * from SC where Cno='8';

添加结果:

（1）统计离散数学的成绩分布情况，即按照各分数段统计人数：
主要分为两个步骤:

1. 建立统计表（gradesum_8）
2. 为统计功能建立存储过程（grade_sum）

drop table if exists gradesum_8;
drop procedure if exists grade_sum;

--建立统计表（gradesum_8）
create table gradesum_8(
rank char(20) primary key,
total int
);
insert into gradesum_8(rank,total) values('<60',NULL);
insert into gradesum_8(rank,total) values('60~70',NULL);
insert into gradesum_8(rank,total) values('70~80',NULL);
insert into gradesum_8(rank,total) values('80~90',NULL);
insert into gradesum_8(rank,total) values('90~100',NULL);
go
--为统计功能建立存储过程（grade_sum）
create procedure grade_sum
as
declare
	@_60 int,
	@60_70 int,
	@70_80 int,
	@80_90 int,
	@90_100 int
	
begin
	select @_60=count(*) from SC
	where SC.Cno='8' and Grade<60;
	select @60_70=count(*) from SC
	where SC.Cno='8' and Grade>=60 and Grade <70;
	select @70_80=count(*) from SC
	where SC.Cno='8' and Grade>=70 and Grade <80;
	select @80_90=count(*) from SC
	where SC.Cno='8' and Grade>=80 and Grade <90;
	select @90_100=count(*) from SC
	where SC.Cno='8' and Grade>=90 and Grade <=100;
	
	update  gradesum_8 set total=@_60 where rank='<60';
	update gradesum_8 set total=@60_70 where rank='60~70';
	update gradesum_8 set total=@70_80 where rank='70~80';
	update gradesum_8 set total=@80_90 where rank='80~90';
	update gradesum_8 set total=@90_100 where rank='90~100';
end

然后调用存储过程，让我们验证一下结果:

exec grade_sum;
select * from gradesum_8;

结果:

一点没差！
（2）统计任意一门课的平均成绩：
依旧先来给平均成绩建个表(AvgSC)

drop table if exists AvgSC;

create table AvgSC(
	Cno char(4),    --课程号
	CNAME CHAR(40),  -- 课程名
	AvgScore FLOAT, --平均分
	foreign key(Cno) references Course(Cno)
);
insert into AvgSC--子查询结果插入
select Cno,Cname,NULL
from Course;

select * from AvgSC;

结果:

然后就是插入数据了，和第一题类似，先创建一个存储过程：

drop procedure if exists AvgCourse;
go
create procedure AvgCourse
as
declare
	@i int,
	@avg int,
	@sum int;
begin
	set @i=1
	select @sum=count(*)
	from AvgSC;
	while @i<=@sum --这个地方直接把所有的课程分别写出来也可以，我嫌麻烦就用循环了
		begin
			select @avg=Avg(Grade)
			from SC,AvgSC
			where SC.Cno=@i;
			update AvgSC set AvgScore=@avg where AvgSC.Cno=@i;
			set @i=@i+1;
		end;
end;

验证一下结果:

exec AvgCourse;
select* from AvgSC;

结果也是满足了要求⭐⭐⭐

（3）将学生选课成绩从百分制改为等级制(即A、B、C、D、E)：
比起前面两个，这个就相对简单了许多
给SC表再添加一列用来填写ABCDE，然后分数判断更新数据即可

alter table SC drop column if exists Score_level ;
alter table SC add  Score_level char(4);
drop procedure if exists Createlevel;
go
create procedure Createlevel
as
begin
	update SC set Score_level = 'E' where Grade < 60;
	update SC set Score_level = 'D' where Grade >= 60 AND Grade < 70;
	update SC set Score_level = 'C' where Grade >= 70 AND Grade < 80;
	update SC set Score_level = 'B' where Grade >= 80 AND Grade < 90;
	update SC set Score_level = 'A' where Grade >= 90; 
end;
go
exec Createlevel;
select* from SC;

检查一下结果:

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

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

-- 部门表
create table Department(
	Dno char(15) primary key, -- 部门号
	Dname char(15) unique, -- 部门名称
	Mname char(15) not null, -- 经理名
	Phone char(15) not null -- 电话
);

-- 职工表
create table Employee(
	Eno char(15) primary key, -- 职工号
	Ename char(15) not null, -- 姓名
	Eage int check(Eage > 0 and Eage <= 60) not null,-- 职工年龄不得超过60岁
	Duty char(15) not null, -- 职务
	Salary float, -- 工资
	Dno char(15) references Department(Dno) -- 部门号 参照完整性
);

一些问题

1.题的难度不是很大，主要还是比较麻烦，其实让我很奇怪的是为什么增加列是下面这连两个句子:

alter table SC drop column if exists Score_level ;
alter table SC add  Score_level char(4);

删除要比增加多一个column，不然删除的是索引，上网查了才知道，要加强记忆
还有就是 add不能用 if not exists,会出现以下报错

而column drop 却可以用 if exists
2.第二问里面用了还没有学的循环，while循环的结构如下

declare @i int  
set @i=0
while @i<5
begin
    <运行内容>
    set @i=@i +1;
end