对学生课程数据库,编写存储过程,完成下面功能: 1)统计离散数学的成绩分布情况,即按照各分数段统计人数; 2)统计任意一门课的平均成绩。 3)将学生选课成绩从百分制改为等级制(即A、B、C、D、E); 要求:提交源程序并标识必要的注释。保证程序能正确编译和运行,认真填写实验报告。
时间: 2024-02-13 09:06:28 浏览: 79
好的,这是一个关于学生课程数据库的存储过程的问题,需要实现三个功能:
1. 统计离散数学的成绩分布情况,即按照各分数段统计人数;
2. 统计任意一门课的平均成绩。
3. 将学生选课成绩从百分制改为等级制(即A、B、C、D、E)。
下面是一个示例的存储过程代码,用于实现上述功能:
```sql
-- 学生选课成绩数据库存储过程
-- 统计离散数学的成绩分布情况,即按照各分数段统计人数
CREATE PROCEDURE DiscreteMathScoreDistribution
AS
BEGIN
-- 统计各分数段人数
SELECT
'[0-59]' AS ScoreRange, COUNT(*) AS Count
FROM
StudentCourse
WHERE
CourseName = '离散数学' AND Score < 60
UNION
SELECT
'[60-69]' AS ScoreRange, COUNT(*) AS Count
FROM
StudentCourse
WHERE
CourseName = '离散数学' AND Score BETWEEN 60 AND 69
UNION
SELECT
'[70-79]' AS ScoreRange, COUNT(*) AS Count
FROM
StudentCourse
WHERE
CourseName = '离散数学' AND Score BETWEEN 70 AND 79
UNION
SELECT
'[80-89]' AS ScoreRange, COUNT(*) AS Count
FROM
StudentCourse
WHERE
CourseName = '离散数学' AND Score BETWEEN 80 AND 89
UNION
SELECT
'[90-100]' AS ScoreRange, COUNT(*) AS Count
FROM
StudentCourse
WHERE
CourseName = '离散数学' AND Score BETWEEN 90 AND 100
END
-- 统计任意一门课的平均成绩
CREATE PROCEDURE CourseAvgScore
@CourseName VARCHAR(50)
AS
BEGIN
-- 计算平均成绩
SELECT AVG(Score) AS AvgScore
FROM StudentCourse
WHERE CourseName = @CourseName
END
-- 将学生选课成绩从百分制改为等级制(即A、B、C、D、E)
CREATE PROCEDURE ConvertScoreToGrade
AS
BEGIN
-- 更新成绩为等级制
UPDATE StudentCourse
SET Grade = CASE
WHEN Score >= 90 THEN 'A'
WHEN Score >= 80 THEN 'B'
WHEN Score >= 70 THEN 'C'
WHEN Score >= 60 THEN 'D'
ELSE 'E'
END
END
```
以上是一个简单的示例代码,需要根据实际情况进行修改和扩展。同时,需要注意存储过程的注释和实验报告的撰写。