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

Oracle SQL 十道经典练习题附完整代码 解题思路在数据库学习和面试中SQL 查询是核心技能之一。本文基于 Oracle 数据库整理了 10 道经典 SQL 练习题涵盖表创建、数据插入、多表关联、分组统计、自连接等高频考点每道题均提供详细解题思路、完整可运行代码及结果说明适合初学者巩固基础也适合进阶者查漏补缺。1. 统计经理的下属人数与平均年龄题目描述现有Employees表存储员工 ID、姓名、汇报对象 ID 及年龄。需返回至少有 1 名下属的经理信息经理 ID、姓名、直接下属人数、下属平均年龄四舍五入到整数结果按employee_id排序。列名类型说明employee/_idint员工唯一 ID主键namevarchar员工姓名reports/_toint汇报对象 ID经理 IDageint员工年龄解题思路表角色拆分将Employees表视为两张表 ——m经理表和e下属表。关联条件通过m.employee_id e.reports_to关联经理与下属确保只统计有下属的经理。统计计算COUNT(e.employee_id)统计每个经理的直接下属人数排除NULL。ROUND(AVG(e.age))计算下属平均年龄并四舍五入。分组排序按经理的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 排序结果说明employee/_idnamereports/_countaverage/_age9Hercy239经理 HercyID9有 2 名下属Alice 和 Bob下属平均年龄(4136)/238.5四舍五入后为 39。2. 查找员工的直属部门题目描述Employee表存储员工与部门的关联关系需找出每个员工的直属部门若员工明确标记primary_flagY则该部门为直属部门。若员工仅属于 1 个部门无论primary_flag为N则该部门默认为直属部门。| 列名 | 类型 | 说明 || — | — | — || employee/_id | int | 员工 ID主键 || department/_id | int | 部门 ID主键 || primary/_flag | varchar | 直属标记Y/N |解题思路筛选逻辑拆分条件 1primary_flagY明确指定直属部门。条件 2primary_flagN且员工仅属于 1 个部门通过子查询统计部门数量。子查询辅助用(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 );结果说明employee/_iddepartment/_id112133433. 统计每位老师教授的科目种类数题目描述Teacher表存储教师、科目与部门的关联(subject_id, dept_id)为复合主键。需统计每位老师教授的不同科目种类数同一科目跨部门仍算 1 种。列名类型说明teacher/_idint教师 IDsubject/_idint科目 ID复合主键dept/_idint部门 ID复合主键解题思路去重统计同一教师的同一科目subject_id相同无论跨多少部门仅算 1 种需用COUNT(DISTINCT subject_id)去重。分组依据按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;结果说明teacher/_idcnt1224教师 1 教授科目 2 和 3共 2 种教师 2 教授科目 1-4共 4 种。4. 统计近 30 天每日活跃用户数题目描述Activity表记录用户社交媒体活动需统计截至 2019-07-27含近 30 天的每日活跃用户数当天有活动即算活跃不展示活跃数为 0 的日期。列名类型说明user/_idint用户 IDsession/_idint会话 IDactivity/_datedate活动日期activity/_typevarchar活动类型如open_session解题思路生成日期范围用 Oracle 特有CONNECT BY生成 2019-06-28 至 2019-07-27 的连续日期近 30 天。关联活动数据用LEFT JOIN关联日期范围与Activity表确保每个日期都能匹配活动数据。筛选有效数据用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; -- 仅展示有活跃用户的日期结果说明dayactive/_users2019-07-2022019-07-2125. 查找非无聊且 ID 为奇数的影片题目描述cinema表存储电影信息需找出描述非 boring且ID 为奇数的影片结果按rating降序排列。列名类型说明idint影片 ID主键movievarchar影片名称descriptionvarchar影片描述ratingfloat影片评分0-10解题思路条件筛选MOD(id, 2) 1用 OracleMOD函数判断 ID 是否为奇数奇数除以 2 余数为 1。description ! boring排除描述为 boring 的影片。排序按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; -- 按评分降序结果说明idmoviedescriptionrating5House cardInteresting9.11Wargreat 3D8.96. 计算产品的平均售价题目描述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 | 销售数量 |解题思路关联表按product_id关联Prices和UnitsSold确保产品匹配。按purchase_date BETWEEN start_date AND end_date关联销售日期与价格有效期确保价格对应。计算逻辑总销售额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;结果说明product/_idaverage/_price16.96216.967. 报告奖金少于 1000 的员工题目描述Employee表存储员工 ID、姓名、上级 ID 及工资。Bonus表存储员工 ID 及奖金empId为外键关联Employee.empId。需报告奖金少于 1000的员工姓名和奖金无奖金则显示NULL。解题思路保留所有员工用LEFT JOIN关联Employee和Bonus确保即使员工无奖金Bonus表无记录也能被保留。筛选条件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 或无奖金结果说明namebonusBradNULLJohnNULLDan5008. 统计每个学生每门科目的考试次数题目描述Students表存储学生 ID 和姓名。Subjects表存储科目名称。Examinations表存储学生参加科目的考试记录可能重复。需统计每个学生每门科目的考试次数无考试则为 0结果按student_id和subject_name排序。解题思路生成全量组合用CROSS JOIN关联Students和Subjects生成所有 “学生 - 科目” 组合确保无遗漏。关联考试记录用LEFT JOIN关联组合与Examinations确保无考试记录的组合也能保留次数为 0。统计次数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;结果说明student/_idstudent/_namesubject/_nameattended/_exams1AliceMath31AlicePhysics21AliceProgramming12BobMath12BobPhysics0…………9. 找出温度高于前一天的日期 ID题目描述Weather表存储每日温度需找出温度高于前一天的日期对应的id无顺序要求。列名类型说明idint记录 ID主键recordDatedate记录日期无重复temperatureint当日温度解题思路自连接将Weather表视为两张表 ——w1当日和w2前一天。关联条件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 -- 当日温度高于前一天结果说明id2410. 计算每台机器的平均进程耗时题目描述Activity表记录机器进程的启动和结束时间(machine_id, process_id, activity_type)为复合主键。需计算每台机器的平均进程耗时总耗时 / 进程数四舍五入到 3 位小数。列名类型说明machine/_idint机器 IDprocess/_idint进程 IDactivity/_typevarchar活动类型start/endtimestampfloat时间戳秒解题思路匹配进程的 start/end用自连接关联Activity表a表取end记录s表取start记录按machine_id和process_id匹配同一进程。计算耗时a.timestamp - s.timestamp为单个进程的耗时。平均耗时总耗时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;结果说明machine/_idprocessing/_time00.89410.99521.456总结本文 10 道题覆盖了 Oracle SQL 的核心考点关键技巧总结如下多表关联通过JOIN内连接、LEFT JOIN左连接、CROSS JOIN交叉连接处理不同场景的关联需求。分组统计GROUP BY结合COUNT、AVG、SUM实现聚合计算DISTINCT用于去重统计。日期处理TO_DATE转换字符串为日期CONNECT BY生成连续日期日期加减实现前后时间对比。自连接将单表视为多表解决 “对比自身数据”如前一天温度、进程 start/end的问题。建议读者逐题手动执行代码理解每一步逻辑逐步提升 SQL 编写能力。《网络安全从零到精通全套学习大礼包》96节从入门到精通的全套视频教程免费领取如果你也想通过学网络安全技术去帮助就业和转行我可以把我自己亲自录制的96节 从零基础到精通的视频教程以及配套学习资料无偿分享给你。网络安全学习路线图想要学习 网络安全作为新手一定要先按照路线图学习方向不对努力白费。对于从来没有接触过网络安全的同学我帮大家准备了从零基础到精通学习成长路线图以及学习规划。可以说是最科学最系统的学习路线大家跟着这个路线图学习准没错。配套实战项目/源码所有视频教程所涉及的实战项目和项目源码学习电子书籍学习网络安全必看的书籍和文章的PDF市面上网络安全书籍确实太多了这些是我精选出来的面试真题/经验以上资料如何领取