1) 使用 SQL 语句创建一个存储过程，要求根据男女生人数来输出不同的信息。如果男生人 数大于女生人数，输出“男比女多”，否则输出“女比男多”，存储过程名称为 ShowPerson。 2) 查询某学生（根据学号查询）某学期选修课程的成绩，结果显示为学号、姓名、课程编 号、成绩、学期，存储过程名为“查询成绩”。 3) 创建存储过程 cjcx，根据输入的课程名称查询该课程的平均成绩、最高分和最低分。执 行存储过程 cjcx，查询“管理学”课程的信息。 4) 创建存储过程“查询学生信息”，如果给出姓名，则查询指定姓名的学生详细信息（包 括学生信息和班级信息）；如果不给出姓名，则查询所有学生的详细信息（包括学生信 息和班级信息）。 5) 创建存储过程“学生成绩综合查询”，参数为姓名、课程名称及学期，显示结果为学号、 姓名、课程名称、成绩、学期。

1) 创建存储过程 ShowPerson：

CREATE PROCEDURE ShowPerson
AS
BEGIN
    DECLARE @maleCount INT, @femaleCount INT
    SELECT @maleCount = COUNT(*) FROM Student WHERE Gender = '男'
    SELECT @femaleCount = COUNT(*) FROM Student WHERE Gender = '女'
    IF @maleCount > @femaleCount
        PRINT '男比女多'
    ELSE
        PRINT '女比男多'
END

2) 创建存储过程查询成绩：

CREATE PROCEDURE 查询成绩
    @studentId INT,
    @semester VARCHAR(50)
AS
BEGIN
    SELECT Student.Id AS 学号, Student.Name AS 姓名, Course.Id AS 课程编号, Score.Score AS 成绩, Score.Semester AS 学期
    FROM Student
    JOIN Score ON Student.Id = Score.StudentId
    JOIN Course ON Score.CourseId = Course.Id
    WHERE Student.Id = @studentId AND Score.Semester = @semester
END

3) 创建存储过程 cjcx：

CREATE PROCEDURE cjcx
    @courseName VARCHAR(50)
AS
BEGIN
    SELECT AVG(Score.Score) AS 平均成绩, MAX(Score.Score) AS 最高分, MIN(Score.Score) AS 最低分
    FROM Score
    JOIN Course ON Score.CourseId = Course.Id
    WHERE Course.Name = @courseName
END

执行存储过程 cjcx，查询“管理学”课程的信息：

EXEC cjcx '管理学'

4) 创建存储过程查询学生信息：

CREATE PROCEDURE 查询学生信息
    @name VARCHAR(50) = NULL
AS
BEGIN
    IF @name IS NULL
        SELECT Student.Id AS 学号, Student.Name AS 姓名, Student.Gender AS 性别, Student.Birthday AS 生日, Class.Name AS 班级
        FROM Student
        JOIN Class ON Student.ClassId = Class.Id
    ELSE
        SELECT Student.Id AS 学号, Student.Name AS 姓名, Student.Gender AS 性别, Student.Birthday AS 生日, Class.Name AS 班级
        FROM Student
        JOIN Class ON Student.ClassId = Class.Id
        WHERE Student.Name = @name
END

5) 创建存储过程学生成绩综合查询：

CREATE PROCEDURE 学生成绩综合查询
    @name VARCHAR(50),
    @courseName VARCHAR(50),
    @semester VARCHAR(50)
AS
BEGIN
    SELECT Student.Id AS 学号, Student.Name AS 姓名, Course.Name AS 课程名称, Score.Score AS 成绩, Score.Semester AS 学期
    FROM Student
    JOIN Score ON Student.Id = Score.StudentId
    JOIN Course ON Score.CourseId = Course.Id
    WHERE Student.Name = @name AND Course.Name = @courseName AND Score.Semester = @semester
END