数据库作业14：第五章： 数据库完整性 习题 + 存储过程

• 假设有下面两个关系模式：
  职工（职工号，姓名，年龄，职务，工资，部门号）， 其中职工号为主码；
  部门（部门号，名称，经理名，电话），其中部门号为主码。

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

1. 定义每个模式的主码；
2. 定义参照完整性；
3. 定义职工年龄不得超过60岁

create table Dept
	(
		Dno   char(9)  primary key,
		Dname char(20),     
		Mname char(20),
		Tel   char(20)
	);
       
create table Staff
	(
		Sno   char(9)  primary key,
		Sname char(20),     
		Ssex  char(2),
		Sage  smallint,
		constraint C1 check(Sage <= 60),
		Post  char(20),
		Pay   char(20),
		Dno   char(9),
		foreign key (Dno) references Dept(Dno)
	);

• 对学生-课程数据库编写存储过程， 完成下述功能：
  （1）统计离散数学的成绩分布情况，即按照各分数段统计人数。

--建立离散数学统计表：
create table LiSan
	(
		Score char(20),	//成绩的分段
		Count int		//人数
	);

进行处理：

if (exists (select * from sys.objects where name = 'Stats'))
    drop procedure Stats 
go
create procedure Stats 
 /*定义存储过程Stats*/
as
declare		/*定义变量*/
@0to20   int,                 
@20to40  int,
@40to60  int,
@60to80  int,
@80to100 int,
@CNO char(8);  

	select @CNO = Cno 
	from Course 
	where Cname = '离散数学';

	select @0to20 = count(*)
	from SC
	where Grade < 20 and Cno = @CNO
	
	select @20to40 = count(*)
	from SC
	where Grade < 40 and Grade >= 20 and Cno = @CNO

	select @40to60 = count(*)
	from SC
	where Grade < 60 and Grade>=40 and Cno = @CNO
	
	select @60to80 = count(*)
	from SC
	where Grade < 80 and Grade >= 60 and Cno = @CNO

	select @80to100 = count(*)
	from SC
	where Grade>=80 and Cno=@CNO

update LiSan set Num = @0to20 where Score = '[0,20)';
update LiSan set Num = @20to40 where Score = '[20,40)';
update LiSan set Num = @40to60 where Score = '[40,60)';
update LiSan set Num = @60to80 where Score = '[60,80)';
update LiSan set Num = @80to100 where Score = '[80,100]';

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

--创建成绩表：
create table Average_student
	(
		Cno char(4),
		Cname char(10),
		Score float 
	);
--存储过程
if (exists (select * from sys.objects where name = 'Avgcore'))
    drop procedure Avgcore 
go
create procedure Avgcore 
as
declare
@S1  float,                 
@S2  float,
@S3  float,
@S8  float;

	select @S1 = avg(Grade)
	from SC
	where Cno = '1'
	
	select @S2 = avg(Grade)
	from SC
	where Cno = '2'
	
	select @S3 = avg(Grade)
	from SC
	where Cno = '3'

	select @S8 = avg(Grade)
	from SC
	where Cno = '8'
	
update average_student set Score = @S1 where Cno = '1';
update average_student set Score = @S2 where Cno = '2';
update average_student set Score = @S3 where Cno = '3';
update average_student set Score = @S8 where  Cno = '8';

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

if (exists (select * from sys.objects where name = 'get_Level'))
    drop procedure get_level 
go
create procedure get_level
as
update SC set Level = 'A' where Grade >= 80;
update SC set Level = 'B' where Grade < 40 and Grade >= 20;
update SC set Level = 'C' where Grade < 60 and Grade >= 40;
update SC set Level = 'D' where Grade < 80 and Grade >= 60;
update SC set Level = 'E' where Grade < 20;

有了一些感觉，还需要加强。