在前兩篇文章裡，用 模擬的辦公室 和 咖啡廳營運資料 來練習 SQL 語法之後，現在我們應該已經對查詢資料庫的思維，有比較直覺的認識了。那今天我們就再練習一組題目，讓腦中的那些 SQL 語法慢慢沉到直覺裡吧。
這次我們用叫車服務app當作例子，把乘客、司機，還有其他與每趟車程有關的資訊都寫進資料庫後，就可以組合一套想像中的簡易查詢練習。
練習的環境
把以下資料貼進平日習慣的環境中練習，或是到我預先建立好的 SQL Fiddle 線上環境： https://sqlfiddle.com/sqlite/online-compiler?id=6819b071-c57e-4365-a8f3-332f0eba8f47
新建資料表
首先先建立這次所需要的空白資料表，比如司機、車輛、乘客、行程、還有付款紀錄、評價、以及優惠碼…等
/* 司機 */
CREATE TABLE driver (
did TEXT PRIMARY KEY,
dname TEXT,
rating REAL, -- 平均星等
city TEXT
);
/* 車輛 (一位司機一台車) */
CREATE TABLE vehicle (
vid TEXT PRIMARY KEY,
did TEXT UNIQUE,
make TEXT,
model TEXT,
year INTEGER,
capacity INTEGER,
FOREIGN KEY (did) REFERENCES driver(did)
);
/* 乘客 */
CREATE TABLE rider (
rid TEXT PRIMARY KEY,
rname TEXT,
signup_dt TEXT, -- yyyy-mm-dd
city TEXT
);
/* 行程 */
CREATE TABLE trip (
tid TEXT PRIMARY KEY,
rid TEXT,
did TEXT,
pickup_time TEXT, -- yyyy-mm-dd hh:mm
dropoff_time TEXT, -- yyyy-mm-dd hh:mm
distance_km REAL,
fare REAL,
promo_code TEXT,
FOREIGN KEY (rid) REFERENCES rider(rid),
FOREIGN KEY (did) REFERENCES driver(did)
);
/* 付款 */
CREATE TABLE payment (
pid TEXT PRIMARY KEY,
tid TEXT,
paid_at TEXT, -- yyyy-mm-dd hh:mm
method TEXT, -- Card / Cash
status TEXT, -- Paid / Failed
amount REAL,
FOREIGN KEY (tid) REFERENCES trip(tid)
);
/* 評價 (乘客對司機打分) */
CREATE TABLE feedback (
fid TEXT PRIMARY KEY,
tid TEXT UNIQUE,
stars INTEGER, -- 1~5
comment TEXT,
FOREIGN KEY (tid) REFERENCES trip(tid)
);
/* 優惠碼 */
CREATE TABLE promo (
code TEXT PRIMARY KEY,
description TEXT,
discount_pct INTEGER,
start_dt TEXT,
end_dt TEXT
);
填充模擬的資料
有了空白資料表後，將接下來練習要用的模擬資料給加進去
/* driver */
INSERT INTO driver VALUES
('d001','Alice' ,4.90,'Taipei'),
('d002','Brian' ,4.70,'Taipei'),
('d003','Cathy' ,4.30,'Taichung'),
('d004','David' ,4.80,'Kaohsiung'),
('d005','Ethan' ,4.10,'Taipei'),
('d006','Flora' ,4.95,'Taichung');
/* vehicle */
INSERT INTO vehicle VALUES
('v001','d001','Toyota' ,'Altis' ,2021,4),
('v002','d002','Honda' ,'Civic' ,2019,4),
('v003','d003','Nissan' ,'Leaf' ,2020,4),
('v004','d004','Tesla' ,'Model 3',2022,4),
('v005','d005','Hyundai','Tucson',2016,5),
('v006','d006','Mazda' ,'CX-5' ,2023,4);
/* rider */
INSERT INTO rider VALUES
('r001','Gina' , '2024-01-12','Taipei'),
('r002','Henry','2023-11-03','Taipei'),
('r003','Irene','2025-02-01','Taichung'),
('r004','Jack' ,'2022-08-25','Kaohsiung'),
('r005','Kelly','2024-03-05','Taipei'),
('r006','Leo' ,'2023-06-14','Taichung'),
('r007','Mia' ,'2024-01-09','Taipei'),
('r008','Nick' ,'2023-05-01','Kaohsiung');
/* promo */
INSERT INTO promo VALUES
('SAVE10','10% off any ride',10,'2025-01-15','2025-02-15'),
('MORNING5','5% off before 10AM',5,'2025-01-01','2025-12-31');
/* trip */
INSERT INTO trip VALUES
('t101','r001','d001','2025-02-18 09:10','2025-02-18 09:40',12.5,300,'MORNING5'),
('t102','r002','d001','2025-02-18 10:20','2025-02-18 10:55',15.0,360,NULL),
('t103','r003','d002','2025-02-18 15:05','2025-02-18 15:35',14.2,340,NULL),
('t104','r001','d002','2025-02-19 08:05','2025-02-19 08:25', 8.8,220,'MORNING5'),
('t105','r004','d003','2025-02-19 09:50','2025-02-19 10:30',22.0,480,'SAVE10'),
('t106','r005','d002','2025-02-19 16:15','2025-02-19 16:45',11.7,280,NULL),
('t107','r006','d004','2025-02-20 18:20','2025-02-20 18:55',18.3,420,NULL),
('t108','r007','d004','2025-02-20 09:40','2025-02-20 10:05', 9.1,230,'MORNING5'),
('t109','r008','d005','2025-02-20 19:05','2025-02-20 19:45',20.4,500,NULL),
('t110','r002','d005','2025-02-21 14:25','2025-02-21 14:55',12.0,300,'SAVE10'),
('t111','r003','d006','2025-02-21 11:10','2025-02-21 11:40',13.3,310,NULL),
('t112','r001','d006','2025-02-22 08:30','2025-02-22 08:55',10.5,250,'MORNING5'),
('t113','r005','d001','2025-02-22 21:15','2025-02-22 21:45',12.8,320,NULL),
('t114','r006','d003','2025-02-22 07:50','2025-02-22 08:20',14.0,330,'MORNING5'),
('t115','r007','d002','2025-02-23 09:05','2025-02-23 09:35',13.6,330,'MORNING5');
/* payment */
INSERT INTO payment VALUES
('p901','t101','2025-02-18 09:41','Card','Paid',285),
('p902','t102','2025-02-18 10:56','Card','Paid',360),
('p903','t103','2025-02-18 15:36','Cash','Paid',340),
('p904','t104','2025-02-19 08:26','Card','Paid',209),
('p905','t105','2025-02-19 10:31','Card','Paid',432),
('p906','t106','2025-02-19 16:46','Cash','Paid',280),
('p907','t107','2025-02-20 18:56','Card','Paid',420),
('p908','t108','2025-02-20 10:06','Card','Paid',219),
('p909','t109','2025-02-20 19:46','Cash','Failed',0),
('p910','t110','2025-02-21 14:56','Card','Paid',270),
('p911','t111','2025-02-21 11:41','Card','Paid',310),
('p912','t112','2025-02-22 08:56','Card','Paid',238),
('p913','t113','2025-02-22 21:46','Cash','Paid',320),
('p914','t114','2025-02-22 08:21','Card','Paid',314),
('p915','t115','2025-02-23 09:36','Card','Paid',314);
/* feedback */
INSERT INTO feedback VALUES
('f001','t101',5,'Smooth ride'),
('f002','t103',4,'Good driver'),
('f003','t105',3,'Car not very clean'),
('f004','t107',5,'Excellent'),
('f005','t110',4,'On time'),
('f006','t111',5,'Great'),
('f007','t112',4,'Nice chat'),
('f008','t114',5,'Early pickup');
練習題 (總共30題)
一樣是三種難度，如果已經做過我們 第一篇 跟 第二篇 的練習，應該可以發現這次寫起來已經漸漸順手起來啦。如果還是覺得寫起來卡卡的、不太確定也沒關係，還是可以試著慢慢寫，讓寫 SQL 查詢這件事情的思維能慢慢變成肌肉記憶。
基礎難度
- 列出 rider 表當中的前 5 筆資料
- 找出所有 rating < 4.5 的司機姓名與評分
- 找出 distance_km > 10 的行程編號與車資
- 統計 capacity >= 5 的車輛數量
- 使用 LIKE 找出司機姓名以 'A' 開頭的司機編號與姓名
- 計算 2025-02-19 這天當中，所有行程的平均車資
- 列出所有司機服務的城市 (不重複)
- 查詢使用優惠碼 (promo_code IS NOT NULL) 的行程編號，以及使用了什麼優惠碼
- 依照 distance_km 降序列出最遠的 3 趟行程
- 統計付款狀態為 'Paid' 的各付款方式 (method) 行程筆數
中等難度
- 計算各城市司機的平均評分
- 列出每位乘客的總車資 (來自 trip.fare)
- 找出行程數超過 2 趟的司機編號與行程數量
- 找出車資高於自己平均車資的行程編號、乘客編號與車資
- 列出車齡 > 5 年的車輛編號、品牌與年份
- 依優惠碼分組，計算各優惠碼為乘客省下的總折扣金額 (提示：fare - amount)
- 對每位司機列出最長距離的行程公里數
- 找出自 2025-02-20 起就沒有再搭車的乘客姓名
- 列出 2025-02-18～2025-02-20 每天的行程總數
- 使用 GROUP_CONCAT 把同一位司機的行程編號串成一欄顯示 (顯示司機編號與行程列表)
進階難度
- 找出搭過至少 3 位不同司機的乘客編號與姓名
- 使用視窗函數，對每位乘客將行程依車資降序排名，顯示乘客編號、行程編號、車資與排名
- 以 CTE 先計算各司機每日收入，再列出收入高於自己平均日收入的日期與金額
- 計算公司整體司機平均評分，列出低於此平均的司機編號、姓名與評分
- 依城市找出行程數最多的司機 (顯示城市、司機姓名、行程數)
- 計算每趟行程的行駛時間 (分鐘)，並列出 tid, distance_km, 行駛時間
- 找出 payment.amount <> trip.fare 的行程編號與差額
- 找出同一對司機-乘客組合出現 2 次以上的行程數與最後一次行程日期
- 建立一個 VIEW 叫做 high_value_trip，內容是 fare 高於全公司平均車資的行程；再查詢此 view 的行程總數
- 使用集合運算找出尚未搭乘任何行程的乘客編號與姓名 (應為空集合或顯示結果)
