Oracle SQL 常用语句练习：统计、计算与数据维护详解

1. 准备环境：理解表结构

• 学生表 (ze_student)：包含学号(s#)、姓名(sname)、出生日期(difdate)及所在班级(grade)。
• 课程表 (ze_course)：包含课程号(c#)及课程名称(cname)。
• 成绩单 (ze_score)：关联表，包含学号(s#)、课程号(c#)及对应成绩(score)。

第1步：基础统计与平均值计算

1.1 统计每个班级的人数
通过对学生表的班级字段进行分组计数即可得到结果：

SELECT grade, COUNT(sname) FROM ze_student GROUP BY grade;

1.2 计算特定年级学生的平均成绩
要求计算2007级各学生的平均成绩，若无成绩记录则视为0分。这里使用子查询配合NVL函数处理空值：

SELECT a.sname, 
       (SELECT AVG(NVL(b.score, 0)) 
        FROM ze_score b 
        WHERE b.s# = a.s#) 
FROM ze_student a 
WHERE grade = 2007;

1.3 多科目平均分极值统计
筛选2007级学生的各科平均分、最高分和最低分（保留两位小数），需连接学生和成绩表：

SELECT b.c#, 
       ROUND(AVG(b.score), 2) AS avg_score,
       MAX(b.score) AS max_score,
       MIN(NVL(b.score, 0)) AS min_score
FROM ze_student a, ze_score b 
WHERE b.s# = a.s# AND a.grade = 2007 
GROUP BY b.c#;

第2步：高级数据处理与格式调整

2.1 基于条件的分数等级划分
统计每人平均成绩，并根据分数段标记为“优秀”、“及格”或“不及格”。利用CASE WHEN语句实现逻辑分支：

SELECT s#, c, 
       CASE 
         WHEN c >= 90 THEN '优秀' 
         WHEN c < 90 AND c >= 60 THEN '及格' 
         ELSE '不及格' 
       END AS jige_status
FROM (SELECT s#, AVG(NVL(score, 0)) AS c 
      FROM ze_score 
      GROUP BY s#) 
ORDER BY jige_status DESC;

2.2 按月统计出生人数
从学生表中提取月份信息并进行分组计数。使用TO_CHAR函数格式化日期字段：

SELECT TO_CHAR(difdate, 'MM') AS month, 
       COUNT(s#) AS birth_count 
FROM ze_student 
GROUP BY TO_CHAR(difdate, 'MM');

2.3 学号格式化处理
要求将学号统一格式化为以'S'开头，总长度不足12位时左侧补零。这涉及字符串拼接与填充函数：

-- 查询预览
SELECT CONCAT('S', LPAD(s#, 11, '0')) AS formatted_s# 
FROM ze_student;

-- 实际更新操作（谨慎执行）
UPDATE ze_student SET s# = CONCAT('S', LPAD(s#, 11, '0'));
UPDATE ze_score SET s# = CONCAT('S', LPAD(s#, 11, '0'));

注意：在执行UPDATE前建议备份数据或使用TRANSACTION控制范围。

第3步：复杂比率计算与维护操作

3.1 课程与个人的及格率计算
及格率即及格人数占总人数的比例。以下示例展示了如何按课程计算各科平均成绩及及格率，以及按学生计算总科目的及格率：

-- 各科及格率
SELECT c#, 
       AVG(NVL(score, 0)) AS avg_score, 
       SUM(NVL(score, 0)) AS total_score, 
       COUNT(s#) AS student_count,
       TRUNC(SUM(CASE WHEN NVL(score, 0) > 60 THEN 1 ELSE 0 END) / COUNT(s#), 2) AS pass_rate
FROM ze_score 
GROUP BY c#;

-- 每人及格率
SELECT s#, 
       AVG(NVL(score, 0)) AS avg_score,
       SUM(NVL(score, 0)) AS total_score,
       COUNT(c#) AS course_count,
       SUM(CASE WHEN NVL(score, 0) > 60 THEN 1 ELSE 0 END) / COUNT(c#) AS personal_pass_rate
FROM ze_score 
GROUP BY s#;

3.2 数据修正与删除
包括为特定班级特定课程加分、重命名课程以及删除错误数据记录：

-- 给2007级数学课所有学生加5分
UPDATE ze_score 
SET score = NVL(score, 0) + 5 
WHERE s# IN (SELECT s# FROM ze_student WHERE grade = 2007) 
AND c# = (SELECT c# FROM ze_course WHERE cname = '数学');

-- 删除张三的大学语文成绩
DELETE FROM ze_score 
WHERE s# IN (SELECT s# FROM ze_student WHERE sname = '张三') 
AND c# = (SELECT c# FROM ze_course WHERE cname = '大学语文');

-- 将名为'数学'的课程改为'高等数学'
UPDATE ze_course 
SET cname = '高等数学' 
WHERE cname LIKE '%数学%';
END;

上述代码片段涵盖了日常数据库维护中的常见场景。在实际生产环境中，请务必先在测试库验证，并确认事务提交方式。

总结