TTư duy “quản trị dữ liệu và phân tích vận hành”.cho nghiệp vụ Data Analyst/ Data Governance Manager —giúp người quản trị có thể xem dữ liệu, để quản trị, kiểm soát, đánh giá chất lượng và ra quyết định.
🎯 MỤC TIÊU
Xây dựng bộ bài tập SQL dành cho Quản trị dữ liệu (Data Governance), áp dụng trên database bán hàng (ERD đã có), theo các trục quản trị thông tin:
- Chất lượng dữ liệu (Data Quality)
- Tính toàn vẹn và liên kết dữ liệu (Data Integrity)
- Bảo mật & quyền truy cập (Data Security & Access Control)
- Tính nhất quán & chuẩn hóa dữ liệu (Data Consistency & Standardization)
- Phân tích vận hành & hiệu suất (Operational Insight)
- Theo dõi thay đổi & kiểm soát phiên bản (Data Audit & Traceability)
🧩 CHỦ ĐỀ 1 – CHẤT LƯỢNG DỮ LIỆU (Data Quality)
| Cấp độ |
Câu hỏi quản trị |
Mục tiêu |
Gợi ý SQL |
| Cơ bản |
1️⃣ Kiểm tra bản ghi khách hàng bị thiếu số điện thoại |
Phát hiện dữ liệu thiếu |
SELECT customerNumber, customerName FROM customers WHERE phone IS NULL OR phone = ''; |
| Cơ bản |
2️⃣ Kiểm tra sản phẩm có giá âm hoặc bằng 0 |
Xác thực tính hợp lệ giá trị |
SELECT productCode, productName, buyPrice FROM products WHERE buyPrice <= 0; |
| Trung cấp |
3️⃣ Đếm tỷ lệ khách hàng thiếu mã bưu điện theo quốc gia |
Đo chất lượng dữ liệu theo khu vực |
SELECT country, COUNT(*) AS total, SUM(CASE WHEN postalCode IS NULL OR postalCode='' THEN 1 ELSE 0 END) AS missingZip FROM customers GROUP BY country; |
| Trung cấp |
4️⃣ Kiểm tra đơn hàng thiếu ngày giao hàng (shippedDate) |
Xác định dữ liệu chưa hoàn chỉnh |
SELECT orderNumber, orderDate FROM orders WHERE shippedDate IS NULL; |
| Nâng cao |
5️⃣ Đo “Data Completeness Score” theo bảng khách hàng |
Đánh giá định lượng chất lượng dữ liệu |
SELECT ROUND( (1 - (SUM(CASE WHEN phone IS NULL OR addressLine1 IS NULL THEN 1 ELSE 0 END)/COUNT(*))) * 100, 2) AS completenessPercent FROM customers; |
| Nâng cao |
6️⃣ Phân tích độ trễ nhập dữ liệu (ngày đơn hàng so với ngày thanh toán) |
Kiểm tra độ nhất quán thời gian nhập |
SELECT orderNumber, DATEDIFF(paymentDate, orderDate) AS delayDays FROM payments p JOIN orders o USING (customerNumber) WHERE DATEDIFF(paymentDate, orderDate) < 0; |
🔗 CHỦ ĐỀ 2 – TÍNH TOÀN VẸN DỮ LIỆU (Data Integrity)
| Cấp độ |
Câu hỏi |
Mục tiêu |
SQL Gợi ý |
| Cơ bản |
1️⃣ Kiểm tra sản phẩm trong orderdetails không tồn tại trong bảng products |
Kiểm tra ràng buộc khóa ngoại |
SELECT DISTINCT od.productCode FROM orderdetails od LEFT JOIN products p ON od.productCode = p.productCode WHERE p.productCode IS NULL; |
| Trung cấp |
2️⃣ Kiểm tra khách hàng có đơn hàng nhưng không có thông tin thanh toán |
Đảm bảo quan hệ “order ↔ payment” |
SELECT DISTINCT o.customerNumber FROM orders o LEFT JOIN payments p ON o.customerNumber = p.customerNumber WHERE p.customerNumber IS NULL; |
| Trung cấp |
3️⃣ Đếm tổng doanh thu từ bảng orders có khớp với tổng từ payments không |
Kiểm tra cân đối dữ liệu kế toán |
SELECT (SELECT SUM(od.quantityOrdered * od.priceEach) FROM orderdetails od) AS totalFromOrders, (SELECT SUM(amount) FROM payments) AS totalFromPayments; |
| Nâng cao |
4️⃣ Tạo báo cáo đối chiếu dữ liệu giữa hai bảng nguồn (sản phẩm & dòng sản phẩm) |
Kiểm tra phân cấp dữ liệu |
SELECT p.productLine, COUNT(p.productCode), pl.productLine FROM products p RIGHT JOIN productlines pl ON p.productLine = pl.productLine; |
| Nâng cao |
5️⃣ Phát hiện các mã khách hàng trùng lặp |
Phòng ngừa lỗi nhập liệu |
SELECT customerName, COUNT(*) FROM customers GROUP BY customerName HAVING COUNT(*) > 1; |
🔐 CHỦ ĐỀ 3 – BẢO MẬT & QUYỀN TRUY CẬP (Data Security)
| Cấp độ |
Câu hỏi |
Mục tiêu |
SQL Gợi ý |
| Cơ bản |
1️⃣ Liệt kê các user có quyền truy cập MySQL |
Kiểm tra người dùng hệ thống |
SELECT User, Host FROM mysql.user; |
| Trung cấp |
2️⃣ Kiểm tra quyền của user Quanlydb |
Đánh giá quyền phân quyền |
SHOW GRANTS FOR 'Quanlydb'@'%'; |
| Trung cấp |
3️⃣ Liệt kê các user có quyền GRANT OPTION |
Phát hiện user có thể cấp quyền cho người khác |
SELECT User, Host FROM mysql.user WHERE Super_priv = 'Y' OR Grant_priv = 'Y'; |
| Nâng cao |
4️⃣ Theo dõi lịch sử đăng nhập (log audit) để phát hiện truy cập bất thường |
Giám sát bảo mật |
(Phụ thuộc cấu hình audit log – nếu có SELECT * FROM mysql.general_log WHERE command_type='Connect' AND event_time >= NOW() - INTERVAL 1 DAY;) |
| Nâng cao |
5️⃣ Đánh giá quyền truy cập bảng chứa dữ liệu nhạy cảm (payments) |
Phân loại dữ liệu cần bảo vệ |
SHOW GRANTS FOR 'Hocvien'@'%'; + kiểm tra quyền trên schema chứa bảng payments |
🧱 CHỦ ĐỀ 4 – TÍNH NHẤT QUÁN & CHUẨN HÓA DỮ LIỆU
| Cấp độ |
Câu hỏi |
Mục tiêu |
SQL Gợi ý |
| Cơ bản |
1️⃣ Tìm các quốc gia viết sai chính tả (“US”, “USA”, “U.S.”, …) |
Chuẩn hóa giá trị danh mục |
SELECT DISTINCT country FROM customers ORDER BY country; |
| Trung cấp |
2️⃣ Chuẩn hóa định dạng mã bưu điện 5 ký tự |
Đánh giá quy tắc chuẩn hóa |
SELECT customerNumber, postalCode FROM customers WHERE LENGTH(postalCode) != 5; |
| Trung cấp |
3️⃣ Phát hiện sản phẩm có productLine không khớp bảng danh mục |
Kiểm tra consistency giữa bảng master & child |
SELECT DISTINCT p.productLine FROM products p LEFT JOIN productlines pl ON p.productLine = pl.productLine WHERE pl.productLine IS NULL; |
| Nâng cao |
4️⃣ Kiểm tra trùng tên sản phẩm khác productCode |
Dò dữ liệu bị nhập lặp |
SELECT productName, COUNT(DISTINCT productCode) FROM products GROUP BY productName HAVING COUNT(DISTINCT productCode) > 1; |
| Nâng cao |
5️⃣ Đánh giá chuẩn hóa bảng customers (tách thuộc tính lặp city, state, country) |
Định hướng chuẩn hóa 3NF |
→ phân tích qua truy vấn: SELECT city, COUNT(DISTINCT country) FROM customers GROUP BY city HAVING COUNT(DISTINCT country)>1; để phát hiện sai phạm logic chuẩn hóa. |
📈 CHỦ ĐỀ 5 – PHÂN TÍCH HIỆU SUẤT & VẬN HÀNH
| Cấp độ |
Câu hỏi |
Mục tiêu |
SQL Gợi ý |
| Cơ bản |
1️⃣ Đếm số đơn hàng theo tháng |
Theo dõi hoạt động bán hàng |
SELECT MONTH(orderDate), COUNT(*) FROM orders GROUP BY MONTH(orderDate); |
| Trung cấp |
2️⃣ Phân tích top 5 nhân viên doanh thu cao nhất |
Đánh giá hiệu suất nhân viên |
SELECT e.firstName, e.lastName, SUM(od.quantityOrdered * od.priceEach) AS revenue FROM employees e JOIN customers c ON e.employeeNumber=c.salesRepEmployeeNumber JOIN orders o ON c.customerNumber=o.customerNumber JOIN orderdetails od ON o.orderNumber=od.orderNumber GROUP BY e.employeeNumber ORDER BY revenue DESC LIMIT 5; |
| Trung cấp |
3️⃣ So sánh doanh thu trung bình theo dòng sản phẩm |
Đánh giá hiệu quả sản phẩm |
SELECT p.productLine, ROUND(AVG(od.quantityOrdered*od.priceEach),2) AS avgRevenue FROM products p JOIN orderdetails od USING(productCode) GROUP BY p.productLine; |
| Nâng cao |
4️⃣ Phân tích tỷ lệ đơn hàng giao trễ |
Giám sát SLA vận hành |
SELECT ROUND(SUM(CASE WHEN shippedDate > requiredDate THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS latePercent FROM orders; |
| Nâng cao |
5️⃣ Phân tích độ phủ khách hàng theo quốc gia |
Theo dõi mở rộng thị trường |
SELECT country, COUNT(DISTINCT customerNumber) AS totalCustomers FROM customers GROUP BY country ORDER BY totalCustomers DESC; |
🧾 CHỦ ĐỀ 6 – THEO DÕI & KIỂM SOÁT PHIÊN BẢN DỮ LIỆU (Data Audit)
| Cấp độ |
Câu hỏi |
Mục tiêu |
SQL Gợi ý |
| Cơ bản |
1️⃣ Ghi lại thời điểm cập nhật cuối cùng của bảng (nếu có cột lastUpdate) |
Kiểm tra vòng đời dữ liệu |
SELECT MAX(lastUpdate) FROM products; |
| Trung cấp |
2️⃣ Theo dõi ai đã sửa dữ liệu gần nhất (nếu bật audit) |
Truy vết thay đổi |
SELECT user_host, event_time, argument FROM mysql.general_log WHERE command_type='Query' AND event_time >= NOW() - INTERVAL 1 DAY; |
| Trung cấp |
3️⃣ Phát hiện đơn hàng bị chỉnh sửa trạng thái nhiều lần |
Kiểm soát integrity trạng thái |
SELECT orderNumber, COUNT(DISTINCT status) FROM orders GROUP BY orderNumber HAVING COUNT(DISTINCT status) > 1; |
| Nâng cao |
4️⃣ Thiết lập bảng audit_log ghi thay đổi khi UPDATE đơn hàng |
Thiết kế cơ chế giám sát dữ liệu |
Tạo trigger: sql CREATE TRIGGER order_audit AFTER UPDATE ON orders FOR EACH ROW INSERT INTO audit_log (orderNumber, oldStatus, newStatus, changedAt) VALUES (OLD.orderNumber, OLD.status, NEW.status, NOW()); |
| Nâng cao |
5️⃣ Báo cáo các thay đổi dữ liệu trong 7 ngày qua |
Giám sát định kỳ |
SELECT * FROM audit_log WHERE changedAt >= NOW() - INTERVAL 7 DAY; |
✅ TỔNG KẾT TƯ DUY
| Trục quản trị |
Mục tiêu quản trị |
Năng lực SQL liên quan |
| Data Quality |
Đảm bảo dữ liệu đầy đủ, hợp lệ |
IS NULL, CASE, COUNT |
| Integrity |
Đảm bảo quan hệ dữ liệu chính xác |
JOIN, LEFT JOIN, NOT EXISTS |
| Security |
Giám sát quyền & truy cập |
SHOW GRANTS, mysql.user |
| Consistency |
Chuẩn hóa, đồng bộ giá trị |
DISTINCT, GROUP BY, LENGTH |
| Operational Insight |
Hiệu suất & KPI |
SUM, AVG, RANK, DATE |
| Auditability |
Theo dõi thay đổi |
TRIGGER, LOG, timestamp |
