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