TUẦN 4 · BÀI TẬP THỰC HÀNH

Bài tập & đáp án

IT004BTTH4Quản lý giáo vụ

Đề bài đọc tự do. Phần đáp án và giải thích chi tiết chỉ dành cho sinh viên đang học lớp thực hành IT004, cần mã truy cập từ giảng viên.

ĐỀ BÀI · PHẦN I-III

Quản lý giáo vụ - Câu 1 ➜ 50

Tuần 4 luyện thêm các hàm mở rộng (CAST, GETDATE, LEFT, RIGHT), CASE WHEN và Alias/Subquery, sau đó áp dụng vào 50 câu chính trên lược đồ Quản lý giáo vụ: Phần I - Ngôn ngữ định nghĩa dữ liệu (câu 1-11), Phần II - Ngôn ngữ thao tác dữ liệu (câu 1-4), Phần III - Ngôn ngữ truy vấn dữ liệu (câu 1-35). Tự làm trước khi xem đáp án. Đề bài chi tiết trong file PDF bên dưới.

Đề bài đầy đủ - Bài tập thực hành Tuần 4 (BTTH4) File PDF · Quản lý giáo vụ

Tải file ↓

Dùng lược đồ dùng chung Quản lý giáo vụ (HOCVIEN, LOP, KHOA, MONHOC, DIEUKIEN, GIAOVIEN, GIANGDAY, KETQUATHI) - Xem đầy đủ thuộc tính, dữ liệu mẫu và script CSDL. Xem lược đồ ›

Yêu cầu nộp bài: Lưu bài làm trong 1 file script tên <MSSV>_<HoVaTen>_BTTH4.sql (MSSV là mã số sinh viên, HoVaTen là họ và tên).

Phần I - Ngôn ngữ định nghĩa dữ liệu (câu 1-11)

Tạo quan hệ và khai báo tất cả các ràng buộc khóa chính, khóa ngoại. Thêm vào 3 thuộc tính GHICHU, DIEMTB, XEPLOAI cho quan hệ HOCVIEN.
Thuộc tính GIOITINH chỉ có giá trị là "Nam" hoặc "Nu".
Điểm số của một lần thi có giá trị từ 0 đến 10 và cần lưu đến 2 số lẽ (VD: 6.22).
Kết quả thi là "Dat" nếu điểm từ 5 đến 10 và "Khong dat" nếu điểm nhỏ hơn 5.
Học viên thi một môn tối đa 3 lần.
Học kỳ chỉ có giá trị từ 1 đến 3.
Học vị của giáo viên chỉ có thể là "CN", "KS", "Ths", "TS", "PTS".
Học viên ít nhất là 18 tuổi.
Giảng dạy một môn học ngày bắt đầu (TUNGAY) phải nhỏ hơn ngày kết thúc (DENNGAY).
Giáo viên khi vào làm ít nhất là 22 tuổi.
Tất cả các môn học đều có số tín chỉ lý thuyết và tín chỉ thực hành chênh lệch nhau không quá 3.

Phần II - Ngôn ngữ thao tác dữ liệu (câu 1-4)

Tăng hệ số lương thêm 0.2 cho những giáo viên là trưởng khoa.
Cập nhật giá trị điểm trung bình tất cả các môn học (DIEMTB) của mỗi học viên (tất cả các môn học đều có hệ số 1 và nếu học viên thi một môn nhiều lần, chỉ lấy điểm của lần thi sau cùng).
Cập nhật giá trị cho cột GHICHU là "Cam thi" đối với trường hợp: Học viên có một môn bất kỳ thi lần thứ 3 dưới 5 điểm.
Cập nhật giá trị cho cột XEPLOAI trong quan hệ HOCVIEN như sau: Nếu DIEMTB ≥ 9 thì "XS" · nếu 8 ≤ DIEMTB < 9 thì "G" · nếu 6.5 ≤ DIEMTB < 8 thì "K" · nếu 5 ≤ DIEMTB < 6.5 thì "TB" · nếu DIEMTB < 5 thì "Y".

Phần III - Ngôn ngữ truy vấn dữ liệu (câu 1-35)

In ra danh sách (mã học viên, họ tên, ngày sinh, mã lớp) lớp trưởng của các lớp.
In ra bảng điểm khi thi (mã học viên, họ tên, lần thi, điểm số) môn CTRR của lớp "K12", sắp xếp theo tên, họ học viên.
In ra danh sách những học viên (mã học viên, họ tên) và những môn học mà học viên đó thi lần thứ nhất đã đạt.
In ra danh sách học viên (mã học viên, họ tên) của lớp "K11" thi môn CTRR không đạt (ở lần thi 1).
* Danh sách học viên (mã học viên, họ tên) của lớp "K" thi môn CTRR không đạt (ở tất cả các lần thi).
Tìm tên những môn học mà giáo viên có tên "Tran Tam Thanh" dạy trong học kỳ 1 năm 2006.
Tìm những môn học (mã môn học, tên môn học) mà giáo viên chủ nhiệm lớp "K11" dạy trong học kỳ 1 năm 2006.
Tìm họ tên lớp trưởng của các lớp mà giáo viên có tên "Nguyen To Lan" dạy môn "Co So Du Lieu".
In ra danh sách những môn học (mã môn học, tên môn học) phải học liền trước môn "Co So Du Lieu".
Môn "Cau Truc Roi Rac" là môn bắt buộc phải học liền trước những môn học (mã môn học, tên môn học) nào.
Tìm họ tên giáo viên dạy môn CTRR cho cả hai lớp "K11" và "K12" trong cùng học kỳ 1 năm 2006.
Tìm những học viên (mã học viên, họ tên) thi không đạt môn CSDL ở lần thi thứ 1 nhưng chưa thi lại môn này.
Tìm giáo viên (mã giáo viên, họ tên) không được phân công giảng dạy bất kỳ môn học nào.
Tìm giáo viên (mã giáo viên, họ tên) không được phân công giảng dạy bất kỳ môn học nào thuộc khoa giáo viên đó phụ trách.
Tìm họ tên các học viên thuộc lớp "K11" thi một môn bất kỳ quá 3 lần vẫn "Khong dat" hoặc thi lần thứ 2 môn CTRR được 5 điểm.
Tìm họ tên giáo viên dạy môn CTRR cho ít nhất hai lớp trong cùng một học kỳ của một năm học.
Danh sách học viên và điểm thi môn CSDL (chỉ lấy điểm của lần thi sau cùng).
Danh sách học viên và điểm thi môn "Co So Du Lieu" (chỉ lấy điểm cao nhất của các lần thi).
Khoa nào (mã khoa, tên khoa) được thành lập sớm nhất.
Có bao nhiêu giáo viên có học hàm là "GS" hoặc "PGS".
Thống kê có bao nhiêu giáo viên có học vị là "CN", "KS", "Ths", "TS", "PTS" trong mỗi khoa.
Mỗi môn học thống kê số lượng học viên theo kết quả (đạt và không đạt).
Tìm giáo viên (mã giáo viên, họ tên) là giáo viên chủ nhiệm của một lớp, đồng thời dạy cho lớp đó ít nhất một môn học.
Tìm họ tên lớp trưởng của lớp có sỉ số cao nhất.
* Tìm họ tên những LOPTRG thi không đạt quá 3 môn (mỗi môn đều thi không đạt ở tất cả các lần thi).
Tìm học viên (mã học viên, họ tên) có số môn đạt điểm 9,10 nhiều nhất.
Trong từng lớp, tìm học viên (mã học viên, họ tên) có số môn đạt điểm 9,10 nhiều nhất.
Trong từng học kỳ của từng năm, mỗi giáo viên phân công dạy bao nhiêu môn học, bao nhiêu lớp.
Trong từng học kỳ của từng năm, tìm giáo viên (mã giáo viên, họ tên) giảng dạy nhiều nhất.
Tìm môn học (mã môn học, tên môn học) có nhiều học viên thi không đạt (ở lần thi thứ 1) nhất.
Tìm học viên (mã học viên, họ tên) thi môn nào cũng đạt (chỉ xét lần thi thứ 1).
* Tìm học viên (mã học viên, họ tên) thi môn nào cũng đạt (chỉ xét lần thi sau cùng).
* Tìm học viên (mã học viên, họ tên) đã thi tất cả các môn đều đạt (chỉ xét lần thi thứ 1).
* Tìm học viên (mã học viên, họ tên) đã thi tất cả các môn đều đạt (chỉ xét lần thi sau cùng).
** Tìm học viên (mã học viên, họ tên) có điểm thi cao nhất trong từng môn (lấy điểm ở lần thi sau cùng).

ĐÁP ÁN

Xem đáp án

Nội dung được bảo vệ

Nhập mã truy cập để xem đáp án Tuần 4.

Mã truy cập được cung cấp bởi giảng viên.

Xác thực thành công - Bạn đã được cấp quyền truy cập đáp án Tuần 4.

Đáp án đầy đủ cho Practice 04 - Quản lý giáo vụ (Phần I 11 câu · Phần II 4 câu · Phần III 35 câu). Gõ lại từng câu vào SSMS để nhớ lâu hơn nhé - Nút Copy ở đây chỉ mang tính hình thức thôi.

CÂU 1 (PHẦN I) · CREATE TABLE

Đề bài: Tạo quan hệ và khai báo tất cả các ràng buộc khóa chính, khóa ngoại. Thêm vào 3 thuộc tính GHICHU, DIEMTB, XEPLOAI cho quan hệ HOCVIEN.

CREATE DATABASE QUANLIGIAOVU_2026;
GO
USE QUANLIGIAOVU_2026;
GO

CREATE TABLE KHOA
(
    MAKHOA CHAR(4) PRIMARY KEY,
    TENKHOA VARCHAR(40),
    NGTLAP SMALLDATETIME,
    TRGKHOA CHAR(4)
);

CREATE TABLE GIAOVIEN
(
    MAGV CHAR(4) PRIMARY KEY,
    HOTEN VARCHAR(40),
    HOCVI VARCHAR(10),
    HOCHAM VARCHAR(10),
    GIOITINH VARCHAR(3),
    NGSINH SMALLDATETIME,
    NGVL SMALLDATETIME,
    HESO NUMERIC(4,2),
    MUCLUONG MONEY,
    MAKHOA CHAR(4),

    FOREIGN KEY (MAKHOA) REFERENCES KHOA(MAKHOA)
);

ALTER TABLE KHOA ADD CONSTRAINT FK_KHOA_TRGKHOA
    FOREIGN KEY (TRGKHOA) REFERENCES GIAOVIEN(MAGV);

CREATE TABLE LOP
(
    MALOP CHAR(3) PRIMARY KEY,
    TENLOP VARCHAR(40),
    TRGLOP CHAR(5),
    SISO TINYINT,
    MAGVCN CHAR(4),

    FOREIGN KEY (MAGVCN) REFERENCES GIAOVIEN(MAGV)
);

CREATE TABLE HOCVIEN
(
    MAHV CHAR(5) PRIMARY KEY,
    HO VARCHAR(40),
    TEN VARCHAR(10),
    NGSINH SMALLDATETIME,
    GIOITINH VARCHAR(3),
    NOISINH VARCHAR(40),
    MALOP CHAR(3),
    GHICHU VARCHAR(20),
    DIEMTB NUMERIC(4,2),
    XEPLOAI VARCHAR(5),

    FOREIGN KEY (MALOP) REFERENCES LOP(MALOP)
);

ALTER TABLE LOP ADD CONSTRAINT FK_LOP_TRGLOP
    FOREIGN KEY (TRGLOP) REFERENCES HOCVIEN(MAHV);

CREATE TABLE MONHOC
(
    MAMH CHAR(10) PRIMARY KEY,
    TENMH VARCHAR(40),
    TCLT TINYINT,
    TCTH TINYINT,
    MAKHOA CHAR(4),

    FOREIGN KEY (MAKHOA) REFERENCES KHOA(MAKHOA)
);

CREATE TABLE DIEUKIEN
(
    MAMH CHAR(10),
    MAMH_TRUOC CHAR(10),

    PRIMARY KEY (MAMH, MAMH_TRUOC),
    FOREIGN KEY (MAMH) REFERENCES MONHOC(MAMH),
    FOREIGN KEY (MAMH_TRUOC) REFERENCES MONHOC(MAMH)
);

CREATE TABLE GIANGDAY
(
    MALOP CHAR(3),
    MAMH CHAR(10),
    MAGV CHAR(4),
    HOCKY TINYINT,
    NAM SMALLINT,
    TUNGAY SMALLDATETIME,
    DENNGAY SMALLDATETIME,

    PRIMARY KEY (MALOP, MAMH),
    FOREIGN KEY (MALOP) REFERENCES LOP(MALOP),
    FOREIGN KEY (MAMH) REFERENCES MONHOC(MAMH),
    FOREIGN KEY (MAGV) REFERENCES GIAOVIEN(MAGV)
);

CREATE TABLE KETQUATHI
(
    MAHV CHAR(5),
    MAMH CHAR(10),
    LANTHI TINYINT,
    NGTHI SMALLDATETIME,
    DIEM NUMERIC(4,2),
    KQUA VARCHAR(10),

    PRIMARY KEY (MAHV, MAMH, LANTHI),
    FOREIGN KEY (MAHV) REFERENCES HOCVIEN(MAHV),
    FOREIGN KEY (MAMH) REFERENCES MONHOC(MAMH)
);

LÀ GÌ?

Cả 8 quan hệ đều tạo bằng CREATE TABLE, khóa ngoại khai báo ngay trong phần định nghĩa cột khi bảng đích đã tồn tại; 3 cột mới GHICHU, DIEMTB, XEPLOAI được thêm thẳng vào HOCVIEN ngay từ lúc tạo bảng.

VÌ SAO?

Thứ tự tạo bảng phải theo đúng chiều phụ thuộc khóa ngoại - GIAOVIEN đợi KHOA, HOCVIEN đợi LOP. Riêng KHOA.TRGKHOA ↔ GIAOVIEN.MAKHOA và LOP.TRGLOP ↔ HOCVIEN.MALOP tham chiếu vòng lẫn nhau nên không thể khai báo cả 2 khóa ngoại cùng lúc trong CREATE TABLE - Phải tạo trước 1 chiều, dùng ALTER TABLE ADD CONSTRAINT bổ sung chiều còn lại sau khi bảng kia đã tồn tại.

CÂU 2 (PHẦN I) · CHECK ... IN

Đề bài: Thuộc tính GIOITINH chỉ có giá trị là "Nam" hoặc "Nu".

ALTER TABLE HOCVIEN ADD CONSTRAINT CK_HOCVIEN_GIOITINH
    CHECK (GIOITINH IN (N'Nam', N'Nu'));
ALTER TABLE GIAOVIEN ADD CONSTRAINT CK_GIAOVIEN_GIOITINH
    CHECK (GIOITINH IN (N'Nam', N'Nu'));

LÀ GÌ?

CHECK ... IN giới hạn cột chỉ nhận đúng 2 giá trị liệt kê, không phân biệt cột đó thuộc bảng nào.

VÌ SAO?

GIOITINH xuất hiện ở cả HOCVIEN lẫn GIAOVIEN trong lược đồ này, đề không chỉ rõ quan hệ nào - Áp dụng ràng buộc cho cả 2 để đảm bảo tính nhất quán dữ liệu ở mọi nơi cột này xuất hiện.

CÂU 3 (PHẦN I) · CHECK

Đề bài: Điểm số của một lần thi có giá trị từ 0 đến 10 và cần lưu đến 2 số lẽ (VD: 6.22).

ALTER TABLE KETQUATHI ADD CONSTRAINT CK_KETQUATHI_DIEM
    CHECK (DIEM BETWEEN 0 AND 10);

Cách khác (kiểm tra định dạng chuỗi)

ALTER TABLE KETQUATHI ADD CONSTRAINT CK_KETQUATHI_DIEM
    CHECK (
        DIEM BETWEEN 0 AND 10
        AND RIGHT(CAST(DIEM AS VARCHAR), 3) LIKE '.__'
    );

RIGHT(CAST(DIEM AS VARCHAR), 3) LIKE '.__' lấy 3 ký tự cuối của chuỗi số rồi đòi hỏi đúng dạng "dấu chấm + 2 ký tự bất kỳ" (VD "6.22" ➜ ".22" khớp mẫu). Cách này hữu ích khi cột không có kiểu dữ liệu cố định thang đo (scale) sẵn - Ở đây NUMERIC(4,2) đã tự đảm bảo đúng 2 số lẻ nên không bắt buộc, nhưng vẫn là kỹ thuật đáng biết khi cần ràng buộc định dạng hiển thị của 1 số thay vì chỉ ràng buộc giá trị.

LÀ GÌ?

BETWEEN 0 AND 10 giới hạn khoảng giá trị đóng ở cả 2 đầu (0 và 10 đều hợp lệ).

VÌ SAO?

Phần "lưu đến 2 số lẻ" đã được đáp ứng ngay từ kiểu dữ liệu NUMERIC(4,2) khai báo cho DIEM ở Câu 1 (4 chữ số, 2 số sau dấu phẩy) - Câu này chỉ còn thiếu ràng buộc khoảng giá trị 0-10.

CÂU 4 (PHẦN I) · CHECK

Đề bài: Kết quả thi là "Dat" nếu điểm từ 5 đến 10 và "Khong dat" nếu điểm nhỏ hơn 5.

ALTER TABLE KETQUATHI ADD CONSTRAINT CK_KETQUATHI_KQUA
    CHECK (
        (DIEM >= 5 AND KQUA = N'Dat')
        OR (DIEM < 5 AND KQUA = N'Khong dat')
    );

LÀ GÌ?

CHECK ràng buộc quan hệ giữa 2 cột trong cùng 1 dòng - Ở đây là DIEM và KQUA phải luôn khớp nhau.

VÌ SAO?

Không thể tách thành 2 CHECK riêng vì 2 điều kiện phụ thuộc lẫn nhau (KQUA đúng hay sai phụ thuộc vào DIEM) - Gộp cả 2 trường hợp trong 1 CHECK bằng OR đảm bảo dữ liệu luôn nhất quán, dù DIEM cao hay thấp.

CÂU 5 (PHẦN I) · CHECK

Đề bài: Học viên thi một môn tối đa 3 lần.

ALTER TABLE KETQUATHI ADD CONSTRAINT CK_KETQUATHI_LANTHI
    CHECK (LANTHI BETWEEN 1 AND 3);

LÀ GÌ?

Giới hạn giá trị LANTHI chỉ được là 1, 2 hoặc 3.

VÌ SAO?

Khóa chính (MAHV, MAMH, LANTHI) đã đảm bảo mỗi cặp (học viên, môn học) không trùng lần thi - Kết hợp với CHECK giới hạn LANTHI ≤ 3, một học viên chỉ có thể có tối đa 3 dòng kết quả thi cho cùng 1 môn.

CÂU 6 (PHẦN I) · CHECK

Đề bài: Học kỳ chỉ có giá trị từ 1 đến 3.

ALTER TABLE GIANGDAY ADD CONSTRAINT CK_GIANGDAY_HOCKY
    CHECK (HOCKY BETWEEN 1 AND 3);

Cách khác (IN)

ALTER TABLE GIANGDAY ADD CONSTRAINT CK_GIANGDAY_HOCKY
    CHECK (HOCKY IN (1, 2, 3));

IN (1, 2, 3) cho kết quả tương đương BETWEEN 1 AND 3 vì đây là số nguyên liên tiếp - BETWEEN gọn hơn khi khoảng giá trị liên tục, còn IN phù hợp hơn nếu sau này danh sách học kỳ hợp lệ không còn liên tục (VD chỉ còn 1 và 3, bỏ học kỳ 2).

LÀ GÌ?

Kỹ thuật giống Câu 5, áp dụng cho cột HOCKY của GIANGDAY.

VÌ SAO?

Một năm học chỉ có 3 học kỳ theo quy định của trung tâm - BETWEEN 1 AND 3 chặn mọi giá trị học kỳ không hợp lệ ngay từ lúc nhập liệu.

CÂU 7 (PHẦN I) · CHECK ... IN

Đề bài: Học vị của giáo viên chỉ có thể là "CN", "KS", "Ths", "TS", "PTS".

ALTER TABLE GIAOVIEN ADD CONSTRAINT CK_GIAOVIEN_HOCVI
    CHECK (HOCVI IN (N'CN', N'KS', N'Ths', N'TS', N'PTS'));

LÀ GÌ?

CHECK ... IN với danh sách đóng 5 giá trị học vị hợp lệ.

VÌ SAO?

Đề liệt kê đúng 5 học vị, không có dấu "…" ở cuối - Đây là danh sách đóng nên IN (...) là lựa chọn phù hợp.

CÂU 8 (PHẦN I) · CHECK ... GETDATE

Đề bài: Học viên ít nhất là 18 tuổi.

ALTER TABLE HOCVIEN ADD CONSTRAINT CK_HOCVIEN_TUOI
    CHECK (NGSINH <= DATEADD(YEAR, -18, GETDATE()));

Cách khác (YEAR)

ALTER TABLE HOCVIEN ADD CONSTRAINT CK_HOCVIEN_TUOI
    CHECK (YEAR(GETDATE()) - YEAR(NGSINH) >= 18);

Cách này ngắn gọn và thường gặp, nhưng chỉ so sánh số năm chứ không xét tháng/ngày sinh - Học viên sinh ngày 31/12 vẫn bị tính đủ 18 tuổi ngay từ đầu năm đó dù thực ra còn thiếu gần 1 năm nữa mới tròn tuổi. Cách DATEADD ở trên chính xác đến từng ngày nên vẫn là lựa chọn nên dùng.

Lỗi thường gặp: Viết CHECK (GETDATE() - NGSINH >= 18) (trừ trực tiếp 2 cột ngày rồi so với số 18) trông giống hướng đúng nhưng thực chất sai hoàn toàn. Hiệu số 2 giá trị ngày tháng trong SQL Server trả về số ngày chênh lệch, không phải số năm - So sánh số ngày đó với số nguyên 18 chỉ tương đương kiểm tra "đã sinh được từ 18 ngày trở lên", không phải 18 năm.

LÀ GÌ?

DATEADD(YEAR, -18, GETDATE()) tính ra ngày đúng 18 năm trước ngày hiện tại; học viên hợp lệ phải sinh trước hoặc đúng ngày đó.

VÌ SAO?

SQL Server cho phép CHECK tham chiếu GETDATE(), nhưng ràng buộc chỉ được kiểm tra tại thời điểm INSERT/UPDATE - Một học viên hợp lệ lúc nhập liệu vẫn giữ nguyên trong bảng dù thời gian trôi qua, CHECK không tự động rà soát lại các dòng đã có.

CÂU 9 (PHẦN I) · CHECK

Đề bài: Giảng dạy một môn học ngày bắt đầu (TUNGAY) phải nhỏ hơn ngày kết thúc (DENNGAY).

ALTER TABLE GIANGDAY ADD CONSTRAINT CK_GIANGDAY_NGAY
    CHECK (TUNGAY < DENNGAY);

LÀ GÌ?

So sánh trực tiếp 2 cột ngày tháng trong cùng 1 dòng.

VÌ SAO?

Một môn học không thể kết thúc trước khi bắt đầu - Ràng buộc này giữ cho khoảng thời gian giảng dạy luôn hợp lý về mặt logic.

CÂU 10 (PHẦN I) · CHECK

Đề bài: Giáo viên khi vào làm ít nhất là 22 tuổi.

ALTER TABLE GIAOVIEN ADD CONSTRAINT CK_GIAOVIEN_TUOI_VAOLAM
    CHECK (NGVL >= DATEADD(YEAR, 22, NGSINH));

Cách khác (YEAR)

ALTER TABLE GIAOVIEN ADD CONSTRAINT CK_GIAOVIEN_TUOI_VAOLAM
    CHECK (YEAR(NGVL) - YEAR(NGSINH) >= 22);

Cùng nhược điểm với cách YEAR ở Câu 8 - Không xét chính xác đến tháng/ngày, nên có thể tính dư vài tháng tuổi cho giáo viên sinh cuối năm. Lưu ý: Viết CHECK ((NGVL - NGSINH) >= 22) (trừ trực tiếp 2 cột ngày) là sai giống hệt lỗi ở Câu 8 - Kết quả phép trừ là số ngày chênh lệch, so với 22 chỉ kiểm tra "cách nhau ít nhất 22 ngày", không phải 22 năm.

LÀ GÌ?

DATEADD(YEAR, 22, NGSINH) tính ngày giáo viên tròn 22 tuổi; NGVL phải từ ngày đó trở đi.

VÌ SAO?

Khác Câu 8, ràng buộc này so sánh 2 cột trong cùng 1 dòng (NGSINH và NGVL) thay vì so với GETDATE() - Nhờ vậy nó luôn đúng và ổn định vĩnh viễn, không bị lỗi thời như CHECK dùng ngày hiện tại.

CÂU 11 (PHẦN I) · CHECK ... ABS

Đề bài: Tất cả các môn học đều có số tín chỉ lý thuyết và tín chỉ thực hành chênh lệch nhau không quá 3.

ALTER TABLE MONHOC ADD CONSTRAINT CK_MONHOC_TC
    CHECK (ABS(TCLT - TCTH) <= 3);

LÀ GÌ?

ABS(...) lấy giá trị tuyệt đối, nên chênh lệch được tính đúng dù TCLT lớn hơn hay nhỏ hơn TCTH.

VÌ SAO?

Đề chỉ nói "chênh lệch nhau không quá 3", không quan tâm tín chỉ nào nhiều hơn - Nếu viết TCLT - TCTH <= 3 mà thiếu ABS, trường hợp TCTH lớn hơn TCLT nhiều vẫn lọt qua được, sai với ý nghĩa đề bài.

CÂU 1 (PHẦN II) · UPDATE ... SUBQUERY

Đề bài: Tăng hệ số lương thêm 0.2 cho những giáo viên là trưởng khoa.

UPDATE GIAOVIEN
SET HESO = HESO + 0.2
WHERE MAGV IN (
    SELECT TRGKHOA FROM KHOA WHERE TRGKHOA IS NOT NULL
);

LÀ GÌ?

Subquery lấy toàn bộ MAGV đang là TRGKHOA của bất kỳ khoa nào, dùng IN để lọc đúng những giáo viên đó trong UPDATE.

VÌ SAO?

TRGKHOA có thể NULL (khoa KTMT chưa có trưởng khoa). Với IN, lọc NULL không bắt buộc vì NULL không được so khớp nhưng cũng không làm sai kết quả - Tuy nhiên nên giữ thói quen lọc IS NOT NULL, vì nếu đổi sang NOT IN ở bài khác, sự hiện diện của NULL trong danh sách sẽ làm cả câu lệnh trả về rỗng, đây là lỗi rất dễ mắc phải.

CÂU 2 (PHẦN II) · UPDATE ... CORRELATED SUBQUERY

Đề bài: Cập nhật giá trị điểm trung bình tất cả các môn học (DIEMTB) của mỗi học viên (tất cả các môn học đều có hệ số 1 và nếu học viên thi một môn nhiều lần, chỉ lấy điểm của lần thi sau cùng).

UPDATE HOCVIEN
SET DIEMTB = (
    SELECT AVG(kq.DIEM)
    FROM KETQUATHI kq
    WHERE kq.MAHV = HOCVIEN.MAHV
      AND kq.LANTHI = (
          SELECT MAX(kq2.LANTHI)
          FROM KETQUATHI kq2
          WHERE kq2.MAHV = kq.MAHV AND kq2.MAMH = kq.MAMH
      )
);

Cách khác (UPDATE ... FROM ... JOIN)

UPDATE HOCVIEN
SET DIEMTB = t.Diem
FROM HOCVIEN
JOIN (
    SELECT kq.MAHV, AVG(kq.DIEM) AS Diem
    FROM KETQUATHI kq
    WHERE kq.LANTHI = (
        SELECT MAX(kq2.LANTHI) FROM KETQUATHI kq2
        WHERE kq2.MAHV = kq.MAHV AND kq2.MAMH = kq.MAMH
    )
    GROUP BY kq.MAHV
) t ON HOCVIEN.MAHV = t.MAHV;

Cú pháp UPDATE ... FROM ... JOIN (đặc trưng của SQL Server) tính điểm trung bình 1 lần bằng GROUP BY trong bảng dẫn xuất t, rồi JOIN thẳng vào HOCVIEN - Cho cùng kết quả với cách scalar subquery ở trên, nhưng chỉ chạy 1 lượt tính toán cho tất cả học viên thay vì lặp lại subquery cho từng dòng, nên thường nhanh hơn trên bảng lớn. Mẹo an toàn: Chạy thử phần SELECT bên trong t trước khi UPDATE để kiểm tra số liệu đúng chưa.

LÀ GÌ?

Subquery lồng trong cùng dùng MAX(LANTHI) để chỉ lấy đúng lần thi sau cùng của từng cặp (MAHV, MAMH); subquery ngoài AVG(DIEM) trên đúng tập đã lọc đó cho từng học viên.

VÌ SAO?

Không thể AVG(DIEM) trực tiếp trên toàn bộ KETQUATHI vì 1 học viên có thể có nhiều dòng cho cùng 1 môn (thi lại) - Nếu không lọc lần thi sau cùng, điểm trung bình sẽ bị lệch do tính luôn cả những lần thi trước đó chưa đạt.

CÂU 3 (PHẦN II) · UPDATE ... SUBQUERY

Đề bài: Cập nhật giá trị cho cột GHICHU là "Cam thi" đối với trường hợp: Học viên có một môn bất kỳ thi lần thứ 3 dưới 5 điểm.

UPDATE HOCVIEN
SET GHICHU = N'Cam thi'
WHERE MAHV IN (
    SELECT MAHV
    FROM KETQUATHI
    WHERE LANTHI = 3 AND DIEM < 5
);

LÀ GÌ?

Subquery lấy MAHV của mọi dòng kết quả thi lần 3 mà điểm dưới 5, dùng IN để đánh dấu đúng những học viên đó.

VÌ SAO?

Chỉ cần 1 môn thỏa điều kiện là đủ để bị "Cam thi" ("một môn bất kỳ") - Không cần GROUP BY hay đếm số lượng, chỉ cần học viên đó xuất hiện ít nhất 1 lần trong tập kết quả đã lọc.

CÂU 4 (PHẦN II) · UPDATE ... CASE WHEN

Đề bài: Cập nhật giá trị cho cột XEPLOAI trong quan hệ HOCVIEN như sau: Nếu DIEMTB ≥ 9 thì "XS" · nếu 8 ≤ DIEMTB < 9 thì "G" · nếu 6.5 ≤ DIEMTB < 8 thì "K" · nếu 5 ≤ DIEMTB < 6.5 thì "TB" · nếu DIEMTB < 5 thì "Y".

UPDATE HOCVIEN
SET XEPLOAI = CASE
    WHEN DIEMTB >= 9 THEN N'XS'
    WHEN DIEMTB >= 8 THEN N'G'
    WHEN DIEMTB >= 6.5 THEN N'K'
    WHEN DIEMTB >= 5 THEN N'TB'
    ELSE N'Y'
END;

LÀ GÌ?

CASE WHEN xét lần lượt từ ngưỡng cao nhất xuống thấp nhất - SQL Server dừng ở điều kiện đúng đầu tiên nên không cần viết đủ cả cận trên lẫn cận dưới cho mỗi nhánh.

VÌ SAO?

Ví dụ nhánh "G" chỉ cần viết DIEMTB >= 8 mà không cần thêm AND DIEMTB < 9, vì nếu DIEMTB thật sự ≥ 9 thì nhánh "XS" phía trên đã khớp trước và CASE dừng lại ở đó rồi - Viết thêm điều kiện cận trên là thừa.

CÂU 1 (PHẦN III) · JOIN

Đề bài: In ra danh sách (mã học viên, họ tên, ngày sinh, mã lớp) lớp trưởng của các lớp.

SELECT hv.MAHV, hv.HO + N' ' + hv.TEN AS HOTEN, hv.NGSINH, hv.MALOP
FROM HOCVIEN hv
JOIN LOP l ON hv.MAHV = l.TRGLOP;

Cách khác (subquery IN)

SELECT MAHV, HO + N' ' + TEN AS HOTEN, NGSINH, MALOP
FROM HOCVIEN
WHERE MAHV IN (SELECT TRGLOP FROM LOP);

Vì đề chỉ cần thông tin từ HOCVIEN (không cần cột nào của LOP), có thể thay JOIN bằng subquery IN để lọc trực tiếp trên HOCVIEN mà không cần join bảng nào cả - Ngắn gọn hơn, nhưng chỉ dùng được khi không cần lấy thêm cột nào từ bảng còn lại.

LÀ GÌ?

JOIN HOCVIEN với LOP qua TRGLOP để lấy đúng những học viên đang là lớp trưởng; HO + ' ' + TEN ghép thành họ tên đầy đủ vì HOCVIEN lưu 2 cột riêng.

VÌ SAO?

Dùng JOIN (không phải subquery) vì cần lấy đủ 4 cột thông tin học viên - Đề yêu cầu cả họ tên và ngày sinh nên phải join trực tiếp vào bảng HOCVIEN.

CÂU 2 (PHẦN III) · JOIN ... ORDER BY

Đề bài: In ra bảng điểm khi thi (mã học viên, họ tên, lần thi, điểm số) môn CTRR của lớp "K12", sắp xếp theo tên, họ học viên.

SELECT hv.MAHV, hv.HO + N' ' + hv.TEN AS HOTEN, kq.LANTHI, kq.DIEM
FROM HOCVIEN hv
JOIN KETQUATHI kq ON hv.MAHV = kq.MAHV
WHERE hv.MALOP = N'K12' AND kq.MAMH = N'CTRR'
ORDER BY hv.TEN, hv.HO;

LÀ GÌ?

JOIN HOCVIEN với KETQUATHI qua MAHV, lọc đúng lớp và môn học; ORDER BY TEN trước HO vì "sắp xếp theo tên, họ" nghĩa là ưu tiên tên trước.

VÌ SAO?

Không cần JOIN thêm LOP vì MALOP đã có sẵn ngay trong HOCVIEN - Không cần đi qua bảng LOP mới lọc được lớp "K12".

CÂU 3 (PHẦN III) · JOIN

Đề bài: In ra danh sách những học viên (mã học viên, họ tên) và những môn học mà học viên đó thi lần thứ nhất đã đạt.

SELECT hv.MAHV, hv.HO + N' ' + hv.TEN AS HOTEN, kq.MAMH
FROM HOCVIEN hv
JOIN KETQUATHI kq ON hv.MAHV = kq.MAHV
WHERE kq.LANTHI = 1 AND kq.KQUA = N'Dat';

Cách khác (lọc theo DIEM)

SELECT hv.MAHV, hv.HO + N' ' + hv.TEN AS HOTEN, kq.MAMH
FROM HOCVIEN hv
JOIN KETQUATHI kq ON hv.MAHV = kq.MAHV
WHERE kq.LANTHI = 1 AND kq.DIEM >= 5;

Nhờ CHECK ràng buộc ở Câu 4 (Phần I) đảm bảo DIEM và KQUA luôn khớp nhau (DIEM >= 5 khi và chỉ khi KQUA = 'Dat'), lọc theo DIEM >= 5 hay KQUA = 'Dat' luôn cho cùng 1 kết quả - Chọn cột nào cũng được, miễn ràng buộc đó còn hiệu lực.

LÀ GÌ?

Lọc trực tiếp KETQUATHI theo LANTHI = 1 và KQUA = 'Dat', JOIN sang HOCVIEN chỉ để lấy họ tên.

VÌ SAO?

Đề yêu cầu in luôn "những môn học" đạt được ở lần 1 - Nên phải giữ lại MAMH trong kết quả, không GROUP BY hay gộp lại, mỗi dòng là 1 cặp (học viên, môn học) thỏa điều kiện.

CÂU 4 (PHẦN III) · JOIN

Đề bài: In ra danh sách học viên (mã học viên, họ tên) của lớp "K11" thi môn CTRR không đạt (ở lần thi 1).

SELECT hv.MAHV, hv.HO + N' ' + hv.TEN AS HOTEN
FROM HOCVIEN hv
JOIN KETQUATHI kq ON hv.MAHV = kq.MAHV
WHERE hv.MALOP = N'K11' AND kq.MAMH = N'CTRR'
  AND kq.LANTHI = 1 AND kq.KQUA = N'Khong Dat';

Cách khác (lọc theo DIEM)

SELECT hv.MAHV, hv.HO + N' ' + hv.TEN AS HOTEN
FROM HOCVIEN hv
JOIN KETQUATHI kq ON hv.MAHV = kq.MAHV
WHERE hv.MALOP = N'K11' AND kq.MAMH = N'CTRR'
  AND kq.LANTHI = 1 AND kq.DIEM < 5;

Cùng lý do với Câu 3 - DIEM < 5 và KQUA = 'Khong Dat' luôn đi cùng nhau nhờ ràng buộc CHECK ở Câu 4 (Phần I), nên 2 cách lọc cho kết quả giống hệt nhau.

LÀ GÌ?

JOIN rồi lọc 4 điều kiện cùng lúc bằng AND: Đúng lớp, đúng môn, đúng lần thi, đúng kết quả.

VÌ SAO?

Chỉ xét "lần thi 1" nên phải lọc rõ LANTHI = 1 - Nếu bỏ điều kiện này, học viên thi lại nhiều lần vẫn có thể lọt vào kết quả dù lần thi sau đã đạt.

CÂU 5 (PHẦN III) · NOT EXISTS / NOT IN

Đề bài: * Danh sách học viên (mã học viên, họ tên) của lớp "K" thi môn CTRR không đạt (ở tất cả các lần thi).

SELECT DISTINCT hv.MAHV, hv.HO + N' ' + hv.TEN AS HOTEN
FROM HOCVIEN hv
JOIN KETQUATHI kq ON hv.MAHV = kq.MAHV
WHERE hv.MALOP LIKE N'K%' AND kq.MAMH = N'CTRR'
  AND NOT EXISTS (
      SELECT * FROM KETQUATHI kq2
      WHERE kq2.MAHV = hv.MAHV AND kq2.MAMH = N'CTRR' AND kq2.KQUA = N'Dat'
  );

Cách khác (NOT IN)

SELECT DISTINCT hv.MAHV, hv.HO + N' ' + hv.TEN AS HOTEN
FROM HOCVIEN hv
JOIN KETQUATHI kq ON hv.MAHV = kq.MAHV
WHERE hv.MALOP LIKE N'K%' AND kq.MAMH = N'CTRR'
  AND hv.MAHV NOT IN (
      SELECT MAHV FROM KETQUATHI WHERE MAMH = N'CTRR' AND KQUA = N'Dat'
  );

NOT IN chỉ an toàn ở đây vì KETQUATHI.MAHV là 1 phần khóa chính - Không bao giờ NULL, nên danh sách con không dính bẫy NULL làm NOT IN trả về rỗng.

LÀ GÌ?

JOIN với KETQUATHI lọc đúng học viên có thi môn CTRR (vừa đảm bảo họ có thi, vừa lọc theo lớp); NOT EXISTS đảm bảo không có lần thi CTRR nào đạt - Kết hợp lại nghĩa là mọi lần thi CTRR của học viên này đều không đạt.

VÌ SAO?

"Lớp 'K'" ở đây nên hiểu là tiền tố mã lớp - Toàn bộ lớp trong lược đồ này đều có mã bắt đầu bằng "K" (K11, K12, K13), nên "lớp 'K'" tương đương với MALOP LIKE 'K%' (tất cả các lớp), không phải 1 lớp cụ thể - Đây là kiểu ra đề bằng tiền tố giống Câu 3 (Tuần 2, mã sản phẩm bắt đầu bằng "B"). Nếu chỉ lọc KQUA = 'Khong Dat' như Câu 4, học viên thi lại nhiều lần và có 1 lần đạt vẫn bị liệt kê nhầm - Phải chắc chắn không có lần thi nào đạt mới đúng nghĩa "ở tất cả các lần thi". DISTINCT tránh trùng dòng vì JOIN KETQUATHI có thể khớp nhiều lần thi CTRR của cùng 1 học viên.

CÂU 6 (PHẦN III) · JOIN ... DISTINCT

Đề bài: Tìm tên những môn học mà giáo viên có tên "Tran Tam Thanh" dạy trong học kỳ 1 năm 2006.

SELECT DISTINCT mh.TENMH
FROM GIANGDAY gd
JOIN GIAOVIEN gv ON gd.MAGV = gv.MAGV
JOIN MONHOC mh ON gd.MAMH = mh.MAMH
WHERE gv.HOTEN = N'Tran Tam Thanh' AND gd.HOCKY = 1 AND gd.NAM = 2006;

LÀ GÌ?

JOIN 3 bảng GIANGDAY - GIAOVIEN - MONHOC để đi từ tên giáo viên ra tên môn học; DISTINCT vì 1 giáo viên có thể dạy cùng môn cho nhiều lớp trong cùng học kỳ (trùng dòng).

VÌ SAO?

Đề chỉ yêu cầu "tên môn học" (không cần mã), và theo dữ liệu mẫu giáo viên GV02 dạy CTRR cho cả K11 lẫn K12 trong cùng học kỳ 1/2006 - DISTINCT tránh in trùng tên môn.

CÂU 7 (PHẦN III) · SUBQUERY

Đề bài: Tìm những môn học (mã môn học, tên môn học) mà giáo viên chủ nhiệm lớp "K11" dạy trong học kỳ 1 năm 2006.

SELECT DISTINCT mh.MAMH, mh.TENMH
FROM GIANGDAY gd
JOIN MONHOC mh ON gd.MAMH = mh.MAMH
WHERE gd.MAGV = (SELECT MAGVCN FROM LOP WHERE MALOP = N'K11')
  AND gd.HOCKY = 1 AND gd.NAM = 2006;

LÀ GÌ?

Subquery lấy đúng 1 mã giáo viên chủ nhiệm của lớp K11 từ LOP.MAGVCN, rồi lọc GIANGDAY theo đúng giáo viên đó và đúng học kỳ/năm.

VÌ SAO?

Dùng subquery độc lập với dấu = (thay vì IN) vì mỗi lớp chỉ có đúng 1 giáo viên chủ nhiệm, subquery chắc chắn trả về 1 giá trị - Không cần join thêm bảng LOP vào truy vấn chính.

CÂU 8 (PHẦN III) · SUBQUERY ... IN

Đề bài: Tìm họ tên lớp trưởng của các lớp mà giáo viên có tên "Nguyen To Lan" dạy môn "Co So Du Lieu".

SELECT DISTINCT hv.HO + N' ' + hv.TEN AS HOTEN
FROM LOP l
JOIN HOCVIEN hv ON l.TRGLOP = hv.MAHV
WHERE l.MALOP IN (
    SELECT gd.MALOP
    FROM GIANGDAY gd
    JOIN GIAOVIEN gv ON gd.MAGV = gv.MAGV
    JOIN MONHOC mh ON gd.MAMH = mh.MAMH
    WHERE gv.HOTEN = N'Nguyen To Lan' AND mh.TENMH = N'Co So Du Lieu'
);

LÀ GÌ?

Subquery tìm các mã lớp mà giáo viên "Nguyen To Lan" dạy môn "Co So Du Lieu"; truy vấn ngoài JOIN LOP với HOCVIEN qua TRGLOP để lấy họ tên lớp trưởng của những lớp đó.

VÌ SAO?

1 giáo viên có thể dạy môn này cho nhiều lớp khác nhau nên subquery dùng IN (không phải =) - DISTINCT phòng trường hợp trùng lớp trưởng nếu có nhiều dòng GIANGDAY khớp cùng 1 lớp.

CÂU 9 (PHẦN III) · SUBQUERY

Đề bài: In ra danh sách những môn học (mã môn học, tên môn học) phải học liền trước môn "Co So Du Lieu".

SELECT mh.MAMH, mh.TENMH
FROM DIEUKIEN dk
JOIN MONHOC mh ON dk.MAMH_TRUOC = mh.MAMH
WHERE dk.MAMH = (SELECT MAMH FROM MONHOC WHERE TENMH = N'Co So Du Lieu');

LÀ GÌ?

Subquery tra mã môn học ứng với tên "Co So Du Lieu"; DIEUKIEN.MAMH_TRUOC liệt kê những môn phải học trước môn đó, JOIN MONHOC để lấy tên.

VÌ SAO?

Đề cho tên môn chứ không cho mã môn, nên phải tra mã trước bằng subquery - Không nên gõ cứng mã môn trực tiếp vào câu lệnh dù biết trước dữ liệu mẫu, vì cách viết phụ thuộc tên vẫn đúng ngay cả khi mã môn học đổi khác.

CÂU 10 (PHẦN III) · SUBQUERY

Đề bài: Môn "Cau Truc Roi Rac" là môn bắt buộc phải học liền trước những môn học (mã môn học, tên môn học) nào.

SELECT mh.MAMH, mh.TENMH
FROM DIEUKIEN dk
JOIN MONHOC mh ON dk.MAMH = mh.MAMH
WHERE dk.MAMH_TRUOC = (SELECT MAMH FROM MONHOC WHERE TENMH = N'Cau Truc Roi Rac');

LÀ GÌ?

Ngược chiều với Câu 9 - Lần này lọc theo MAMH_TRUOC rồi lấy MAMH (môn học yêu cầu môn CTRR làm điều kiện tiên quyết).

VÌ SAO?

DIEUKIEN lưu cặp (MAMH, MAMH_TRUOC), Câu 9 đi từ MAMH tìm MAMH_TRUOC, Câu 10 đi ngược lại từ MAMH_TRUOC tìm MAMH - Cùng 1 bảng nhưng lọc theo cột khác nhau tùy chiều câu hỏi.

CÂU 11 (PHẦN III) · SUBQUERY ... IN

Đề bài: Tìm họ tên giáo viên dạy môn CTRR cho cả hai lớp "K11" và "K12" trong cùng học kỳ 1 năm 2006.

SELECT gv.HOTEN
FROM GIAOVIEN gv
WHERE gv.MAGV IN (
    SELECT MAGV FROM GIANGDAY
    WHERE MALOP = N'K11' AND MAMH = N'CTRR' AND HOCKY = 1 AND NAM = 2006
)
AND gv.MAGV IN (
    SELECT MAGV FROM GIANGDAY
    WHERE MALOP = N'K12' AND MAMH = N'CTRR' AND HOCKY = 1 AND NAM = 2006
);

Cách khác (INTERSECT)

(SELECT gv.HOTEN
 FROM GIAOVIEN gv JOIN GIANGDAY gd ON gv.MAGV = gd.MAGV
 WHERE gd.MAMH = N'CTRR' AND gd.HOCKY = 1 AND gd.NAM = 2006 AND gd.MALOP = N'K11')
INTERSECT
(SELECT gv.HOTEN
 FROM GIAOVIEN gv JOIN GIANGDAY gd ON gv.MAGV = gd.MAGV
 WHERE gd.MAMH = N'CTRR' AND gd.HOCKY = 1 AND gd.NAM = 2006 AND gd.MALOP = N'K12');

INTERSECT lấy giao của 2 tập kết quả - Danh sách giáo viên dạy K11 giao với danh sách giáo viên dạy K12 chính là những giáo viên dạy cả 2 lớp, cùng ý nghĩa với 2 IN nối bằng AND ở trên nhưng diễn đạt trực tiếp bằng phép toán tập hợp.

LÀ GÌ?

2 subquery độc lập, mỗi subquery lấy MAGV dạy CTRR cho đúng 1 lớp (K11 hoặc K12) trong học kỳ 1/2006; giáo viên hợp lệ phải thỏa cả 2 điều kiện IN cùng lúc.

VÌ SAO?

Không thể gộp chung 1 điều kiện MALOP IN ('K11','K12') vì như vậy chỉ cần dạy 1 trong 2 lớp là đã lọt qua - Phải tách 2 subquery riêng, nối bằng AND, để bắt buộc giáo viên đó dạy cho đủ cả 2 lớp.

CÂU 12 (PHẦN III) · NOT EXISTS

Đề bài: Tìm những học viên (mã học viên, họ tên) thi không đạt môn CSDL ở lần thi thứ 1 nhưng chưa thi lại môn này.

SELECT hv.MAHV, hv.HO + N' ' + hv.TEN AS HOTEN
FROM HOCVIEN hv
JOIN KETQUATHI kq ON hv.MAHV = kq.MAHV
WHERE kq.MAMH = N'CSDL' AND kq.LANTHI = 1 AND kq.KQUA = N'Khong Dat'
  AND NOT EXISTS (
      SELECT * FROM KETQUATHI kq2
      WHERE kq2.MAHV = hv.MAHV AND kq2.MAMH = N'CSDL' AND kq2.LANTHI > 1
  );

LÀ GÌ?

Điều kiện chính lọc đúng lần thi 1 không đạt môn CSDL; NOT EXISTS kiểm tra không tồn tại bất kỳ dòng nào có LANTHI > 1 cho cùng học viên, cùng môn.

VÌ SAO?

"Chưa thi lại" nghĩa là không có bản ghi nào ở lần thi thứ 2 trở đi - Dùng LANTHI > 1 (không phải cố định LANTHI = 2) để chắc chắn bắt được cả trường hợp hiếm gặp học viên đã có LANTHI = 3 nhưng thiếu LANTHI = 2 trong dữ liệu (lược đồ không có ràng buộc bắt buộc lần thi phải liên tục) - NOT EXISTS là công cụ đúng để kiểm tra "không tồn tại", khác với chỉ đếm hay lọc trực tiếp.

CÂU 13 (PHẦN III) · NOT EXISTS

Đề bài: Tìm giáo viên (mã giáo viên, họ tên) không được phân công giảng dạy bất kỳ môn học nào.

SELECT gv.MAGV, gv.HOTEN
FROM GIAOVIEN gv
WHERE NOT EXISTS (
    SELECT * FROM GIANGDAY gd WHERE gd.MAGV = gv.MAGV
);

Cách khác (LEFT JOIN)

SELECT gv.MAGV, gv.HOTEN
FROM GIAOVIEN gv LEFT JOIN GIANGDAY gd ON gv.MAGV = gd.MAGV
WHERE gd.MAGV IS NULL;

LEFT JOIN giữ lại tất cả giáo viên kể cả khi không khớp được dòng GIANGDAY nào (cột phía GIANGDAY sẽ là NULL); lọc WHERE gd.MAGV IS NULL chính là cách "anti-join" kinh điển để tìm những dòng không có đối tác khớp, cho cùng kết quả với NOT EXISTS.

LÀ GÌ?

NOT EXISTS kiểm tra giáo viên đó không xuất hiện trong bất kỳ dòng GIANGDAY nào.

VÌ SAO?

Đây là dạng truy vấn phủ định toàn phần (không có bất kỳ) - NOT EXISTS luôn là lựa chọn an toàn nhất, tránh được bẫy NULL mà NOT IN có thể gặp phải.

CÂU 14 (PHẦN III) · NOT EXISTS

Đề bài: Tìm giáo viên (mã giáo viên, họ tên) không được phân công giảng dạy bất kỳ môn học nào thuộc khoa giáo viên đó phụ trách.

SELECT gv.MAGV, gv.HOTEN
FROM GIAOVIEN gv
WHERE NOT EXISTS (
    SELECT * FROM GIANGDAY gd
    JOIN MONHOC mh ON gd.MAMH = mh.MAMH
    WHERE gd.MAGV = gv.MAGV AND mh.MAKHOA = gv.MAKHOA
);

Cách khác (LEFT JOIN)

SELECT gv.MAGV, gv.HOTEN
FROM GIAOVIEN gv
LEFT JOIN GIANGDAY gd ON gv.MAGV = gd.MAGV
LEFT JOIN MONHOC mh ON gd.MAMH = mh.MAMH AND mh.MAKHOA = gv.MAKHOA
WHERE mh.MAMH IS NULL;

Cùng kỹ thuật anti-join như Câu 13, nhưng điều kiện "đúng khoa của giáo viên" (mh.MAKHOA = gv.MAKHOA) phải đặt ngay trong mệnh đề ON của LEFT JOIN MONHOC (không phải WHERE) - Nếu đặt trong WHERE, những dòng không khớp bị LEFT JOIN giữ lại (đã có MONHOC là NULL) sẽ bị loại nhầm trước khi kịp xét đến điều kiện IS NULL.

LÀ GÌ?

NOT EXISTS kiểm tra không tồn tại dòng GIANGDAY nào của giáo viên đó ứng với 1 môn học thuộc đúng khoa của giáo viên (mh.MAKHOA = gv.MAKHOA).

VÌ SAO?

Khác Câu 13 (không dạy môn nào, bất kỳ khoa nào), câu này chỉ xét các môn thuộc khoa của chính giáo viên đó - Giáo viên có thể dạy môn của khoa khác vẫn thỏa điều kiện, miễn là không dạy môn nào của khoa mình.

CÂU 15 (PHẦN III) · EXISTS ... OR

Đề bài: Tìm họ tên các học viên thuộc lớp "K11" thi một môn bất kỳ quá 3 lần vẫn "Khong dat" hoặc thi lần thứ 2 môn CTRR được 5 điểm.

SELECT DISTINCT hv.HO + N' ' + hv.TEN AS HOTEN
FROM HOCVIEN hv
WHERE hv.MALOP = N'K11'
  AND (
      EXISTS (
          SELECT * FROM KETQUATHI kq
          WHERE kq.MAHV = hv.MAHV AND kq.LANTHI = 3 AND kq.KQUA = N'Khong Dat'
      )
      OR EXISTS (
          SELECT * FROM KETQUATHI kq
          WHERE kq.MAHV = hv.MAHV AND kq.MAMH = N'CTRR' AND kq.LANTHI = 2 AND kq.DIEM = 5
      )
  );

LÀ GÌ?

2 nhánh EXISTS nối bằng OR trong cùng 1 dấu ngoặc: Nhánh 1 kiểm tra có môn nào thi đến lần 3 (lần tối đa) mà vẫn không đạt; nhánh 2 kiểm tra thi CTRR lần 2 đúng 5 điểm.

VÌ SAO?

"Thi một môn bất kỳ quá 3 lần vẫn không đạt" - Theo ràng buộc LANTHI tối đa là 3 (Câu 5, Phần I) - Được hiểu là đã thi đến lần thứ 3 (lần cuối cùng được phép) mà vẫn "Khong dat", tức đã hết cơ hội thi lại; đây là cách diễn đạt gần nhất với ràng buộc dữ liệu thực tế của lược đồ. Lưu ý bẫy: Nếu viết điều kiện theo đúng câu chữ thành LANTHI > 3, nhánh này sẽ luôn rỗng (không bao giờ đúng), vì CHECK ở Câu 5 (Phần I) đã chặn LANTHI không bao giờ vượt quá 3 - Phải hiểu "quá 3 lần vẫn không đạt" theo đúng cơ chế ràng buộc của lược đồ (đã dùng hết 3 lần thi), chứ không thể dịch nguyên văn từ "quá 3" sang > 3.

CÂU 16 (PHẦN III) · GROUP BY ... HAVING

Đề bài: Tìm họ tên giáo viên dạy môn CTRR cho ít nhất hai lớp trong cùng một học kỳ của một năm học.

SELECT gv.HOTEN
FROM GIAOVIEN gv
WHERE gv.MAGV IN (
    SELECT MAGV
    FROM GIANGDAY
    WHERE MAMH = N'CTRR'
    GROUP BY MAGV, HOCKY, NAM
    HAVING COUNT(DISTINCT MALOP) >= 2
);

LÀ GÌ?

GROUP BY theo (MAGV, HOCKY, NAM) rồi đếm số lớp khác nhau bằng COUNT(DISTINCT MALOP); HAVING >= 2 lọc đúng những nhóm dạy từ 2 lớp trở lên.

VÌ SAO?

Phải GROUP BY cả HOCKY và NAM (không chỉ MAGV) vì đề yêu cầu "trong cùng một học kỳ của một năm học" - Nếu chỉ GROUP BY MAGV, 1 giáo viên dạy K11 học kỳ 1 và K12 học kỳ 2 (2 lớp khác học kỳ) sẽ bị đếm nhầm là thỏa điều kiện. Lưu ý: Phải GROUP BY theo MAGV (mã, là khóa chính) rồi mới lấy HOTEN ra sau - Nếu gộp nhóm trực tiếp theo HOTEN thay vì MAGV, 2 giáo viên trùng tên (nếu có) sẽ vô tình bị gộp chung thành 1 nhóm, sai lệch kết quả đếm.

CÂU 17 (PHẦN III) · SUBQUERY ... MAX

Đề bài: Danh sách học viên và điểm thi môn CSDL (chỉ lấy điểm của lần thi sau cùng).

SELECT hv.MAHV, hv.HO + N' ' + hv.TEN AS HOTEN, kq.DIEM
FROM HOCVIEN hv
JOIN KETQUATHI kq ON hv.MAHV = kq.MAHV
WHERE kq.MAMH = N'CSDL'
  AND kq.LANTHI = (
      SELECT MAX(kq2.LANTHI) FROM KETQUATHI kq2
      WHERE kq2.MAHV = kq.MAHV AND kq2.MAMH = N'CSDL'
  );

LÀ GÌ?

Subquery MAX(LANTHI) tìm lần thi sau cùng của từng học viên cho môn CSDL; điều kiện ngoài chỉ giữ đúng dòng khớp lần thi đó.

VÌ SAO?

Học viên có thể thi lại môn CSDL nhiều lần - Không lọc lần thi sau cùng sẽ in ra cả những điểm của các lần thi trước, sai với yêu cầu đề bài.

CÂU 18 (PHẦN III) · GROUP BY ... MAX

Đề bài: Danh sách học viên và điểm thi môn "Co So Du Lieu" (chỉ lấy điểm cao nhất của các lần thi).

SELECT hv.MAHV, hv.HO + N' ' + hv.TEN AS HOTEN, MAX(kq.DIEM) AS DIEM
FROM HOCVIEN hv
JOIN KETQUATHI kq ON hv.MAHV = kq.MAHV
WHERE kq.MAMH = (SELECT MAMH FROM MONHOC WHERE TENMH = N'Co So Du Lieu')
GROUP BY hv.MAHV, hv.HO, hv.TEN;

LÀ GÌ?

GROUP BY theo từng học viên rồi lấy MAX(DIEM) - Khác Câu 17 (lọc theo lần thi sau cùng), câu này lấy đúng điểm số cao nhất bất kể lần thi nào.

VÌ SAO?

"Điểm cao nhất của các lần thi" khác hẳn "điểm lần thi sau cùng" ở Câu 17 - 1 học viên có thể đạt điểm cao nhất ở lần thi đầu rồi thi lại bị điểm thấp hơn, MAX(DIEM) luôn lấy đúng giá trị cao nhất bất kể thứ tự lần thi.

CÂU 19 (PHẦN III) · SUBQUERY ... MIN

Đề bài: Khoa nào (mã khoa, tên khoa) được thành lập sớm nhất.

SELECT MAKHOA, TENKHOA
FROM KHOA
WHERE NGTLAP = (SELECT MIN(NGTLAP) FROM KHOA);

LÀ GÌ?

Subquery MIN(NGTLAP) tìm ngày thành lập sớm nhất; truy vấn ngoài lọc đúng khoa có ngày thành lập bằng giá trị đó.

VÌ SAO?

Không dùng ORDER BY kèm TOP 1 vì nếu có 2 khoa cùng thành lập sớm nhất (trùng ngày), TOP 1 sẽ bỏ sót 1 khoa - So sánh trực tiếp với MIN(...) luôn lấy đủ tất cả các khoa đồng hạng.

CÂU 20 (PHẦN III) · COUNT

Đề bài: Có bao nhiêu giáo viên có học hàm là "GS" hoặc "PGS".

SELECT COUNT(*) AS SoLuong
FROM GIAOVIEN
WHERE HOCHAM IN (N'GS', N'PGS');

LÀ GÌ?

COUNT(*) đếm số dòng thỏa điều kiện HOCHAM thuộc 1 trong 2 giá trị.

VÌ SAO?

HOCHAM có thể NULL (giáo viên chưa có học hàm) - IN (...) tự động bỏ qua các dòng NULL mà không cần thêm điều kiện IS NOT NULL riêng.

CÂU 21 (PHẦN III) · GROUP BY

Đề bài: Thống kê có bao nhiêu giáo viên có học vị là "CN", "KS", "Ths", "TS", "PTS" trong mỗi khoa.

SELECT MAKHOA, HOCVI, COUNT(*) AS SoLuong
FROM GIAOVIEN
WHERE HOCVI IN (N'CN', N'KS', N'Ths', N'TS', N'PTS')
GROUP BY MAKHOA, HOCVI;

LÀ GÌ?

GROUP BY theo cặp (MAKHOA, HOCVI), COUNT(*) đếm số giáo viên trong từng nhóm.

VÌ SAO?

Phải GROUP BY cả 2 cột cùng lúc vì đề cần thống kê chi tiết đến từng (khoa, học vị) - Nếu chỉ GROUP BY MAKHOA sẽ gộp lẫn các học vị khác nhau vào 1 dòng.

CÂU 22 (PHẦN III) · GROUP BY

Đề bài: Mỗi môn học thống kê số lượng học viên theo kết quả (đạt và không đạt).

SELECT MAMH, KQUA, COUNT(*) AS SoLuong
FROM KETQUATHI
GROUP BY MAMH, KQUA;

Cách khác (pivot bằng CASE WHEN)

SELECT MAMH,
    COUNT(CASE WHEN KQUA = N'Dat' THEN 1 END) AS SoLuongDat,
    COUNT(CASE WHEN KQUA = N'Khong Dat' THEN 1 END) AS SoLuongKhongDat
FROM KETQUATHI
GROUP BY MAMH;

COUNT(CASE WHEN ... THEN 1 END) chỉ đếm những dòng khớp điều kiện trong CASE (các dòng không khớp trả về NULL, và COUNT luôn bỏ qua NULL) - Kỹ thuật này "xoay" (pivot) kết quả từ nhiều dòng (mỗi KQUA 1 dòng) thành 1 dòng duy nhất mỗi môn học với 2 cột riêng biệt, dễ đọc hơn khi cần so sánh Đạt/Không đạt cạnh nhau.

LÀ GÌ?

GROUP BY theo cặp (MAMH, KQUA) để đếm số lượt thi ứng với từng kết quả của từng môn.

VÌ SAO?

Đếm theo lượt thi (mỗi dòng KETQUATHI), không loại trừ học viên thi lại nhiều lần - Trong ngữ cảnh bảng KETQUATHI, mỗi dòng đúng 1 lượt thi nên đếm trực tiếp theo dòng là hợp lý nhất; 1 học viên thi lại có thể vừa góp mặt ở nhóm "Khong Dat" (lần trước) vừa ở nhóm "Dat" (lần sau) của cùng 1 môn.

CÂU 23 (PHẦN III) · EXISTS

Đề bài: Tìm giáo viên (mã giáo viên, họ tên) là giáo viên chủ nhiệm của một lớp, đồng thời dạy cho lớp đó ít nhất một môn học.

SELECT gv.MAGV, gv.HOTEN
FROM GIAOVIEN gv
JOIN LOP l ON gv.MAGV = l.MAGVCN
WHERE EXISTS (
    SELECT * FROM GIANGDAY gd WHERE gd.MAGV = gv.MAGV AND gd.MALOP = l.MALOP
);

LÀ GÌ?

JOIN GIAOVIEN với LOP qua MAGVCN để xác định giáo viên chủ nhiệm lớp nào; EXISTS kiểm tra giáo viên đó có dạy môn nào cho đúng lớp mình chủ nhiệm không.

VÌ SAO?

Phải so khớp đúng MALOP giữa GIANGDAY và LOP đang xét (gd.MALOP = l.MALOP) - Nếu chỉ kiểm tra "có dạy môn nào đó" mà không ràng buộc đúng lớp, giáo viên chủ nhiệm lớp này nhưng dạy môn cho lớp khác vẫn bị tính sai.

CÂU 24 (PHẦN III) · SUBQUERY ... MAX

Đề bài: Tìm họ tên lớp trưởng của lớp có sỉ số cao nhất.

SELECT hv.HO + N' ' + hv.TEN AS HOTEN
FROM LOP l
JOIN HOCVIEN hv ON l.TRGLOP = hv.MAHV
WHERE l.SISO = (SELECT MAX(SISO) FROM LOP);

LÀ GÌ?

Subquery MAX(SISO) tìm sỉ số lớn nhất; truy vấn ngoài JOIN LOP - HOCVIEN qua TRGLOP để lấy họ tên lớp trưởng của đúng lớp đó.

VÌ SAO?

Cách viết này tự động lấy đủ nhiều lớp nếu có nhiều lớp cùng đồng hạng sỉ số cao nhất, không bỏ sót như TOP 1 - Cùng lý do đã dùng ở Câu 19.

CÂU 25 (PHẦN III) · SCALAR SUBQUERY

Đề bài: * Tìm họ tên những LOPTRG thi không đạt quá 3 môn (mỗi môn đều thi không đạt ở tất cả các lần thi).

SELECT hv.MAHV, hv.HO + N' ' + hv.TEN AS HOTEN
FROM HOCVIEN hv
WHERE hv.MAHV IN (SELECT TRGLOP FROM LOP)
  AND (
      SELECT COUNT(DISTINCT kq.MAMH)
      FROM KETQUATHI kq
      WHERE kq.MAHV = hv.MAHV
        AND NOT EXISTS (
            SELECT * FROM KETQUATHI kq2
            WHERE kq2.MAHV = kq.MAHV AND kq2.MAMH = kq.MAMH AND kq2.KQUA = N'Dat'
        )
  ) > 3;

LÀ GÌ?

Subquery đếm COUNT(DISTINCT MAMH), trong đó với mỗi môn, NOT EXISTS đảm bảo không có lần thi nào đạt (giống ý tưởng Câu 5) - Kết quả là số môn mà học viên đó chưa từng đạt dù đã thi.

VÌ SAO?

hv.MAHV IN (SELECT TRGLOP FROM LOP) lọc đúng những học viên đang là lớp trưởng (LOPTRG) của bất kỳ lớp nào; điều kiện đếm > 3 đặt trong dấu ngoặc riêng vì đây là scalar subquery so sánh trực tiếp, không phải HAVING (vì truy vấn ngoài không GROUP BY).

CÂU 26 (PHẦN III) · GROUP BY ... TOP WITH TIES

Đề bài: Tìm học viên (mã học viên, họ tên) có số môn đạt điểm 9,10 nhiều nhất.

SELECT TOP 1 WITH TIES hv.MAHV, hv.HO + N' ' + hv.TEN AS HOTEN
FROM HOCVIEN hv
JOIN KETQUATHI kq ON hv.MAHV = kq.MAHV
WHERE kq.DIEM IN (9, 10)
GROUP BY hv.MAHV, hv.HO, hv.TEN
ORDER BY COUNT(*) DESC;

LÀ GÌ?

GROUP BY theo học viên, đếm số lượt điểm 9 hoặc 10; TOP 1 WITH TIES giữ nguyên tất cả học viên đồng hạng cao nhất thay vì chỉ lấy đúng 1 dòng.

VÌ SAO?

Cần TOP ... WITH TIES (không phải TOP 1 thường) vì có thể có nhiều học viên cùng đạt số lượng điểm 9-10 nhiều nhất bằng nhau - TOP 1 thường sẽ chỉ giữ ngẫu nhiên 1 trong số đó.

CÂU 27 (PHẦN III) · CORRELATED SUBQUERY ... MAX

Đề bài: Trong từng lớp, tìm học viên (mã học viên, họ tên) có số môn đạt điểm 9,10 nhiều nhất.

SELECT t.MALOP, t.MAHV, t.HOTEN, t.SoMon
FROM (
    SELECT hv.MALOP, hv.MAHV, hv.HO + N' ' + hv.TEN AS HOTEN, COUNT(*) AS SoMon
    FROM HOCVIEN hv
    JOIN KETQUATHI kq ON hv.MAHV = kq.MAHV
    WHERE kq.DIEM IN (9, 10)
    GROUP BY hv.MALOP, hv.MAHV, hv.HO, hv.TEN
) t
WHERE t.SoMon = (
    SELECT MAX(t2.SoMon)
    FROM (
        SELECT hv2.MALOP, hv2.MAHV, COUNT(*) AS SoMon
        FROM HOCVIEN hv2
        JOIN KETQUATHI kq2 ON hv2.MAHV = kq2.MAHV
        WHERE kq2.DIEM IN (9, 10)
        GROUP BY hv2.MALOP, hv2.MAHV
    ) t2
    WHERE t2.MALOP = t.MALOP
);

LÀ GÌ?

Truy vấn con t tính số môn đạt điểm 9-10 của từng học viên; truy vấn con t2 tính lại số liệu tương tự rồi lấy MAX theo đúng lớp tương ứng (t2.MALOP = t.MALOP) để so sánh riêng cho từng lớp.

VÌ SAO?

Khác Câu 26 (so sánh toàn trường), câu này phải tìm giá trị lớn nhất riêng cho từng lớp - Không thể dùng chung 1 giá trị MAX như Câu 19/24, nên phải tương quan (correlated) MAX theo đúng MALOP đang xét.

CÂU 28 (PHẦN III) · GROUP BY ... COUNT DISTINCT

Đề bài: Trong từng học kỳ của từng năm, mỗi giáo viên phân công dạy bao nhiêu môn học, bao nhiêu lớp.

SELECT MAGV, HOCKY, NAM, COUNT(DISTINCT MAMH) AS SoMonHoc, COUNT(DISTINCT MALOP) AS SoLop
FROM GIANGDAY
GROUP BY MAGV, HOCKY, NAM;

LÀ GÌ?

GROUP BY theo (MAGV, HOCKY, NAM); COUNT(DISTINCT MAMH) đếm số môn khác nhau, COUNT(DISTINCT MALOP) đếm số lớp khác nhau trong cùng nhóm.

VÌ SAO?

Phải dùng DISTINCT trong COUNT vì 1 giáo viên có thể xuất hiện nhiều dòng GIANGDAY khác lớp nhưng cùng môn (hoặc ngược lại) trong cùng học kỳ/năm - Đếm thẳng COUNT(*) sẽ ra số dòng phân công, không phải số môn/số lớp thực tế.

CÂU 29 (PHẦN III) · CORRELATED SUBQUERY ... MAX

Đề bài: Trong từng học kỳ của từng năm, tìm giáo viên (mã giáo viên, họ tên) giảng dạy nhiều nhất.

SELECT t.MAGV, gv.HOTEN, t.HOCKY, t.NAM, t.SoLuot
FROM (
    SELECT MAGV, HOCKY, NAM, COUNT(*) AS SoLuot
    FROM GIANGDAY
    GROUP BY MAGV, HOCKY, NAM
) t
JOIN GIAOVIEN gv ON t.MAGV = gv.MAGV
WHERE t.SoLuot = (
    SELECT MAX(t2.SoLuot)
    FROM (
        SELECT MAGV, COUNT(*) AS SoLuot
        FROM GIANGDAY
        WHERE HOCKY = t.HOCKY AND NAM = t.NAM
        GROUP BY MAGV
    ) t2
);

LÀ GÌ?

Truy vấn con t tính số lượt phân công (số dòng GIANGDAY) của từng giáo viên theo từng (học kỳ, năm); so sánh với MAX của đúng học kỳ/năm đó (tương quan qua HOCKY, NAM) để giữ lại giáo viên dạy nhiều nhất trong từng nhóm.

VÌ SAO?

Tương tự Câu 27, "nhiều nhất" ở đây là nhiều nhất riêng trong từng học kỳ/năm - Không phải nhiều nhất toàn bộ dữ liệu, nên MAX phải tương quan theo đúng (HOCKY, NAM) đang xét.

CÂU 30 (PHẦN III) · GROUP BY ... TOP WITH TIES

Đề bài: Tìm môn học (mã môn học, tên môn học) có nhiều học viên thi không đạt (ở lần thi thứ 1) nhất.

SELECT TOP 1 WITH TIES mh.MAMH, mh.TENMH
FROM MONHOC mh
JOIN KETQUATHI kq ON mh.MAMH = kq.MAMH
WHERE kq.LANTHI = 1 AND kq.KQUA = N'Khong Dat'
GROUP BY mh.MAMH, mh.TENMH
ORDER BY COUNT(*) DESC;

LÀ GÌ?

GROUP BY theo môn học, đếm số học viên thi lần 1 không đạt; TOP 1 WITH TIES giữ tất cả môn đồng hạng cao nhất.

VÌ SAO?

Giống lý do ở Câu 26 - TOP ... WITH TIES tránh bỏ sót nếu có nhiều môn cùng số lượng thi rớt lần 1 nhiều nhất.

CÂU 31 (PHẦN III) · EXISTS / NOT EXISTS

Đề bài: Tìm học viên (mã học viên, họ tên) thi môn nào cũng đạt (chỉ xét lần thi thứ 1).

SELECT hv.MAHV, hv.HO + N' ' + hv.TEN AS HOTEN
FROM HOCVIEN hv
WHERE EXISTS (
    SELECT * FROM KETQUATHI kq WHERE kq.MAHV = hv.MAHV AND kq.LANTHI = 1
)
AND NOT EXISTS (
    SELECT * FROM KETQUATHI kq
    WHERE kq.MAHV = hv.MAHV AND kq.LANTHI = 1 AND kq.KQUA = N'Khong Dat'
);

LÀ GÌ?

EXISTS đảm bảo học viên có ít nhất 1 lượt thi lần 1; NOT EXISTS đảm bảo không có lượt thi lần 1 nào bị "Khong Dat" - Kết hợp nghĩa là mọi môn thi lần 1 đều đạt.

VÌ SAO?

Phải có cả 2 điều kiện EXISTS và NOT EXISTS - Nếu chỉ có NOT EXISTS, học viên chưa từng thi môn nào cũng thỏa mãn "không có lần nào rớt" một cách vô nghĩa, dù họ chưa thi môn nào cả.

CÂU 32 (PHẦN III) · EXISTS / NOT EXISTS

Đề bài: * Tìm học viên (mã học viên, họ tên) thi môn nào cũng đạt (chỉ xét lần thi sau cùng).

SELECT hv.MAHV, hv.HO + N' ' + hv.TEN AS HOTEN
FROM HOCVIEN hv
WHERE EXISTS (
    SELECT * FROM KETQUATHI kq WHERE kq.MAHV = hv.MAHV
)
AND NOT EXISTS (
    SELECT * FROM KETQUATHI kq
    WHERE kq.MAHV = hv.MAHV
      AND kq.KQUA = N'Khong Dat'
      AND kq.LANTHI = (
          SELECT MAX(kq2.LANTHI) FROM KETQUATHI kq2
          WHERE kq2.MAHV = kq.MAHV AND kq2.MAMH = kq.MAMH
      )
);

LÀ GÌ?

NOT EXISTS lần này kiểm tra không có môn nào mà lần thi sau cùng (MAX(LANTHI) tương ứng từng môn) bị "Khong Dat" - Khác Câu 31 chỉ xét đúng LANTHI = 1.

VÌ SAO?

"Lần thi sau cùng" của mỗi môn có thể khác nhau tùy học viên (có môn thi 1 lần, có môn thi lại 2-3 lần) - Phải tính lại MAX(LANTHI) tương quan theo từng cặp (MAHV, MAMH) như đã làm ở Câu 17, không thể dùng chung 1 giá trị LANTHI cố định như Câu 31.

CÂU 33 (PHẦN III) · DOUBLE NOT EXISTS

Đề bài: * Tìm học viên (mã học viên, họ tên) đã thi tất cả các môn đều đạt (chỉ xét lần thi thứ 1).

SELECT hv.MAHV, hv.HO + N' ' + hv.TEN AS HOTEN
FROM HOCVIEN hv
WHERE NOT EXISTS (
    SELECT mh.MAMH
    FROM MONHOC mh
    WHERE NOT EXISTS (
        SELECT * FROM KETQUATHI kq
        WHERE kq.MAHV = hv.MAHV AND kq.MAMH = mh.MAMH AND kq.LANTHI = 1 AND kq.KQUA = N'Dat'
    )
);

LÀ GÌ?

Đây là phép chia (division) kinh điển bằng 2 lớp NOT EXISTS lồng nhau - "Không tồn tại môn học nào mà học viên không đạt ở lần thi 1" tương đương "mọi môn học đều đã đạt ở lần thi 1".

VÌ SAO?

Khác Câu 31 (chỉ xét những môn học viên có thi), câu này đòi hỏi học viên phải thi đủ tất cả môn học hiện có trong MONHOC - Cấu trúc "không tồn tại X mà không tồn tại Y" là mẫu chuẩn cho dạng câu hỏi "với mọi" (for all) trong SQL, vì SQL không có toán tử tương đương trực tiếp.

CÂU 34 (PHẦN III) · DOUBLE NOT EXISTS

Đề bài: * Tìm học viên (mã học viên, họ tên) đã thi tất cả các môn đều đạt (chỉ xét lần thi sau cùng).

SELECT hv.MAHV, hv.HO + N' ' + hv.TEN AS HOTEN
FROM HOCVIEN hv
WHERE NOT EXISTS (
    SELECT mh.MAMH
    FROM MONHOC mh
    WHERE NOT EXISTS (
        SELECT * FROM KETQUATHI kq
        WHERE kq.MAHV = hv.MAHV AND kq.MAMH = mh.MAMH AND kq.KQUA = N'Dat'
          AND kq.LANTHI = (
              SELECT MAX(kq2.LANTHI) FROM KETQUATHI kq2
              WHERE kq2.MAHV = hv.MAHV AND kq2.MAMH = mh.MAMH
          )
    )
);

LÀ GÌ?

Cấu trúc phép chia giống Câu 33, nhưng điều kiện "đạt" ở lớp NOT EXISTS bên trong lấy đúng lần thi sau cùng của từng môn thay vì cố định LANTHI = 1.

VÌ SAO?

Kết hợp kỹ thuật "lần thi sau cùng" (Câu 17, Câu 32) với kỹ thuật phép chia (Câu 33) - Học viên phải đạt ở lần thi gần nhất của mọi môn học hiện có, mới được tính là thỏa điều kiện.

CÂU 35 (PHẦN III) · CORRELATED SUBQUERY ... MAX

Đề bài: ** Tìm học viên (mã học viên, họ tên) có điểm thi cao nhất trong từng môn (lấy điểm ở lần thi sau cùng).

SELECT t.MAMH, mh.TENMH, t.MAHV, t.HOTEN, t.Diem
FROM (
    SELECT kq.MAMH, hv.MAHV, hv.HO + N' ' + hv.TEN AS HOTEN, kq.DIEM AS Diem
    FROM KETQUATHI kq
    JOIN HOCVIEN hv ON kq.MAHV = hv.MAHV
    WHERE kq.LANTHI = (
        SELECT MAX(kq2.LANTHI) FROM KETQUATHI kq2
        WHERE kq2.MAHV = kq.MAHV AND kq2.MAMH = kq.MAMH
    )
) t
JOIN MONHOC mh ON t.MAMH = mh.MAMH
WHERE t.Diem = (
    SELECT MAX(t2.Diem)
    FROM (
        SELECT kq3.MAMH, kq3.DIEM AS Diem
        FROM KETQUATHI kq3
        WHERE kq3.LANTHI = (
            SELECT MAX(kq4.LANTHI) FROM KETQUATHI kq4
            WHERE kq4.MAHV = kq3.MAHV AND kq4.MAMH = kq3.MAMH
        )
    ) t2
    WHERE t2.MAMH = t.MAMH
);

LÀ GÌ?

Truy vấn con t dựng danh sách điểm ở đúng lần thi sau cùng của từng cặp (học viên, môn học) - Kỹ thuật giống Câu 17; sau đó lọc lại những dòng có điểm bằng đúng điểm cao nhất của riêng môn đó (t2.MAMH = t.MAMH).

VÌ SAO?

Đây là bài toán "giá trị lớn nhất theo từng nhóm" (giống Câu 27, Câu 29) áp dụng trên tập dữ liệu đã lọc lần thi sau cùng (giống Câu 17) - Phải kết hợp cả 2 kỹ thuật cùng lúc: Vừa tương quan MAX theo MAMH, vừa loại các lần thi không phải sau cùng.