SQL数据统计处理

实验4 SQL数据统计处理

1. 实验环境：

操作系统：Windows 11 家庭版

数据库管理系统：SQL SERVER 2019

客户端：SSMS 19.3（SQL Server Management Studio 19.3.4.0）

数据库模式：MyDB

学生表：Student(Sno,Sname,Ssex,Sage,Sdept)

课程表：Course(Cno,Cname,Cpno,Ccredit)

选课表：SC(Sno,Cno,Grade)

数据库的值：如图2.4（教材52页）

1、在学生选课表中，统计学生平均分大于70的成绩记录。包括学生学号、总分、平均分、课程门数、课程最高分、最低分字段。

实验代码：

SELECT Sno as 学号,

    SUM(grade) AS 总分,

    AVG(grade) AS 平均分,

    COUNT(Cno) AS 课程门数,

    MAX(grade) AS 最高分,

    MIN(grade) AS 最低分

FROM

    SC

GROUP BY

    Sno

HAVING

    AVG(grade) > 70;

运行结果：

2、在学生选课表和学生表中，统计学生平均分在60~70和90~100的学生成绩记录，包括学生学号、学生姓名、总分、平均分、最高分、最低分、所修课程门数字段。

实验代码：

SELECT Student.Sno as 学号,

    Sname As 姓名,

    SUM(grade) AS 总分,

    AVG(grade) AS 平均分,

    MAX(grade) AS 最高分,

    MIN(grade) AS 最低分,

    COUNT(Cno) AS 所修课程门数

FROM

    SC,Student

where SC.sno=Student.sno

GROUP BY

    Student.Sno,Student.Sname

HAVING

    (AVG(grade)>=60 and avg(grade) <=70)

    or

    (AVG(grade)>=90 and avg(grade) <=100)

运行结果：

3、在学生选课表和学生表中，查询性别为女并且平均分大于80的学生基本信息。

实验代码：

select

    Student.Sno,

    Student.Sname,

    Student.Ssex,

    Student.Sage,

    Student.Sdept

from Student,SC

where Student.Sno=SC.Sno

Group by Student.Sno,

    Student.Sname,

    Student.Ssex,

    Student.Sage,

    Student.Sdept

having Student.Ssex='女'

and avg(SC.grade)>80

运行结果：

4、统计平均分各分数段人数。

实验代码：

-- 60分以下的人数

SELECT

    '60以下'as 分数段,

    COUNT(*) AS 人数

FROM

    (SELECT student.sno, AVG(grade) AS avg_score

     FROM student,SC

     where Student.Sno=SC.Sno

     GROUP BY Student.sno

     HAVING  AVG(grade) < 60) AS SubQuery60

UNION all

-- 60-80的人数

SELECT

    '60-80'as 分数段,

    COUNT(*) AS 人数

FROM

    (SELECT student.sno, AVG(grade) AS avg_score

     FROM student,SC

     where Student.Sno=SC.Sno

     GROUP BY Student.sno

     HAVING  AVG(grade) >= 60 and AVG(grade)<80 ) AS SubQuery6079

UNION ALL

-- 80-100的人数

SELECT

    '80-100'as 分数段,

    COUNT(*) AS 人数

FROM

    (SELECT student.sno, AVG(grade) AS avg_score

     FROM student,SC

     where Student.Sno=SC.Sno

     GROUP BY Student.sno

     HAVING  AVG(grade)>=80 and AVG(grade) <=100) AS SubQuery80100;

运行结果：

5.在学生选课表、学生表中，查询学生成绩重修（成绩<60）门数大于10门的学生基本信息（查询结果包括学号、姓名、性别字段）。

实验代码：

SELECT

    Student.Sno,

    Student.Sname,

    Student.Ssex

FROM

    Student,SC

WHERE

Student.Sno=SC.Sno

and

    Student.Sno IN (

        SELECT

            SC.Sno

        FROM

            SC

        WHERE

            SC.grade < 60 and Student.Sno=SC.Sno

        GROUP BY

            SC.Sno

        HAVING

            COUNT(*) > 10

    );

运行结果：

6. 统计每门课程成绩均在80分以上的各学生平均分。

实验代码：

SELECT

    sc.Sno,

    AVG(sc.Grade) AS 平均分

FROM

    SC

WHERE

    sc.Sno IN (

        SELECT

            s2.Sno

        FROM

            SC s2

        GROUP BY

            s2.Sno

        HAVING

            MIN(s2.Grade) >= 80 AND

            COUNT(DISTINCT s2.Cno) = (

                SELECT

                    COUNT(DISTINCT Cno)

                FROM

                    SC

                WHERE

                    SC.Sno = s2.Sno

            )

    )

GROUP BY

    sc.Sno;

运行结果：

1. 实验总结：

在实验过程中，我们注意到了SQL查询语句的编写需要仔细考虑逻辑和语法，以确保查询的准确性。此外，对于统计不同分数段人数的查询，第一次使用了UNION语句，让结果的三次输出界面变成了一次

总体来说，本次实验加深了对SQL查询语言的理解和应用，为将来进行更复杂的数据库操作和数据分析打下了良好的基础