CREATE DATABASE QL_Phi_Co_Hoang_Duc_Anh;
USE QL_Phi_Co_Hoang_Duc_Anh;
CREATE TABLE NHANVIEN (
MANV VARCHAR(10) NOT NULL PRIMARY KEY,
HOTENNV NVARCHAR(30),
NGAYSINH DATE,
DIACHI NVARCHAR(30),
LUONGCB INT
);
CREATE TABLE PHICO (
MAPC VARCHAR(10) NOT NULL PRIMARY KEY,
TENPHICO VARCHAR(30),
KHOANGCACHBAY INT
);
CREATE TABLE CHUYENBAY (
MACB VARCHAR(10) NOT NULL PRIMARY KEY,
MAPC VARCHAR(10) NOT NULL,
NOIXP VARCHAR(30),
NOIDEN VARCHAR(30),
GIOXP DATETIME,
GIODEN DATETIME
FOREIGN KEY(MAPC) REFERENCES PHICO(MAPC)
);
DROP TABLE IF EXISTS CHTIET_CHUYENBAY;
CREATE TABLE CHITIET_CHUYENBAY(
MACB VARCHAR(10) NOT NULL,
MANV VARCHAR(10) NOT NULL,
PRIMARY KEY (MACB, MANV),
FOREIGN KEY (MACB) REFERENCES CHUYENBAY(MACB),
FOREIGN KEY (MANV) REFERENCES NHANVIEN(MANV)
);
INSERT INTO NHANVIEN (MANV, HOTENNV, NGAYSINH, DIACHI, LUONGCB)
VALUES
('MPC.001', N'Đào Duy Thịnh', '1985-05-15', N'30, Võ Nguyên Giáp', 50000);
INSERT INTO PHICO (MAPC, TENPHICO, KHOANGCACHBAY)
VALUES
('BL750', 'AirbusB320', 5000);
INSERT INTO CHUYENBAY (MACB, MAPC, NOIXP, NOIDEN, GIOXP, GIODEN)
VALUES
('CBND.0001', 'BL750', 'HANOI', 'TPHCM', '2019-10-24 07:00:00', '2019-10-24
10:00:00');
INSERT INTO CHITIET_CHUYENBAY (MACB, MANV)
VALUES
('CBND.0001', 'MPC.001');
---------------------------------------------
-- 1
SELECT PC.MAPC, PC.TENPHICO, PC.KHOANGCACHBAY
FROM PHICO PC
WHERE PC.KHOANGCACHBAY BETWEEN 1000 AND 5000
-- 2
SELECT CB.MACB, CB.NOIXP, CB.NOIDEN, CB.GIOXP, CB.GIODEN
FROM CHUYENBAY CB
JOIN (
SELECT TOP 1 CB.MACB, COUNT(CTCB.MANV) AS SNV
FROM CHUYENBAY CB
JOIN CHITIET_CHUYENBAY CTCB ON CB.MACB = CTCB.MACB
WHERE YEAR(CB.GIOXP) = 2019
GROUP BY CB.MACB, CTCB.MACB
ORDER BY SNV DESC
) AS TMP ON CB.MACB = TMP.MACB
-- 3
SELECT NV.MANV, NV.HOTENNV, NV.DIACHI, TMP.SOLANBAY, TMP.SOLANBAY *
NV.LUONGCB
FROM NHANVIEN NV
JOIN (
SELECT NV.MANV, COUNT(CTCB.MACB) AS SOLANBAY
FROM NHANVIEN NV
JOIN CHITIET_CHUYENBAY CTCB ON NV.MANV = CTCB.MANV
JOIN CHUYENBAY CB ON CTCB.MACB = CB.MACB
WHERE CAST(CB.GIOXP AS DATE) BETWEEN CAST('2020-01-01' AS DATE)
AND CAST('2020-07-01' AS DATE)
GROUP BY NV.MANV
) AS TMP ON NV.MANV = TMP.MANV
-- 4
SELECT * FROM PHICO PC
EXCEPT
SELECT * FROM PHICO PC
WHERE EXISTS (
SELECT * FROM CHUYENBAY CB
WHERE CB.MAPC = PC.MAPC
AND YEAR(CB.GIOXP) BETWEEN 2012 AND 2020
)
-- 5
SELECT TMP.MANV, TMP.HOTENNV, TMP.NGAYSINH, TMP.SOLANBAY
FROM (
SELECT NV.MANV, NV.HOTENNV, NV.NGAYSINH, COUNT(CTCB.MACB) AS
SOLANBAY
FROM NHANVIEN NV
JOIN CHITIET_CHUYENBAY CTCB ON NV.MANV = CTCB.MANV
JOIN CHUYENBAY CB ON CTCB.MACB = CB.MACB
WHERE YEAR(CB.GIOXP) = 2019
GROUP BY NV.MANV, NV.HOTENNV, NV.NGAYSINH
) AS TMP
WHERE TMP.SOLANBAY = (
SELECT COUNT(CB.MACB)
FROM CHUYENBAY CB
WHERE YEAR(CB.GIOXP) = 2019
)
-- 7
ALTER TABLE PHICO
ADD CONSTRAINT CHECK_KCB CHECK(KHOANGCACHBAY >= 500);
ALTER TABLE CHUYENBAY
ADD CONSTRAINT CHECK_TG CHECK(GIOXP < GIODEN);
-- 6
SELECT TOP 1 TMP.MANV, TMP.HOTENNV, TMP.NGAYSINH, TMP.TUOI
FROM (
SELECT NV.MANV, NV.HOTENNV, NV.NGAYSINH, (YEAR(GETDATE()) -
YEAR(NV.NGAYSINH)) AS TUOI , COUNT(CB.MACB) AS SOLANBAY,
SUM(PC.KHOANGCACHBAY) AS TONGKHOANGCACHBAY
FROM NHANVIEN NV
JOIN CHITIET_CHUYENBAY CTCB ON NV.MANV = CTCB.MANV
JOIN CHUYENBAY CB ON CTCB.MACB = CB.MACB
JOIN PHICO PC ON CB.MAPC = PC.MAPC
GROUP BY NV.MANV, NV.HOTENNV, NV.NGAYSINH
) AS TMP
WHERE TMP.TONGKHOANGCACHBAY >= 4000 AND YEAR(TMP.NGAYSINH)
BETWEEN 1984 AND 1989
ORDER BY TMP.SOLANBAY ASC
