SQL từ cơ bản đến nâng cao
Trang chủ / Module 16
🏆 Module 16 / 16 · 10-12 giờ

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.

CapstonePhỏng vấnTổng hợp
📌 Vì sao module này quan trọng

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).

  1. 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.
  2. Phần B — Báo cáo: 8 câu hỏi nghiệp vụ.
  3. 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.
  4. 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');
Lỗi PostgreSQL trả về
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ặn
Lỗi PostgreSQL trả về
CREATE 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
🧠 Cách chuyên gia — Ghi chú thiết kế
  • Khoá ngoại ghép (order_id, product_id) trỏ vào PK ghép của order_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 UPDATE trê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 tstzrange cho thời hạn mã gọn hơn hai cột starts_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ả.

B1 — Top 5 khách theo doanh thu thuầnDễ

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;
Kết quả
 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.

B2 — Biên lợi nhuận thực tế theo sản phẩmTrung bình

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;
Kết quả
  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').

B3 — Tỉ lệ huỷ theo thành phố (chỉ thành phố đủ lớn)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;
Kết quả
 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).

B4 — Khách quay lại trong 30 ngàyKhó

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;
Kết quả
 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().

B5 — Thời gian giao hàng theo phương thức thanh toán (trung vị và p90)Trung bình

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;
Kết quả
 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.

B6 — Doanh thu theo danh mục có tổng phụTrung bình

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;
Kết quả
    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.

B7 — Khách có nguy cơ rời bỏKhó

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;
Kết quả
 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.

B8 — Nhập dữ liệu khách từ file bẩnKhó

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;
Kết quả
 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;
Kết quả
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;
Kết quả
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ỏiTrả 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ỏiTrả 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ỏiTrả 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ỏiTrả 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

Bản đóng gói dự ánTổng hợp

Đó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

✅ 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ẻ