实验4 SQL数据统计处理
- 实验环境:
操作系统: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;
运行结果:
- 实验总结:
在实验过程中,我们注意到了SQL查询语句的编写需要仔细考虑逻辑和语法,以确保查询的准确性。此外,对于统计不同分数段人数的查询,第一次使用了UNION语句,让结果的三次输出界面变成了一次
总体来说,本次实验加深了对SQL查询语言的理解和应用,为将来进行更复杂的数据库操作和数据分析打下了良好的基础