第五章作业

数据库完整性习题

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

create table Department(
	Dno char(10) primary key, --部门号（主码）
	Dname char(10) unique, --部门名称
	Mname char(5) not null, --经理名
	Phone char(11) not null --电话
);

-- 职工表
create table Employee(
	Eno char(15) primary key, --职工号（主码）
	Ename char(5) not null, --姓名
	Eage smallint check(Eage>0 and Eage<= 60), --年龄（不超过60岁）
	Duty char(10) not null, --职务
	Salary int, --工资
	Dno char(10),
	foreign key (Dno)references Department(Dno)--定义外键约束（参照完整性）
);

第八章作业

对学生—课程数据库编写存储过程，完成下述功能：
（1）统计离散数学的成绩分布情况，即按照各分数段统计人数。
（2）统计任意一门课的平均成绩。
（3）将学生选课成绩从百分制改为等级制（即A、B、C、D、E）。

数据准备，插入课程，插入不同分数段的学生的成绩。

insert into Course
values('8','离散数学',NULL,4)
insert into SC
values('201215121',8,95),('201215122',8,85),('201215123',8,75),('201215125',8,65),('201215128',8,55);

由于是在以前的基础上建立的，也有很多的以前的数据

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

1 先建立一张表来存储数据

create table rank(
	score char(15),
	num int
);

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

select * from rank;

2 建立过程的思路就是用@来定义存储过程操作的变量，最后再赋值到表

create procedure Proc_rank
as
begin  
   	declare	/*定义变量*/
	@Cno char(4),
	@less60 int, -- [0,60)
	@less70 int,-- [60,70)
	@less80 int, -- [70,80)
	@less90 int, -- [80,90)
	@less100 int -- [90,100]
	

	select @Cno = Cno from Course where Cname = '离散数学';
	select @less60 = count(*) from SC where Grade < 60 and Cno = @Cno;
	select @less70 = count(*) from SC where Grade >= 60 and Grade < 70 and Cno = @Cno;
	select @less80 = count(*) from SC where Grade >= 70 and Grade < 80 and Cno = @Cno;	
	select @less90 = count(*) from SC where Grade >= 80 and Grade < 90 and Cno = @Cno;
	select @less100 = count(*) from SC where Grade >= 90 and Grade < 100 and Cno = @Cno;
	

	update rank set num = @less60 where score = '[0,60)';
	update rank set num = @less70 where score = '[60,70)';
	update rank set num = @less80 where score = '[70,80)';
    update rank set num = @less90 where score = '[80,90)';
	update rank set num = @less100 where score = '[90,100]';
end;

--分开执行

exec Proc_rank; 
select * from SC where Cno = 8;
select * from rank;

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

1 先看一下自己的数据库中有几门课程

select distinct Cname,SC.Cno 
from Course,SC
where SC.Cno=Course.Cno

2 建立一张AvgGrade表。

create table AvgGrade (
	Cname char(10),  --课程名
	AvgScore float  --平均分
);
insert into AvgGrade
values('数据库', 0), ('数学', 0), ('信息系统', 0),('离散数学',0);
select  * from AvgGrade

3 创建存储过程

create procedure Proc_AVG
as
begin  
   	declare	-- 定义变量
	@1 float,
	@2 float,
	@3 float,
	@8 float;
	
	-- 赋值
	select @1 = avg(Grade) from SC where Cno = '1';	
	select @2 = avg(Grade) from SC where Cno = '2';	
	select @3 = avg(Grade) from SC where Cno = '3';	
	select @8 = avg(Grade) from SC where Cno = '8';
	

	update AvgGrade set AvgScore = @1 where Cname = '数据库';
	update AvgGrade set AvgScore = @2 where Cname = '数学';
	update AvgGrade set AvgScore = @3 where Cname = '信息系统';
	update AvgGrade set AvgScore = @8 where Cname = '离散数学';
end;
--分开执行
exec Proc_AVG;
select * from AvgGrade;

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

1 这里选择直接在原表上进行添加，先修改原表

alter table SC
add level char(2);
select * from SC;

2 编写存储过程

create procedure Proc_LEVEL
as
begin
	update SC set Level = 'A' where Grade>=90 and Grade<=100;
	update SC set Level = 'B' where Grade>=80 and Grade<90;
	update SC set Level = 'C' where Grade>=70 and Grade<80;
	update SC set Level = 'D' where Grade>=60 and Grade<70;
	update SC set Level = 'E' where Grade>=0 and Grade<60;
end;
--分开执行
exec Proc_LEVEL;
select * from SC;