IT004.F13-CSDL-ThCuoiKi.sql

/*
	Họ tên: Lê Văn Duyệt 
	MSSV: 13520171
	Lớp: IT004.F13
	Đề: 2
*/

------------------------------------------

-- INIT 
CREATE DATABASE TH_CUOIKI
USE TH_CUOIKI


-- CREATE TABLE
CREATE TABLE SINHVIEN (
	MaSV CHAR(4) PRIMARY KEY,
	HoTen VARCHAR(50),
	MaLop CHAR(8),
	DiemTB FLOAT
)

CREATE TABLE LOP (
	MaLop CHAR(8) PRIMARY KEY,
	TenLop VARCHAR(20),
	KhoaHoc INT,
	MaKhoa CHAR(4)
)

CREATE TABLE KHOA (
	MaKhoa CHAR(4) PRIMARY KEY,
	TenKhoa VARCHAR(20)
)

CREATE TABLE MONHOC (
	MaMon CHAR(6) PRIMARY KEY,
	TenMon VARCHAR(20),
	MaKhoa CHAR(4),
	SoTC INT
)

CREATE TABLE DIEMTHI (
	MaMon CHAR(6) FOREIGN KEY REFERENCES MONHOC(MaMon),
	MaSV CHAR(4) FOREIGN KEY REFERENCES SINHVIEN(MaSV),
	Diem FLOAT,
	CONSTRAINT PK_DIEMTHI PRIMARY KEY (MaMon, MaSV)
)

-- SAME DATA 
INSERT INTO SINHVIEN (MaSV, HoTen, MaLop, DiemTB) 
VALUES 
	('0001', 'Nguyen Van A', 'IT001', 0), 
	('0002', 'Nguyen Van B', 'IT001', 0), 
	('0003', 'Nguyen Van C', 'IT002', 0), 
	('0004', 'Nguyen Van D', 'IT002', 0);

INSERT INTO LOP (MaLop, TenLop, KhoaHoc, MaKhoa)
VALUES 
	('IT001', 'A', '2013', 'IS01'),
	('IT002', 'B', '2013', 'IS01'),
	('IT003', 'B', '2014', 'IS02'),
	('IT004', 'C', '2015', 'IS02');


INSERT INTO KHOA (MaKhoa, TenKhoa) 
VALUES 
	('IS01', 'HTTT1'),
	('IS02', 'HTTT2'),
	('IS03', 'HTTT3'),
	('IS04', 'HTTT4');

INSERT INTO MONHOC (MaMon, TenMon, MaKhoa, SoTC)
VALUES 
	('MA001', 'Toan', 'IS01', 4),
	('MA002', 'Toan 2', 'IS02', 3),
	('MA003', 'Toan 3', 'IS02', 2),
	('MA004', 'Toan 4', 'IS03', 4);

INSERT INTO DIEMTHI (MaMon, MaSV, Diem)
VALUES 
	('MA001', '0001', 6),
	('MA002', '0001', 1),
	('MA003', '0003', 2),
	('MA001', '0002', 7),
	('MA004', '0001', 2);

-- ------------------------------------- --

-- 2.1 Điểm thi của 1 SV có giá trị từ 0 đến 10 
ALTER TABLE DIEMTHI ADD CONSTRAINT DIEMTHI_CHECK_DIEM CHECK(Diem BETWEEN 0 AND 10)
	-- TEST 
	INSERT INTO DIEMTHI (MaMon, MaSV, Diem)
	VALUES 
		('MA003', '0001', 16),
		('MA002', '0001', 3);
	
-- 2.2 Điểm trung bình của 1 sv là điểm trung bình của tất cả các môn mà SV đó đã học. 
CREATE TRIGGER DIEM_TB_SV_DELETE_DIEMTHI 
ON DIEMTHI 
FOR DELETE 
AS 
	DECLARE @MaSV CHAR(4)
	DECLARE @DiemTB FLOAT 
	
	SELECT @MaSV = MaSV FROM DELETED
	SELECT @DiemTB = AVG(Diem) FROM DIEMTHI WHERE MaSV = @MaSV
	UPDATE SINHVIEN SET DiemTB = @DiemTB WHERE MaSV = @MaSV

	------------------------------
	-- TEST 
	DELETE FROM DIEMTHI WHERE MaSV = '0001' AND MaMon = 'MA001'
	SELECT * FROM SINHVIEN WHERE MaSV = '0001'
	
-- 3.1 (MaSV, HoTen, TenKhoa) khóa 2013, đã học môn 'Co so Du Lieu'
SELECT MaSV, HoTen, TenKhoa
FROM SINHVIEN SV, KHOA, LOP, MONHOC
WHERE 
	SV.MaLop = LOP.MaLop 
	AND KHOA.MaKhoa = LOP.MaKhoa 
	AND MONHOC.MaKhoa = KHOA.MaKhoa
	
	AND LOP.KhoaHoc = '2013' 
	AND MONHOC.TenMon = 'Co so Du Lieu'
	
-- 3.2 Danh sách sinh viên của khoa "He thong Thong tin" chưa rớt môn nào
SELECT DIEMTHI.MASV, HoTen, TenKhoa
FROM SINHVIEN SV, KHOA, DIEMTHI, LOP
WHERE 
	SV.MaLop = LOP.MaLop 
	AND KHOA.MaKhoa = LOP.MaKhoa 
	AND DIEMTHI.MaSV = SV.MaSV 
	AND KHOA.TenKhoa = 'He thong Thong tin'
	AND DIEMTHI.Diem >= 5
GROUP BY DIEMTHI.MASV, SV.HoTen, KHOA.TenKhoa
HAVING COUNT(DIEMTHI.Diem) > 0

-- 3.3 Liệt kê danh sách sinh viên của khóa 2011, học tất cả môn của khoa "He thong Thong tin"
SELECT DISTINCT SV.MaSV, SV.HoTen, KHOA.TenKhoa
FROM SINHVIEN SV, KHOA, LOP, MONHOC
WHERE 
	SV.MaLop = LOP.MaLop
	AND LOP.MaKhoa = KHOA.MaKhoa
	AND LOP.KhoaHoc = '2011' 
	AND NOT EXISTS (
		SELECT * FROM MONHOC, KHOA
		WHERE MONHOC.MAKHOA = KHOA.MAKHOA
			AND KHOA.TenKhoa = 'He thong Thong tin'
			AND NOT EXISTS (
				SELECT * FROM MONHOC, LOP
				WHERE MONHOC.MaMon = KHOA.MaKhoa AND LOP.MaLop = SV.MaLop
			)
	)

-- 3.4 Thống kê số lượng sv có điểm trung bình lớn hơn 8.0 theo từng khoa, sắp theo số lượng SV giảm dần 
SELECT KHOA.MaKhoa, KHOA.TenKhoa, COUNT(SV.MaSV) AS SL
FROM KHOA, SINHVIEN SV, LOP
WHERE 
	SV.MaLop = LOP.MaLop 
	AND LOP.MaKhoa = KHOA.MaKhoa 
	AND SV.DiemTB >= 0
GROUP BY KHOA.MaKhoa, KHOA.TenKhoa
ORDER BY COUNT(SV.MaSV) DESC

-- 3.5 Trong các SV có điểm TB cao nhất, liệt kê danh sách SV k có môn nào đã học điểm dưới 5
SELECT TOP 1 WITH TIES SV.MaSV, SV.HoTen, KHOA.TenKhoa
FROM SINHVIEN SV, KHOA, LOP, DIEMTHI
WHERE 
	SV.MaLop = LOP.MaLop 
	AND LOP.MaKhoa = KHOA.MaKhoa 
	AND DIEMTHI.MaSV = SV.MaSV 
	AND DIEMTHI.Diem >= 5
ORDER BY SV.DiemTB DESC