SQL数据库设计与存储过程-优快云博客

本文介绍了两个SQL数据库表的设计，包括部门和员工信息，并通过存储过程实现对特定课程成绩的统计与平均分计算，还定义了成绩等级。

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

6、

create table 部门
(
	部门号 char(10),
	名称 char(10),
	经理名 char(10),
	电话 char(15)
	constraint dept_key primary key(部门号)
);

create table 职工
(
	职工号 char(10),
	姓名 char(10),
	年龄 int,
	职务 char(10),
	工资 int,
	部门号 char(10),
	constraint worker_key primary key(职工号),
	constraint worker_age_border check(年龄<=60),
	constraint worker_foreign_dept foreign key(部门号) references 部门(部门号)
);

2、

先在原Course表添加离散数学课程

insert
into course
values ('8', '离散数学', null, 4);
select * from course;

在这里插入图片描述

在建立一张表记录离散数学成绩分级

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

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

select * from s;

在这里插入图片描述

①

create procedure Proc_G
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 s set num = @less60 where score = '[0,60)';
	select @more60less70 = count(*) from SC where Grade >= 60 and Grade < 70 and Cno = @Cno;
	update s set num = @more60less70 where score = '[60,70)';
	select @more70less80 = count(*) from SC where Grade >= 70 and Grade < 80 and Cno = @Cno;
	update s set num = @more70less80 where score = '[70,80)';
	select @more80less90 = count(*) from SC where Grade >= 80 and Grade < 90 and Cno = @Cno;
	update s set num = @more80less90 where score = '[80,90)';
	select @more90less100 = count(*) from SC where Grade >= 90 and Grade < 100 and Cno = @Cno;
	update s set num = @more90less100 where score = '[90,100]';
end;
exec Proc_s; 
select * from SC where Cno = 8;
select * from s;

在这里插入图片描述

②

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 score_to_level
as
begin
	update SC set Grade_level='A' where Grade>=90;
	update SC set Grade_level='B' where Grade>=80 and Grade<90;
	update SC set Grade_level='C' where Grade>=70 and Grade<80;
	update SC set Grade_level='D' where Grade>=60 and Grade<80;
	update SC set Grade_level='E' where Grade<90;
end