1、在学生选课表中，统计学生平均分大于70的成绩记录。包括学生学号、总分、平均分、课程门数、课程最高分、最低分字段。
2、在学生选课表和学生表中，统计学生平均分在60~70和90~100的学生成绩记录，包括学生学号、学生姓名、总分、平均分、最高分、最低分、所修课程门数字段。
3、在学生选课表和学生表中，查询性别为女并且平均分大于80的学生基本信息。
4、统计平均分各分数段人数。
5.在学生选课表、学生表中，查询学生成绩重修（成绩<60）门数大于10门的学生基本信息（查询结果包括学号、姓名、性别字段）。
### 实验4 SQL数据统计处理知识点详解
#### 1. 统计学生平均分大于70的成绩记录
在本实验的第一部分中，我们关注的是如何筛选出那些平均分超过70分的学生及其相关信息。这里使用到了`GROUP BY`、`SUM`、`AVG`、`COUNT`、`MAX`和`MIN`等聚合函数来计算每个学生的总分、平均分、选课数量以及分数的最大最小值，并通过`HAVING`子句来限定只返回那些平均分大于70的学生信息。
**知识点解析**:
- **`GROUP BY`**: 用于对数据进行分组，便于后续的聚合操作。在这个例子中，我们需要按照学生学号进行分组，以便计算每位学生的各项指标。
- **`SUM`**: 计算总和，这里用来计算每位学生的总分。
- **`AVG`**: 计算平均值，用于计算每位学生的平均成绩。
- **`COUNT`**: 计数器，计算每组内的行数，在这里用以得出每位学生选课的数量。
- **`MAX`和`MIN`**: 分别计算最大值和最小值，这里用来找出每位学生的最高分和最低分。
- **`HAVING`**: 类似于`WHERE`子句，但应用于`GROUP BY`之后的结果集上，用来过滤掉不满足条件的分组。在这里，它确保了只返回平均分大于70分的学生。
**示例代码**:
```sql
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的学生成绩记录
这部分实验要求统计那些平均分位于60到70之间或者90到100之间的学生的信息。同样地，我们利用`GROUP BY`、`SUM`、`AVG`等聚合函数，并结合`HAVING`子句来完成这一任务。此外，还需要从两个表（学生表和选课表）中获取信息，因此还涉及到表之间的连接操作。
**知识点解析**:
- **表连接**:
- **隐式连接**: 使用逗号`,`来连接表，这种写法要求在`WHERE`子句中明确指定连接条件。例如：
```sql
SELECT ...
FROM SC, Student
WHERE SC.Sno = Student.Sno;
```
- **显式连接**: 使用`JOIN`关键字来连接表，这种方式更易于阅读和维护。例如：
```sql
SELECT ...
FROM SC JOIN Student ON SC.Sno = Student.Sno;
```
**示例代码**:
```sql
SELECT Student.Sno as 学号,
Sname As 姓名,
SUM(grade) AS 总分,
AVG(grade) AS 平均分,
MAX(grade) AS 最高分,
MIN(grade) AS 最低分,
COUNT(Cno) AS 所修课程门数
FROM SC
JOIN Student ON 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的学生基本信息
这一部分的目标是筛选出那些性别为女性且平均分高于80分的学生的基本信息。与前两部分类似，这里仍然需要使用到`GROUP BY`和`HAVING`子句，同时还要加上一个额外的条件来筛选性别。
**知识点解析**:
- **多条件筛选**:
- 使用逻辑运算符`AND`和`OR`来组合多个条件。
- 在`HAVING`子句中可以同时应用多个条件。
**示例代码**:
```sql
SELECT Student.Sno,
Student.Sname,
Student.Ssex,
Student.Sage,
Student.Sdept
FROM Student
JOIN SC ON 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. 统计平均分各分数段人数
这部分的任务是将学生的平均分划分为不同的分数段，并统计每个分数段的人数。这里需要用到嵌套查询和`UNION ALL`来组合不同分数段的结果。
**知识点解析**:
- **嵌套查询**:
- 外层查询负责统计每个分数段的人数。
- 内层查询则用来过滤出符合特定分数范围的学生。
**示例代码**:
```sql
-- 60分以下的人数
SELECT '60以下' AS 分数段,
COUNT(*) AS 人数
FROM (
SELECT Student.Sno,
AVG(grade) AS avg_score
FROM Student
JOIN SC ON 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
JOIN SC ON Student.Sno = SC.Sno
GROUP BY Student.Sno
HAVING AVG(grade) BETWEEN 60 AND 80
) AS SubQuery6079
UNION ALL
-- 80-100的人数
SELECT '80-100' AS 分数段,
COUNT(*) AS 人数
FROM (
SELECT Student.Sno,
AVG(grade) AS avg_score
FROM Student
JOIN SC ON Student.Sno = SC.Sno
GROUP BY Student.Sno
HAVING AVG(grade) BETWEEN 80 AND 100
) AS SubQuery80100;
```
---
#### 5. 查询学生成绩重修（成绩<60）门数大于10门的学生基本信息
最后一部分要求筛选出那些有10门以上重修课程（成绩低于60分）的学生的基本信息。这一步同样需要使用到`GROUP BY`和`HAVING`子句，但是这次的重点在于如何统计重修的课程数量。
**知识点解析**:
- **子查询**:
- 在这里使用了一个子查询来计算每位学生重修的课程数量。
- 主查询根据这个子查询的结果进一步筛选出符合条件的学生。
**示例代码**:
```sql
SELECT Student.Sno AS 学号,
Sname AS 姓名,
Ssex AS 性别
FROM Student
WHERE EXISTS (
SELECT *
FROM SC
WHERE SC.Sno = Student.Sno
GROUP BY SC.Sno
HAVING COUNT(*) FILTER (WHERE grade < 60) > 10
);
```
以上就是本次实验的所有知识点详解。这些SQL语句不仅能够帮助理解如何进行数据统计处理，同时也展示了SQL的强大功能和灵活性。
