63 lines
2.1 KiB
Transact-SQL
63 lines
2.1 KiB
Transact-SQL
-- 1. 统计离散数学的成绩分布情况，即按照各分数段统计人数
|
||
CREATE PROCEDURE sp_GetDiscreteMathGradeDistribution
|
||
AS
|
||
BEGIN
|
||
DECLARE @CourseName NVARCHAR(20) = N'离散数学';
|
||
DECLARE @TargetCno CHAR(8);
|
||
SELECT @TargetCno = Cno
|
||
FROM Course
|
||
WHERE Cname = @CourseName;
|
||
SELECT GradeCategory AS N'成绩段',
|
||
COUNT(*) AS N'人数'
|
||
FROM (SELECT CASE
|
||
WHEN Grade >= 90 AND Grade <= 100 THEN N'90-100 (优秀)'
|
||
WHEN Grade >= 80 AND Grade < 90 THEN N'80-89 (良好)'
|
||
WHEN Grade >= 70 AND Grade < 80 THEN N'70-79 (中等)'
|
||
WHEN Grade >= 60 AND Grade < 70 THEN N'60-69 (及格)'
|
||
WHEN Grade >= 0 AND Grade < 60 THEN N'<60 (不及格)'
|
||
END AS GradeCategory
|
||
FROM SC
|
||
WHERE Cno = @TargetCno) AS GradeDistribution
|
||
GROUP BY GradeCategory
|
||
ORDER BY CASE GradeCategory
|
||
WHEN N'90-100 (优秀)' THEN 1
|
||
WHEN N'80-89 (良好)' THEN 2
|
||
WHEN N'70-79 (中等)' THEN 3
|
||
WHEN N'60-69 (及格)' THEN 4
|
||
WHEN N'<60 (不及格)' THEN 5
|
||
END;
|
||
END;
|
||
Go
|
||
|
||
-- 2. 统计任意一门课的平均成绩
|
||
CREATE FUNCTION fn_GetAverageCourseGrade(@InputCno CHAR(8))
|
||
RETURNS DECIMAL(5, 2)
|
||
AS
|
||
BEGIN
|
||
DECLARE @AverageGrade DECIMAL(5, 2);
|
||
SELECT @AverageGrade = AVG(CAST(Grade AS DECIMAL(5, 2)))
|
||
FROM SC
|
||
WHERE Cno = @InputCno
|
||
AND Grade IS NOT NULL;
|
||
RETURN @AverageGrade;
|
||
END;
|
||
Go
|
||
|
||
-- 3. 将学生选课成绩从百分制改为等级制（即A、B、C、D、E）
|
||
CREATE PROCEDURE sp_UpdateSCGradeToClass
|
||
AS
|
||
BEGIN
|
||
BEGIN TRANSACTION;
|
||
UPDATE SC
|
||
SET Class =
|
||
CASE
|
||
WHEN Grade >= 90 AND Grade <= 100 THEN 'A'
|
||
WHEN Grade >= 80 AND Grade < 90 THEN 'B'
|
||
WHEN Grade >= 70 AND Grade < 80 THEN 'C'
|
||
WHEN Grade >= 60 AND Grade < 70 THEN 'D'
|
||
WHEN Grade >= 0 AND Grade < 60 THEN 'E'
|
||
END
|
||
WHERE Grade IS NOT NULL;
|
||
COMMIT TRANSACTION;
|
||
END;
|
||
Go |