exam.sql

-- A:
CREATE DATABASE TEST;
GO
USE TEST;
GO

CREATE TABLE NHANVIEN (
	MANV VARCHAR(10) PRIMARY KEY NOT NULL,
	HOTENNV NVARCHAR(50) NOT NULL,
	NGAYSINH DATE,
	DIACHI NVARCHAR(255),
	LUONGCB MONEY);
GO

INSERT INTO NHANVIEN (MANV, HOTENNV, NGAYSINH, DIACHI, LUONGCB)
VALUES ('MPC.001', N'Phạm Thanh Bình', '1985-05-13', N'17, Trần Nguyên Hãn', 70000),
		('MPC.002', N'Nguyễn Thành Duy', '1904-05-12', N'253, Nguyễn Thái Học', 45000);
GO

CREATE TABLE PHICO (
	MAPC VARCHAR(10) NOT NULL PRIMARY KEY,
	TENPHICO NVARCHAR(50) NOT NULL,
	KHOANGCACHBAY INT);
GO

INSERT INTO PHICO (MAPC, TENPHICO, KHOANGCACHBAY)
VALUES ('BL750', 'Airbus B320', 5000),
		('QH228', 'Airbus B321', 1660);
GO

CREATE TABLE CHUYENBAY (
	MACB VARCHAR(10) PRIMARY KEY,
	MAPC VARCHAR(10) FOREIGN KEY REFERENCES PHICO(MAPC),
	NoiXP VARCHAR(20) NOT NULL,
	NoiDen VARCHAR(20) NOT NULL,
	GioXP DATETIME NOT NULL,
	GioDen DATETIME NOT NULL);
GO

INSERT INTO CHUYENBAY
VALUES ('CBND.0001', 'BL750', 'HANOI', 'TPHCM', '2019-10-24 07:00:00', '2019-10-24 10:00:00');
GO

CREATE TABLE CHITIET_CHUYENBAY (
	MACB VARCHAR(10) FOREIGN KEY REFERENCES CHUYENBAY(MACB),
	MANV VARCHAR(10) FOREIGN KEY REFERENCES NHANVIEN(MANV));
GO

INSERT INTO CHITIET_CHUYENBAY VALUES ('CBND.0001', 'MPC.001');
GO

/* B */
-- B1:
SELECT * FROM PHICO
WHERE KHOANGCACHBAY BETWEEN 1000 AND 5000;
GO

-- B2:
SELECT CB.MACB, NoiXP, NoiDen, GioXP, GioDen, TENPHICO, COUNT(CCB.MANV) AS SONHANVIEN
FROM CHUYENBAY CB
	JOIN PHICO PC ON PC.MAPC = CB.MAPC
	JOIN CHITIET_CHUYENBAY CCB ON CCB.MACB = CB.MACB
WHERE YEAR(CB.GioXP) = 2019
GROUP BY CB.MACB, NoiXP, NoiDen, GioXP, GioDen, TENPHICO;
GO

-- B3:
SELECT NV.MANV, HOTENNV, DIACHI, COUNT(CCB.MACB) AS SOLAN_THAMGIA_BAY, COUNT(CCB.MACB) * LUONGCB AS TONGLUONG
FROM NHANVIEN NV
	JOIN CHITIET_CHUYENBAY CCB ON CCB.MANV = NV.MANV
GROUP BY NV.MANV, HOTENNV, DIACHI, LUONGCB;
GO

-- B4:
SELECT *
FROM NHANVIEN NV
	JOIN CHITIET_CHUYENBAY CCB ON CCB.MANV = NV.MANV
	JOIN CHUYENBAY CB ON CB.MACB = CCB.MACB
WHERE YEAR(CB.GioXP) NOT BETWEEN 2012 AND 2020;
GO

-- B5:
SELECT TOP 1 NV.MANV, HOTENNV, YEAR(GETDATE()) - YEAR(NGAYSINH) AS TUOI, COUNT(CCB.MACB) AS SOLANBAY
FROM NHANVIEN NV JOIN CHITIET_CHUYENBAY CCB ON CCB.MANV = NV.MANV
				JOIN CHUYENBAY CB ON CB.MACB = CCB.MACB
				JOIN PHICO PC ON PC.MAPC = CB.MAPC
WHERE YEAR(NGAYSINH) BETWEEN 1984 AND 1989
	AND YEAR(CB.GioXP) = 2019
	AND KHOANGCACHBAY >= 4000
GROUP BY NV.MANV, HOTENNV, NGAYSINH
ORDER BY SOLANBAY DESC;
GO

-- B6:
ALTER TABLE PHICO
ADD CHECK (KHOANGCACHBAY >= 500);
GO

ALTER TABLE CHUYENBAY
ADD CHECK (GioXP < GioDen);
GO