Ôn tập thực hành Cơ sở dữ liệu

01 · MỤC TIÊU & BỨC TRANH TỔNG QUAN

Mục tiêu

Sau Tuần 6, sinh viên có thể:

• Hệ thống lại toàn bộ kiến thức thực hành đã học
• Đọc và phân tích một bài toán quản lý thực tế
• Xác định được các Entity, Attribute, Relationship
• Chuyển từ mô hình ER/EER sang mô hình quan hệ
• Xác định Primary Key, Foreign Key, Candidate Key
• Tạo CSDL và các bảng bằng SQL, thiết lập các ràng buộc toàn vẹn
• Thực hiện SELECT, WHERE, ORDER BY, GROUP BY, HAVING, JOIN, Subquery, hàm tổng hợp, INSERT, UPDATE, DELETE
• Kết hợp nhiều kỹ thuật SQL để giải quyết một bài toán hoàn chỉnh
• Phát hiện và sửa các lỗi thường gặp khi viết truy vấn

Bức tranh tổng quan

Sinh viên cần nhìn lại toàn bộ quy trình xây dựng một CSDL:

02 · ÔN TẬP MÔ HÌNH CƠ SỞ DỮ LIỆU

Mô hình dữ liệu

Entity

Entity là đối tượng cần quản lý trong hệ thống. Ví dụ hệ thống quản lý sinh viên:

Attribute

Ví dụ Entity SINHVIEN:

03 · KHÓA TRONG CƠ SỞ DỮ LIỆU

Khóa trong CSDL

Primary Key

Primary Key dùng để định danh duy nhất một bản ghi:

CREATE TABLE SinhVien (
    MaSV VARCHAR(10) PRIMARY KEY,
    HoTen NVARCHAR(100),
    NgaySinh DATE
);

MaSV không được trùng và không được NULL.

Foreign Key

Foreign Key dùng để tạo mối quan hệ giữa các bảng:

CREATE TABLE SinhVien (
    MaSV VARCHAR(10) PRIMARY KEY,
    HoTen NVARCHAR(100),
    MaLop VARCHAR(10),

    FOREIGN KEY (MaLop)
        REFERENCES Lop(MaLop)
);

Quan hệ - Một lớp có nhiều sinh viên:

04 · ÔN TẬP TẠO CƠ SỞ DỮ LIỆU & INSERT

Tạo CSDL & INSERT

Ví dụ xây dựng CSDL Quản lý sinh viên và kết quả học tập, gồm 4 bảng LOP, SINHVIEN, MONHOC, KETQUA với quan hệ:

LOP (1) ➜ SINHVIEN (N): Một lớp có nhiều sinh viên.

SINHVIEN (1) ➜ KETQUA (N): Một sinh viên có nhiều kết quả học tập.

MONHOC (1) ➜ KETQUA (N): Một môn học có nhiều kết quả học tập.

Tạo bảng

CREATE TABLE Lop (
    MaLop VARCHAR(10) PRIMARY KEY,
    TenLop NVARCHAR(100)
);

CREATE TABLE MonHoc (
    MaMH VARCHAR(10) PRIMARY KEY,
    TenMH NVARCHAR(100),
    SoTinChi INT
);

CREATE TABLE SinhVien (
    MaSV VARCHAR(10) PRIMARY KEY,
    HoTen NVARCHAR(100),
    MaLop VARCHAR(10),

    FOREIGN KEY (MaLop)
        REFERENCES Lop(MaLop)
);

INSERT

INSERT INTO Lop
VALUES
('HTTT01', N'Hệ thống thông tin 01'),
('HTTT02', N'Hệ thống thông tin 02');

INSERT INTO SinhVien
VALUES
('SV001', N'Nguyễn Văn An', 'HTTT01'),
('SV002', N'Trần Văn Bình', 'HTTT01'),
('SV003', N'Lê Văn Cường', 'HTTT02');

05 · ÔN TẬP SELECT, WHERE & LIKE

SELECT, WHERE & LIKE

SELECT

Lấy toàn bộ dữ liệu:

SELECT *
FROM SinhVien;

Chọn một số cột và đặt bí danh:

SELECT
    MaSV AS [Mã sinh viên],
    HoTen AS [Họ tên]
FROM SinhVien;

WHERE

Tìm các sinh viên thuộc lớp HTTT01:

SELECT *
FROM SinhVien
WHERE MaLop = 'HTTT01';

Nhiều điều kiện:

SELECT *
FROM SinhVien
WHERE MaLop = 'HTTT01'
AND HoTen LIKE N'Nguyễn%';

LIKE - % và _

Xem chi tiết đầy đủ về hai ký tự đại diện ở Tuần 2 - Toán tử đặc biệt. Ôn tập nhanh:

-- Bắt đầu bằng "Nguyễn"
WHERE HoTen LIKE N'Nguyễn%'

-- Kết thúc bằng "An"
WHERE HoTen LIKE N'%An'

-- Có chứa "Văn"
WHERE HoTen LIKE N'%Văn%'

-- Đúng 4 ký tự, bắt đầu "PB0" + 1 ký tự bất kỳ
WHERE MaPB LIKE 'PB0_'

06 · ORDER BY & HÀM TỔNG HỢP

ORDER BY & hàm tổng hợp

ORDER BY

Sắp xếp sinh viên theo tên:

SELECT *
FROM SinhVien
ORDER BY HoTen ASC;   -- Đổi thành DESC để sắp xếp giảm dần

Hàm tổng hợp

Các hàm cần nhớ: COUNT(), SUM(), AVG(), MIN(), MAX(). Ví dụ có bao nhiêu sinh viên:

SELECT COUNT(*) AS SoLuongSinhVien
FROM SinhVien;

07 · GROUP BY & HAVING

GROUP BY & HAVING

GROUP BY

Đếm số sinh viên của từng lớp:

SELECT
    MaLop,
    COUNT(*) AS SoLuong
FROM SinhVien
GROUP BY MaLop;

HAVING

Chỉ lấy những lớp có từ 2 sinh viên trở lên:

SELECT
    MaLop,
    COUNT(*) AS SoLuong
FROM SinhVien
GROUP BY MaLop
HAVING COUNT(*) >= 2;

08 · ÔN TẬP JOIN

JOIN

Đây là nội dung trọng tâm của tuần ôn tập. Muốn hiển thị Mã SV, Họ tên, Tên lớp:

SELECT
    SV.MaSV,
    SV.HoTen,
    L.TenLop
FROM SinhVien SV
JOIN Lop L
    ON SV.MaLop = L.MaLop;

INNER JOIN

Chỉ lấy những bản ghi có quan hệ ở cả hai bảng - Nếu một lớp chưa có sinh viên, lớp đó không xuất hiện trong kết quả.

LEFT JOIN

Hiển thị tất cả các lớp, kể cả lớp chưa có sinh viên - Đây là dạng câu hỏi sinh viên rất dễ gặp trong bài thực hành:

SELECT
    L.MaLop,
    L.TenLop,
    SV.MaSV,
    SV.HoTen
FROM Lop L
LEFT JOIN SinhVien SV
    ON L.MaLop = SV.MaLop;

JOIN nhiều bảng

Ví dụ có LOP ➜ SINHVIEN ➜ KETQUA ➜ MONHOC. Truy vấn sinh viên nào học môn nào và đạt bao nhiêu điểm:

SELECT
    SV.MaSV,
    SV.HoTen,
    MH.TenMH,
    KQ.Diem
FROM SinhVien SV
JOIN KetQua KQ
    ON SV.MaSV = KQ.MaSV
JOIN MonHoc MH
    ON KQ.MaMH = MH.MaMH;

Đây là dạng truy vấn tổng hợp mà sinh viên cần thành thạo trước khi kết thúc phần thực hành.

09 · ÔN TẬP SUBQUERY

Subquery

Tìm sinh viên có điểm lớn hơn điểm trung bình:

SELECT *
FROM KetQua
WHERE Diem > (
    SELECT AVG(Diem)
    FROM KetQua
);

Tư duy:

10 · ÔN TẬP UPDATE & DELETE

UPDATE & DELETE

UPDATE

Sửa tên sinh viên:

UPDATE SinhVien
SET HoTen = N'Nguyễn Văn Nam'
WHERE MaSV = 'SV001';

DELETE

DELETE FROM SinhVien
WHERE MaSV = 'SV001';

Tương tự - Luôn kiểm tra WHERE trước khi DELETE.

11 · YÊU CẦU THỰC HÀNH

Yêu cầu thực hành

Bài thực hành chính thức của tuần này dùng lược đồ cổ điển "Nhà cung cấp - Phụ tùng - Vận chuyển" (Quản lý hàng hóa) - Schema, dữ liệu mẫu và toàn bộ 36 câu nằm trong trang bài tập bên dưới.

12 · BÀI TẬP THÁCH THỨC

Bài tập thách thức

Rèn luyện thêm với lược đồ Quản lý đào tạo (LOP, SINHVIEN, MONHOC, KETQUA) đã dùng xuyên suốt các mục lý thuyết ở trên - Không còn là bài thực hành chính thức, nhưng vẫn hữu ích để luyện phản xạ. Sinh viên khá/giỏi thực hiện thêm - Mỗi bài có gợi ý ẩn, bấm để mở.

13 · CÁC LỖI THƯỜNG GẶP

Các lỗi thường gặp

Lỗi 1 - JOIN sai điều kiện

-- Sai
JOIN Lop L ON SV.MaSV = L.MaLop

-- Đúng
JOIN Lop L ON SV.MaLop = L.MaLop

Lỗi 2 - Dùng WHERE thay cho HAVING

-- Sai tư duy
WHERE COUNT(*) > 2

-- Đúng
GROUP BY MaLop
HAVING COUNT(*) > 2

Lỗi 3 - Quên GROUP BY

-- Sai
SELECT MaLop, COUNT(*)
FROM SinhVien;

-- Phải có GROUP BY
SELECT MaLop, COUNT(*)
FROM SinhVien
GROUP BY MaLop;

Lỗi 4 - UPDATE/DELETE không có WHERE

Luôn kiểm tra bằng SELECT trước khi chạy UPDATE hoặc DELETE:

SELECT *
FROM SinhVien
WHERE MaSV = 'SV001';

14 · CHECKLIST ÔN TẬP

Checklist ôn tập

Sinh viên tự đánh giá trước khi thi thực hành:

Cơ sở dữ liệu

• Hiểu Entity, Attribute, Relationship
• Vẽ được ERD
• Xác định Primary Key, Foreign Key

SQL cơ bản

• CREATE DATABASE, CREATE TABLE
• INSERT, SELECT, WHERE, ORDER BY, LIKE

SQL nâng cao

• GROUP BY, HAVING
• COUNT, SUM, AVG, MIN, MAX
• INNER JOIN, LEFT JOIN, Subquery

Thao tác dữ liệu & tổng hợp

• UPDATE, DELETE có kiểm tra WHERE trước khi chạy
• Đọc đề, xác định bảng, quan hệ và khóa trước khi viết SQL
• Giải thích được câu SQL của mình