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 [Link], [Link], [Link]
FROM PHICO PC
WHERE [Link] BETWEEN 1000 AND 5000
-- 2
SELECT [Link], [Link], [Link], [Link], [Link]
FROM CHUYENBAY CB
JOIN (
SELECT TOP 1 [Link], COUNT([Link]) AS SNV
FROM CHUYENBAY CB
JOIN CHITIET_CHUYENBAY CTCB ON [Link] = [Link]
WHERE YEAR([Link]) = 2019
GROUP BY [Link], [Link]
ORDER BY SNV DESC
) AS TMP ON [Link] = [Link]
-- 3
SELECT [Link], [Link], [Link], [Link], [Link] *
[Link]
FROM NHANVIEN NV
JOIN (
SELECT [Link], COUNT([Link]) AS SOLANBAY
FROM NHANVIEN NV
JOIN CHITIET_CHUYENBAY CTCB ON [Link] = [Link]
JOIN CHUYENBAY CB ON [Link] = [Link]
WHERE CAST([Link] AS DATE) BETWEEN CAST('2020-01-01' AS DATE)
AND CAST('2020-07-01' AS DATE)
GROUP BY [Link]
) AS TMP ON [Link] = [Link]
-- 4
SELECT * FROM PHICO PC
EXCEPT
SELECT * FROM PHICO PC
WHERE EXISTS (
SELECT * FROM CHUYENBAY CB
WHERE [Link] = [Link]
AND YEAR([Link]) BETWEEN 2012 AND 2020
)
-- 5
SELECT [Link], [Link], [Link], [Link]
FROM (
SELECT [Link], [Link], [Link], COUNT([Link]) AS
SOLANBAY
FROM NHANVIEN NV
JOIN CHITIET_CHUYENBAY CTCB ON [Link] = [Link]
JOIN CHUYENBAY CB ON [Link] = [Link]
WHERE YEAR([Link]) = 2019
GROUP BY [Link], [Link], [Link]
) AS TMP
WHERE [Link] = (
SELECT COUNT([Link])
FROM CHUYENBAY CB
WHERE YEAR([Link]) = 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 [Link], [Link], [Link], [Link]
FROM (
SELECT [Link], [Link], [Link], (YEAR(GETDATE()) -
YEAR([Link])) AS TUOI , COUNT([Link]) AS SOLANBAY,
SUM([Link]) AS TONGKHOANGCACHBAY
FROM NHANVIEN NV
JOIN CHITIET_CHUYENBAY CTCB ON [Link] = [Link]
JOIN CHUYENBAY CB ON [Link] = [Link]
JOIN PHICO PC ON [Link] = [Link]
GROUP BY [Link], [Link], [Link]
) AS TMP
WHERE [Link] >= 4000 AND YEAR([Link])
BETWEEN 1984 AND 1989
ORDER BY [Link] ASC