Dự án cuối khoá và bộ câu hỏi phỏng vấn SQL
Dự án tổng hợp trên ShopViet: mở rộng schema, 8 câu hỏi nghiệp vụ từ dễ đến khó, tối ưu truy vấn, thiết kế đặt hàng không bán vượt kho, bộ kiểm tra chất lượng dữ liệu — cùng 40 câu hỏi phỏng vấn SQL.
Kiến thức chỉ thật sự thuộc về bạn khi tự giải một bài toán không có hướng dẫn từng bước. Dự án này mô phỏng việc bạn nhận được một yêu cầu thật từ "ban giám đốc ShopViet" và phải tự thiết kế, truy vấn, tối ưu, bảo vệ dữ liệu. Phần phỏng vấn giúp bạn diễn đạt lại những gì đã làm thành câu trả lời mạch lạc.
🎯 Mục tiêu học tập
- Tự thiết kế mở rộng schema (bảng hoàn trả/khuyến mãi) với ràng buộc đầy đủ
- Giải 8 bài toán phân tích nhiều mức độ, tự kiểm tra tính đúng bằng đối soát
- Tối ưu một truy vấn chậm bằng quy trình đo – sửa – đo lại và thiết kế đúng index
- Trả lời được các câu hỏi phỏng vấn SQL phổ biến, kèm ví dụ và lý do
Đề bài
Bạn vừa gia nhập ShopViet. Ban giám đốc đưa ra bốn yêu cầu. Hãy tự làm trước, rồi mới xem lời giải gợi ý bên dưới. Tiêu chí chấm: đúng (có đối soát), đọc được (CTE đặt tên tốt), an toàn (NULL/thời gian/nhân dòng), nhanh (có EXPLAIN khi cần).
- Phần A — Schema: thêm khả năng hoàn trả hàng (returns) và mã khuyến mãi (coupons) vào hệ thống.
- Phần B — Báo cáo: 8 câu hỏi nghiệp vụ.
- Phần C — Hiệu năng: trang quản trị "đơn của một khách" chạy chậm khi dữ liệu lớn.
- Phần D — Đồng thời & chất lượng: thiết kế đặt hàng không bán vượt tồn kho; bộ kiểm tra dữ liệu.
Phần A — Mở rộng schema
Yêu cầu: một dòng hàng có thể được hoàn trả một phần (số lượng ≤ số lượng đã mua, không hoàn quá tổng đã mua trên cùng dòng), có lý do, thời điểm; mã khuyến mãi có thời hạn, giới hạn lượt dùng, và mỗi đơn dùng tối đa một mã.
CREATE TABLE coupons (
code text PRIMARY KEY CHECK (code = upper(code)),
discount_pct integer NOT NULL CHECK (discount_pct BETWEEN 1 AND 90),
valid_during tstzrange NOT NULL,
max_uses integer CHECK (max_uses > 0)
);
ALTER TABLE orders ADD COLUMN coupon_code text REFERENCES coupons(code);
CREATE TABLE returns (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id integer NOT NULL,
product_id integer NOT NULL,
qty integer NOT NULL CHECK (qty > 0),
reason text NOT NULL CHECK (reason IN ('loi_san_pham', 'doi_y', 'giao_cham', 'khac')),
returned_at timestamptz NOT NULL DEFAULT now(),
FOREIGN KEY (order_id, product_id) REFERENCES order_items (order_id, product_id) -- khoá ngoại ghép
);
CREATE INDEX ON returns (order_id, product_id);
-- Thử: hoàn hàng cho dòng không tồn tại => từ chối
INSERT INTO returns (order_id, product_id, qty, reason) VALUES (1, 20, 1, 'doi_y');CREATE TABLE
ALTER TABLE
CREATE TABLE
CREATE INDEX
ERROR: insert or update on table "returns" violates foreign key constraint "returns_order_id_product_id_fkey"
DETAIL: Key (order_id, product_id)=(1, 20) is not present in table "order_items".-- Ràng buộc "không hoàn quá số đã mua" không biểu diễn được bằng CHECK đơn giản => dùng trigger + khoá dòng
CREATE TABLE returns (id serial PRIMARY KEY, order_id int, product_id int, qty int CHECK (qty > 0));
CREATE FUNCTION chan_hoan_qua() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE da_mua int; da_hoan int;
BEGIN
SELECT qty INTO da_mua FROM order_items WHERE order_id = NEW.order_id AND product_id = NEW.product_id FOR UPDATE; -- khoá dòng: chặn hai lần hoàn đồng thời
IF da_mua IS NULL THEN RAISE EXCEPTION 'Dòng hàng không tồn tại'; END IF;
SELECT COALESCE(sum(qty), 0) INTO da_hoan FROM returns WHERE order_id = NEW.order_id AND product_id = NEW.product_id;
IF da_hoan + NEW.qty > da_mua THEN
RAISE EXCEPTION 'Hoàn % nhưng chỉ mua % (đã hoàn %)', NEW.qty, da_mua, da_hoan;
END IF;
RETURN NEW;
END $$;
CREATE TRIGGER trg_hoan BEFORE INSERT ON returns FOR EACH ROW EXECUTE FUNCTION chan_hoan_qua();
INSERT INTO returns (order_id, product_id, qty) VALUES (1, 9, 1); -- đơn 1 mua 3 sản phẩm #9: hoàn 1 → OK
INSERT INTO returns (order_id, product_id, qty) VALUES (1, 9, 5); -- tổng hoàn 6 > 3 → bị chặnCREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 1
ERROR: Hoàn 5 nhưng chỉ mua 3 (đã hoàn 1)
CONTEXT: PL/pgSQL function chan_hoan_qua() line 8 at RAISE- Khoá ngoại ghép
(order_id, product_id)trỏ vào PK ghép củaorder_itemsđảm bảo chỉ hoàn dòng thật. - Quy tắc "tổng hoàn ≤ đã mua" liên quan nhiều dòng → trigger có
FOR UPDATEtrên dòng cha để tuần tự hoá các lần hoàn đồng thời (nếu không, hai phiên cùng thấy "đã hoàn 0" rồi cùng chèn). - Dùng
tstzrangecho thời hạn mã gọn hơn hai cộtstarts_at/ends_at+ kiểm tra@>.
Phần B — Tám câu hỏi nghiệp vụ (kèm lời giải gợi ý)
Dưới đây mỗi câu có lời giải ẩn. Hãy cố gắng tự viết trước; lời giải chỉ là một cách làm đúng — cách của bạn có thể khác miễn cho cùng kết quả.
Doanh thu thuần = tiền hàng sau giảm giá của các đơn không huỷ. Liệt kê 5 khách đứng đầu kèm hạng, % đóng góp trên tổng và % luỹ kế.
Xem lời giải & giải thích
WITH dt AS (
SELECT o.customer_id, sum(oi.qty * oi.unit_price * (100 - oi.discount_pct) / 100) AS dt
FROM orders o JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY o.customer_id
)
SELECT rank() OVER (ORDER BY dt DESC) AS hang, c.full_name, round(dt / 1e6) AS dt_trieu,
round(100.0 * dt / sum(dt) OVER (), 2) AS pct_tong,
round(100.0 * sum(dt) OVER (ORDER BY dt DESC ROWS UNBOUNDED PRECEDING) / sum(dt) OVER (), 2) AS pct_luy_ke
FROM dt JOIN customers c ON c.id = dt.customer_id
ORDER BY dt DESC LIMIT 5; hang | full_name | dt_trieu | pct_tong | pct_luy_ke
------+----------------+----------+----------+------------
1 | Lê Khánh Linh | 417 | 3.41 | 3.41
2 | Đặng Thu Hà | 414 | 3.39 | 6.80
3 | Nguyễn Thu Hà | 395 | 3.23 | 10.03
4 | Lê Văn An | 388 | 3.17 | 13.20
5 | Hoàng Ngọc Lan | 362 | 2.96 | 16.16
(5 rows)Grain: một dòng/khách. Tổng hợp trước (CTE) rồi mới nối tên khách và dùng window cho % — không bị fan-out.
Biên thực tế = (giá bán sau giảm − giá vốn) / giá bán sau giảm, tính trên các dòng đã bán (đơn không huỷ), có trọng số theo số lượng. Liệt kê 5 sản phẩm biên thấp nhất.
Xem lời giải & giải thích
SELECT p.sku, p.name,
round(100.0 * sum(oi.qty * (oi.unit_price * (100 - oi.discount_pct) / 100 - p.cost))
/ NULLIF(sum(oi.qty * oi.unit_price * (100 - oi.discount_pct) / 100), 0), 1) AS bien_pct,
sum(oi.qty) AS da_ban
FROM order_items oi
JOIN orders o ON o.id = oi.order_id AND o.status <> 'cancelled'
JOIN products p ON p.id = oi.product_id
GROUP BY p.id, p.sku, p.name
ORDER BY bien_pct, p.sku LIMIT 5; sku | name | bien_pct | da_ban
--------+-----------------------+----------+--------
LT-002 | Laptop Zenbook 16 Pro | 18.5 | 120
LT-001 | Laptop Zenbook 14 | 18.8 | 162
LT-003 | Laptop Student 15 | 18.9 | 39
PH-003 | Điện thoại Nova Pro | 22.5 | 39
PH-001 | Điện thoại Aurora X1 | 23.5 | 121
(5 rows)Biên có trọng số = tổng lãi / tổng doanh thu — không phải trung bình các biên từng dòng (bẫy 'trung bình của trung bình').
Tỉ lệ đơn huỷ theo thành phố khách, chỉ hiện thành phố có ít nhất 30 đơn, gộp khách chưa có thành phố thành 'Chưa rõ'.
Xem lời giải & giải thích
SELECT COALESCE(c.city, 'Chưa rõ') AS thanh_pho, count(*) AS so_don,
count(*) FILTER (WHERE o.status = 'cancelled') AS huy,
round(100.0 * count(*) FILTER (WHERE o.status = 'cancelled') / count(*), 1) AS ty_le_huy_pct
FROM orders o JOIN customers c ON c.id = o.customer_id
GROUP BY 1 HAVING count(*) >= 30
ORDER BY ty_le_huy_pct DESC, thanh_pho; thanh_pho | so_don | huy | ty_le_huy_pct
-----------+--------+-----+---------------
Chưa rõ | 30 | 4 | 13.3
TP.HCM | 72 | 8 | 11.1
Cần Thơ | 75 | 8 | 10.7
Đà Nẵng | 74 | 7 | 9.5
Hà Nội | 76 | 7 | 9.2
Hải Phòng | 73 | 6 | 8.2
(6 rows)Điều kiện trên số lượng đơn là điều kiện trên nhóm ⇒ HAVING. NULL city được xử lý bằng COALESCE ở cả SELECT và GROUP BY (dùng GROUP BY 1).
Với mỗi khách có ít nhất 2 đơn không huỷ: có đơn thứ hai trong vòng 30 ngày kể từ đơn đầu không? Tính tỉ lệ khách 'quay lại nhanh' theo tháng của đơn đầu.
Xem lời giải & giải thích
WITH x AS (
SELECT customer_id, ordered_at,
row_number() OVER (PARTITION BY customer_id ORDER BY ordered_at, id) AS stt
FROM orders WHERE status <> 'cancelled'
), kh AS (
SELECT a.customer_id, date_trunc('month', a.ordered_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date AS thang_don_dau,
(b.ordered_at - a.ordered_at) <= interval '30 days' AS quay_lai_nhanh
FROM x a JOIN x b ON b.customer_id = a.customer_id AND a.stt = 1 AND b.stt = 2
)
SELECT thang_don_dau, count(*) AS so_khach, count(*) FILTER (WHERE quay_lai_nhanh) AS quay_lai_30n,
round(100.0 * count(*) FILTER (WHERE quay_lai_nhanh) / count(*)) AS pct
FROM kh GROUP BY 1 ORDER BY 1; thang_don_dau | so_khach | quay_lai_30n | pct
---------------+----------+--------------+-----
2024-01-01 | 1 | 1 | 100
2024-02-01 | 11 | 8 | 73
2024-03-01 | 8 | 5 | 63
2024-04-01 | 11 | 5 | 45
2024-05-01 | 14 | 8 | 57
2024-06-01 | 7 | 5 | 71
2024-07-01 | 2 | 2 | 100
(7 rows)Đánh số đơn của từng khách rồi nối đơn #1 với đơn #2 (self-join trên bảng đã đánh số). Có thể viết gọn hơn bằng lead().
Với các đơn đã giao (shipped_at không NULL) và có thanh toán, tính trung vị và p90 số giờ từ lúc đặt đến lúc giao, theo phương thức thanh toán.
Xem lời giải & giải thích
SELECT p.method, count(*) AS so_don,
round((percentile_cont(0.5) WITHIN GROUP (ORDER BY extract(epoch FROM o.shipped_at - o.ordered_at) / 3600))::numeric, 1) AS trung_vi_gio,
round((percentile_cont(0.9) WITHIN GROUP (ORDER BY extract(epoch FROM o.shipped_at - o.ordered_at) / 3600))::numeric, 1) AS p90_gio
FROM orders o JOIN payments p ON p.order_id = o.id
WHERE o.shipped_at IS NOT NULL
GROUP BY p.method ORDER BY p.method; method | so_don | trung_vi_gio | p90_gio
--------+--------+--------------+---------
card | 93 | 75.0 | 107.0
cod | 93 | 69.0 | 105.6
momo | 94 | 75.0 | 106.7
(3 rows)Dữ liệu mẫu có thời gian giao tất định nên các con số gần nhau — trong thực tế p90 lệch xa trung vị chính là thông tin quan trọng.
Báo cáo doanh thu thuần theo (danh mục gốc → danh mục con) có tổng phụ từng danh mục gốc và tổng cộng.
Xem lời giải & giải thích
SELECT COALESCE(par.name, '== TỔNG ==') AS goc, CASE WHEN GROUPING(par.name) = 0 AND GROUPING(cat.name) = 1 THEN '(tổng phụ)' ELSE cat.name END AS con,
round(sum(oi.qty * oi.unit_price * (100 - oi.discount_pct) / 100) / 1e6) AS trieu
FROM order_items oi JOIN orders o ON o.id = oi.order_id AND o.status <> 'cancelled'
JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id JOIN categories par ON par.id = cat.parent_id
GROUP BY ROLLUP (par.name, cat.name) ORDER BY par.name NULLS LAST, cat.name NULLS LAST; goc | con | trieu
------------+------------+-------
Điện tử | Điện thoại | 2792
Điện tử | Laptop | 8173
Điện tử | Phụ kiện | 196
Điện tử | (tổng phụ) | 11161
Gia dụng | Nhà bếp | 426
Gia dụng | (tổng phụ) | 426
Thời trang | Áo | 125
Thời trang | Giày | 165
Thời trang | (tổng phụ) | 290
== TỔNG == | NULL | 11877
(10 rows)ROLLUP thay cho ba truy vấn UNION ALL; GROUPING() phân biệt dòng tổng hợp với NULL thật.
Tại ngày tham chiếu 2024-12-01: khách từng có ≥ 3 đơn không huỷ nhưng không có đơn nào trong 90 ngày gần nhất. Liệt kê id, số đơn, ngày mua cuối và số ngày im lặng; sắp theo tổng chi tiêu giảm dần.
Xem lời giải & giải thích
SELECT o.customer_id, count(*) AS so_don, max(o.ordered_at)::date AS mua_cuoi,
date '2024-12-01' - max(o.ordered_at)::date AS so_ngay_im_lang, round(sum(p.amount) / 1e6) AS chi_trieu
FROM orders o LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status <> 'cancelled' AND o.ordered_at < '2024-12-01'
GROUP BY o.customer_id
HAVING count(*) >= 3 AND max(o.ordered_at) < date '2024-12-01' - 90
ORDER BY chi_trieu DESC NULLS LAST LIMIT 5; customer_id | so_don | mua_cuoi | so_ngay_im_lang | chi_trieu
-------------+--------+------------+-----------------+-----------
26 | 8 | 2024-08-03 | 120 | 357
8 | 8 | 2024-06-13 | 171 | 336
28 | 7 | 2024-07-21 | 133 | 281
32 | 7 | 2024-08-31 | 92 | 275
20 | 7 | 2024-08-05 | 118 | 256
(5 rows)Cả hai điều kiện (≥ 3 đơn, đơn cuối quá 90 ngày) đều trên nhóm ⇒ HAVING. Dùng ngày tham chiếu cố định thay vì now() để kết quả tái lập được.
Có danh sách khách mới (VALUES bên dưới) với email lẫn hoa/thường/khoảng trắng và trùng nhau. Nạp vào customers: chuẩn hoá email, bỏ trùng trong file (giữ bản đầu), bỏ qua khách đã tồn tại; báo cáo số dòng thêm mới và số dòng bị loại.
Xem lời giải & giải thích
WITH file_nhap(stt, ten, email, thanh_pho) AS (VALUES
(1, 'Mai Anh', ' NewUser1@Example.com ', 'Hà Nội'),
(2, 'Mai Anh 2', 'newuser1@example.com', 'Hà Nội'), -- trùng trong file
(3, 'Lan Phương', 'user7@example.com', 'Huế'), -- đã có trong DB
(4, 'Tuấn Kiệt', 'newuser2@example.com', NULL)),
sach AS (
SELECT DISTINCT ON (lower(trim(email))) ten, lower(trim(email)) AS email, thanh_pho
FROM file_nhap ORDER BY lower(trim(email)), stt -- stt nhỏ nhất = bản xuất hiện đầu tiên
), them AS (
INSERT INTO customers (id, full_name, email, city, created_at)
SELECT 1000 + row_number() OVER (ORDER BY email), ten, email, thanh_pho, now()
FROM sach
ON CONFLICT (email) DO NOTHING
RETURNING id, email
)
SELECT (SELECT count(*) FROM file_nhap) AS dong_trong_file,
(SELECT count(*) FROM them) AS them_moi,
(SELECT count(*) FROM file_nhap) - (SELECT count(*) FROM them) AS bi_loai; dong_trong_file | them_moi | bi_loai
-----------------+----------+---------
4 | 2 | 2
(1 row)Chuẩn hoá (lower/trim) → khử trùng trong file (DISTINCT ON) → ON CONFLICT DO NOTHING cho trùng với DB; RETURNING trong CTE cho ta đếm chính xác dòng thật sự được thêm. (id 1000+ là minh hoạ — thực tế dùng IDENTITY.)
Phần C — Tối ưu: trang 'đơn của một khách'
Giả sử bảng big_orders có 1 triệu đơn và trang quản trị chạy: SELECT * FROM big_orders WHERE customer_id = 4242 AND status = 'delivered' ORDER BY ordered_at DESC LIMIT 20. Hãy (1) đo, (2) thiết kế index, (3) đo lại và giải thích.
CREATE TABLE big_orders AS
SELECT i AS id, (i::bigint * 7919) % 100000 + 1 AS customer_id,
(ARRAY['pending','paid','shipped','delivered','cancelled'])[1 + (i + i / 100000) % 5] AS status,
timestamptz '2023-01-01' + (i % 500000) * interval '1 minute' AS ordered_at, (i % 1000) * 1000 AS amount
FROM generate_series(1, 1000000) AS i;
ANALYZE big_orders;
-- (1) Trước khi tối ưu
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT * FROM big_orders WHERE customer_id = 4242 AND status = 'delivered' ORDER BY ordered_at DESC LIMIT 20;
-- (2) Index: hai cột lọc bằng đứng trước, cột sắp xếp cuối cùng, cùng chiều DESC
CREATE INDEX idx_kh_status_time ON big_orders (customer_id, status, ordered_at DESC);
ANALYZE big_orders;
-- (3) Sau khi tối ưu
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT * FROM big_orders WHERE customer_id = 4242 AND status = 'delivered' ORDER BY ordered_at DESC LIMIT 20;SELECT 1000000
ANALYZE
QUERY PLAN
-------------------------------------------------------------------------------------
Limit (actual rows=2 loops=1)
-> Sort (actual rows=2 loops=1)
Sort Key: ordered_at DESC
Sort Method: quicksort Memory: 25kB
-> Gather (actual rows=2 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on big_orders (actual rows=1 loops=3)
Filter: ((customer_id = 4242) AND (status = 'delivered'::text))
Rows Removed by Filter: 333333
Planning Time: 0.265 ms
Execution Time: 56.583 ms
(12 rows)
CREATE INDEX
ANALYZE
QUERY PLAN
---------------------------------------------------------------------------------
Limit (actual rows=2 loops=1)
-> Index Scan using idx_kh_status_time on big_orders (actual rows=2 loops=1)
Index Cond: ((customer_id = 4242) AND (status = 'delivered'::text))
Planning Time: 0.175 ms
Execution Time: 0.069 ms
(5 rows)
Câu hỏi suy ngẫm: (a) Vì sao đặt ordered_at sau cùng? (b) Nếu truy vấn không lọc status thì index này còn dùng tốt không? (c) Chi phí của index này khi INSERT? (d) Nếu 99% đơn đều là 'delivered' thì partial index có ích không?
Gợi ý trả lời
(a) Hai cột đầu là so sánh bằng nên các dòng khớp nằm liền kề trong index và đã sắp theo ordered_at DESC — đọc 20 mục đầu là xong, không sort. (b) Chỉ dùng được phần tiền tố customer_id (và phải sort lại) — có thể cần index (customer_id, ordered_at DESC) riêng nếu truy vấn đó phổ biến. (c) Mỗi INSERT phải ghi thêm một mục vào index (và UPDATE các cột trong index phá HOT update): đo throughput ghi. (d) Không — partial index chỉ có lợi khi điều kiện chọn ra phần nhỏ của bảng; ngược lại để nó trên 'status <> delivered' nếu truy vấn hay tìm đơn bất thường.
Phần D — Đặt hàng không bán vượt kho & bộ kiểm tra dữ liệu
D1. Thiết kế quy trình "đặt hàng": nhận danh sách (product_id, qty), trừ kho, tạo đơn và dòng hàng, tất cả hoặc không gì cả, an toàn khi nhiều người đặt cùng lúc, không deadlock.
-- Khung gợi ý (một transaction):
-- 1) sắp danh sách theo product_id tăng dần (khoá theo thứ tự nhất quán => không deadlock)
-- 2) với mỗi dòng: UPDATE inventory SET qty_on_hand = qty_on_hand - $qty WHERE product_id = $id AND qty_on_hand >= $qty;
-- nếu 0 dòng bị ảnh hưởng => hết hàng => ROLLBACK và báo lỗi cho khách
-- 3) INSERT orders ... RETURNING id; INSERT order_items (kèm unit_price = giá hiện tại làm snapshot)
-- 4) COMMIT. CHECK (qty_on_hand >= 0) là lưới an toàn cuối cùng.
CREATE TEMP TABLE don_moi (product_id int, qty int);
INSERT INTO don_moi VALUES (1, 2), (3, 1);
WITH tru_kho AS (
UPDATE inventory i SET qty_on_hand = i.qty_on_hand - d.qty
FROM don_moi d WHERE i.product_id = d.product_id AND i.qty_on_hand >= d.qty
RETURNING i.product_id
), don AS (
INSERT INTO orders (id, customer_id, status, ordered_at) SELECT 9001, 1, 'pending', now()
WHERE (SELECT count(*) FROM tru_kho) = (SELECT count(*) FROM don_moi) -- chỉ tạo đơn nếu TRỪ ĐƯỢC TẤT CẢ
RETURNING id
)
SELECT (SELECT count(*) FROM tru_kho) AS dong_tru_duoc, (SELECT count(*) FROM don_moi) AS dong_yeu_cau, (SELECT count(*) FROM don) AS don_tao;CREATE TABLE
INSERT 0 2
dong_tru_duoc | dong_yeu_cau | don_tao
---------------+--------------+---------
2 | 2 | 1
(1 row)
Trong thực tế quy trình nằm trong một transaction ở tầng ứng dụng hoặc một hàm PL/pgSQL; điểm mấu chốt: trừ kho nguyên tử có điều kiện, kiểm tra số dòng bị ảnh hưởng, rollback toàn bộ nếu thiếu, sắp thứ tự khoá, và CHECK (qty_on_hand >= 0) làm lưới cuối.
D2. Bộ kiểm tra dữ liệu: hãy mở rộng truy vấn kiểm tra ở Module 12 cho các bảng mới (returns, coupons): hoàn quá số đã mua, mã khuyến mãi dùng ngoài hạn, vượt số lượt dùng tối đa, đơn có coupon_code không tồn tại. Mỗi truy vấn trả về 0 khi dữ liệu sạch; đặt chúng thành một job định kỳ cảnh báo khi > 0.
40 câu hỏi phỏng vấn SQL (đáp án ngắn)
Nền tảng
| Câu hỏi | Trả lời ngắn |
|---|---|
| 1. WHERE khác HAVING? | WHERE lọc dòng trước khi gom nhóm (không dùng hàm tổng hợp); HAVING lọc nhóm sau GROUP BY. |
| 2. Thứ tự thực thi logic của SELECT? | FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT (window) → DISTINCT → ORDER BY → LIMIT. |
| 3. INNER, LEFT, FULL, CROSS JOIN? | Chỉ khớp / giữ hết bảng trái / giữ hết hai bên / tích Descartes. Nêu được bẫy điều kiện ở ON vs WHERE của LEFT JOIN. |
4. NULL là gì? NULL = NULL? | "Không biết"; so sánh cho UNKNOWN, không TRUE. Dùng IS NULL / IS DISTINCT FROM. |
| 5. COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col)? | Đếm dòng / đếm giá trị khác NULL / đếm giá trị khác nhau khác NULL. |
| 6. UNION vs UNION ALL? | UNION khử trùng (chậm hơn); UNION ALL giữ trùng. Mặc định UNION ALL. |
| 7. Khoá chính vs khoá ngoại vs unique? | PK định danh duy nhất & NOT NULL; FK đảm bảo toàn vẹn tham chiếu; UNIQUE chống trùng nhưng cho phép NULL (PG: nhiều NULL). |
| 8. DELETE vs TRUNCATE vs DROP? | Xoá dòng (có WHERE, trigger, rollback được) / xoá sạch nhanh (không WHERE) / xoá cả bảng. |
| 9. Subquery vs JOIN vs EXISTS? | JOIN lấy cột; EXISTS kiểm tra tồn tại (an toàn NULL); planner thường biến subquery tương đương thành semi/anti-join. |
| 10. NOT IN vs NOT EXISTS? | NOT IN trả rỗng nếu subquery có NULL; NOT EXISTS an toàn. |
Nâng cao
| Câu hỏi | Trả lời ngắn |
|---|---|
| 11. ROW_NUMBER, RANK, DENSE_RANK? | Hoà thì: tuỳ ý (1,2,3) / hạng bằng nhau nhảy cách (1,1,3) / bằng nhau không nhảy (1,1,2). |
| 12. Top-N mỗi nhóm? | CTE với ROW_NUMBER() OVER (PARTITION BY nhóm ORDER BY ...) rồi lọc rn ≤ N; hoặc LATERAL + LIMIT. |
| 13. Tổng luỹ kế và bẫy frame? | SUM() OVER (ORDER BY x ROWS UNBOUNDED PRECEDING); frame mặc định RANGE gộp các dòng cùng giá trị. |
| 14. LAG/LEAD dùng để làm gì? | So với dòng trước/sau trong cùng cửa sổ: tăng trưởng kỳ trước, khoảng cách thời gian. |
| 15. CTE vs subquery vs view vs temp table? | CTE: chia bước trong 1 câu lệnh; view: truy vấn có tên tái dùng; materialized view: lưu kết quả; temp table: kết quả trung gian lớn cần index. |
| 16. CTE đệ quy hoạt động ra sao? | Neo UNION ALL phần đệ quy tham chiếu chính nó, lặp tới khi không sinh dòng; cần điều kiện dừng/CYCLE. |
| 17. Tìm dòng trùng và xoá giữ bản mới nhất? | ROW_NUMBER() OVER (PARTITION BY khoá ORDER BY thời_gian DESC) rồi xoá rn > 1 (theo ctid/id). |
| 18. Gaps & islands? | Giá trị − ROW_NUMBER() là hằng số trong một chuỗi liên tiếp → GROUP BY nó. |
| 19. Pivot trong SQL? | count/sum(...) FILTER (WHERE ...) hoặc CASE trong hàm tổng hợp; crosstab (tablefunc). |
| 20. Fan-out là gì? Tránh thế nào? | JOIN 1-n nhân dòng cha làm SUM/COUNT sai; tổng hợp về cùng grain trước khi nối. |
Hiệu năng & thiết kế
| Câu hỏi | Trả lời ngắn |
|---|---|
| 21. Index hoạt động thế nào? | B-tree đã sắp xếp: tìm O(log n) thay vì quét O(n); đổi lại tốn chỗ và làm ghi chậm. |
| 22. Thứ tự cột trong composite index? | Cột = trước, rồi cột khoảng/ORDER BY; chỉ dùng tốt cho tiền tố trái. |
| 23. Vì sao index không được dùng? | Hàm/ép kiểu quanh cột, LIKE '%x', kém chọn lọc, bảng nhỏ, thống kê cũ, thứ tự cột sai. |
| 24. Đọc EXPLAIN ANALYZE? | Từ trong ra ngoài; so rows ước lượng/thực tế; Seq vs Index Scan; Rows Removed by Filter; loops; Buffers. |
| 25. Covering index / partial index? | INCLUDE để index-only scan; WHERE trong index chỉ lập chỉ mục phần dữ liệu nóng. |
| 26. Chuẩn hoá và khi nào phi chuẩn hoá? | 1NF–3NF giảm trùng lặp/bất thường; phi chuẩn hoá có chủ đích khi cần đọc nhanh hoặc snapshot lịch sử (unit_price). |
| 27. Phân trang tốt nhất? | Keyset (WHERE (a,id) > (…) ORDER BY a,id LIMIT n); OFFSET chậm dần và không ổn định. |
| 28. float vs numeric? | float xấp xỉ nhị phân (sai số); numeric chính xác — bắt buộc cho tiền. |
| 29. timestamp vs timestamptz? | timestamptz là thời điểm tuyệt đối (lưu UTC); timestamp "trần" dễ sai múi giờ. |
| 30. Soft delete: ưu/nhược? | Giữ lịch sử/khôi phục; nhưng dễ quên lọc, unique cần partial index, vướng quyền được xoá dữ liệu cá nhân. |
Transaction, đồng thời & ứng dụng
| Câu hỏi | Trả lời ngắn |
|---|---|
| 31. ACID? | Atomicity, Consistency, Isolation, Durability — nêu ví dụ chuyển tiền. |
| 32. Các mức cô lập và hiện tượng? | READ COMMITTED (mặc định PG) / REPEATABLE READ / SERIALIZABLE; non-repeatable read, phantom, lost update, write skew. |
| 33. Lost update và cách chữa? | Cập nhật nguyên tử SET x = x - n WHERE x >= n, SELECT FOR UPDATE, version (optimistic), cô lập cao + retry. |
| 34. Deadlock là gì? Phòng tránh? | Hai transaction chờ khoá nhau; khoá theo thứ tự nhất quán, giữ transaction ngắn, retry khi lỗi 40P01. |
| 35. SKIP LOCKED dùng khi nào? | Hàng đợi công việc nhiều worker không trùng việc, không chờ nhau. |
| 36. SQL injection và phòng chống? | Tham số hoá (prepared statement), danh sách trắng cho định danh, quyền tối thiểu. |
| 37. N+1 là gì? | 1 truy vấn danh sách + N truy vấn con; sửa bằng JOIN / batch IN (= ANY) / eager loading. |
| 38. UPSERT và idempotency? | INSERT ... ON CONFLICT DO UPDATE/NOTHING nguyên tử; thiết kế để chạy lại không gây hại. |
| 39. Migration an toàn trên bảng lớn? | Thêm nullable → backfill theo lô → ràng buộc NOT VALID/VALIDATE → CONCURRENTLY; expand/contract; lock_timeout. |
| 40. Một truy vấn chậm đột ngột, bạn làm gì? | Xem EXPLAIN (ANALYZE, BUFFERS); kiểm tra thống kê (ANALYZE), kế hoạch đổi, khoá chờ (pg_stat_activity), bloat, tải đồng thời; sửa nguyên nhân gốc; đo lại. |
🏋️ Bài tập thực hành
Đóng gói kết quả dự án thành một thư mục: schema.sql (Phần A), reports.sql (8 câu B), perf.md (EXPLAIN trước/sau + giải thích), checks.sql (bộ kiểm tra dữ liệu), order_flow.sql (D1) và README.md mô tả cách chạy. Bạn có thể đưa lên GitHub như một mục trong hồ sơ năng lực.
Xem lời giải & giải thích
Tiêu chí tự chấm: (1) chạy lại từ đầu trên database mới được (psql -f theo thứ tự); (2) mọi truy vấn có chú thích grain và giả định; (3) có đối soát hoặc kiểm tra chéo cho ít nhất 3 báo cáo; (4) ghi rõ các đánh đổi thiết kế (vì sao trigger, vì sao index này).
📚 Tài nguyên học tập
-
PostgreSQL ExercisesLuyện thêm bài tập SQL tương tácBài tập
-
LeetCode — Database problemsLuyện câu hỏi SQL phỏng vấnBài tập
-
Database Engineering for Backend & AI EngineersBước tiếp theo: replication, sharding, NoSQL, bảo mật, database cho AIKhoá liên quan
-
100 câu Database & Kiến trúc nâng caoÔn phỏng vấn database ở mức kiến trúcKhoá liên quan
✅ Tự đánh giá hoàn thành
- Hoàn thành Phần A–D của dự án và có đối soát cho các báo cáo
- Có EXPLAIN trước/sau cho truy vấn tối ưu và giải thích được lý do chọn index
- Trả lời trôi chảy ít nhất 30/40 câu hỏi phỏng vấn kèm ví dụ
- Đã đóng gói dự án thành repo có README để chia sẻ