今天我們就來動手寫點 SQL，好嗎？
過去帶課時，我發現學生在學完 SQL 的概念後，如果能夠立刻練習對理解的效果是最佳的。於是我選了幾種生活中常用的、會利用到資料庫的一些情境來做為練習的主題，像是查詢員工工時、計算薪水、追專案進度等等都是在辦公室裡很容易出現的日常情境。
題目分為三個難度等級，不勉強一定要全部一口氣做完，如果遇到不會的，可以分成好幾天甚至好幾個禮拜慢慢想、慢慢寫出來，才能真正內化成自己的能力喔，準備好了嗎？打開編輯器，從第一題開始，試著讓資料庫乖乖回應你的每一道指令吧！
建立好練習環境
將下面練習用的資料表，貼進自己習慣的環境裡練習。或是如果只是想要簡單在線上練習，也可以直接用我在 SQL Fiddle 建立好的練習範例 (這裡我使用的是 SQLite)：https://sqlfiddle.com/sqlite/online-compiler?id=109bad62-11f6-4ba0-8332-26c2c56fbe56
建立資料表
首先我們需要先虛構一組簡單的辦公室員工資料表，包含部門、員工、專案、每個員工在專案上的工時、以及酬勞等等
/* 部門 */
CREATE TABLE department (
dept_id TEXT PRIMARY KEY,
dname TEXT
);
/* 員工 */
CREATE TABLE employee (
eno TEXT PRIMARY KEY,
ename TEXT,
gender TEXT,
age INTEGER,
dept_id TEXT,
hire_date TEXT, -- yyyy-mm-dd
FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);
/* 專案 */
CREATE TABLE project (
pno TEXT PRIMARY KEY,
pname TEXT,
dept_id TEXT,
budget REAL,
FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);
/* 員工專案工時 */
CREATE TABLE assignment (
eno TEXT,
pno TEXT,
hours INTEGER,
PRIMARY KEY (eno, pno),
FOREIGN KEY (eno) REFERENCES employee(eno),
FOREIGN KEY (pno) REFERENCES project(pno)
);
/* 薪資（月薪） */
CREATE TABLE salary (
eno TEXT,
month TEXT, -- yyyy-mm
amount REAL,
PRIMARY KEY (eno, month),
FOREIGN KEY (eno) REFERENCES employee(eno)
);
插入範例資料
接著，在每張表裡面填入一些虛構的練習用資料
/* department */
INSERT INTO department VALUES
('d001','Research'),('d002','Marketing'),
('d003','HR'),('d004','Finance');
/* employee */
INSERT INTO employee VALUES
('e001','Alice' ,'F',29,'d001','2019-03-15'),
('e002','Bob' ,'M',35,'d002','2017-07-20'),
('e003','Charlie','M',28,'d002','2021-01-05'),
('e004','Diana' ,'F',42,'d001','2010-11-11'),
('e005','Evan' ,'M',31,'d003','2018-04-23'),
('e006','Fiona' ,'F',26,'d004','2022-06-01'),
('e007','George' ,'M',39,'d004','2015-09-09'),
('e008','Helen' ,'F',45,'d003','2008-12-30'),
('e009','Ivan' ,'M',27,'d001','2020-08-18'),
('e010','Julia' ,'F',33,'d002','2016-02-14'),
('e011','Kevin' ,'M',30,'d003','2019-10-10'),
('e012','Laura' ,'F',24,'d001','2023-01-10');
/* project */
INSERT INTO project VALUES
('p001','AI Engine' ,'d001',200000),
('p002','Ad Campaign' ,'d002',150000),
('p003','Benefit Overhaul','d003', 80000),
('p004','ERP Migration' ,'d004',120000),
('p005','Mobile App' ,'d001',180000),
('p006','Market Research','d002', 60000);
/* assignment */
INSERT INTO assignment VALUES
('e001','p001',320),('e001','p005',120),
('e002','p002',400),('e002','p006', 80),
('e003','p002',200),('e003','p006',150),
('e004','p001',250),('e004','p004', 60),
('e005','p003',180),
('e006','p004',210),('e007','p004',130),
('e008','p003',200),
('e009','p001',150),
('e010','p002',300),
('e011','p003',120),
('e012','p005', 60);
/* salary (2025-01) */
INSERT INTO salary VALUES
('e001','2025-01',7000), ('e002','2025-01',7500),
('e003','2025-01',5200), ('e004','2025-01',9000),
('e005','2025-01',6200), ('e006','2025-01',4800),
('e007','2025-01',8800), ('e008','2025-01',9500),
('e009','2025-01',5100), ('e010','2025-01',7000),
('e011','2025-01',6000), ('e012','2025-01',4500);
練習題 (共30題)
這裡我分成三種難度，每種難度都有 10 小題
基礎難度
- 列出 employee 表當中的前 5 筆資料
- 查詢 department 的所有部門名稱
- 查詢年齡 > 30 的員工姓名有哪些、以及他們的年齡
- 查詢 assignment 中，p001 專案的員工編號以及花費的工時
- 統計性別為 'F' 的員工人數
- 使用 LIKE 找出員工姓名以 'A' 開頭的員工編號與姓名
- 查詢 2025-01 月的平均薪資
- 列出屬於 Marketing (d002) 部門的全部員工姓名與聘用日期，並且依日期由舊到新排序
- 查詢 budget > 100000 的專案編號與名稱
- 列出 assignment 中 hours > 300 的紀錄，並依照工時由高至低排序
中等難度
- 計算各個部門所屬員工的平均年齡
- 查詢每個專案的總花費工時
- 查出員工 e002 參與的專案數量有幾個
- 找出 budget 最高的專案名稱及其部門名稱
- 列出還沒有被指派任何員工的專案編號、以及專案名稱
- 找出參與兩個以上專案的員工編號、姓名，以及負責專案數量
- 查詢薪資高於公司平均的員工編號、姓名與薪資
- 列出每個部門的員工總數，並依人數由多到少排序
- 將每位員工參與的專案名稱用逗號串聯之後顯示出來 (可用 GROUP_CONCAT)
- 找出最早聘用的三位員工姓名及聘用日期
進階難度
- 找出同時參與 p001 與 p005 這兩個專案的員工編號與姓名
- 找出沒有參與任何 Research 部門專案的員工姓名
- 使用視窗函數 (Window function) 找出各部門當中薪資最高的員工，以及他的薪資
- 列出工時高於部門平均工時的員工編號、姓名與工時
- 以 2025-01-01 為基準，計算每位員工距離入職滿 5 年還差多少天
- 使用 CTE 計算各專案中員工工時的百分比，列出員工編號、專案編號、百分比，並依百分比降序來排序
- 查詢所有部門平均薪資與公司平均薪資的差額 (可以是負值)
- 找出與員工 e001 一起參與過至少 1 個專案的其他員工姓名，並顯示共同專案數量
- 查詢 assignment 中，每位員工最新一次參與的專案 (依插入順序視為最新)，並只顯示出員工編號、專案編號、工時
- 建立一個名為 high_budget_project 的View，包含所有 budget > 公司專案平均的專案，再查詢這個view當中的專案總數
_5.jpg)
