Giải đề SQL SEVER [Thực hành 2025]

CREATE DATABASE QL_LICHTAPHUAN
USE QL_LICHTAPHUAN

-- CAU 1
-- TAO BANG NHANVIEN
CREATE TABLE NHANVIEN(
	MANV VARCHAR(5) PRIMARY KEY,
	HOTEN NVARCHAR(51),
	TRINHDO NVARCHAR(20)
)

-- TAO BANG CHUYENDE
CREATE TABLE CHUYENDE(
	MACD VARCHAR(5) PRIMARY KEY,
	TENCD NVARCHAR(51),
	SONGAY INT
)
-- TAO BANG PHANCONG
CREATE TABLE PHANCONG(
	MANV VARCHAR(5),
	MACD VARCHAR(5),
	NGAYPC DATE,
	PRIMARY KEY(MANV, MACD),
	FOREIGN KEY(MANV) REFERENCES NHANVIEN(MANV),
	FOREIGN KEY(MACD) REFERENCES CHUYENDE(MACD)
)

-- TAO BANG LICHTAPHUAN
CREATE TABLE LICHTAPHUAN(
	MANV VARCHAR(5),
	MACD VARCHAR(5),
	PRIMARY KEY(MANV, MACD),
	FOREIGN KEY(MANV) REFERENCES NHANVIEN(MANV),
	FOREIGN KEY(MACD) REFERENCES CHUYENDE(MACD),
	NGAYBD DATE,
	NGAYKT DATE
)

-- CAU 2
-- A/
ALTER TABLE NHANVIEN
ADD CONSTRAINT DEFAULT_TRINHDO DEFAULT N'Kỹ sư' FOR TRINHDO

-- TH CÓ NHẬP TRINHDO
INSERT INTO NHANVIEN
VALUES('NV001', N'NGUYỄN VĂN A', N'Thạc sĩ')
-- TH KHÔNG NHẬP TRINHDO
INSERT INTO NHANVIEN
VALUES('NV002', N'NGUYỄN VĂN B', DEFAULT)

-- B
ALTER TABLE CHUYENDE
ADD CONSTRAINT CHECK_TRINHDO CHECK (SONGAY >= 1 AND SONGAY <= 5)
-- TH THOA
INSERT INTO CHUYENDE
VALUES('CD001', N'ỨNG DỤNG AI', 2)
-- TH KHONG THOA
INSERT INTO CHUYENDE
VALUES('CD001', N'ỨNG DỤNG AI', 9)

-- C
ALTER TABLE CHUYENDE
ADD CONSTRAINT UNIQUE_TENCD UNIQUE (TENCD)

-- TH THOA
INSERT INTO CHUYENDE
VALUES('CD002', N'ỨNG DỤNG AI new', 2)
-- TH KHONG THOA
INSERT INTO CHUYENDE
VALUES('CD002', N'ỨNG DỤNG AI', 3)

-- D
CREATE TRIGGER KT_NGAYBD_NGAYPC ON PHANCONG
FOR INSERT, UPDATE
AS
BEGIN
	IF EXISTS (
		SELECT * 
		FROM INSERTED I 
		JOIN LICHTAPHUAN L ON L.MACD = I.MACD
		WHERE I.NGAYPC > L.NGAYBD
		)
			BEGIN
				PRINT(N'THÀNH CÔNG');
				COMMIT TRAN
			END
	ELSE
			BEGIN
				PRINT(N'THẤT BẠI');
				COMMIT TRAN
			END
END;

-- INSERT 
-- TH THOA
INSERT INTO LICHTAPHUAN
VALUES('NV001', 'CD002', '2024-09-5', '2024-10-11')

INSERT INTO PHANCONG
VALUES('NV001', 'CD002', '2024-09-10')

-- TH KHONGTHOA
INSERT INTO LICHTAPHUAN
VALUES('NV001', 'CD001', '2024-09-5', '2024-10-11')

INSERT INTO PHANCONG
VALUES('NV001', 'CD001', '2024-09-1')

-- UPDATE
-- TH THOA
UPDATE PHANCONG
SET NGAYPC = '2024-09-20'
WHERE MACD = 'CD001'

-- TH KHONG THOA
UPDATE PHANCONG
SET NGAYPC = '2024-09-2'
WHERE MACD = 'CD001'

-- E
CREATE TRIGGER CN_NGAYKT ON LICHTAPHUAN
FOR INSERT, UPDATE
AS
BEGIN
	UPDATE L
	SET NGAYKT = DATEADD(DAY, C.SONGAY, I.NGAYBD)
	FROM LICHTAPHUAN L
	JOIN INSERTED I ON I.MACD = L.MACD AND L.MANV = I.MANV -- UPDATE
	jOIN CHUYENDE C ON I.MACD = C.MACD -- INSERT
END

-- INSERT 
-- TH THOA
INSERT INTO CHUYENDE
VALUES('CD003', 'AI', 5)

INSERT INTO LICHTAPHUAN
VALUES('NV001', 'CD003', '2024-09-5', NULL)

-- UPDATE 
-- TH THOA
UPDATE LICHTAPHUAN
SET NGAYBD = '2024-3-2'
WHERE MACD = 'CD003'

-- CAU 3
-- NHAP NHANVIEN
INSERT INTO NHANVIEN VALUES
('NV003', N'NGUYỄN VĂN C', N'Tiến sĩ'),
('NV004', N'NGUYỄN VĂN D', N'Thạc sĩ'),
('NV005', N'NGUYỄN VĂN E', N'Kỹ sư'),
('NV006', N'NGUYỄN VĂN F', N'Tiến sĩ')

-- NHAP CHUYENDE
INSERT INTO CHUYENDE VALUES
('CD004', N'An toàn trong sản xuất', 3),
('CD005', N'Kỹ năng đặt câu hỏi phỏng vấn', 5),
('CD006', N'Kỹ năng ATTT', 2),
('CD007', N'Kỹ năng sống', 1)

-- NHAP LICHTAPHUAN
INSERT INTO LICHTAPHUAN VALUES
('NV003', 'CD002','2024-1-1', NULL),
('NV005', 'CD006','2024-2-1', NULL),
('NV006', 'CD004','2024-3-1', NULL),
('NV004', 'CD005','2024-4-1', NULL)

-- NHAP PHANCONG
INSERT INTO PHANCONG VALUES
('NV002', 'CD002', '2024-2-2'),
('NV002', 'CD003', '2024-4-2'),
('NV003', 'CD003', '2024-4-2'),
('NV004', 'CD005', '2024-7-2')

-- CAU 4
-- A
SELECT T.MANV, N.HOTEN
FROM LICHTAPHUAN T
JOIN NHANVIEN N ON N.MANV = T.MANV
JOIN CHUYENDE C ON C.MACD = T.MACD
WHERE TENCD = N'An toàn trong sản xuất'

-- B
SELECT TRINHDO, COUNT(N.MANV) 
FROM LICHTAPHUAN T
JOIN NHANVIEN N ON N.MANV = T.MANV
JOIN CHUYENDE C ON C.MACD = T.MACD
WHERE C.TENCD = N'Kỹ năng đặt câu hỏi phỏng vấn'
GROUP BY TRINHDO
HAVING COUNT(N.MANV) > 0

-- C
SELECT T.MACD, C.TENCD, T.NGAYBD
FROM LICHTAPHUAN T
JOIN CHUYENDE C ON C.MACD = T.MACD
WHERE MONTH(T.NGAYBD) = 5 AND YEAR(T.NGAYBD) = 2025

-- D
SELECT N.MANV, N.HOTEN, C.NGAYPC
FROM PHANCONG C
JOIN NHANVIEN N ON N.MANV = C.MANV
WHERE YEAR(C.NGAYPC) = 2024 AND MONTH(C.NGAYPC) = 2

EXCEPT

SELECT N.MANV, N.HOTEN, C.NGAYPC
FROM LICHTAPHUAN L
JOIN PHANCONG C ON C.MANV = L.MANV
JOIN NHANVIEN N ON N.MANV = L.MANV
WHERE YEAR(C.NGAYPC) = 2024 AND MONTH(C.NGAYPC) = 2