数据库第五章课后作业

本文通过具体实例介绍了如何使用SQL定义数据库模式、实现完整性约束,并通过存储过程进行数据统计和转换。包括定义职工和部门表,创建存储过程统计离散数学课程成绩分布及平均分,以及将成绩转换为等级。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

1、假设有下面两个关系模式:

    职工(职工号,姓名,年龄,职务,工资,部门号),其中职工号为主码

    部门(部门号,名称,经理名,电话),其中部门号为主码

    用SQl定义这两个关系模式,要求在模式中完成以下完整性约束条件的定义:

 (1)定义每个模式的主码;

 (2)定义参照完整性;

 (3)定义职工年龄不得超过60岁


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

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

 

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值