数据库第八周实验——第五章课后题

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

  职工（职工号，姓名，年龄，职务，工资，部门号）， 其中职工号为主码；
  部门（部门号，名称，经理名，电话），其中部门号为主码。

用 SQL 语言定义这两个关系模式，要求在模式中完成以下完整性约束条件的定义：
（1）定义每个模式的主码；
（2）定义参照完整性；
（3）定义职工年龄不得超过60岁

create table Dep
( 
	Dnum int primary key,
	Dname char(10),
	Djlname char(10),
	Dtel char(20)
);
 
create table Staff
( 
	  Snum char(10) primary key,
	  Sname char(10),
	  Sage int,
	  constraint C1 check (Sage<=60),
	  Swjob char(10),
	  Ssalary char(10),
	  Sdnum int 
	  foreign key(Sdnum) references Dep(Dnum)
  );

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

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

标准SQL

create procedure divide_grade()
as
	declare score_90_100 int;
	declare	score_80_90 int;
	declare score_70_80 int;
	declare	score_60_70 int;
	declare	score_0_60 int;
	declare	score int;
	set score_90_100:=
		(select count(*) from SC where Grade>=90 and Cno=
			(select Cno from Course where Cname='离散数学'));
	set score_80_90:=
		(select count(*) from SC where Grade>=80 and Grade<90 and Cno=
			(select Cno from Course where Cname='离散数学'));
	set score_70_80:=
		(select count(*) from SC where Grade>=70 and Grade<80 and Cno=
			(select Cno from Course where Cname='离散数学'));
	set score_60_70:=
		(select count(*) from SC where Grade>=60 and Grade<70 and Cno=
			(select Cno from Course where Cname='离散数学'));
	set score_0_60:=
		(select count(*) from SC where Grade>=0 and Grade<60 and Cno=
			(select Cno from Course where Cname='离散数学'));
	select score_90_100 as '[90,100]',     --显示
		score_80_90 as '[80,90)',
		score_70_80 as '[70,80)',
		score_60_70 as '[60,70)',
		score_0_60 as '[0,60)'; 	

或者

create procedure divide_rank
as
	declare score_90_100 int,
			score_80_90 int,
			score_70_80 int,
			score_60_70 int,
			score_0_60 int,
			score int;
	select score_90_100=count(*) from SC,Course where SC.Cno=Course.Cno and Grade>=90 and Grade<=100 and Cname='离散数学' ;
	select score_80_90 =count(*) from SC,Course where SC.Cno=Course.Cno and Grade>=80 and Grade<90 and Cname='离散数学' ;
	select score_70_80 =count(*) from SC,Course where SC.Cno=Course.Cno and Grade>=70 and Grade<80 and Cname='离散数学' ;
	select score_60_70 =count(*) from SC,Course where SC.Cno=Course.Cno and Grade>=60 and Grade<70 and Cname='离散数学' ;
	select score_0_60  =count(*) from SC,Course where SC.Cno=Course.Cno and Grade>=0 and Grade<60 and Cname='离散数学' ;
	select score_90_100 as '[90,100]',     --显示
		   score_80_90  as '[80,90)',
		   score_70_80  as '[70,80)',
		   score_60_70  as '[60,70)',
		   score_0_60   as '[0,60)';

T-SQL

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

--存储过程
if (exists(select * from sys.objects where name='Grade_distribution'))
	drop procedure Grade_distribution
go
create procedure Grade_distribution
@Cname char(10)
as
declare                    --定义变量
	@less60 int,
	@Grade_60_69 int,
	@Grade_70_79 int,
	@Grade_80_89 int,
	@Grade_90_100 int,
	@CNO char(10);

begin
	select @CNO=Cno from Course where Cname=@Cname;
	if @CNO is null
	begin 
		print '不存在+@Cname+课程'
		rollback;                   --回滚事务
		return;
end;

	select @less60=count(*) from SC where Grade<60 and Cno=@CNO;
	select @Grade_60_69=count(*) from SC where Grade>=60 and Grade<70 and Cno=@CNO;
	select @Grade_70_79=count(*) from SC where Grade>=70 and Grade<80 and Cno=@CNO;
	select @Grade_80_89=count(*) from SC where Grade>=80 and Grade<90 and Cno=@CNO;
	select @Grade_90_100=count(*) from SC where Grade>=90 and Grade<=100 and Cno=@CNO;
end;

	update LiSan set Count=@less60 where Score='[0,60):';
	update LiSan set Count=@Grade_60_69 where Score='[60,69]:';
	update LiSan set Count=@Grade_70_79 where Score='[70,79]:';
	update LiSan set Count=@Grade_80_89 where Score='[80,89]:';
	update LiSan set Count=@Grade_90_100 where Score='[90,100]:';
	
--执行存储过程
EXEC Grade_distribution
@Cname='离散数学';

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

标准SQL

create procedure aver_score(course_name varchar(10))
as
begin
	select avg(Grade) from SC where Cno=
		(select Cno from Course where Cname=course_name);
end

或者

create procedure AVG_Cscore(course_name char(10))
as
	select avg(Grade) 
	from SC,Course 
	where SC.Cno=Course.Cno and Course.Cname=course_name

T-SQL

--建表
create table Avg_Grade
(
	 Cname char(10), --课程名称
	 Cno int, --课程号
	 A_Gra int --该课程的平均成绩
 );

--存储过程
if (exists(select * from sys.objects where name='Course_AVG'))
	drop procedure Course_AVG
go
create procedure Course_AVG
@Cname char(10)
as
begin transaction trans
declare                      --定义变量
	@Cno char(20),           --课程号
	@Cavg int               --平均成绩

begin 
	select Cno=@Cno from Course where Cname=@Cname;
	if @Cno is null
	begin
		print'+@Cname+不存在'
		rollback;                  --回滚事务
		return;
	end;
end;
	--select @Cavg=avg(Grade) from SC where Cno =@Cno;
	--update Avg_Grade set A_Gra=@Cavg where Cno =@Cno and Cname =@Cname;
	select @Cavg=avg(Grade) from SC where Cno=@Cno;
	print @Cname+'平均分:'+convert(varchar,@Cavg);
	commit;
	return;

--执行存储
EXEC Course_AVG
	@Cname='数据库';

create procedure aver_score
	@course_name varchar(10)
as
	select avg(Grade) from SC where Cno=
		(select Cno from Course where Cname=@course_name);

或者

create procedure AVG_Cscore
	@course_name char(10)
as
	select avg(Grade) 
	from SC,Course 
	where SC.Cno=Course.Cno and Course.Cname=@course_name;

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

标准SQL和T-SQL一样

--存储过程
alter table SC add level char(1)      --增加level存储等级

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>=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<60;

----执行过程
EXEC get_Level
select * from SC;