Query_QLGV.sql

-- A Ngôn ngữ định nghĩa dữ liệu (Data Definition Language)
-- 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. Thêm vào 3 thuộc tính GHICHU, DIEMTB, XEPLOAI cho quan hệ HOCVIEN.
-- Tạo PRIMARY KEY
ALTER TABLE KHOA ADD CONSTRAINT PK_KHOA PRIMARY KEY(MAKHOA)
ALTER TABLE MONHOC ADD CONSTRAINT PK_MONHOC PRIMARY KEY(MAMH)
ALTER TABLE DIEUKIEN ADD CONSTRAINT PK_DIEUKIEN PRIMARY KEY(MAMH, MAMH_TRUOC)
ALTER TABLE GIAOVIEN ADD CONSTRAINT PK_GIAOVIEN PRIMARY KEY(MAGV)
ALTER TABLE LOP ADD CONSTRAINT PK_LOP PRIMARY KEY(MALOP)
ALTER TABLE HOCVIEN ADD CONSTRAINT PK_HOCVIEN PRIMARY KEY(MAHV)
ALTER TABLE GIANGDAY ADD CONSTRAINT PK_GIANGDAY PRIMARY KEY(MALOP, MAMH)
ALTER TABLE KETQUATHI ADD CONSTRAINT PK_KETQUATHI PRIMARY KEY(MAHV, MAMH, LANTHI)

-- Tạo FOREIGN KEY
ALTER TABLE KHOA ADD CONSTRAINT FK_KHOA_GIAOVIEN FOREIGN KEY(TRGKHOA) REFERENCES GIAOVIEN(MAGV)
ALTER TABLE MONHOC ADD CONSTRAINT FK_MONHOC_KHOA FOREIGN KEY(MAKHOA) REFERENCES KHOA(MAKHOA)
ALTER TABLE DIEUKIEN ADD CONSTRAINT FK_DIEUKIEN_MONHOC FOREIGN KEY(MAMH) REFERENCES MONHOC(MAMH)
ALTER TABLE GIAOVIEN ADD CONSTRAINT FK_GIAOVIEN_KHOA FOREIGN KEY(MAKHOA) REFERENCES KHOA(MAKHOA)
ALTER TABLE LOP ADD CONSTRAINT FK_LOP_GIAOVIEN FOREIGN KEY(MAGVCN) REFERENCES GIAOVIEN(MAGV)
ALTER TABLE LOP ADD CONSTRAINT FK_LOP_HOCVIEN FOREIGN KEY(TRGLOP) REFERENCES HOCVIEN(MAHV)
ALTER TABLE HOCVIEN ADD CONSTRAINT FK_HOCVIEN_LOP FOREIGN KEY(MALOP) REFERENCES LOP(MALOP)
ALTER TABLE GIANGDAY ADD CONSTRAINT FK_GIANGDAY_LOP FOREIGN KEY(MALOP) REFERENCES LOP(MALOP)
ALTER TABLE GIANGDAY ADD CONSTRAINT FK_GIANGDAY_MONHOC FOREIGN KEY(MAMH) REFERENCES MONHOC(MAMH)
ALTER TABLE GIANGDAY ADD CONSTRAINT FK_GIANGDAY_GIAOVIEN FOREIGN KEY(MAGV) REFERENCES GIAOVIEN(MAGV)
ALTER TABLE KETQUATHI ADD CONSTRAINT FK_KETQUATHI_HOCVIEN FOREIGN KEY(MAHV) REFERENCES HOCVIEN(MAHV)
ALTER TABLE KETQUATHI ADD CONSTRAINT FK_KETQUATHI_MONHOC FOREIGN KEY(MAMH) REFERENCES MONHOC(MAMH)

-- Thêm thuộc tính 
ALTER TABLE HOCVIEN ADD GHICHU VARCHAR(60)
ALTER TABLE HOCVIEN ADD DIEMTB NUMERIC(4, 2)
ALTER TABLE HOCVIEN ADD XEPLOAI VARCHAR(10)

-- 3. Thuộc tính GIOITINH chỉ có giá trị là “Nam” hoặc “Nu”.
ALTER TABLE HOCVIEN ADD CONSTRAINT CK_GIOITINH 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).
ALTER TABLE KETQUATHI ADD CONSTRAINT CK_DIEM CHECK(
	DIEM BETWEEN 0 AND 10 AND
	LEN(SUBSTRING(CAST(DIEM AS VARCHAR), CHARINDEX('.', DIEM) + 1, 1000)) >= 2
)

-- 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.
ALTER TABLE KETQUATHI ADD CONSTRAINT CK_KQUA CHECK(KQUA = IIF(DIEM BETWEEN 5 AND 10, 'Dat', 'Khong dat'))

-- 6. Học viên thi một môn tối đa 3 lần.
ALTER TABLE KETQUATHI ADD CONSTRAINT CK_SOLANTHI CHECK(LANTHI <= 3)

-- 7. Học kỳ chỉ có giá trị từ 1 đến 3.
ALTER TABLE GIANGDAY ADD CONSTRAINT CK_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”.
ALTER TABLE GIAOVIEN ADD CONSTRAINT CK_HOCVI CHECK(HOCVI IN ('CN', 'KS', 'Ths', 'TS', 'PTS'))

-- 11. Học viên ít nhất là 18 tuổi.
ALTER TABLE HOCVIEN ADD CONSTRAINT CK_TUOI CHECK(GETDATE() - 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).
ALTER TABLE GIANGDAY ADD CONSTRAINT CK_NGAY CHECK(TUNGAY < DENNGAY)

-- 13. Giáo viên khi vào làm ít nhất là 22 tuổi.
ALTER TABLE GIAOVIEN ADD CONSTRAINT CK_NGVL CHECK(GETDATE() - NGVL >= 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.
ALTER TABLE MONHOC ADD CONSTRAINT CK_TC CHECK(ABS(TCLT - TCTH) <= 3)

-- B Ngôn ngữ thao tác dữ liệu (Data Manipulation Language)
-- 19. 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.02 
WHERE MAGV IN (
	SELECT TRGKHOA FROM KHOA
)

/* 20. 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 HV 
SET DIEMTB = DTB_HOCVIEN.DTB
FROM HOCVIEN HV LEFT JOIN (
	SELECT MAHV, AVG(DIEM) AS DTB 
	FROM KETQUATHI A
	WHERE NOT EXISTS (
		SELECT 1 
		FROM KETQUATHI B 
		WHERE A.MAHV = B.MAHV AND A.MAMH = B.MAMH AND A.LANTHI < B.LANTHI
	) 
	GROUP BY MAHV
) DTB_HOCVIEN
ON HV.MAHV = DTB_HOCVIEN.MAHV

-- 21. 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 = 'Cam thi'
WHERE MAHV IN (
	SELECT MAHV 
	FROM KETQUATHI 
	WHERE LANTHI = 3 AND DIEM < 5
)

/*22. Cập nhật giá trị cho cột XEPLOAI trong quan hệ HOCVIEN như sau:
	a. Nếu DIEMTB ≥ 9 thì XEPLOAI = “XS”
	b. Nếu 8 ≤ DIEMTB < 9 thì XEPLOAI = “G”
	c. Nếu 6.5 ≤ DIEMTB < 8 thì XEPLOAI = “K”
	d. Nếu 5 ≤ DIEMTB < 6.5 thì XEPLOAI = “TB”
	e. Nếu DIEMTB < 5 thì XEPLOAI = “Y” */
UPDATE HOCVIEN 
SET XEPLOAI = CASE 
	WHEN DIEMTB >= 9 THEN 'XS'
	WHEN DIEMTB >= 8 THEN 'G'
	WHEN DIEMTB >= 6.5 THEN 'K'
	WHEN DIEMTB >= 5 THEN 'TB'
	ELSE 'Y'
END

-- C Ngôn ngữ truy vấn dữ liệu
-- 23. 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, HO + ' ' + TEN AS HOTEN, NGSINH, HV.MALOP 
FROM HOCVIEN HV INNER JOIN LOP 
ON HV.MAHV = LOP.TRGLOP

-- 24. 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 KQ.MAHV, HO + ' ' + TEN AS HOTEN, LANTHI, DIEM 
FROM KETQUATHI KQ INNER JOIN HOCVIEN HV 
ON KQ.MAHV = HV.MAHV
WHERE LEFT(KQ.MAHV, 3) = 'K12' AND MAMH = 'CTRR'
ORDER BY TEN, HO

-- 25. 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 KQ.MAHV, HO + ' ' + TEN AS HOTEN, MAMH
FROM KETQUATHI KQ INNER JOIN HOCVIEN HV 
ON KQ.MAHV = HV.MAHV
GROUP BY KQ.MAHV, HO, TEN, MAMH, KQUA
HAVING MAX(LANTHI) = 1 AND KQUA ='DAT'
ORDER BY KQ.MAHV

-- 26. 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 KQ.MAHV, HO + ' ' + TEN AS HOTEN
FROM KETQUATHI KQ INNER JOIN HOCVIEN HV 
ON KQ.MAHV = HV.MAHV
WHERE LEFT(KQ.MAHV, 3) = 'K11' AND MAMH = 'CTRR' AND LANTHI = 1 AND KQUA = 'Khong Dat'

-- 27. 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 MAMH, TENMH FROM MONHOC
WHERE MAMH IN (
	SELECT DISTINCT MAMH 
	FROM GIANGDAY GD INNER JOIN GIAOVIEN GV 
	ON GD.MAGV = GV.MAGV 
	WHERE HOTEN = 'Tran Tam Thanh' AND HOCKY = 1 AND NAM = 2006
)

-- 28. 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 MAMH, TENMH FROM MONHOC
WHERE MAMH IN (
	SELECT DISTINCT MAMH FROM GIANGDAY WHERE MAGV IN (
		SELECT MAGVCN FROM LOP WHERE MALOP = 'K11'
	) AND HOCKY = 1 AND NAM = 2006
)

-- 29. 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 HO + ' ' + TEN AS HOTEN FROM HOCVIEN
WHERE MAHV IN (
	SELECT TRGLOP FROM LOP 
	WHERE MALOP IN (
		SELECT DISTINCT MALOP FROM GIANGDAY 
		WHERE MAGV IN (
			SELECT MAGV FROM GIAOVIEN WHERE HOTEN = 'Nguyen To Lan'
		) AND MAMH IN (
			SELECT MAMH FROM MONHOC WHERE TENMH = 'Co So Du Lieu'
		)
	)
)

-- 30. 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 MAMH, TENMH FROM MONHOC
WHERE MAMH IN (
	SELECT MAMH_TRUOC FROM DIEUKIEN WHERE MAMH IN (
		SELECT MAMH FROM MONHOC WHERE TENMH = 'Co So Du Lieu'
	)
)

-- 31. 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 HOTEN FROM GIAOVIEN 
WHERE MAGV IN (
	SELECT MAGV FROM GIANGDAY 
	WHERE MAMH = 'CTRR' AND MALOP IN ('K11', 'K12') AND HOCKY = 1 AND NAM = 2006
	GROUP BY MAGV 
	HAVING COUNT(DISTINCT MALOP) = 2
)