Oracle SQL 十道经典练习题（附完整代码 + 解题思路）

Oracle SQL 十道经典练习题（附完整代码 + 解题思路）

在数据库学习和面试中，SQL 查询是核心技能之一。本文基于 Oracle 数据库，整理了 10 道经典 SQL 练习题，涵盖表创建、数据插入、多表关联、分组统计、自连接等高频考点，每道题均提供详细解题思路、完整可运行代码及结果说明，适合初学者巩固基础，也适合进阶者查漏补缺。

1. 统计经理的下属人数与平均年龄

题目描述

现有 Employees 表，存储员工 ID、姓名、汇报对象 ID 及年龄。需返回至少有 1 名下属的经理信息：经理 ID、姓名、直接下属人数、下属平均年龄（四舍五入到整数），结果按 employee_id 排序。

解题思路

1. 表角色拆分：将 Employees 表视为两张表 ——m（经理表）和 e（下属表）。
2. 关联条件：通过 m.employee_id = e.reports_to 关联经理与下属，确保只统计有下属的经理。
3. 统计计算：
   • COUNT(e.employee_id)：统计每个经理的直接下属人数（排除 NULL）。
   • ROUND(AVG(e.age))：计算下属平均年龄并四舍五入。
4. 分组排序：按经理的 employee_id 和 name 分组，结果按 employee_id 升序排列。

完整代码

sql

-- 1. 创建 Employees 表
CREATE TABLE Employees (
    employee_id INT,
    name VARCHAR2(50),
    reports_to INT,
    age INT
);
-- 2. 插入示例数据（示例 1）
INSERT INTO Employees (employee_id, name, reports_to, age) 
VALUES (9, 'Hercy', NULL, 43);
INSERT INTO Employees (employee_id, name, reports_to, age) 
VALUES (6, 'Alice', 9, 41);
INSERT INTO Employees (employee_id, name, reports_to, age) 
VALUES (4, 'Bob', 9, 36);
INSERT INTO Employees (employee_id, name, reports_to, age) 
VALUES (2, 'Winston', NULL, 37);
-- 3. 执行查询
SELECT 
    m.employee_id,
    m.name,
    COUNT(e.employee_id) AS reports_count,  -- 下属人数
    ROUND(AVG(e.age)) AS average_age        -- 下属平均年龄（四舍五入）
FROM 
    Employees m
JOIN 
    Employees e ON m.employee_id = e.reports_to  -- 经理与下属关联
GROUP BY 
    m.employee_id, m.name
ORDER BY 
    m.employee_id;  -- 按经理 ID 排序

结果说明

• 经理 Hercy（ID=9）有 2 名下属（Alice 和 Bob），下属平均年龄 (41+36)/2=38.5，四舍五入后为 39。

2. 查找员工的直属部门

题目描述

Employee 表存储员工与部门的关联关系，需找出每个员工的直属部门：

• 若员工明确标记 primary_flag='Y'，则该部门为直属部门。
• 若员工仅属于 1 个部门（无论 primary_flag 为 N），则该部门默认为直属部门。
  | 列名 | 类型 | 说明 |
  | — | — | — |
  | employee/_id | int | 员工 ID（主键） |
  | department/_id | int | 部门 ID（主键） |
  | primary/_flag | varchar | 直属标记（Y/N） |

解题思路

1. 筛选逻辑拆分：
   • 条件 1：primary_flag='Y'（明确指定直属部门）。
   • 条件 2：primary_flag='N' 且员工仅属于 1 个部门（通过子查询统计部门数量）。
2. 子查询辅助：用 (SELECT COUNT(*) FROM Employee WHERE employee_id = e.employee_id) = 1 判断员工是否仅在 1 个部门。

完整代码

sql

-- 1. 创建 Employee 表
CREATE TABLE Employee (
    employee_id INT,
    department_id INT,
    primary_flag VARCHAR2(1),
    CONSTRAINT pk_emp_dept PRIMARY KEY (employee_id, department_id)  -- 复合主键
);
-- 2. 插入示例数据
INSERT INTO Employee (employee_id, department_id, primary_flag) 
VALUES (1, 1, 'N'),
       (2, 1, 'Y'),
       (2, 2, 'N'),
       (3, 3, 'N'),
       (4, 2, 'N'),
       (4, 3, 'Y'),
       (4, 4, 'N');
-- 3. 执行查询
SELECT 
    e.employee_id, 
    e.department_id
FROM Employee e
WHERE 
    -- 条件 1：明确标记直属部门
    e.primary_flag = 'Y'
    -- 条件 2：仅属于 1 个部门，默认该部门为直属
    OR (
        e.primary_flag = 'N' 
        AND (SELECT COUNT(*) FROM Employee WHERE employee_id = e.employee_id) = 1
    );

结果说明

3. 统计每位老师教授的科目种类数

题目描述

Teacher 表存储教师、科目与部门的关联，(subject_id, dept_id) 为复合主键。需统计每位老师教授的不同科目种类数（同一科目跨部门仍算 1 种）。

解题思路

1. 去重统计：同一教师的同一科目（subject_id 相同）无论跨多少部门，仅算 1 种，需用 COUNT(DISTINCT subject_id) 去重。
2. 分组依据：按 teacher_id 分组，确保统计每个教师的科目数量。

完整代码

sql

-- 1. 创建 Teacher 表（复合主键）
CREATE TABLE Teacher (
    teacher_id INT,
    subject_id INT,
    dept_id INT,
    CONSTRAINT pk_teacher PRIMARY KEY (subject_id, dept_id)
);
-- 2. 插入示例数据
INSERT INTO Teacher (teacher_id, subject_id, dept_id) 
VALUES (1, 2, 3),
       (1, 2, 4),
       (1, 3, 3),
       (2, 1, 1),
       (2, 2, 1),
       (2, 3, 1),
       (2, 4, 1);
-- 3. 执行查询
SELECT 
    teacher_id, 
    COUNT(DISTINCT subject_id) AS cnt  -- 统计不同科目数
FROM Teacher
GROUP BY teacher_id;

结果说明

• 教师 1 教授科目 2 和 3（共 2 种）；教师 2 教授科目 1-4（共 4 种）。

4. 统计近 30 天每日活跃用户数

题目描述

Activity 表记录用户社交媒体活动，需统计截至 2019-07-27（含）近 30 天的每日活跃用户数（当天有活动即算活跃，不展示活跃数为 0 的日期）。

解题思路

1. 生成日期范围：用 Oracle 特有 CONNECT BY 生成 2019-06-28 至 2019-07-27 的连续日期（近 30 天）。
2. 关联活动数据：用 LEFT JOIN 关联日期范围与 Activity 表，确保每个日期都能匹配活动数据。
3. 筛选有效数据：用 HAVING COUNT(DISTINCT user_id) > 0 过滤活跃数为 0 的日期，避免冗余。

完整代码

sql

-- 1. 创建 Activity 表
CREATE TABLE Activity (
    user_id INT,
    session_id INT,
    activity_date DATE,
    activity_type VARCHAR2(20)
);
-- 2. 插入示例数据
INSERT INTO Activity (user_id, session_id, activity_date, activity_type) 
VALUES (1, 1, TO_DATE('2019-07-20', 'YYYY-MM-DD'), 'open_session'),
       (1, 1, TO_DATE('2019-07-20', 'YYYY-MM-DD'), 'scroll_down'),
       (1, 1, TO_DATE('2019-07-20', 'YYYY-MM-DD'), 'end_session'),
       (2, 4, TO_DATE('2019-07-21', 'YYYY-MM-DD'), 'open_session'),
       (2, 4, TO_DATE('2019-07-21', 'YYYY-MM-DD'), 'send_message'),
       (2, 4, TO_DATE('2019-07-21', 'YYYY-MM-DD'), 'end_session'),
       (3, 2, TO_DATE('2019-07-21', 'YYYY-MM-DD'), 'open_session'),
       (3, 2, TO_DATE('2019-07-21', 'YYYY-MM-DD'), 'end_session'),
       (4, 3, TO_DATE('2019-06-25', 'YYYY-MM-DD'), 'open_session'),
       (4, 3, TO_DATE('2019-06-25', 'YYYY-MM-DD'), 'end_session');
-- 3. 执行查询（生成日期范围 + 统计活跃用户）
WITH DateRange AS (
    -- 生成 2019-06-28 至 2019-07-27 的连续日期
    SELECT TO_DATE('2019-06-28', 'YYYY-MM-DD') + LEVEL - 1 AS day
    FROM DUAL
    CONNECT BY LEVEL <= 30
)
SELECT 
    dr.day,
    COUNT(DISTINCT a.user_id) AS active_users  -- 每日活跃用户数（去重）
FROM DateRange dr
LEFT JOIN Activity a 
    ON a.activity_date = dr.day
GROUP BY dr.day
HAVING COUNT(DISTINCT a.user_id) > 0;  -- 仅展示有活跃用户的日期

结果说明

5. 查找非无聊且 ID 为奇数的影片

题目描述

cinema 表存储电影信息，需找出描述非 boring 且 ID 为奇数的影片，结果按 rating 降序排列。

解题思路

1. 条件筛选：
   • MOD(id, 2) = 1：用 Oracle MOD 函数判断 ID 是否为奇数（奇数除以 2 余数为 1）。
   • description != 'boring'：排除描述为 boring 的影片。
2. 排序：按 rating 降序，确保高评分影片在前。

完整代码

sql

-- 1. 创建 cinema 表
CREATE TABLE cinema (
    id INT,
    movie VARCHAR2(50),
    description VARCHAR2(100),
    rating FLOAT,
    CONSTRAINT pk_cinema PRIMARY KEY (id)
);
-- 2. 插入示例数据
INSERT INTO cinema (id, movie, description, rating) 
VALUES (1, 'War', 'great 3D', 8.9),
       (2, 'Science', 'fiction', 8.5),
       (3, 'irish', 'boring', 6.2),
       (4, 'Ice song', 'Fantacy', 8.6),
       (5, 'House card', 'Interesting', 9.1);
-- 3. 执行查询
SELECT 
    id,
    movie,
    description,
    rating
FROM cinema
WHERE 
    MOD(id, 2) = 1  -- ID 为奇数
    AND description != 'boring'  -- 非 boring 描述
ORDER BY rating DESC;  -- 按评分降序

结果说明

6. 计算产品的平均售价

题目描述

• Prices 表：存储产品在不同时间段的价格（product_id, start_date, end_date 为复合主键）。
• UnitsSold 表：存储产品的销售记录（可能有重复）。
  需计算每个产品的平均售价（总销售额 / 总销量，四舍五入到 2 位小数）。
  | 表名 | 列名 | 类型 | 说明 |
  | — | — | — | — |
  | Prices | product/_id | int | 产品 ID |
  | Prices | start/_date | date | 价格生效起始日期 |
  | Prices | end/_date | date | 价格生效结束日期 |
  | Prices | price | float | 产品价格 |
  | UnitsSold | product/_id | int | 产品 ID |
  | UnitsSold | purchase/_date | date | 购买日期 |
  | UnitsSold | units | int | 销售数量 |

解题思路

1. 关联表：
   • 按 product_id 关联 Prices 和 UnitsSold，确保产品匹配。
   • 按 purchase_date BETWEEN start_date AND end_date 关联销售日期与价格有效期，确保价格对应。
2. 计算逻辑：
   • 总销售额：SUM(u.units * p.price)（销量 × 对应价格）。
   • 总销量：SUM(u.units)。
   • 平均售价：ROUND(总销售额 / 总销量, 2)（四舍五入到 2 位小数）。

完整代码

sql

-- 1. 创建 Prices 表
CREATE TABLE Prices (
    product_id INT,
    start_date DATE,
    end_date DATE,
    price FLOAT,
    CONSTRAINT pk_prices PRIMARY KEY (product_id, start_date, end_date)
);
-- 2. 创建 UnitsSold 表
CREATE TABLE UnitsSold (
    product_id INT,
    purchase_date DATE,
    units INT
);
-- 3. 插入示例数据
INSERT INTO Prices (product_id, start_date, end_date, price) 
VALUES (1, TO_DATE('2019-02-17', 'YYYY-MM-DD'), TO_DATE('2019-02-28', 'YYYY-MM-DD'), 5),
       (1, TO_DATE('2019-03-01', 'YYYY-MM-DD'), TO_DATE('2019-03-22', 'YYYY-MM-DD'), 20),
       (2, TO_DATE('2019-02-01', 'YYYY-MM-DD'), TO_DATE('2019-02-20', 'YYYY-MM-DD'), 15),
       (2, TO_DATE('2019-02-21', 'YYYY-MM-DD'), TO_DATE('2019-03-31', 'YYYY-MM-DD'), 30);
INSERT INTO UnitsSold (product_id, purchase_date, units) 
VALUES (1, TO_DATE('2019-02-25', 'YYYY-MM-DD'), 100),
       (1, TO_DATE('2019-03-01', 'YYYY-MM-DD'), 15),
       (2, TO_DATE('2019-02-10', 'YYYY-MM-DD'), 200),
       (2, TO_DATE('2019-03-22', 'YYYY-MM-DD'), 30);
-- 4. 执行查询
SELECT 
    u.product_id,
    ROUND(SUM(u.units * p.price) / SUM(u.units), 2) AS average_price
FROM UnitsSold u
JOIN Prices p 
    ON u.product_id = p.product_id
    AND u.purchase_date BETWEEN p.start_date AND p.end_date  -- 销售日期在价格有效期内
GROUP BY u.product_id;

结果说明

7. 报告奖金少于 1000 的员工

题目描述

• Employee 表：存储员工 ID、姓名、上级 ID 及工资。
• Bonus 表：存储员工 ID 及奖金（empId 为外键，关联 Employee.empId）。
  需报告奖金少于 1000 的员工姓名和奖金（无奖金则显示 NULL）。

解题思路

1. 保留所有员工：用 LEFT JOIN 关联 Employee 和 Bonus，确保即使员工无奖金（Bonus 表无记录）也能被保留。
2. 筛选条件：b.bonus < 1000 OR b.bonus IS NULL，包含两种情况：
   • 有奖金但少于 1000。
   • 无奖金（bonus 为 NULL）。

完整代码

sql

-- 1. 创建 Employee 表
CREATE TABLE Employee (
    empId INT,
    name VARCHAR2(50),
    supervisor INT,
    salary INT,
    CONSTRAINT pk_employee PRIMARY KEY (empId)
);
-- 2. 创建 Bonus 表
CREATE TABLE Bonus (
    empId INT,
    bonus INT,
    CONSTRAINT pk_bonus PRIMARY KEY (empId),
    CONSTRAINT fk_bonus_emp FOREIGN KEY (empId) REFERENCES Employee(empId)
);
-- 3. 插入示例数据
INSERT INTO Employee (empId, name, supervisor, salary) 
VALUES (3, 'Brad', NULL, 4000),
       (1, 'John', 3, 1000),
       (2, 'Dan', 3, 2000),
       (4, 'Thomas', 3, 4000);
INSERT INTO Bonus (empId, bonus) 
VALUES (2, 500),
       (4, 2000);
-- 4. 执行查询
SELECT 
    e.name,
    b.bonus
FROM Employee e
LEFT JOIN Bonus b 
    ON e.empId = b.empId  -- 关联员工与奖金
WHERE 
    b.bonus < 1000 OR b.bonus IS NULL;  -- 奖金少于 1000 或无奖金

结果说明

8. 统计每个学生每门科目的考试次数

题目描述

• Students 表：存储学生 ID 和姓名。
• Subjects 表：存储科目名称。
• Examinations 表：存储学生参加科目的考试记录（可能重复）。
  需统计每个学生每门科目的考试次数（无考试则为 0），结果按 student_id 和 subject_name 排序。

解题思路

1. 生成全量组合：用 CROSS JOIN 关联 Students 和 Subjects，生成所有 “学生 - 科目” 组合（确保无遗漏）。
2. 关联考试记录：用 LEFT JOIN 关联组合与 Examinations，确保无考试记录的组合也能保留（次数为 0）。
3. 统计次数：COUNT(e.student_id) 统计考试次数（NULL 会被忽略，无考试时为 0）。

完整代码

sql

-- 1. 创建 Students 表
CREATE TABLE Students (
    student_id INT,
    student_name VARCHAR2(50),
    CONSTRAINT pk_students PRIMARY KEY (student_id)
);
-- 2. 创建 Subjects 表
CREATE TABLE Subjects (
    subject_name VARCHAR2(50),
    CONSTRAINT pk_subjects PRIMARY KEY (subject_name)
);
-- 3. 创建 Examinations 表
CREATE TABLE Examinations (
    student_id INT,
    subject_name VARCHAR2(50)
);
-- 4. 插入示例数据
INSERT INTO Students (student_id, student_name) 
VALUES (1, 'Alice'), (2, 'Bob'), (13, 'John'), (6, 'Alex');
INSERT INTO Subjects (subject_name) 
VALUES ('Math'), ('Physics'), ('Programming');
INSERT INTO Examinations (student_id, subject_name) 
VALUES (1, 'Math'), (1, 'Physics'), (1, 'Programming'),
       (2, 'Programming'), (1, 'Physics'), (1, 'Math'),
       (13, 'Math'), (13, 'Programming'), (13, 'Physics'),
       (2, 'Math'), (1, 'Math');
-- 5. 执行查询
SELECT 
    s.student_id,
    s.student_name,
    sub.subject_name,
    COUNT(e.student_id) AS attended_exams  -- 考试次数（无考试为 0）
FROM Students s
CROSS JOIN Subjects sub  -- 生成所有学生-科目组合
LEFT JOIN Examinations e 
    ON s.student_id = e.student_id 
    AND sub.subject_name = e.subject_name
GROUP BY 
    s.student_id, s.student_name, sub.subject_name
ORDER BY 
    s.student_id, sub.subject_name;

结果说明

9. 找出温度高于前一天的日期 ID

题目描述

Weather 表存储每日温度，需找出温度高于前一天的日期对应的 id（无顺序要求）。

解题思路

1. 自连接：将 Weather 表视为两张表 ——w1（当日）和 w2（前一天）。
2. 关联条件：
   • w1.recordDate = w2.recordDate + 1：确保 w1 是 w2 的后一天（Oracle 日期加 1 表示加 1 天）。
   • w1.temperature > w2.temperature：筛选当日温度高于前一天的记录。

完整代码

sql

-- 1. 创建 Weather 表
CREATE TABLE Weather (
    id INT,
    recordDate DATE,
    temperature INT,
    CONSTRAINT pk_weather PRIMARY KEY (id)
);
-- 2. 插入示例数据
INSERT INTO Weather (id, recordDate, temperature) 
VALUES (1, TO_DATE('2015-01-01', 'YYYY-MM-DD'), 10),
       (2, TO_DATE('2015-01-02', 'YYYY-MM-DD'), 25),
       (3, TO_DATE('2015-01-03', 'YYYY-MM-DD'), 20),
       (4, TO_DATE('2015-01-04', 'YYYY-MM-DD'), 30);
-- 3. 执行查询
SELECT 
    w1.id
FROM Weather w1
JOIN Weather w2 
    ON w1.recordDate = w2.recordDate + 1  -- w1 是 w2 的后一天
    AND w1.temperature > w2.temperature   -- 当日温度高于前一天

结果说明

10. 计算每台机器的平均进程耗时

题目描述

Activity 表记录机器进程的启动和结束时间，(machine_id, process_id, activity_type) 为复合主键。需计算每台机器的平均进程耗时（总耗时 / 进程数，四舍五入到 3 位小数）。

解题思路

1. 匹配进程的 start/end：用自连接关联 Activity 表，a 表取 end 记录，s 表取 start 记录，按 machine_id 和 process_id 匹配同一进程。
2. 计算耗时：a.timestamp - s.timestamp 为单个进程的耗时。
3. 平均耗时：
   • 总耗时：SUM(a.timestamp - s.timestamp)。
   • 进程数：COUNT(DISTINCT a.process_id)（按机器分组统计进程数）。
   • 平均耗时：ROUND(总耗时 / 进程数, 3)。

完整代码

sql

-- 1. 创建 Activity 表
CREATE TABLE Activity (
    machine_id INT,
    process_id INT,
    activity_type VARCHAR2(10),
    timestamp FLOAT,
    CONSTRAINT pk_activity PRIMARY KEY (machine_id, process_id, activity_type)
);
-- 2. 插入示例数据
INSERT INTO Activity (machine_id, process_id, activity_type, timestamp) 
VALUES (0, 0, 'start', 0.712), (0, 0, 'end', 1.520),
       (0, 1, 'start', 3.140), (0, 1, 'end', 4.120),
       (1, 0, 'start', 0.550), (1, 0, 'end', 1.550),
       (1, 1, 'start', 0.430), (1, 1, 'end', 1.420),
       (2, 0, 'start', 4.100), (2, 0, 'end', 4.512),
       (2, 1, 'start', 2.500), (2, 1, 'end', 5.000);
-- 3. 执行查询
SELECT 
    a.machine_id,
    ROUND(
        SUM(a.timestamp - s.timestamp) / COUNT(DISTINCT a.process_id), 
        3
    ) AS processing_time
FROM Activity a
JOIN Activity s 
    ON a.machine_id = s.machine_id 
    AND a.process_id = s.process_id 
    AND a.activity_type = 'end' 
    AND s.activity_type = 'start'
GROUP BY a.machine_id;

结果说明

总结

本文 10 道题覆盖了 Oracle SQL 的核心考点，关键技巧总结如下：

1. 多表关联：通过 JOIN（内连接）、LEFT JOIN（左连接）、CROSS JOIN（交叉连接）处理不同场景的关联需求。
2. 分组统计：GROUP BY 结合 COUNT、AVG、SUM 实现聚合计算，DISTINCT 用于去重统计。
3. 日期处理：TO_DATE 转换字符串为日期，CONNECT BY 生成连续日期，日期加减实现前后时间对比。
4. 自连接：将单表视为多表，解决 “对比自身数据”（如前一天温度、进程 start/end）的问题。
   建议读者逐题手动执行代码，理解每一步逻辑，逐步提升 SQL 编写能力。

《网络安全从零到精通全套学习大礼包》

96节从入门到精通的全套视频教程免费领取

如果你也想通过学网络安全技术去帮助就业和转行，我可以把我自己亲自录制的96节 从零基础到精通的视频教程以及配套学习资料无偿分享给你。

网络安全学习路线图

想要学习 网络安全，作为新手一定要先按照路线图学习，方向不对，努力白费。对于从来没有接触过网络安全的同学，我帮大家准备了从零基础到精通学习成长路线图以及学习规划。可以说是最科学最系统的学习路线，大家跟着这个路线图学习准没错。

配套实战项目/源码

所有视频教程所涉及的实战项目和项目源码

学习电子书籍

学习网络安全必看的书籍和文章的PDF，市面上网络安全书籍确实太多了，这些是我精选出来的

面试真题/经验

以上资料如何领取？