Đến nội dung chính
notes.vinhnq.com

Tìm kiếm

Database & ORMMid16 phút đọc

Thực hành SQL: JOIN, GROUP BY và chuẩn hoá qua 15 câu trên database thư viện

Ôn 12 thẻ
Trong bài này

Cụm bài Thực hành backend: 1. Yêu cầu và thiết kế dữ liệu · 2. API và chống overbooking · 3. Ôn tập phỏng vấn · 4. Đệ quy và sắp xếp · 5. Bài tập SQL · 6. SQL trên database E-Learning

Bài này ôn nhanh phần database của buổi thực hành rồi giải 15 câu SQL trên database thư viện; ở nhiều câu đáp án gốc sai và mình ghi rõ sai ở đâu, vì sao. Làm xong, bạn viết được JOIN, GROUP BY/HAVING đúng và nhận ra ba bẫy hay gặp: LEFT JOIN, COUNT và điều kiện không dùng được index. Lý thuyết đầy đủ ở các bài Database; bộ 9 câu trên database E-Learning (rating, tiến độ học, tài khoản bị khoá) ở phần 6.

1. Ôn tập database

Ý chính: quan hệ trong ERD quyết định join qua đâu, loại JOIN quyết định dòng nào được giữ, còn thứ tự thực thi logic giải thích vì sao WHERE và HAVING khác nhau. Chi tiết ở bài Database.

ERD (Entity Relationship Diagram) mô tả dữ liệu bằng thực thể (entity, sẽ thành bảng), thuộc tính (attribute, là cột, trong đó có khoá chính) và quan hệ (relationship). Mỗi quan hệ có lực lượng (cardinality — một bên ứng với bao nhiêu bên kia):

Lực lượng Ví dụ Cài đặt
1–1 một user có một hồ sơ khoá ngoại unique (hoặc dùng chung khoá chính)
1–N một khách hàng có nhiều đơn hàng khoá ngoại ở phía “nhiều”
N–M sinh viên và khoá học bảng nối có hai khoá ngoại

Các loại JOIN:

flowchart LR
  I[INNER JOIN<br/>chỉ dòng khớp ở cả hai bảng]
  L[LEFT JOIN<br/>mọi dòng bảng trái + phần khớp bên phải, không khớp thì NULL]
  R[RIGHT JOIN<br/>ngược lại của LEFT]
  F[FULL OUTER JOIN<br/>tất cả hai bên - MySQL không có, giả lập bằng LEFT UNION RIGHT]
  C[CROSS JOIN<br/>tích Descartes]
  S[SELF JOIN<br/>bảng join với chính nó]

GROUP BY, HAVING, hàm tổng hợp:

  • Hàm tổng hợp (COUNT, SUM, AVG, MIN, MAX) gộp nhiều dòng thành một giá trị.
  • GROUP BY chia dòng thành nhóm để tổng hợp theo từng nhóm.
  • WHERE lọc dòng trước khi nhóm; HAVING lọc nhóm sau khi tổng hợp.
  • COUNT(*) đếm dòng, COUNT(col) bỏ qua NULL. Điều này quyết định kết quả của LEFT JOIN.

Thứ tự thực thi logic của một câu SELECT:

flowchart LR
  A[FROM / JOIN] --> B[WHERE] --> C[GROUP BY] --> D[HAVING] --> E[SELECT<br/>tính biểu thức, alias, window function] --> F[DISTINCT] --> G[ORDER BY] --> H[LIMIT / OFFSET]

Hệ quả:

  • Không dùng được alias của SELECT trong WHERE (alias chưa tồn tại); dùng được trong ORDER BY.
  • MySQL nới lỏng, cho phép alias trong HAVING. PostgreSQL và chuẩn SQL thì không.
  • Đây là thứ tự logic; optimizer có thể thực thi khác miễn kết quả giống.

Chuẩn hoá (tách bảng để mỗi sự thật chỉ lưu một nơi):

Dạng Yêu cầu Sai Đúng
1NF mỗi ô chứa một giá trị nguyên tử, không có nhóm lặp cột phones = "0901,0902" bảng user_phone(user_id, phone)
2NF đạt 1NF, mọi cột không khoá phụ thuộc vào toàn bộ khoá chính (chỉ có ý nghĩa với khoá ghép) enrollment(student_id, course_id, course_title): course_title chỉ phụ thuộc course_id chuyển course_title về bảng course
3NF đạt 2NF và không có phụ thuộc bắc cầu (cột không khoá phụ thuộc vào cột không khoá khác) employee(id, dept_id, dept_name): dept_name phụ thuộc dept_id tách bảng department

Bài tập

Bài 1 — SQL trên cơ sở dữ liệu thư viện (15 câu)

Đề cho một database thư viện: sách, nhà xuất bản, thành viên (thẻ thư viện), nhân viên và phiếu mượn. Mình đặt lại tên theo snake_case và thêm cột return_date: đề hỏi “chưa trả” và “trả không trễ hạn”, không có ngày trả thì không trả lời đúng được.

publisher(pub_id PK, name, city)
book(book_id PK, title, pub_id FK, price, type)
member(card_no PK, name, address, phone)
employee(emp_id PK, name)
book_invoice(invoice_no PK, book_id FK, card_no FK, emp_id FK,
             date_out DATE, due_date DATE, return_date DATE NULL)  -- mỗi phiếu là một cuốn sách

Tự viết câu SQL trước rồi mới mở lời giải. Ba bẫy đáng nhớ nhất nằm dưới câu 2, 12 và 13.

Câu 1. Sách đã được mượn ít nhất một lần, kèm tên nhà xuất bản.

Lời giải câu 1: EXISTS thay vì JOIN + DISTINCT
SELECT b.title, p.name AS publisher
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
WHERE EXISTS (SELECT 1 FROM book_invoice i WHERE i.book_id = b.book_id);

EXISTS dừng ngay khi tìm thấy một phiếu và không tạo dòng trùng. Cách JOIN ... DISTINCT title sẽ gộp nhầm hai cuốn khác nhau nhưng trùng tên.

Câu 2. Số sách mỗi nhà xuất bản đã xuất bản (kể cả nhà xuất bản chưa có sách nào).

Lời giải câu 2: LEFT JOIN và COUNT(b.book_id)
SELECT p.pub_id, p.name, COUNT(b.book_id) AS total_books
FROM publisher p
LEFT JOIN book b ON b.pub_id = p.pub_id
GROUP BY p.pub_id, p.name;

Câu 3. Sách có giá cao hơn giá trung bình, kèm nhà xuất bản.

Lời giải câu 3: subquery tính AVG
SELECT b.title, p.name AS publisher, b.price
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
WHERE b.price > (SELECT AVG(price) FROM book);

Câu 4. Mọi thành viên và số sách họ đã mượn (kể cả người chưa mượn).

Lời giải câu 4: LEFT JOIN và COUNT(i.invoice_no)
SELECT m.card_no, m.name, COUNT(i.invoice_no) AS total_borrowed
FROM member m
LEFT JOIN book_invoice i ON i.card_no = m.card_no
GROUP BY m.card_no, m.name;

Câu 5. Sách đắt nhất và rẻ nhất, kèm nhà xuất bản (lấy hết nếu có nhiều cuốn đồng giá).

Lời giải câu 5: RANK() hai chiều
SELECT title, publisher, price
FROM (
  SELECT b.title, p.name AS publisher, b.price,
         RANK() OVER (ORDER BY b.price DESC) AS r_desc,
         RANK() OVER (ORDER BY b.price ASC)  AS r_asc
  FROM book b JOIN publisher p ON p.pub_id = b.pub_id
) t
WHERE r_desc = 1 OR r_asc = 1;

RANK() là window function — hàm tính trên một “cửa sổ” các dòng liên quan mà không gộp chúng lại như GROUP BY.

Câu 6. Tổng số sách mà tất cả các nhà xuất bản đã xuất bản.

Lời giải câu 6: một con số tổng

Đề gốc ghi câu này gần như trùng câu 2; mình hiểu là một con số tổng:

SELECT COUNT(*) AS total_books FROM book WHERE pub_id IS NOT NULL;

Câu 7. Sách có ít nhất một lần mượn được trả đúng hạn, kèm nhà xuất bản.

Lời giải câu 7: so return_date với due_date
SELECT b.title, p.name AS publisher
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
WHERE EXISTS (
  SELECT 1 FROM book_invoice i
  WHERE i.book_id = b.book_id
    AND i.return_date IS NOT NULL
    AND i.return_date <= i.due_date
);

Đính chính: đáp án gốc so due_date >= date_out. Điều kiện này gần như luôn đúng (hạn trả luôn sau ngày mượn) nên không nói gì về việc trễ hạn. “Không trễ” phải so ngày trả thực tế với hạn trả.

Câu 8. Thành viên đã mượn sách và số sách đã mượn, giảm dần.

Lời giải câu 8: INNER JOIN và ORDER BY
SELECT m.*, COUNT(*) AS total_borrowed
FROM member m
JOIN book_invoice i ON i.card_no = m.card_no
GROUP BY m.card_no
ORDER BY total_borrowed DESC;

Đính chính: đáp án gốc dùng LEFT JOIN (lấy cả người chưa mượn, trái với đề) và thiếu ORDER BY.

SELECT m.* với GROUP BY m.card_no hợp lệ trong MySQL vì các cột phụ thuộc hàm vào khoá chính (biết khoá chính là biết đúng một giá trị của mỗi cột).

Câu 9. Nhà xuất bản có từ hai sách trở lên.

Lời giải câu 9: HAVING COUNT(*) >= 2
SELECT p.pub_id, p.name, COUNT(*) AS total_books
FROM publisher p
JOIN book b ON b.pub_id = p.pub_id
GROUP BY p.pub_id, p.name
HAVING COUNT(*) >= 2;

Câu 10. Thành viên đã mượn sách thể loại “crime story” ít nhất hai lần.

Lời giải câu 10: INNER JOIN, WHERE rồi HAVING
SELECT m.card_no, m.name, COUNT(*) AS crime_borrowed
FROM member m
JOIN book_invoice i ON i.card_no = m.card_no
JOIN book b         ON b.book_id = i.book_id
WHERE b.type = 'crime story'
GROUP BY m.card_no, m.name
HAVING COUNT(*) >= 2;

Đáp án gốc viết LEFT JOIN rồi lọc WHERE b.type = .... Điều kiện trên bảng bên phải biến LEFT JOIN thành INNER JOIN, nên viết thẳng INNER JOIN cho rõ ý.

HAVING alias >= 2 chỉ chạy trên MySQL; viết HAVING COUNT(*) >= 2 thì chạy mọi nơi.

Câu 11. Sách đang được mượn chưa trả, ngày mượn và số ngày quá hạn.

Lời giải câu 11: return_date IS NULL và GREATEST
SELECT b.title, i.date_out, i.due_date,
       GREATEST(DATEDIFF(CURRENT_DATE, i.due_date), 0) AS days_overdue
FROM book_invoice i
JOIN book b ON b.book_id = i.book_id
WHERE i.return_date IS NULL;

Đính chính: đáp án gốc chỉ lọc due_date < NOW() mà không kiểm tra chưa trả, nên sách đã trả từ lâu vẫn hiện là “quá hạn”. GREATEST(..., 0) để sách chưa đến hạn có số ngày quá hạn bằng 0 thay vì số âm.

Câu 12. Sách chưa trả và quá hạn hơn 7 ngày, kèm nhà xuất bản.

Lời giải câu 12: so due_date với một hằng số
SELECT b.title, p.name AS publisher, DATEDIFF(CURRENT_DATE, i.due_date) AS days_overdue
FROM book_invoice i
JOIN book b      ON b.book_id = i.book_id
JOIN publisher p ON p.pub_id = b.pub_id
WHERE i.return_date IS NULL
  AND i.due_date < CURRENT_DATE - INTERVAL 7 DAY;

Câu 13. Hôm nay mỗi nhân viên lập bao nhiêu phiếu (kể cả nhân viên chưa lập phiếu nào).

Lời giải câu 13: điều kiện ngày nằm trong ON
SELECT e.emp_id, e.name, COUNT(i.invoice_no) AS invoices_today
FROM employee e
LEFT JOIN book_invoice i
       ON i.emp_id = e.emp_id
      AND i.date_out >= CURRENT_DATE
      AND i.date_out <  CURRENT_DATE + INTERVAL 1 DAY
GROUP BY e.emp_id, e.name;

Câu 14. Sách của nhà xuất bản ở Hà Nội đã được mượn, kèm số lần mượn.

Lời giải câu 14: join ba bảng rồi GROUP BY
SELECT b.book_id, b.title, COUNT(*) AS times_borrowed
FROM book b
JOIN publisher p    ON p.pub_id = b.pub_id
JOIN book_invoice i ON i.book_id = b.book_id
WHERE p.city = 'Hanoi'
GROUP BY b.book_id, b.title;

Câu 15. Sách của nhà xuất bản ở Hà Nội có giá thấp hơn giá trung bình.

Lời giải câu 15: đề mơ hồ về “giá trung bình”
SELECT b.title, b.price
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
WHERE p.city = 'Hanoi'
  AND b.price < (SELECT AVG(price) FROM book);

Đề mơ hồ: “trung bình của tất cả sách” hay “trung bình của sách Hà Nội”? Mình theo nghĩa đen (tất cả sách). Nếu là trung bình của sách Hà Nội, subquery phải lọc thêm city = 'Hanoi'. Gặp chỗ mơ hồ như vậy trong công việc thì hỏi lại, đừng đoán.

Ghi nhớ

  • Đọc được thứ tự thực thi logic của SELECT và giải thích vì sao alias không dùng được trong WHERE.
  • Giải thích được vì sao điều kiện bảng bên phải phải nằm trong ON khi cần giữ dòng không khớp.
  • Biết khi nào dùng COUNT(*), COUNT(col), COUNT(DISTINCT col).
  • Nhận ra điều kiện non-sargable và viết lại thành khoảng giá trị.
  • Biết khi nào dùng EXISTS thay cho JOIN ... DISTINCT, và RANK thay cho hai subquery.

Câu hỏi phỏng vấn

Junior

WHERE và HAVING khác nhau thế nào?

WHERE lọc từng dòng trước khi GROUP BY; HAVING lọc các nhóm sau khi tổng hợp.

WHERE không dùng được hàm tổng hợp, còn HAVING dùng được COUNT(*) >= 2. Lọc được ở WHERE thì nên lọc ở WHERE: ít dòng hơn để nhóm.

INNER JOIN và LEFT JOIN khác nhau thế nào? Cho ví dụ cần LEFT JOIN.

INNER JOIN chỉ giữ các cặp dòng khớp ở cả hai bảng; LEFT JOIN giữ mọi dòng của bảng bên trái, dòng nào không khớp thì các cột bên phải là NULL.

Cần LEFT JOIN khi kết quả phải có cả những thực thể “không có gì”: liệt kê mọi nhà xuất bản kèm số sách, kể cả nhà xuất bản chưa có sách nào (đếm bằng COUNT(b.book_id) để ra 0).

Mid

Vì sao đặt điều kiện lọc bảng bên phải trong WHERE lại làm hỏng LEFT JOIN?

Vì WHERE chạy sau JOIN và loại luôn các dòng không khớp, nên LEFT JOIN trở thành INNER JOIN.

LEFT JOIN giữ dòng bên trái không khớp bằng cách điền NULL cho bên phải. Điều kiện như i.date_out = today với NULL cho kết quả unknown nên dòng đó bị loại. Muốn giữ dòng không khớp (để đếm ra 0), đặt điều kiện vào mệnh đề ON.

COUNT(*), COUNT(col) và COUNT(DISTINCT col) khác nhau thế nào?

COUNT(*) đếm số dòng, COUNT(col) chỉ đếm dòng mà col khác NULL, COUNT(DISTINCT col) đếm số giá trị khác nhau.

  • COUNT(*) đếm cả dòng toàn NULL. Sau LEFT JOIN, COUNT(cột_bên_phải) cho ra 0 với dòng không khớp, còn COUNT(*) cho ra 1.
  • COUNT(DISTINCT col) dùng để chống fan-out (dòng cha bị nhân bản khi join xuống bảng con), ví dụ đếm chương khi đã join xuống bài học.
  • Hiệu năng: InnoDB không lưu sẵn số dòng, COUNT(*) trên bảng lớn vẫn phải quét một index.
ROW_NUMBER, RANK và DENSE_RANK khác nhau thế nào?

Cả ba là window function đánh số theo ORDER BY trong OVER; chúng khác nhau ở cách xử lý dòng đồng hạng.

Với điểm 10, 9, 9, 8:

  • ROW_NUMBER cho 1, 2, 3, 4: luôn khác nhau. Ai đứng trước trong hai dòng bằng nhau thì không xác định nếu không thêm tiêu chí phụ.
  • RANK cho 1, 2, 2, 4: đồng hạng rồi nhảy cóc.
  • DENSE_RANK cho 1, 2, 2, 3: đồng hạng, không nhảy.

“Top 5 giữ cả người đồng hạng” dùng DENSE_RANK hoặc RANK tuỳ cách hiểu. “Mỗi nhóm lấy đúng một dòng mới nhất” dùng ROW_NUMBER() OVER (PARTITION BY ...).

Senior

Khi nào bạn chủ động phi chuẩn hoá dữ liệu?

Khi một truy vấn đọc quan trọng phải join nhiều bảng hoặc tổng hợp lại liên tục, hoặc khi dữ liệu cần được “đóng băng” theo thời điểm.

  • Đọc nặng: tiến độ khoá học, số bình luận, tổng đơn hàng.
  • Đóng băng: giá trong đơn hàng, địa chỉ giao hàng.
  • Điều kiện: biết rõ nguồn sự thật, có cơ chế đồng bộ (cùng transaction, event, job đối soát) và chấp nhận được độ trễ hoặc chi phí ghi tăng thêm.
Một query báo cáo dùng DATE(created_at) = CURDATE() chạy ngày càng chậm. Vì sao, sửa thế nào?

Bọc cột trong hàm khiến biểu thức non-sargable, MySQL phải quét toàn bộ; sửa bằng cách viết lại thành một khoảng trên chính cột đó.

  • Vì sao: MySQL không dùng được B-tree index trên created_at, nên chậm dần khi bảng lớn.
  • Sửa: created_at >= CURDATE() AND created_at < CURDATE() + INTERVAL 1 DAY, kiểm tra bằng EXPLAIN.
  • Cách khác: functional index (MySQL 8.0.13+) hoặc generated column có index. Viết lại điều kiện vẫn là đơn giản nhất.

Từ vựng tiếng Anh

Term Phát âm Nghĩa Ví dụ câu tiếng Anh dùng trong phỏng vấn
aggregate function /ˈæɡrɪɡət ˈfʌŋkʃn/ hàm tổng hợp “COUNT and AVG are aggregate functions, so I filter on them in HAVING.”
correlated subquery /ˈkɔːrəleɪtɪd ˈsʌbkwɪri/ truy vấn con tương quan “An EXISTS correlated subquery avoids duplicate rows from the join.”
fan-out /ˈfæn aʊt/ nhân bản dòng khi join “Joining down to lessons fans out the chapters, so I count distinct ids.”
sargable /ˈsɑːrɡəbl/ dùng được index khi lọc “Wrapping the column in DATE() makes the predicate non-sargable.”
window function /ˈwɪndoʊ ˈfʌŋkʃn/ hàm cửa sổ “I used DENSE_RANK as a window function to keep ties in the top five.”
normalization /ˌnɔːrmələˈzeɪʃn/ chuẩn hoá dữ liệu “Normalization removes update anomalies; denormalization trades them for read speed.”
transitive dependency /ˈtrænsətɪv dɪˈpendənsi/ phụ thuộc bắc cầu “Department name depends on department id, which is a transitive dependency.”
cardinality /ˌkɑːr.dəˈnæl.ə.ti/ lực lượng quan hệ (1–1, 1–N, N–M) “The cardinality between students and courses is many-to-many.”
functional dependency /ˈfʌŋk.ʃən.əl dɪˈpen.dən.si/ phụ thuộc hàm “Every column has a functional dependency on the primary key, so MySQL accepts the GROUP BY.”

Ghi chép dựa trên khoá Fundamentals of Backend (Ronin Engineer), viết lại theo cách hiểu của mình.