使用SQL server 创建存储过程, 查看“计算机基础”课程笔试平均分和实验平均分,以及未通过考试的学员名单。(笔试成绩满分 80 分,上机成绩满分 20 分,双科成绩均达到满分 的 80%以上为优秀, 单科不足满分的60%为不合格, 否则为合格)。(提示: 用带 有游标的存储过程)
时间: 2024-01-21 20:17:47 浏览: 98
下面是一个使用游标的SQL Server存储过程,用于计算“计算机基础”课程的笔试平均分和实验平均分,以及未通过考试的学员名单:
CREATE PROCEDURE sp_CompSciGrades
AS
BEGIN
SET NOCOUNT ON;
DECLARE @student_id INT;
DECLARE @exam_score INT;
DECLARE @lab_score INT;
DECLARE @avg_exam_score FLOAT;
DECLARE @avg_lab_score FLOAT;
DECLARE @num_passed INT;
DECLARE @num_failed INT;
DECLARE @num_students INT;
DECLARE @passed_students TABLE (student_id INT);
DECLARE @failed_students TABLE (student_id INT);
DECLARE cur_student CURSOR FOR
SELECT student_id, exam_score, lab_score
FROM CompSciGrades;
SET @num_passed = 0;
SET @num_failed = 0;
SET @num_students = 0;
SET @avg_exam_score = 0;
SET @avg_lab_score = 0;
OPEN cur_student;
FETCH NEXT FROM cur_student INTO @student_id, @exam_score, @lab_score;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @num_students = @num_students + 1;
IF @exam_score >= 64 AND @lab_score >= 16 AND ((@exam_score / 80.0) + (@lab_score / 20.0)) >= 0.8
BEGIN
SET @num_passed = @num_passed + 1;
INSERT INTO @passed_students (student_id) VALUES (@student_id);
END
ELSE
BEGIN
SET @num_failed = @num_failed + 1;
INSERT INTO @failed_students (student_id) VALUES (@student_id);
END
SET @avg_exam_score = @avg_exam_score + @exam_score;
SET @avg_lab_score = @avg_lab_score + @lab_score;
FETCH NEXT FROM cur_student INTO @student_id, @exam_score, @lab_score;
END
CLOSE cur_student;
DEALLOCATE cur_student;
SET @avg_exam_score = @avg_exam_score / @num_students;
SET @avg_lab_score = @avg_lab_score / @num_students;
SELECT 'Average Exam Score' AS metric, @avg_exam_score AS value
UNION ALL
SELECT 'Average Lab Score', @avg_lab_score
UNION ALL
SELECT 'Number of Passed Students', CAST(@num_passed AS VARCHAR(10))
UNION ALL
SELECT 'Number of Failed Students', CAST(@num_failed AS VARCHAR(10));
SELECT 'Failed Students' AS metric, student_id
FROM @failed_students
ORDER BY student_id;
END
该存储过程使用游标来迭代每个学生的成绩,并根据给定的优秀和不合格标准来确定他们是否通过考试。它还计算笔试平均分和实验平均分,并返回未通过考试的学员名单。
相关推荐













