SQL練習題(持續更新中) 詳情 - MySQL 窩瓜小冬瓜動態日志

測試表創建

-- 1. 部門表（departments）
CREATE TABLE IF NOT EXISTS departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '部門ID，主鍵自增',
    dept_name VARCHAR(50) NOT NULL UNIQUE COMMENT '部門名稱，唯一不可重複',
    location VARCHAR(100) COMMENT '部門所在地',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '記錄創建時間'
) COMMENT '公司部門信息表';

-- 2. 員工表（employees）
CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '員工ID，主鍵自增',
    name VARCHAR(50) NOT NULL COMMENT '員工姓名',
    gender ENUM('男', '女', '未知') DEFAULT '未知' COMMENT '員工性別',
    department VARCHAR(50) COMMENT '所屬部門（關聯departments表的dept_name）',
    hire_date DATE NOT NULL COMMENT '入職日期',
    phone VARCHAR(20) UNIQUE COMMENT '手機號碼，唯一',
    email VARCHAR(100) UNIQUE COMMENT '郵箱，唯一',
    manager_id INT COMMENT '直屬上級ID（自關聯，關聯本表的id）',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '記錄創建時間',
    FOREIGN KEY (department) REFERENCES departments(dept_name) ON UPDATE CASCADE,
    FOREIGN KEY (manager_id) REFERENCES employees(id) ON DELETE SET NULL
) COMMENT '公司員工信息表';

-- 3. 技能表（skills）
CREATE TABLE IF NOT EXISTS skills (
    skill_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '技能ID，主鍵自增',
    skill_name VARCHAR(50) NOT NULL UNIQUE COMMENT '技能名稱，唯一',
    skill_type VARCHAR(30) COMMENT '技能類型（如：編程語言、工具等）',
    description VARCHAR(200) COMMENT '技能描述'
) COMMENT '技能信息表';

-- 4. 員工-技能中間表（employee_skills）
CREATE TABLE IF NOT EXISTS employee_skills (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '記錄ID，主鍵自增',
    employee_id INT NOT NULL COMMENT '員工ID，關聯employees表',
    skill_id INT NOT NULL COMMENT '技能ID，關聯skills表',
    proficiency INT CHECK (proficiency BETWEEN 1 AND 5) COMMENT '熟練程度（1-5，5為最高）',
    learned_date DATE COMMENT '掌握該技能的日期',
    UNIQUE KEY uk_employee_skill (employee_id, skill_id),
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (skill_id) REFERENCES skills(skill_id) ON DELETE CASCADE
) COMMENT '員工與技能的關聯表（多對多）';

-- 5. 薪資記錄表（salary_records）
CREATE TABLE IF NOT EXISTS salary_records (
    record_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '記錄ID，主鍵自增',
    employee_id INT NOT NULL COMMENT '員工ID，關聯員工表',
    basic_salary DECIMAL(10, 2) NOT NULL CHECK (basic_salary >= 0) COMMENT '基本工資',
    bonus DECIMAL(10, 2) DEFAULT 0 CHECK (bonus >= 0) COMMENT '獎金',
    subsidy DECIMAL(10, 2) DEFAULT 0 CHECK (subsidy >= 0) COMMENT '補貼',
    total_salary DECIMAL(10, 2) GENERATED ALWAYS AS (basic_salary + bonus + subsidy) STORED COMMENT '總薪資（自動計算）',
    effective_date DATE NOT NULL COMMENT '生效日期',
    expire_date DATE COMMENT '失效日期（NULL表示當前生效）',
    reason VARCHAR(200) COMMENT '薪資調整原因',
    created_by VARCHAR(50) COMMENT '操作人',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '記錄創建時間',
    CONSTRAINT uk_employee_effective UNIQUE (employee_id, effective_date),
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) COMMENT '員工薪資變動記錄表';

插入數據

-- 先插入部門數據（因為員工表依賴部門表）
INSERT INTO departments (dept_name, location) VALUES
('技術部', '北京'),
('市場部', '上海'),
('人事部', '廣州'),
('財務部', '深圳');

-- 插入員工數據
-- 插入部門數據（使用IGNORE避免重複）
INSERT IGNORE INTO departments (dept_name, location) VALUES
('技術部', '北京海淀區'),
('市場部', '上海浦東新區'),
('人事部', '廣州天河區'),
('財務部', '深圳南山區'),
('運營部', '杭州西湖區');

-- 插入員工數據（包含部門關聯和上級關聯）
INSERT IGNORE INTO employees (id, name, gender, department, hire_date, phone, email, manager_id) VALUES
(1, '張三', '男', '技術部', '2020-01-15', '13800138001', 'zhangsan@example.com', NULL),
(2, '李四', '女', '市場部', '2021-03-20', '13900139002', 'lisi@example.com', NULL),
(3, '王五', '男', '技術部', '2019-11-05', '13700137003', 'wangwu@example.com', 1),
(4, '趙六', '女', '人事部', '2022-05-10', '13600136004', 'zhaoliu@example.com', NULL),
(5, '錢七', '男', '技術部', '2021-09-30', '13500135005', 'qianqi@example.com', 1),
(6, '孫八', '女', '財務部', '2020-07-22', '13400134006', 'sunba@example.com', NULL),
(7, '周九', '男', '市場部', '2022-01-18', '13300133007', 'zhoujiu@example.com', 2),
(8, '吳十', '女', '運營部', '2021-06-05', '13200132008', 'wushi@example.com', NULL),
(9, '鄭十一', '男', '財務部', '2023-02-10', '13100131009', 'zheng11@example.com', 6),
(10, '王十二', '女', '運營部', '2022-09-15', '13001300130', 'wang12@example.com', 8);

-- 插入技能數據
INSERT IGNORE INTO skills (skill_id, skill_name, skill_type, description) VALUES
(1, 'Java', '編程語言', '面向對象的編程語言'),
(2, 'Python', '編程語言', '簡潔易學的腳本語言'),
(3, 'MySQL', '數據庫', '關係型數據庫管理系統'),
(4, 'JavaScript', '編程語言', '前端開發主要語言'),
(5, 'Excel', '辦公軟件', '數據處理與分析工具'),
(6, 'PPT', '辦公軟件', '演示文稿製作工具'),
(7, 'Vue', '前端框架', '漸進式JavaScript框架'),
(8, 'Spring Boot', '後端框架', 'Java開發框架'),
(9, '數據分析', '業務能力', '數據挖掘與分析能力'),
(10, '項目管理', '管理能力', '項目規劃與執行能力');

-- 插入員工-技能關聯數據
INSERT IGNORE INTO employee_skills (employee_id, skill_id, proficiency, learned_date) VALUES
(1, 1, 5, '2018-06-10'),  -- 張三：Java（熟練5）
(1, 3, 4, '2019-01-15'),  -- 張三：MySQL（熟練4）
(1, 8, 5, '2019-05-20'),  -- 張三：Spring Boot（熟練5）
(3, 1, 4, '2019-03-20'),  -- 王五：Java（熟練4）
(3, 2, 3, '2020-05-10'),  -- 王五：Python（熟練3）
(3, 3, 3, '2019-12-05'),  -- 王五：MySQL（熟練3）
(5, 1, 3, '2021-02-28'),  -- 錢七：Java（熟練3）
(5, 4, 2, '2022-01-15'),  -- 錢七：JavaScript（熟練2）
(5, 7, 2, '2022-03-10'),  -- 錢七：Vue（熟練2）
(2, 5, 4, '2020-11-05'),  -- 李四：Excel（熟練4）
(2, 6, 5, '2019-09-30'),  -- 李四：PPT（熟練5）
(2, 9, 4, '2021-01-20'),  -- 李四：數據分析（熟練4）
(4, 5, 5, '2021-07-20'),  -- 趙六：Excel（熟練5）
(4, 10, 3, '2022-08-15'), -- 趙六：項目管理（熟練3）
(6, 3, 4, '2019-05-15'),  -- 孫八：MySQL（熟練4）
(6, 5, 4, '2018-11-10'),  -- 孫八：Excel（熟練4）
(7, 6, 3, '2021-05-10'),  -- 周九：PPT（熟練3）
(7, 9, 2, '2022-03-20'),  -- 周九：數據分析（熟練2）
(8, 10, 4, '2020-08-05'), -- 吳十：項目管理（熟練4）
(10, 9, 3, '2022-11-10'); -- 王十二：數據分析（熟練3）

-- 插入薪資記錄數據（包含初始薪資和調整記錄）
INSERT IGNORE INTO salary_records (record_id, employee_id, basic_salary, bonus, subsidy, effective_date, expire_date, reason, created_by) VALUES
-- 張三的薪資記錄
(1, 1, 7000, 500, 500, '2020-01-15', '2021-12-31', '入職初始薪資', 'admin'),
(2, 1, 8000, 800, 500, '2022-01-01', NULL, '年度調薪', 'admin'),

-- 李四的薪資記錄
(3, 2, 6000, 300, 200, '2021-03-20', '2022-06-30', '入職初始薪資', 'admin'),
(4, 2, 6500, 400, 200, '2022-07-01', NULL, '半年度調薪', 'admin'),

-- 王五的薪資記錄
(5, 3, 8500, 500, 200, '2019-11-05', '2021-05-31', '入職初始薪資', 'admin'),
(6, 3, 9200, 600, 400, '2021-06-01', NULL, '晉升調薪', 'admin'),

-- 趙六的薪資記錄
(7, 4, 5500, 200, 100, '2022-05-10', NULL, '入職初始薪資', 'admin'),

-- 錢七的薪資記錄
(8, 5, 7000, 300, 200, '2021-09-30', '2023-02-28', '入職初始薪資', 'admin'),
(9, 5, 7500, 400, 200, '2023-03-01', NULL, '年度調薪', 'admin'),

-- 孫八的薪資記錄
(10, 6, 7200, 500, 300, '2020-07-22', '2022-12-31', '入職初始薪資', 'admin'),
(11, 6, 7800, 600, 300, '2023-01-01', NULL, '年度調薪', 'admin'),

-- 周九的薪資記錄
(12, 7, 6200, 200, 100, '2022-01-18', '2023-06-30', '入職初始薪資', 'admin'),
(13, 7, 6800, 300, 100, '2023-07-01', NULL, '年度調薪', 'admin'),

-- 吳十的薪資記錄
(14, 8, 6500, 400, 300, '2021-06-05', '2022-11-30', '入職初始薪資', 'admin'),
(15, 8, 7000, 500, 300, '2022-12-01', NULL, '年度調薪', 'admin'),

-- 鄭十一的薪資記錄
(16, 9, 5800, 200, 100, '2023-02-10', NULL, '入職初始薪資', 'admin'),

-- 王十二的薪資記錄
(17, 10, 6000, 300, 200, '2022-09-15', '2023-08-31', '入職初始薪資', 'admin'),
(18, 10, 6300, 300, 200, '2023-09-01', NULL, '年度調薪', 'admin');

一、基礎查詢與條件篩選（單表操作）

題目：查詢所有部門(departments)的部門名稱(dept_name)和所在地(location)，並按部門名稱(dept_name)、升序(ASC)排序。
```
SELECT dept_name, location 
FROM departments 
ORDER BY dept_name ASC;
```
題目：查詢員工表(employees)中部門類型(department)是技術部並且入職時間(hire_date)在2021年及之後的員工，顯示姓名(name)、入職日期(hire_date)和手機號(phone)。
```
SELECT name, hire_date, phone 
FROM employees 
WHERE department = '技術部' 
  AND hire_date >= '2021-01-01';
```
題目：查詢薪資記錄(salary_records)中總薪資（total_salary）在8000-10000之間的記錄，顯示員工ID(employee_id)、總薪資(total_salary)和生效日期(effective_date),並按生效日期(effective_date)、降序(desc)排序。
```
SELECT employee_id,total_salary,effective_date
FROM salary_records
WHERE total_salary BETWEEN 8000 AND 10000
ORDER BY effective_date;
```
題目：查詢技能表(skills)中技能類型(skill_type)為編程語言的記錄，顯示技能ID(skill_id)、技能名稱(skill_name)和技能描述(description)。
```
SELECT skill_id,skill_name,description
FROM skills
WHERE skill_type='編程語言'；
```
題目：查詢員工-技能中間表(employee_skills)中技能熟練度(proficiency)為5並且掌握技能日期(learned_date)在2020年的記錄，顯示員工ID(employee_id)、技能ID(skill_id)和掌握技能日期(learned_date)，並按掌握日期(learned_date)進行降序(ASC)進行排序。
```
SELECT employee_id,skill_id,learned_date
FROM employee_skills
WHERE proficiency =5
  AND learned_date >= '2019-01-01'
ORDER BY learned_date ASC;
```

二、聚合函數與分組查詢

題目：統計每個部門(departments)的員工人數，顯示部門名稱和員工人數，過濾人大於等於3的部門。
```
SELECT department AS 部門名稱,
       COUNT(*) AS 員工人數
FROM employees
GROUP BY department
HAVING COUNT(*) >= 3;
```

題目：計算各部門(department)當前生效薪資(total_salary)的平均值（取expire_date IS NULL的記錄），顯示部門名稱(department)和平均薪資（保留2位小數）。

SELECT e.department AS 部門名稱,
       ROUND(AVG(sr.total_salary), 2) AS 平均薪資
FROM employees e
JOIN salary_records sr ON e.id = sr.employee_id
WHERE sr.expire_date IS NULL
GROUP BY e.department;

題目：統計每種技能的掌握人數，按人數從多到少進行排序，包含無人掌握的技能（顯示0）。

SELECT 
      s.skill_name AS 技能名稱,
      COUNT(es.employee_id) AS 掌握人數
FROM skills s
LEFT JOIN employee_skills es ON s.skill_id = es.skill_id
GROUP BY s.skill_name
ORDER BY 掌握人數 desc;

三、連接查詢（多表關聯）

題目：查詢所有員工的姓名、所屬部門名稱及部門所在地，包括未分配部門的員工（若有）。

SELECT 
  e.name AS 員工姓名,
  d.dept_name AS 部門名稱,
  d.location AS 部門所在地
FROM employees e
LEFT JOIN departments d ON e.department = d.dept_name;

題目：查詢掌握“Java”技能的員工姓名、部門及熟練程度，要求熟練程度≥4。

SELECT 
  e.name AS 員工姓名,
  e.department AS 部門,
  es.proficiency AS 熟練程度
FROM employees e
JOIN employee_skills es ON e.id = es.employee_id
JOIN skills s ON es.skill_id = s.skill_id
WHERE s.skill_name = 'Java' 
  AND es.proficiency >= 4;

題目：查詢2023年有薪資調整的員工姓名及調整前後的總薪資（需顯示“調整前薪資”和“調整後薪資”）。

SELECT 
  e.name AS 員工姓名,
  prev.total_salary AS 調整前薪資,
  curr.total_salary AS 調整後薪資,
  curr.effective_date AS 調整日期
FROM employees e
JOIN salary_records curr ON e.id = curr.employee_id
JOIN salary_records prev ON e.id = prev.employee_id 
  AND prev.expire_date = curr.effective_date - INTERVAL 1 DAY
WHERE YEAR(curr.effective_date) = 2023;

四、子查詢與嵌套查詢

題目：查詢薪資高於本部門平均薪資的員工，顯示姓名、部門和當前總薪資。

SELECT 
  e.name AS 員工姓名,
  e.department AS 部門,
  sr.total_salary AS 當前總薪資
FROM employees e
JOIN salary_records sr ON e.id = sr.employee_id
WHERE sr.expire_date IS NULL
  AND sr.total_salary > (
    SELECT AVG(sr2.total_salary)
    FROM employees e2
    JOIN salary_records sr2 ON e2.id = sr2.employee_id
    WHERE sr2.expire_date IS NULL
      AND e2.department = e.department
  );

題目：找出同時掌握“Java”和“MySQL”兩種技能的員工姓名。

SELECT e.name AS 員工姓名
FROM employees e
WHERE EXISTS (
  SELECT 1 
  FROM employee_skills es 
  JOIN skills s ON es.skill_id = s.skill_id
  WHERE es.employee_id = e.id AND s.skill_name = 'Java'
)
AND EXISTS (
  SELECT 1 
  FROM employee_skills es 
  JOIN skills s ON es.skill_id = s.skill_id
  WHERE es.employee_id = e.id AND s.skill_name = 'MySQL'
);

題目：查詢各部門中薪資最高的員工，顯示部門名稱、員工姓名和最高薪資。

SELECT 
  dept_name AS 部門名稱,
  name AS 員工姓名,
  max_salary AS 最高薪資
FROM (
  SELECT 
    e.department AS dept_name,
    e.name,
    sr.total_salary,
    MAX(sr.total_salary) OVER (PARTITION BY e.department) AS max_salary
  FROM employees e
  JOIN salary_records sr ON e.id = sr.employee_id
  WHERE sr.expire_date IS NULL
) AS sub
WHERE total_salary = max_salary
ORDER BY dept_name;

五、窗口函數與高級查詢

題目：給每個部門的員工按當前薪資從高到低排名，顯示姓名、部門、薪資和排名（同一部門內排名）。

SELECT 
  e.name AS 員工姓名,
  e.department AS 部門,
  sr.total_salary AS 薪資,
  RANK() OVER (PARTITION BY e.department ORDER BY sr.total_salary DESC) AS 排名
FROM employees e
JOIN salary_records sr ON e.id = sr.employee_id
WHERE sr.expire_date IS NULL
ORDER BY e.department, 排名;

題目：計算每個員工的薪資較上一次調整的漲幅比例，顯示姓名、調整日期和漲幅（保留1位小數）。

SELECT 
  e.name AS 員工姓名,
  curr.effective_date AS 調整日期,
  curr.total_salary AS 當前薪資,
  prev.total_salary AS 上次薪資,
  ROUND(
    (curr.total_salary - prev.total_salary) / prev.total_salary * 100, 
    1
  ) AS 漲幅百分比
FROM employees e
JOIN salary_records curr ON e.id = curr.employee_id
JOIN salary_records prev ON e.id = prev.employee_id
  AND prev.expire_date = curr.effective_date - INTERVAL 1 DAY
ORDER BY e.name, 調整日期;

題目：統計各部門中不同技能類型的員工數量，例如“技術部”的“編程語言”技能有多少人掌握。

SELECT 
  e.department AS 部門,
  s.skill_type AS 技能類型,
  COUNT(DISTINCT e.id) AS 員工數量
FROM employees e
LEFT JOIN employee_skills es ON e.id = es.employee_id
LEFT JOIN skills s ON es.skill_id = s.skill_id
GROUP BY e.department, s.skill_type
ORDER BY e.department, s.skill_type;

六、綜合場景查詢

題目：查詢“張三”的所有上級（包含多級上級），顯示上級姓名和職位關係（如“直接上級”、“間接上級”）。

WITH RECURSIVE manager_chain AS (
  -- 起始節點：張三的直接上級
  SELECT 
    m.id AS manager_id,
    m.name AS manager_name,
    1 AS level,
    '直接上級' AS relation
  FROM employees e
  LEFT JOIN employees m ON e.manager_id = m.id
  WHERE e.name = '張三'

  UNION ALL

  -- 遞歸查詢：上級的上級
  SELECT 
    m2.id AS manager_id,
    m2.name AS manager_name,
    mc.level + 1 AS level,
    CONCAT('間接上級（', mc.level + 1, '級）') AS relation
  FROM manager_chain mc
  JOIN employees m2 ON mc.manager_id = m2.manager_id
  WHERE m2.id IS NOT NULL
)
SELECT manager_name AS 上級姓名, relation AS 職位關係
FROM manager_chain;

題目：分析技能掌握情況與薪資的關係：計算掌握3種及以上技能的員工平均薪資，與掌握不足3種技能的員工平均薪資對比。

SELECT 
  CASE 
    WHEN skill_count >= 3 THEN '掌握3種及以上技能'
    ELSE '掌握不足3種技能'
  END AS 技能掌握情況,
  ROUND(AVG(total_salary), 2) AS 平均薪資
FROM (
  -- 子查詢：統計每個員工的技能數量
  SELECT 
    e.id,
    e.name,
    COUNT(DISTINCT es.skill_id) AS skill_count,
    sr.total_salary
  FROM employees e
  LEFT JOIN employee_skills es ON e.id = es.employee_id
  JOIN salary_records sr ON e.id = sr.employee_id
  WHERE sr.expire_date IS NULL
  GROUP BY e.id, e.name, sr.total_salary
) AS skill_stats
GROUP BY 技能掌握情況;

題目：查詢各部門近2年（2022-2023）薪資調整總次數，並計算調整次數最多的部門。

SELECT 
  部門,
  調整次數
FROM (
  SELECT 
    e.department AS 部門,
    COUNT(sr.record_id) AS 調整次數,
    RANK() OVER (ORDER BY COUNT(sr.record_id) DESC) AS rnk
  FROM employees e
  JOIN salary_records sr ON e.id = sr.employee_id
  WHERE YEAR(sr.effective_date) BETWEEN 2022 AND 2023
  GROUP BY e.department
) AS dept_adjust
WHERE rnk = 1;

窩瓜小冬瓜動態日志

@xyjzfx

標簽

MySQL (192)

動態

SQL練習題(持續更新中) - 動態詳情

一、基礎查詢與條件篩選（單表操作）

二、聚合函數與分組查詢

三、連接查詢（多表關聯）

四、子查詢與嵌套查詢

五、窗口函數與高級查詢

六、綜合場景查詢

Add a new 評論

窩瓜小冬瓜 動態日志

@xyjzfx

標簽

MySQL (192)

動態

SQL練習題(持續更新中) - 動態 詳情

一、基礎查詢與條件篩選（單表操作）

二、聚合函數與分組查詢

三、連接查詢（多表關聯）

四、子查詢與嵌套查詢

五、窗口函數與高級查詢

六、綜合場景查詢

Add a new 評論

窩瓜小冬瓜動態日志

SQL練習題(持續更新中) - 動態詳情