Phân Loại Dạng SQL

SELECT sv.MaSV, sv.HoTen, sv.MaLop, sv.NgaySinh,
       lhpsv.DiemQT, lhpsv.DiemThi, lhpsv.DiemTKHP
FROM   SinhVien sv
INNER JOIN LopHocPhan_SinhVien lhpsv
       ON sv.MaSV = lhpsv.MaSV
INNER JOIN LopHocPhan lhp
       ON lhpsv.MaHP = lhp.MaHP
      AND lhpsv.MaLHP = lhp.MaLHP
WHERE lhp.MaHP  = 'MaHocPhan'
  AND lhp.MaLHP = 'MaLopHocPhan';

💡 Thêm bảng nào cần thêm INNER JOIN tương ứng. Điều kiện WHERE cố định.

-- Ví dụ: Bộ môn + Trưởng bộ môn
SELECT bm.MaBM, bm.TenBM, gv.MaGV, gv.HoTen
FROM   BoMon bm
INNER JOIN GiangVien gv
       ON bm.MaTBM = gv.MaGV;  -- FK trưởng bộ môn

-- Ví dụ: Học phần + tiên quyết (nếu có)
SELECT hp.MaHP, hp.TenHP,
       tq.MaHP AS MaHPTQ, hp2.TenHP AS TenHPTQ
FROM   HocPhan hp
LEFT JOIN TienQuyet tq  ON hp.MaHP = tq.MaHP
LEFT JOIN HocPhan  hp2 ON tq.MaHPTQ = hp2.MaHP;

SELECT bm.MaBM, bm.TenBM, COUNT(hp.MaHP) AS SoHocPhan
FROM   BoMon bm
INNER JOIN HocPhan hp ON bm.MaBM = hp.MaBM
INNER JOIN Khoa    k  ON bm.MaKhoa = k.MaKhoa
WHERE  k.TenKhoa LIKE N'%Công nghệ thông tin%'
GROUP BY bm.MaBM, bm.TenBM;

SELECT NhomCot, COUNT(*) AS SoLuong
FROM   Bang
JOIN   BangLien ON ...dk_join...
WHERE  ...dieu_kien_loc...          -- lọc trước nhóm
GROUP BY NhomCot
HAVING COUNT(*) > nguong          -- lọc SAU nhóm
ORDER BY SoLuong DESC;

⚡ Nhớ: WHERE lọc hàng, HAVING lọc nhóm — không được hoán đổi.

SELECT NhomCot, AVG(DiemTKHP) AS DiemTBNhom
FROM   LopHocPhan_SinhVien lhpsv
JOIN   BangNhom bn ON ...join...
WHERE  lhpsv.DiemTKHP IS NOT NULL
GROUP BY NhomCot;

SELECT
  MaLHP,
  COUNT(*) AS TongSV,
  ROUND(
    SUM(CASE WHEN DiemTKHP >= 8.5 THEN 1 ELSE 0 END)
    * 100.0 / COUNT(*), 2)        AS PhanTramTren85,
  ROUND(
    SUM(CASE WHEN DiemTKHP >= 7
              AND  DiemTKHP < 8.5 THEN 1 ELSE 0 END)
    * 100.0 / COUNT(*), 2)        AS PhanTram7to85
  -- ... thêm các dải điểm còn lại ...
FROM   LopHocPhan_SinhVien
GROUP BY MaLHP
HAVING SUM(CASE WHEN DiemTKHP >= 4 THEN 1 ELSE 0 END)
       * 100.0 / COUNT(*) < 50;  -- câu 44: chỉ lớp < 50%

-- Cách 1: NOT EXISTS
SELECT gv.*
FROM   GiangVien gv
WHERE NOT EXISTS (
  SELECT 1 FROM LopHocPhan lhp
  WHERE  lhp.MaGV     = gv.MaGV
    AND  lhp.NamHoc   = '2023-2024'
    AND  lhp.HocKy    = 2
);

-- Cách 2: LEFT JOIN + IS NULL (tương đương)
SELECT gv.*
FROM   GiangVien gv
LEFT JOIN LopHocPhan lhp
       ON  lhp.MaGV   = gv.MaGV
      AND  lhp.NamHoc = '2023-2024'
      AND  lhp.HocKy  = 2
WHERE lhp.MaGV IS NULL;

-- Kiểm tra tiên quyết: HP tiên quyết mà SV chưa đạt
SELECT tq.MaHPTQ
FROM   TienQuyet tq
WHERE  tq.MaHP = 'MaHocPhan'
  AND  tq.MaHPTQ NOT IN (
    SELECT lhpsv.MaHP
    FROM   LopHocPhan_SinhVien lhpsv
    WHERE  lhpsv.MaSV      = 'MaSinhVien'
      AND  lhpsv.DiemTKHP >= 4
  );
-- Nếu không có hàng trả về → đã đủ tiên quyết

-- HP trong CTDT chưa đăng ký hoặc điểm < 4
SELECT hp.MaHP, hp.TenHP
FROM   CTDT_HocPhan ctdthp
JOIN   HocPhan hp ON ctdthp.MaHP = hp.MaHP
WHERE  ctdthp.MaCTDT = (SELECT MaCTDT FROM SinhVien
                          WHERE MaSV = 'MaSV')
  AND  hp.MaHP NOT IN (
    SELECT MaHP FROM LopHocPhan_SinhVien
    WHERE  MaSV = 'MaSV' AND DiemTKHP >= 4
  );

SELECT sv.MaSV, sv.HoTen, lhpsv.DiemTKHP
FROM   LopHocPhan_SinhVien lhpsv
JOIN   SinhVien sv ON sv.MaSV = lhpsv.MaSV
WHERE  lhpsv.MaHP  = 'MaHP'
  AND  lhpsv.MaLHP = 'MaLHP'
  AND  lhpsv.DiemTKHP < (
    SELECT AVG(DiemTKHP)
    FROM   LopHocPhan_SinhVien
    WHERE  MaHP = 'MaHP' AND MaLHP = 'MaLHP'
  );

-- Câu 4: Tính DiemTKHP = DiemQT * TrọngSo + DiemThi * TrọngSo
UPDATE LopHocPhan_SinhVien
SET    DiemTKHP = ROUND(DiemQT * 0.4 + DiemThi * 0.6, 1)
WHERE  DiemQT  IS NOT NULL
  AND  DiemThi IS NOT NULL;

-- Câu 38: Cập nhật LanHoc = thứ tự học theo thời gian
UPDATE t
SET    t.LanHoc = sub.LanHoc
FROM   LopHocPhan_SinhVien t
JOIN   (
  SELECT MaSV, MaHP, MaLHP,
         ROW_NUMBER() OVER (
           PARTITION BY MaSV, MaHP
           ORDER BY NamHoc, HocKy       -- cần join LopHocPhan
         ) AS LanHoc
  FROM   LopHocPhan_SinhVien lhpsv2
  JOIN   LopHocPhan lhp ON lhpsv2.MaHP = lhp.MaHP
                         AND lhpsv2.MaLHP = lhp.MaLHP
) sub ON t.MaSV = sub.MaSV AND t.MaHP = sub.MaHP
       AND t.MaLHP = sub.MaLHP;

UPDATE LopHocPhan_SinhVien
SET
  DiemHe4 = CASE
    WHEN DiemTKHP >= 9.5               THEN 4.0
    WHEN DiemTKHP >= 8.5               THEN 3.8
    WHEN DiemTKHP >= 8.0               THEN 3.5
    WHEN DiemTKHP >= 7.0               THEN 3.0
    WHEN DiemTKHP >= 6.0               THEN 2.5
    WHEN DiemTKHP >= 5.5               THEN 2.0
    WHEN DiemTKHP >= 5.0               THEN 1.5
    WHEN DiemTKHP >= 4.0               THEN 1.0
    WHEN DiemTKHP >= 3.0               THEN 0.5
    ELSE 0
  END,
  DiemHeChu = CASE
    WHEN DiemTKHP >= 9.5 THEN 'A+'
    WHEN DiemTKHP >= 8.5 THEN 'A'
    -- ... tương tự ...
    ELSE 'F'
  END
WHERE DiemTKHP IS NOT NULL;

-- Câu 6: Xóa LHP không có SV
DELETE FROM LopHocPhan
WHERE NOT EXISTS (
  SELECT 1 FROM LopHocPhan_SinhVien lhpsv
  WHERE  lhpsv.MaHP  = LopHocPhan.MaHP
    AND  lhpsv.MaLHP = LopHocPhan.MaLHP
);

-- Câu 7: Cập nhật thông tin SV cụ thể
UPDATE SinhVien
SET    DiaChi = N'DiaChi mới',
       SoDienThoai = 'SDT mới'
WHERE  MaSV = '201200043';

WITH DiemCaoNhat AS (
  SELECT MaSV, MaHP,
         MAX(DiemTKHP) AS DiemMax
  FROM   LopHocPhan_SinhVien
  WHERE  MaSV = 'MaSV'
    AND  DiemTKHP IS NOT NULL
  GROUP BY MaSV, MaHP
)
SELECT
  d.MaSV,
  ROUND(
    SUM(d.DiemMax * hp.SoTinChi)
    / SUM(hp.SoTinChi), 2
  ) AS DiemTichLuy
FROM   DiemCaoNhat d
JOIN   HocPhan hp ON d.MaHP = hp.MaHP
GROUP BY d.MaSV;

WITH HPDat AS (
  SELECT lhpsv.MaSV, lhpsv.MaHP,
         MAX(lhpsv.DiemTKHP) AS DiemMax
  FROM   LopHocPhan_SinhVien lhpsv
  WHERE  lhpsv.MaSV = 'MaSV'
  GROUP BY lhpsv.MaSV, lhpsv.MaHP
  HAVING MAX(lhpsv.DiemTKHP) >= 4
)
SELECT h.MaSV,
       SUM(hp.SoTinChi) AS TongTinChiTichLuy
FROM   HPDat h
JOIN   HocPhan hp ON h.MaHP = hp.MaHP
GROUP BY h.MaSV;

WITH Ranked AS (
  SELECT *,
         RANK() OVER (
           PARTITION BY MaHP, MaLHP
           ORDER BY DiemTKHP DESC
         ) AS Rank
  FROM   LopHocPhan_SinhVien
  WHERE  DiemTKHP IS NOT NULL
)
SELECT r.MaSV, sv.HoTen, r.MaHP, r.MaLHP, r.DiemTKHP
FROM   Ranked r
JOIN   SinhVien sv ON sv.MaSV = r.MaSV
WHERE  r.Rank = 1;            -- lấy điểm cao nhất, giữ đồng hạng

💡 RANK() giữ đồng hạng. ROW_NUMBER() chỉ lấy 1 dòng. Chọn theo yêu cầu.

-- Câu 42: Xếp hạng SV theo GPA trong từng lớp
WITH GPA AS (
  -- tính GPA ở đây...
)
SELECT
  g.MaSV, sv.HoTen, sv.MaLop,
  g.GPA,
  RANK() OVER (
    PARTITION BY sv.MaLop
    ORDER BY g.GPA DESC
  ) AS XepHang
FROM   GPA g
JOIN   SinhVien sv ON sv.MaSV = g.MaSV;

-- Câu 28: GV dạy nhiều LHP nhất HK2 2023-2024
WITH Count_LHP AS (
  SELECT MaGV, COUNT(*) AS SoLHP
  FROM   LopHocPhan
  WHERE  NamHoc = '2023-2024' AND HocKy = 2
  GROUP BY MaGV
)
SELECT * FROM Count_LHP
WHERE  SoLHP = (SELECT MAX(SoLHP) FROM Count_LHP);

WITH GPA_Ranked AS (
  -- ... tính GPA cho mỗi SV ...
),
Ranked AS (
  SELECT g.*,
         DENSE_RANK() OVER (
           PARTITION BY sv.MaLop
           ORDER BY g.GPA DESC
         ) AS Rank
  FROM   GPA_Ranked g
  JOIN   SinhVien sv ON sv.MaSV = g.MaSV
)
SELECT * FROM Ranked
WHERE  Rank <= 5;   -- top 5 mỗi lớp

-- Câu 16: SV + GV sinh nhật tháng hiện tại
SELECT 'SinhVien' AS LoaiNguoi,
       MaSV AS MaID, HoTen, NgaySinh
FROM   SinhVien sv
JOIN   Lop lp ON sv.MaLop = lp.MaLop
JOIN   Khoa  k  ON lp.MaKhoa = k.MaKhoa
WHERE  k.TenKhoa LIKE N'%Công nghệ thông tin%'
  AND  MONTH(sv.NgaySinh) = MONTH(GETDATE())

UNION ALL

SELECT 'GiangVien',
       MaGV, HoTen, NgaySinh
FROM   GiangVien gv
JOIN   BoMon bm ON gv.MaBM = bm.MaBM
JOIN   Khoa  k  ON bm.MaKhoa = k.MaKhoa
WHERE  k.TenKhoa LIKE N'%Công nghệ thông tin%'
  AND  MONTH(gv.NgaySinh) = MONTH(GETDATE());

WITH SoDuoi4 AS (
  SELECT MaHP, COUNT(*) AS SoSVDuoi4
  FROM   LopHocPhan_SinhVien
  WHERE  DiemTKHP < 4
  GROUP BY MaHP
)
SELECT MaHP, SoSVDuoi4
FROM   SoDuoi4
WHERE  SoSVDuoi4 = (
  SELECT MAX(SoSVDuoi4) FROM SoDuoi4
);

-- Câu 40: GV dạy từ 3 LHP trở lên năm học 2023-2024
SELECT gv.MaGV, gv.HoTen, COUNT(lhp.MaLHP) AS SoLHP
FROM   GiangVien gv
JOIN   LopHocPhan lhp ON gv.MaGV = lhp.MaGV
WHERE  lhp.NamHoc = '2023-2024'
GROUP BY gv.MaGV, gv.HoTen
HAVING COUNT(lhp.MaLHP) >= 3;

-- Câu 43: SV hoàn thành TẤT CẢ HP bắt buộc
SELECT sv.MaSV, sv.HoTen
FROM   SinhVien sv
JOIN   CTDT_HocPhan ctdthp
       ON ctdthp.MaCTDT = sv.MaCTDT
      AND ctdthp.BatBuoc = 1
LEFT JOIN LopHocPhan_SinhVien lhpsv
       ON lhpsv.MaSV  = sv.MaSV
      AND lhpsv.MaHP  = ctdthp.MaHP
      AND lhpsv.DiemTKHP >= 4
GROUP BY sv.MaSV, sv.HoTen, sv.MaCTDT
HAVING
  COUNT(lhpsv.MaHP)       -- số HP bắt buộc đã đạt
  = (SELECT COUNT(*) FROM CTDT_HocPhan
     WHERE MaCTDT = sv.MaCTDT AND BatBuoc = 1);