繼上篇我們 用假想的員工資料做為 SQL 的情境練習 之後，今天我們再來動手寫點 SQL，這次我們換個日常情境，就選…咖啡廳吧！

現在假設我們開了一間小咖啡館，那麼就會有像是「會員資料、飲品菜單、每日訂單、點單明細、以及店員排班」這些表格。把買咖啡的人、煮咖啡的人、還有那杯咖啡本身…都記錄下來後，就又能組合出一堆實用的查詢練習了。

這次我一樣將題目粗分成三種難度，我們可以今天先做幾個簡單的暖暖身，明天再挑戰進階，甚至隔幾個禮拜慢慢回來補完。重點是邊想邊寫，讓每一條 SQL 真的變成自己的直覺。準備好了嗎？那就打開編輯器開始吧！

建立練習環境

首先我們需要先建立練習用的資料表，可以把下面的資料貼到自己習慣的環境裡之後執行。或是也可以直接到我建立好的 SQL Fiddle 環境裡頭練習： https://sqlfiddle.com/sqlite/online-compiler?id=c6941478-bfe2-4dcd-940b-df78e27e031c

新建資料表

在這邊我們需要先建立接下來要用到的一些空白 tables，像是會員、飲料的菜單、簡易的店員資料、班表、訂單內容與明細等等

/* 會員 */
CREATE TABLE customer (
cid       TEXT PRIMARY KEY,
cname     TEXT,
gender    TEXT,
age       INTEGER,
signup_dt TEXT      -- yyyy-mm-dd
);


/* 飲品菜單 */
CREATE TABLE drink (
did     TEXT PRIMARY KEY,
dname   TEXT,
price   REAL,
hot     INTEGER     -- 1=熱飲, 0=冰飲
);


/* 店員 */
CREATE TABLE barista (
bid    TEXT PRIMARY KEY,
bname  TEXT,
hire_dt TEXT
);


/* 排班 */
CREATE TABLE shift (
bid     TEXT,
sdate   TEXT,   -- yyyy-mm-dd
shift   TEXT,   -- Morning / Afternoon / Evening
PRIMARY KEY (bid, sdate, shift),
FOREIGN KEY (bid) REFERENCES barista(bid)
);


/* 訂單主檔 */
CREATE TABLE orders (
oid        TEXT PRIMARY KEY,
cid        TEXT,
bid        TEXT,          -- 當班店員
order_dt   TEXT,          -- yyyy-mm-dd hh:mm
FOREIGN KEY (cid) REFERENCES customer(cid),
FOREIGN KEY (bid) REFERENCES barista(bid)
);



/* 訂單明細 */
CREATE TABLE order_item (
oid     TEXT,
did     TEXT,
qty     INTEGER,
PRIMARY KEY (oid, did),
FOREIGN KEY (oid) REFERENCES orders(oid),
FOREIGN KEY (did) REFERENCES drink(did)
);



插入資料

然後，在這些表格裡填充一些練習用的資料

/* customer */
INSERT INTO customer VALUES
('c001','Amber','F',25,'2023-02-14'),
('c002','Benson','M',31,'2022-11-05'),
('c003','Cathy','F',28,'2024-01-20'),
('c004','Derek','M',35,'2023-08-09'),
('c005','Eva','F',22,'2024-03-12'),
('c006','Felix','M',40,'2022-06-30'),
('c007','Grace','F',27,'2023-12-01'),
('c008','Henry','M',33,'2021-09-17');


/* drink */
INSERT INTO drink VALUES
('d001','Espresso', 90 ,1),
('d002','Americano',100,1),
('d003','Latte'    ,120,1),
('d004','Mocha'    ,130,1),
('d005','Cold Brew',110,0),
('d006','Iced Latte',125,0),
('d007','Matcha'   ,140,1),
('d008','Lemon Tea',95 ,0);


/* barista */
INSERT INTO barista VALUES
('b001','Ivy'   ,'2020-05-01'),
('b002','Jason' ,'2021-03-15'),
('b003','Kelly' ,'2019-11-23'),
('b004','Leo'   ,'2022-07-07'),
('b005','Mia'   ,'2023-04-18');


/* shift (一週示意) */
INSERT INTO shift VALUES
('b001','2025-01-19','Morning'),('b001','2025-01-20','Afternoon'),
('b002','2025-01-19','Afternoon'),('b002','2025-01-21','Morning'),
('b003','2025-01-20','Morning'),('b003','2025-01-22','Evening'),
('b004','2025-01-21','Evening'),('b004','2025-01-23','Morning'),
('b005','2025-01-22','Afternoon'),('b005','2025-01-23','Evening');


/* orders */
INSERT INTO orders VALUES
('o001','c001','b001','2025-01-19 09:15'),
('o002','c002','b001','2025-01-19 09:45'),
('o003','c003','b002','2025-01-19 15:10'),
('o004','c004','b003','2025-01-20 10:05'),
('o005','c001','b003','2025-01-20 10:30'),
('o006','c005','b002','2025-01-21 08:55'),
('o007','c006','b004','2025-01-21 18:20'),
('o008','c007','b004','2025-01-23 09:40'),
('o009','c008','b005','2025-01-23 19:05'),
('o010','c002','b005','2025-01-23 19:25');


/* order_item */
INSERT INTO order_item VALUES
('o001','d003',1),('o001','d001',1),
('o002','d002',2),
('o003','d006',1),('o003','d005',1),
('o004','d004',1),
('o005','d001',2),
('o006','d002',1),('o006','d008',1),
('o007','d005',2),('o007','d007',1),
('o008','d003',1),('o008','d008',1),
('o009','d007',1),
('o010','d006',2),('o010','d002',1);



練習題 (共30題)

這裡我一樣分成三種難度。(題目看起來很多，不用拚一次寫完沒關係，慢慢寫、慢慢想，比較容易內化進去)

基礎難度

1. 將 customer 表前 5 筆資料列出來
2. 查詢所有熱飲 (hot = 1) 的飲品名稱與價格
3. 找出年齡 ≥ 30 歲的會員姓名與年齡
4. 列出 2025-05-19 全部訂單編號與對應會員編號
5. 計算性別為 'M' 的會員人數
6. 使用 LIKE 找出會員姓名以 'C' 開頭者的會員編號與姓名
7. 查詢 2025-01-23 當天的訂單總筆數
8. 查詢 price > 120 的飲品編號與名稱，依價格由高到低排序
9. 列出 order_item 中 qty >= 2 的紀錄 (顯示訂單編號、飲品編號、數量)
10. 找出 shift 表裡排 Evening 班的店員姓名 (不重複)

中等難度

1. 計算各飲品的總銷售杯數
2. 列出每位店員的接單金額 (金額＝qty * price)，並依金額降序
3. 找出會員 c002 累積消費總額
4. 列出沒有被點過的飲品編號與名稱
5. 對每位會員統計最早一次點單日期
6. 找出一天內下 2 筆以上訂單的會員編號與下單日期
7. 查詢平均杯數 > 1 的訂單編號與平均杯數 (提示: AVG(qty))
8. 列出每種飲品的單日最高銷售杯數與日期
9. 使用 GROUP_CONCAT 把同一張訂單中的飲品名稱用逗號串起來顯示 (顯示訂單編號與飲品清單)
10. 找出 2025-01-19 到 2025-01-21 之間，每天營業額最高的店員姓名與金額

進階難度

1. 找出同時點過 Espresso (d001) 與 Latte (d003) 的會員編號與姓名
2. 找出從未在 Evening 班時段下單的會員姓名
3. 使用視窗函數 (Window function)找出各會員最新一筆訂單日期與金額
4. 以 CTE 計算每位店員每日營業額，並找出高於自己平均營業額的日期與金額
5. 將 2025-01-23 當天訂單依下單時間排序，使用 LAG 計算相鄰兩筆訂單間隔分鐘數
6. 餘額思考：若會員每消費 200 元累積 1 點數，計算各會員目前點數 (用子查詢或 CTE 都可以)
7. 找出單筆訂單金額排名前 3 的訂單編號、金額與店員姓名
8. 查詢同時在 2025-01-23 上過早班 (Morning) 又上過晚班 (Evening) 的店員姓名
9. 建立一個 view 叫 hot_seller，內容是總銷售杯數超過全店平均的飲品；再查 view，列出飲品名稱
10. 對 orders 與 order_item 進行集合運算，找出沒有任何明細卻存在於 orders 的訂單編號 (應為空集合，檢查資料完整性)