Đáp án bài tập thực hành CSDL

Bài viết này là phần 2/2 thuộc series Cơ sở dữ liệu

HOCVIEN (MAHV, HO, TEN, NGSINH, GIOITINH, NOISINH, MALOP)
LOP (MALOP, TENLOP, TRGLOP, SISO, MAGVCN)
KHOA (MAKHOA, TENKHOA, NGTLAP, TRGKHOA)
MONHOC (MAMH, TENMH, TCLT, TCTH, MAKHOA)
DIEUKIEN (MAMH, MAMH_TRUOC)
GIAOVIEN (MAGV, HOTEN, HOCVI,HOCHAM,GIOITINH, NGSINH, NGVL,HESO, MUCLUONG, MAKHOA)
GIANGDAY (MALOP, MAMH, MAGV, HOCKY, NAM, TUNGAY, DENNGAY)
KETQUATHI (MAHV, MAMH, LANTHI, NGTHI, DIEM, KQUA)

bai tap quan ly giao vụ csdl sql server — Bài tập Quản lý giáo vụ CSDL SQL Server

I. Ngôn ngữ định nghĩa dữ liệu (Data Definition Language) – Khởi tạo CSDL Quản lý giáo vụ

Ở bài trước chúng ta đã thực hành Bài tập CSDL Quản lý bán hàng. Phần này chúng ta sẽ tiếp tục đến với phần Thực hành CSDL Quản lý giáo vụ

Download Microsoft SQL Server tại đây.

1. 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 của CSDL quản lý giáo vụ và thêm vào 3 thuộc tính GHICHU, DIEMTB, XEPLOAI cho quan hệ HOCVIEN.

2. Mã học viên là một chuỗi 5 ký tự, 3 ký tự đầu là mã lớp, 2 ký tự cuối cùng là số thứ tự học viên trong lớp. VD: “K1101”

SQL

CREATE TRIGGER trg_ins_upd_HOCVIEN
ON HOCVIEN
FOR INSERT, UPDATE 
AS
BEGIN
	DECLARE @SISO INT, @MAHV VARCHAR(5), @MALOP VARCHAR(3)

	SELECT @MAHV = MAHV, @MALOP = MALOP FROM INSERTED
	SELECT @SISO = SISO FROM LOP WHERE LOP.MALOP = @MALOP
	IF LEFT(@MAHV,3) <> @MALOP
	BEGIN
		PRINT('3 ki tu dau cua MAHV phai la MALOP')
		ROLLBACK TRANSACTION
	END
	ELSE IF CAST(RIGHT(@MAHV, 2) AS INT) NOT BETWEEN 1 AND @SISO
	BEGIN
		PRINT('2 ki tu cuoi cua MAHV phai la so thu tu hoc vien trong lop')
		ROLLBACK TRANSACTION
	END
END

3. Thuộc tính GIOITINH chỉ có giá trị là “Nam” hoặc “Nu”.

SQL

ALTER TABLE HOCVIEN ADD CONSTRAINT CHECK_GTHV CHECK (GIOITINH IN ('Nam', 'Nu'))
ALTER TABLE GIAOVIEN ADD CONSTRAINT CHECK_GTGV CHECK (GIOITINH IN ('Nam', 'Nu'))

4. Đ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).

SQL

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

5. 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.

SQL

ALTER TABLE KETQUATHI ADD CONSTRAINT CHECK_KETQUA CHECK
(	
	(KQUA = 'Dat' AND DIEM BETWEEN 5 AND 10)
	OR (KQUA = 'Khong dat' AND DIEM < 5)
)

6. Học viên thi một môn tối đa 3 lần.

SQL

ALTER TABLE KETQUATHI ADD CONSTRAINT CHECK_LANTHI CHECK (LANTHI <= 3)

7. Học kỳ chỉ có giá trị từ 1 đến 3.

SQL

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

8. Học vị của giáo viên chỉ có thể là “CN”, “KS”, “Ths”, ”TS”, ”PTS”.

SQL

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

9. Lớp trưởng của một lớp phải là học viên của lớp đó.

SQL

CREATE TRIGGER trg_ins_udt_LopTruong ON LOP
FOR INSERT, UPDATE
AS
BEGIN
	IF NOT EXISTS (SELECT * FROM INSERTED I, HOCVIEN HV
	WHERE I.TRGLOP = HV.MAHV AND I.MALOP = HV.MALOP)
	BEGIN
		PRINT 'Error: Lop truong cua mot lop phai la hoc vien cua lop do'
		ROLLBACK TRANSACTION
	END
END

CREATE TRIGGER trg_del_HOCVIEN ON HOCVIEN
FOR DELETE
AS
BEGIN
	IF EXISTS (SELECT * FROM DELETED D, INSERTED I, LOP L 
	WHERE D.MAHV = L.TRGLOP AND D.MALOP = L.MALOP)
	BEGIN
		PRINT 'Error: Hoc vien hien tai dang la truong lop'
		ROLLBACK TRANSACTION
	END
END

10. Trưởng khoa phải là giáo viên thuộc khoa và có học vị “TS” hoặc “PTS”.

SQL

--- Sửa quan hệ GIAOVIEN
GO
CREATE TRIGGER TRG_UPDATE_GIAOVIEN ON GIAOVIEN
FOR UPDATE
AS
BEGIN
	IF(SELECT COUNT(*)
	FROM inserted I , KHOA K
	WHERE K.TRGKHOA=I.MAGV AND I.MAKHOA=K.MAKHOA)=0
	BEGIN
		PRINT 'ERROR'
		ROLLBACK TRANSACTION
	END
	ELSE
		BEGIN
		PRINT'THANH CONG'
	END 
END
--- Xóa GIAOVIEN
GO
CREATE TRIGGER TRG10_DELETE_GIAOVIEN ON GIAOVIEN
FOR DELETE
AS
BEGIN
	DECLARE @MAGV CHAR(4), @TRGKHOA CHAR(4), @MAKHOA VARCHAR(4)
	SELECT @MAGV = MAGV, @MAKHOA=MAKHOA
	FROM DELETED
	SELECT @TRGKHOA=TRGKHOA
	FROM KHOA
	WHERE MAKHOA=@MAKHOA
	IF(@MAGV = @TRGKHOA)
	BEGIN 
		PRINT ' Khong duoc xoa'
		ROLLBACK TRANSACTION
	END
	ELSE
	BEGIN
		PRINT 'Xoa thanh cong!'
	END
END

11. Học viên ít nhất là 18 tuổi.

SQL

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

12. 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).

SQL

ALTER TABLE GIANGDAY ADD CONSTRAINT CHECK_GIANGDAY CHECK (TUNGAY < DENNGAY)

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

SQL

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

14. 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.

SQL

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

15. Học viên chỉ được thi một môn học nào đó khi lớp của học viên đã học xong môn học này.

16. Mỗi học kỳ của một năm học, một lớp chỉ được học tối đa 3 môn.

SQL

GO
CREATE TRIGGER TRG16_INSERT_GIANGDAY ON GIANGDAY
FOR INSERT ,UPDATE
AS
BEGIN
      IF(SELECT COUNT(*)
      FROM inserted I , GIANGDAY GD
      WHERE I.MALOP=GD.MALOP AND I.HOCKY=GD.HOCKY)>3
      BEGIN
            PRINT 'ERROR'
            ROLLBACK TRANS
      END
      ELSE
            BEGIN
            PRINT 'THANHCONG'
      END
END

17. Sỉ số của một lớp bằng với số lượng học viên thuộc lớp đó.

SQL

--- Sửa, THÊM sỉ số

GO
CREATE TRIGGER TRG17_INSERT_LOP ON LOP
FOR INSERT, UPDATE
AS
BEGIN
      DECLARE @SISO TINYINT, @DEMHOCVIEN TINYINT, @MALOP CHAR(3)
      SELECT @SISO = SISO, @MALOP = MALOP
      FROM INSERTED I
      SELECT @DEMHOCVIEN = COUNT(MAHV)
      FROM HOCVIEN
      WHERE MALOP =@MALOP
      IF(@SISO<>@DEMHOCVIEN)
      BEGIN
            PRINT 'Khong cho sua si so'
            ROLLBACK TRANSACTION
      END
      ELSE
      BEGIN
            PRINT 'Sua si so thanh cong'
      END
END

18. Trong quan hệ DIEUKIEN giá trị của thuộc tính MAMH và MAMH_TRUOC trong cùng một bộ không được giống nhau (“A”,”A”) và cũng không tồn tại hai bộ (“A”,”B”) và (“B”,”A”).

19. Các giáo viên có cùng học vị, học hàm, hệ số lương thì mức lương bằng nhau.

SQL

GO
CREATE TRIGGER TRG19_INSERTED_GIAOVIEN ON GIAOVIEN
FOR INSERT , UPDATE
AS
BEGIN
      IF(SELECT COUNT (*)
      FROM inserted I , GIAOVIEN GV
      WHERE I.HOCHAM=GV.HOCHAM AND I.HOCVI=GV.HOCVI AND I.HESO=GV.HESO AND I.MUCLUONG!=GV.MUCLUONG)>0
      BEGIN
            PRINT 'ERROR'
            ROLLBACK TRAN
      END
      ELSE
      BEGIN
            PRINT 'THANHCONG'
      END
END

20. Học viên chỉ được thi lại (lần thi >1) khi điểm của lần thi trước đó dưới 5.

SQL

GO
CREATE TRIGGER TRG20_INSERT_KQT ON KETQUATHI
FOR INSERT
AS
BEGIN
      DECLARE @LANTHI TINYINT, @MAHV CHAR(5), @DIEM NUMERIC(4,2)
      SELECT @LANTHI = KETQUATHI.LANTHI +1, @MAHV = I.MAHV, @DIEM = KETQUATHI.DIEM
      FROM INSERTED I JOIN KETQUATHI ON I.MAHV =KETQUATHI.MAHV
      WHERE I.MAMH = KETQUATHI.MAMH
      IF(@DIEM>5)
      BEGIN
            PRINT 'Khong duoc thi lan nua!'
            ROLLBACK TRANSACTION
      END
      ELSE
      BEGIN
            PRINT 'Them lan thi thanh cong!'
      END
END

21. Ngày thi của lần thi sau phải lớn hơn ngày thi của lần thi trước (cùng học viên, cùng môn học).

SQL

GO
CREATE TRIGGER TRG21_INSERT_KQT ON KETQUATHI
FOR INSERT , UPDATE
AS
BEGIN
      IF(SELECT COUNT(*)
      FROM INSERTED I , KETQUATHI K
      WHERE I.LANTHI> K.LANTHI AND I.MAHV=K.MAHV AND I.MAMH=I.MAMH AND I.NGTHI>K.NGTHI)=0
      BEGIN
            PRINT 'ERROR'
            ROLLBACK TRAN
      END
      ELSE
      BEGIN
            PRINT 'THANHCONG'
      END
END

22. Học viên chỉ được thi những môn mà lớp của học viên đó đã học xong.

23. Khi phân công giảng dạy một môn học, phải xét đến thứ tự trước sau giữa các môn học (sau khi học xong những môn học phải học trước mới được học những môn liền sau).

24. Giáo viên chỉ được phân công dạy những môn thuộc khoa giáo viên đó phụ trách

II. Ngôn ngữ thao tác dữ liệu (Data Manipulation Language)

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

SQL

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

2. 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).

SQL

UPDATE HocVien
SET DiemTB =
(
      SELECT AVG(Diem)
      FROM KetQuaThi
      WHERE LanThi = (SELECT MAX(LanThi) FROM KetQuaThi KQ WHERE MaHV = KetQuaThi.MaHV GROUP BY MaHV)
      GROUP BY MaHV
      HAVING MaHV = HocVien.MaHV
)

3. 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.

SQL

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

4. Cập nhật giá trị cho cột XEPLOAI trong quan hệ HOCVIEN như sau:

Nếu DIEMTB >= 9 thì XEPLOAI =”XS”
Nếu 8 <= DIEMTB < 9 thì XEPLOAI = “G”
Nếu 6.5 <= DIEMTB < 8 thì XEPLOAI = “K”
Nếu 5 <= DIEMTB < 6.5 thì XEPLOAI = “TB”
Nếu DIEMTB < 5 thì XEPLOAI = ”Y

SQL

UPDATE HOCVIEN
SET XEPLOAI =
(
      CASE
            WHEN DIEMTB >= 9 THEN 'XS'
            WHEN DIEMTB >= 8 AND DIEMTB < 9 THEN 'G'
            WHEN DIEMTB >= 6.5 AND DIEMTB < 8 THEN 'K'
            WHEN DIEMTB >= 5 AND DIEMTB < 6.5 THEN 'TB'
            WHEN DIEMTB < 5 THEN 'Y'
      END
)

III. Ngôn ngữ truy vấn dữ liệu

1. 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.

SQL

SELECT MaHV, (Ho+Ten) HoTen, NgSinh, HocVien.MaLop
FROM HocVien, Lop
WHERE HocVien.MaHV = Lop.TrgLop

2. 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.

SQL

SELECT HocVien.MaHV, (Ho+' '+Ten) HoTen, LanThi, Diem
FROM KetQuaThi, HocVien
WHERE
      KetQuaThi.MaHV = HocVien.MaHV
      AND MaMH = 'CTRR'
      AND MaLop = 'K12'
ORDER BY Ten, Ho

3. 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.

SQL

SELECT HocVien.MaHV, (Ho+' '+Ten) HoTen, TenMH
FROM KetQuaThi, MonHoc, HocVien
WHERE
      KetQuaThi.MaMH = MonHoc.MaMH
      AND KetQuaThi.MaHV = HocVien.MaHV
      AND LanThi = 1 AND KQua = 'Dat'

4. 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).

SQL

SELECT HocVien.MaHV, (Ho+' '+Ten) HoTen
FROM HocVien, KetQuaThi
WHERE
       HocVien.MaHV = KetQuaThi.MaHV
       AND MaLop = 'K11'
       AND MaMH = 'CTRR'
       AND KQua = 'Khong Dat'
       AND LanThi = 1

5. * 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).

SQL

SELECT DISTINCT HocVien.MaHV, (Ho+' '+Ten) HoTen
FROM HocVien, KetQuaThi
WHERE
      HocVien.MaHV = KetQuaThi.MaHV
      AND MaLop like 'K%'
      AND MaMH = 'CTRR'
      AND NOT EXISTS (
SELECT * FROM KetQuaThi
            WHERE
                  KQua = 'Dat'
                  AND MaMH = 'CTRR'
                  AND MaHV = HocVien.MaHV
)

6. 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.

SQL

SELECT DISTINCT TenMH
FROM MonHoc, GiaoVien, GiangDay
WHERE
      MonHoc.MaMH = GiangDay.MaMH
      AND GiaoVien.MaGV = GiangDay.MaGV
      AND HoTen = 'Tran Tam Thanh'
      AND HocKy = 1 AND Nam = 2006

7. 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.

SQL

SELECT DISTINCT   MonHoc.MaMH, TenMH
FROM MonHoc, Lop, GiangDay
WHERE
      GiangDay.MaMH = MonHoc.MaMH
      AND GiangDay.MaGV = Lop.MaGVCN
      AND Lop.MaLop = 'K11'
      AND HocKy = 1 AND Nam = 2006

8. 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”.

SQL

SELECT DISTINCT (HO+' '+TEN) HOTEN
FROM HocVien, Lop, GiaoVien, GiangDay, MonHoc
WHERE
       Lop.TrgLop = HocVien.MaHV
       AND GiangDay.MaLop = Lop.MaLop
       AND GiangDay.MaGV = GiaoVien.MaGV
       AND GiangDay.MaMH = MonHoc.MaMH
       AND HoTen = 'Nguyen To Lan'
       AND TenMH = 'Co So Du Lieu'

9. 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”.

SQL

SELECT MonHocTruoc.MaMH, MonHocTruoc.TenMH
FROM MonHoc, MonHoc AS MonHocTruoc, DieuKien
WHERE
      MonHoc.MaMH = DieuKien.MaMH
      AND MonHocTruoc.MaMH = DieuKien.MaMH_Truoc
      AND MonHoc.TenMH = 'Co So Du Lieu'

10. 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.

SQL

SELECT MonHoc.MaMH, MonHoc.TenMH
FROM MonHoc, MonHoc AS MonHocTruoc, DieuKien
WHERE
      MonHoc.MaMH = DieuKien.MaMH
      AND MonHocTruoc.MaMH = DieuKien.MaMH_Truoc
      AND MonHocTruoc.TenMH = 'Cau Truc Roi Rac'

11. 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.

SQL

SELECT HoTen
FROM GiaoVien, GiangDay
WHERE
      GiaoVien.MaGV = GiangDay.MaGV
      AND MaLop = 'K11'
      AND HocKy = 1
AND Nam = 2006
INTERSECT
SELECT HoTen
FROM GiaoVien, GiangDay
WHERE
      GiaoVien.MaGV = GiangDay.MaGV
      AND MaLop = 'K12'
AND HocKy = 1
AND Nam = 2006

12. 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.

SQL

SELECT HocVien.MaHV, (Ho+' '+Ten) HoTen
FROM HocVien, KetQuaThi
WHERE
      HocVien.MaHV = KetQuaThi.MaHV
      AND MaMH = 'CSDL'
AND LanThi = 1
AND KQua = 'Khong Dat'
      AND NOT EXISTS (
SELECT *
FROM KetQuaThi
WHERE
LanThi > 1
AND KetQuaThi.MaHV = HocVien.MaHV
)

13. 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.

SQL

SELECT MaGV, HoTen
FROM GiaoVien
WHERE MaGV NOT IN (
SELECT MaGV FROM GiangDay
)

14. 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.

SQL

SELECT MaGV, HoTen
FROM GiaoVien
WHERE NOT EXISTS
(
      SELECT *
      FROM MonHoc
      WHERE MonHoc.MaKhoa = GiaoVien.MaKhoa
      AND NOT EXISTS
      (
            SELECT *
            FROM GiangDay
            WHERE GiangDay.MaMH = MonHoc.MaMH
            AND GiangDay.MaGV = GiaoVien.MaGV
      )
)

15. 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.

SQL

SELECT DISTINCT (Ho+' '+Ten) HoTen
FROM HocVien, KetQuaThi
WHERE
      HocVien.MaHV = KetQuaThi.MaHV
      AND MaLop = 'K11'
      AND (
(LanThi = 2 AND Diem = 5)
OR HocVien.MaHV IN (
                  SELECT DISTINCT MaHV
                  FROM KetQuaThi
                  WHERE KQua = 'Khong Dat'
                  GROUP BY MaHV, MaMH
                  HAVING COUNT(*) > 3    
                  )
)

16. 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.

SQL

SELECT HoTen
FROM GiaoVien, GiangDay
WHERE
      GiaoVien.MaGV = GiangDay.MaGV
      AND MaMH = 'CTRR'
GROUP BY GiaoVien.MaGV, HoTen, HocKy
HAVING COUNT(*) >= 2

17. 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).

SQL

SELECT HocVien.*, Diem AS 'Điểm thi CSDL sau cùng'
FROM HocVien, KetQuaThi
WHERE
HocVien.MaHV = KetQuaThi.MaHV
      AND MaMH = 'CSDL'
      AND LanThi = (
            SELECT MAX(LanThi)
            FROM KetQuaThi
            WHERE
MaMH = 'CSDL'
AND KetQuaThi.MaHV = HocVien.MaHV
            GROUP BY MaHV
            )

18. 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).

SQL

SELECT HocVien.*, Diem AS 'Điểm thi CSDL cao nhất'
FROM HocVien, KetQuaThi, MonHoc
WHERE
      HocVien.MaHV = KetQuaThi.MaHV
      AND KetQuaThi.MaMH = MonHoc.MaMH
      AND TenMH = 'Co So Du Lieu'
      AND Diem = (
            SELECT MAX(Diem)
            FROM KetQuaThi, MonHoc
            WHERE
                  KetQuaThi.MaMH = MonHoc.MaMH
                  AND MaHV = HocVien.MaHV
                  AND TenMH = 'Co So Du Lieu'
            GROUP BY MaHV
            )

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

SQL

SELECT MaKhoa, TenKhoa
FROM Khoa
WHERE NgTLap = (SELECT MIN(NgTLap) FROM Khoa)

20. Có bao nhiêu giáo viên có học hàm là “GS” hoặc “PGS”.

SQL

SELECT COUNT(*) 'Số giáo viên có học hàm GS hoặc PGS'
FROM GiaoVien
WHERE HocHam IN ('GS', 'PGS')

21. 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.

SQL

SELECT MaKhoa, HocVi, COUNT(*) 'Số giáo viên'
FROM GiaoVien
GROUP BY MaKhoa, HocVi
ORDER BY MaKhoa

22. 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).

SQL

SELECT MaMH, KQua, COUNT(*) 'Số học viên'
FROM KetQuaThi
GROUP BY MaMH, KQua
ORDER BY MAMH

23. 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.

SQL

SELECT DISTINCT GiaoVien.MaGV, HoTen
FROM GiaoVien, Lop, GiangDay
WHERE GiangDay.MaLop = Lop.MaLop
      AND GiangDay.MaGV = GiaoVien.MaGV
      AND GiaoVien.MaGV = Lop.MaGVCN

24. Tìm họ tên lớp trưởng của lớp có sỉ số cao nhất.

SQL

SELECT Ho+' '+Ten AS 'Họ tên lớp trưởng của lớp có sỉ số cao nhất'
FROM HocVien, Lop
WHERE
      HocVien.MaHV = Lop.TrgLop
      AND Lop.SiSo = (
SELECT MAX(SiSo) FROM Lop
)

25. * 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).

SQL

SELECT Ho + ' ' + Ten 'Họ tên trưởng lớp thi không đạt quá 3 môn'
FROM HocVien, Lop, KetQuaThi
WHERE
      HocVien.MAHV = Lop.TrgLop
      AND HocVien.MaHV = KetQuaThi.MaHV
      AND KQua = 'Khong Dat'
GROUP BY TrgLop, Ho, Ten
HAVING COUNT(*) > 3

26. 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.

SQL

-- Cách 1:

SELECT TOP 1 WITH TIES HocVien.MaHV, (Ho+' '+Ten) AS HoTen
FROM HocVien, KetQuaThi
WHERE
      HocVien.MaHV = KetQuaThi.MaHV
      AND Diem >= 9
GROUP BY HocVien.MaHV, Ho, Ten
ORDER BY COUNT(*) DESC

-- Cách 2:

SELECT HocVien.MaHV, Ho+' '+Ten AS HoTen
FROM HocVien, KetQuaThi
WHERE
      HocVien.MaHV = KetQuaThi.MaHV
      AND Diem >= 9
GROUP BY HocVien.MaHV, Ho, Ten
HAVING COUNT(*) >= ALL(
SELECT COUNT(*)
FROM KetQuaThi
WHERE Diem >= 9
GROUP BY MaHV
)

27. 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.

SQL

SELECT MaLop, MaHV, HoTen
FROM (
      SELECT MaLop, HocVien.MaHV, (Ho+' '+Ten) HoTen, COUNT(*) SoLuongDiem,
RANK() OVER (
PARTITION BY MaLop
ORDER BY COUNT(*) DESC
) AS XepHang
      FROM HocVien, KetQuaThi
      WHERE
            HocVien.MaHV = KetQuaThi.MaHV
            AND Diem >= 9
      GROUP BY
            MaLop, HocVien.MaHV, Ho, Ten
) AS A
WHERE A.XepHang = 1

28. 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.

SQL

SELECT MaGV, COUNT(DISTINCT MaMH) 'Số môn học', COUNT(DISTINCT MALOP) 'Số lớp'
FROM GiangDay
GROUP BY MaGV

29. 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.

SQL

SELECT HocKy, Nam, A.MaGV, HoTen
FROM GiaoVien,
(
      SELECT
            HocKy, Nam, MaGV, RANK() OVER (PARTITION BY HocKy, Nam ORDER BY COUNT(*) DESC) AS XepHang
      FROM GiangDay
      GROUP BY HocKy, Nam, MaGV
) AS A
WHERE
      A.MAGV = GiaoVien.MAGV
      AND XepHang = 1
ORDER BY
      Nam, HocKy

30. 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.

SQL

--- Cách 1:

SELECT TOP 1 WITH TIES
      MonHoc.MaMH, TenMH
FROM
      MonHoc, KetQuaThi
WHERE
      MonHoc.MaMH = KetQuaThi.MaMH
      AND LanThi = 1
      AND KQua = 'Khong Dat'
GROUP BY
      MonHoc.MaMH, TenMH
ORDER BY
      COUNT(*) DESC

-- Cách 2:

SELECT
      MonHoc.MaMH, TenMH
FROM
      MonHoc, KetQuaThi
WHERE
      MonHoc.MaMH = KetQuaThi.MaMH
      AND LanThi = 1
      AND KQua = 'Khong Dat'
GROUP BY
      MonHoc.MaMH, TenMH
HAVING
      COUNT(*) >= ALL (SELECT COUNT(*) FROM KetQuaThi WHERE LanThi = 1 AND KQua = 'Khong Dat' GROUP BY MAMH)

31. 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).

SQL

SELECT DISTINCT
      HocVien.MaHV, (Ho+' '+Ten) HoTen
FROM
      HocVien, KetQuaThi
WHERE
      HocVien.MaHV = KetQuaThi.MaHV
      AND NOT EXISTS
      (
            SELECT *
            FROM KetQuaThi
            WHERE LanThi = 1
            AND KQua = 'Khong Dat'
            AND MaHV = HocVien.MaHV
      )

32. * 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).

SQL

SELECT DISTINCT
      HocVien.MaHV, (Ho+' '+Ten) HoTen
FROM
      HocVien, KetQuaThi
WHERE
      HocVien.MaHV = KetQuaThi.MaHV
      AND NOT EXISTS
      (
            SELECT *
            FROM KetQuaThi
            WHERE LanThi = (SELECT MAX(LanThi) FROM KetQuaThi WHERE MaHV = HocVien.MaHV GROUP BY MaHV)
            AND KQua = 'Khong Dat'
            AND MaHV = HocVien.MaHV
      )

33. * 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).

SQL

SELECT MaHV, (Ho+' '+Ten) HoTen
FROM HocVien
WHERE NOT EXISTS
(
      SELECT *
      FROM MonHoc
      WHERE NOT EXISTS
      (
            SELECT *
            FROM KetQuaThi
            WHERE
                  KetQuaThi.MaMH = MonHoc.MaMH
                  AND KetQuaThi.MaHV = HocVien.MaHV
                  AND LanThi = 1 AND KQua = 'Dat'
      )
)

34. * 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).

35. ** 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)

SQL

SELECT MaMH, MaHV, HoTen
FROM
(
      SELECT
            MaMH, HocVien.MaHV, (Ho+' '+Ten) HoTen, RANK() OVER (PARTITION BY MaMH ORDER BY MAX(Diem) DESC) AS XepHang
      FROM
            HocVien, KetQuaThi
      WHERE
            HocVien.MaHV = KetQuaThi.MaHV
            AND LanThi = (SELECT MAX(LanThi) FROM KetQuaThi WHERE MaHV = HocVien.MaHV GROUP BY MaHV)
      GROUP BY
            MaMH, HocVien.MaHV, Ho, Ten
) AS A
WHERE XepHang = 1

III. Tổng kết

Qua 2 bài tập thực hành CSDL Quản lý bán hàng và Quản lý giáo vụ, chúng ta đã nắm và hiểu được về CSDL quan hệ, SQL cũng như các logic thao tác trên CSDL

Tags: UIT