QLCB_BTVN_TUAN 8, Sql Server

QLCB_BTVN_TUAN 8

CREATE TABLE KHACHHANG(
    MAKH NCHAR(15),
    TEN  NCHAR(50),
    DCHI NCHAR(50),
    DTHOAI NCHAR(12),
    PRIMARY KEY(MAKH)
);

CREATE TABLE NHANVIEN(
    MANV NCHAR(15),
    TEN  NCHAR(50),
    DCHI NCHAR(50),
    DTHOAI NCHAR(12),
    LUONG FLOAT,
    LOAINV BIT,
    PRIMARY KEY(MANV)
);

CREATE TABLE LOAIMB(
    MALOAI NCHAR(15),
    HANGSX NCHAR(15),
    PRIMARY KEY(MALOAI)
);

CREATE TABLE MAYBAY(
    SOHIEU INT,
    MALOAI NCHAR(15),
    PRIMARY KEY(SOHIEU, MALOAI)
)

CREATE TABLE CHUYENBAY(
    MACB  NCHAR(15),
    SBDI  NCHAR(3),
    SBDEN NCHAR(3),
    GIODI TIME,
    GIODEN TIME,
    PRIMARY KEY(MACB)
);

CREATE TABLE LICHBAY(
    NGAYDI DATE,
    MACB   NCHAR(15),
    SOHIEU INT,
    MALOAI NCHAR(15)
    PRIMARY KEY(NGAYDI, MACB)
);

CREATE TABLE DATCHO(
    MAKH NCHAR(15),
    NGAYDI DATE,
    MACB   NCHAR(15)
    PRIMARY KEY(MAKH, NGAYDI, MACB)
);

CREATE TABLE KHANANG(
    MANV NCHAR(15),
    MALOAI NCHAR(15),
    PRIMARY KEY(MANV, MALOAI)
);

CREATE TABLE PHANCONG(
    MANV NCHAR(15),
    NGAYDI DATE,
    MACB NCHAR(15),
    PRIMARY KEY(MANV, NGAYDI, MACB)
);

ALTER TABLE DATCHO
ADD CONSTRAINT FK_DATCHO_KHACHHANG
FOREIGN KEY (MAKH) REFERENCES KHACHHANG(MAKH)

ALTER TABLE DATCHO
ADD CONSTRAINT FK_DATCHO_LICHBAY
FOREIGN KEY (NGAYDI, MACB) REFERENCES LICHBAY(NGAYDI, MACB)

ALTER TABLE LICHBAY
ADD CONSTRAINT FK_LICHBAY_CHUYENBAY
FOREIGN KEY (MACB) REFERENCES CHUYENBAY(MACB)

ALTER TABLE LICHBAY
ADD CONSTRAINT FK_LICHBAY_MAYBAY
FOREIGN KEY (SOHIEU, MALOAI) REFERENCES MAYBAY(SOHIEU, MALOAI)

ALTER TABLE MAYBAY
ADD CONSTRAINT FK_MAYBAY_LOAIMB
FOREIGN KEY (MALOAI) REFERENCES LOAIMB(MALOAI)

ALTER TABLE PHANCONG
ADD CONSTRAINT FK_PHANCONG_LICHBAY
FOREIGN KEY (NGAYDI, MACB) REFERENCES LICHBAY(NGAYDI, MACB)

ALTER TABLE PHANCONG
ADD CONSTRAINT FK_PHANCONG_NHANVIEN
FOREIGN KEY (MANV) REFERENCES NHANVIEN(MANV)

ALTER TABLE KHANANG
ADD CONSTRAINT FK_KHANANG_NHANVIEN
FOREIGN KEY (MANV) REFERENCES NHANVIEN(MANV)

ALTER TABLE KHANANG
ADD CONSTRAINT FK_KHANANG_LOAIMB
FOREIGN KEY (MALOAI) REFERENCES LOAIMB(MALOAI)

INSERT INTO NHANVIEN VALUES
('1006', 'CHI', '12/6 NGUYEN KIEM', '8120012', '150000', '0'),
('1005','GIAO','65 NGUYEN THAI SON','8324467','500000','0'),
('1001','HUONG','8 DIEN BIEN PHU','8330733','500000','1'),
('1002','PHONG','1 LY THUONG KIET','8308117','450000','1'),
('1004','PHUONG','351 LAC LONG QUAN','8308155','250000','0'),
('1003','QUANG','78 TRUONG DINH','8324461','350000','1'),
('1007','TAM','36 NGUYEN VAN CU','8458188','500000','0')

INSERT INTO LOAIMB VALUES
('A310','AIRBUS'),
('A320','AIRBUS'),
('A330','AIRBUS'),
('A340','AIRBUS'),
('B727','BOEING'),
('B747','BOEING'),
('B757','BOEING'),
('DC10','MD'),
('DC9','MD')

INSERT INTO KHACHHANG VALUES
('0009','NGA','223 NGUYEN TRAI','8932320'),
('0101','ANH','567 TRAN PHU','8826729'),
('0045','THU','285 LE LOI','8932203'),
('0012','HA','435 QUANG TRUNG','8933232'),
('0238','HUNG','456 PASTEUR','9812101'),
('0397','THANH','234 LE VAN SI','8952943'),
('0582','MAI','789 NGUYEN DU',''),
('0934','MINH','678 LE LAI',''),
('0091','HAI','345 HUNG VUONG','8893223'),
('0314','PHUONG','395 VO VAN TAN','8232320'),
('0613','VU','348 CMT8','8343232'),
('0586','SON','123 BACH DANG','8556223'),
('0422','TIEN','75 NGUYEN THONG','8332222')

INSERT INTO KHANANG VALUES
('1001','B727'),
('1001','B747'),
('1001','DC10'),
('1001','DC9'),
('1002','A320'),
('1002','A340'),
('1002','B757'),
('1002','DC9'),
('1003','A310'),
('1003','DC9')

INSERT INTO MAYBAY VALUES
('11','B727'),
('13','B727'),
('10','B747'),
('13','B747'),
('22','B757'),
('93','B757'),
('21','DC9'),
('22','DC9'),
('23','DC9'),
('24','DC9'),
('21','DC10'),
('70','A310'),
('80','A310')

INSERT INTO CHUYENBAY VALUES
('100','SLC','BOS','08:00:00','17:50:00'),
('112','DCA','DEN','14:00:00','18:07:00'),
('121','STL','SLC','07:00:00','09:13:00'),
('122','STL','YYV','08:30:00','10:19:00'),
('206','DFW','STL','09:00:00','11:40:00'),
('330','JFK','YYV','16:00:00','18:53:00'),
('334','ORD','MIA','12:00:00','14:14:00'),
('335','MIA','ORD','15:00:00','17:14:00'),
('336','ORD','MIA','18:00:00','20:14:00'),
('337','MIA','ORD','20:30:00','23:53:00'),
('394','DFW','MIA','19:00:00','21:30:00'),
('395','MIA','DFW','21:00:00','23:43:00'),
('449','CDG','DEN','10:00:00','19:29:00'),
('930','YYV','DCA','13:00:00','16:10:00'),
('931','DCA','YYV','17:00:00','18:10:00'),
('932','DCA','YYV','18:00:00','19:10:00'),
('991','BOS','ORD','17:00:00','18:22:00')

INSERT INTO LICHBAY VALUES 
('2000/11/1','100','80','A310'),
('2000/11/1','112','21','DC10'),
('2000/11/1','206','22','DC9'),
('2000/11/1','334','10','B747'),
('2000/11/1','395','23','DC9'),
('2000/11/1','991','22','B757'),
('2000/11/1','337','10','B747'),
('2000/10/31','100','11','B727'),
('2000/10/31','112','11','B727'),
('2000/10/31','206','13','B727'),
('2000/10/31','334','10','B747'),
('2000/10/31','335','10','B747'),
('2000/10/31','337','24','DC9'),
('2000/10/31','449','70','A310')

INSERT INTO DATCHO VALUES
('0009','11/1/2000','100'),
('0009','2000/10/31','449'),
('0045','11/1/2000','991'),
('0012','2000/10/31','206'),
('0238','2000/10/31','334'),
('0582','11/1/2000','991'),
('0091','11/1/2000','100'),
('0314','2000/10/31','449'),
('0613','11/1/2000','100'),
('0586','11/1/2000','991'),
('0586','2000/10/31','100'),
('0422','2000/10/31','449')

INSERT INTO PHANCONG VALUES
('1001','2000/11/1','100'),
('1001','2000/10/31','100'),
('1002','2000/11/1','100'),
('1002','2000/10/31','100'),
('1003','2000/10/31','100'),
('1003','2000/10/31','337'),
('1004','2000/10/31','100'),
('1004','2000/10/31','337'),
('1005','2000/10/31','337'),
('1006','2000/11/1','991'),
('1006','2000/10/31','337'),
('1007','2000/11/1','112'),
('1007','2000/11/1','991'),
('1007','2000/10/31','206')

SELECT LOAI = (CASE
               WHEN LOAINV = 0 THEN 'TIEP VIEN'
               WHEN LOAINV = 1 THEN 'PHI CONG'
               END), LUONG, COUNT(*) AS SLNV
FROM NHANVIEN
GROUP BY LOAINV, LUONG WITH CUBE

SELECT NGAYDI, MACB, COUNT(*) AS SL
FROM LICHBAY
WHERE MALOAI = 'B747' OR MALOAI = 'B727'
GROUP BY NGAYDI, MACB WITH ROLLUP

;WITH TB AS (
    SELECT TEN, LUONG FROM NHANVIEN)
SELECT TEN, LUONG FROM TB UNION
SELECT '@TRUNG BINH NV', AVG(LUONG) FROM TB UNION
SELECT '@MIN', MIN(LUONG) FROM TB UNION
SELECT '@MAX', MAX(LUONG) FROM TB
ORDER BY TEN

;WITH TB AS (
    SELECT KH.MAKH, TEN, COUNT(*) AS TONGSOCB
    FROM KHACHHANG KH LEFT JOIN DATCHO DC ON KH.MAKH = DC.MAKH
    WHERE KH.MAKH>=0009 AND KH.MAKH<=0400
    GROUP BY KH.MAKH, TEN
    )
SELECT MAKH, TEN, TONGSOCB FROM TB UNION
SELECT '', '@TONG SO CB TOAN BO KH', SUM(TONGSOCB) FROM TB

;WITH TB AS (
    SELECT MACB, NGAYDI, COUNT(MANV) AS PHD
    FROM PHANCONG
    GROUP BY MACB, NGAYDI
    )
SELECT MACB, NGAYDI, PHD FROM TB UNION
SELECT 'AVG','', AVG(PHD) FROM TB UNION
SELECT 'MAX', '', MAX(PHD) FROM TB UNION
SELECT 'MIN', '', MIN(PHD) FROM TB;

SELECT SBDI, SBDEN, COUNT(*) AS SLBAY
FROM CHUYENBAY CB LEFT JOIN LICHBAY LB ON LB.MACB = CB.MACB
GROUP BY SBDI, SBDEN WITH CUBE

SELECT GIODI, GIODEN, COUNT(*) AS SLBAY
FROM CHUYENBAY CB LEFT JOIN LICHBAY LB ON LB.MACB = CB.MACB
GROUP BY GIODI, GIODEN WITH CUBE

SELECT TEN, PHUCAP = (CASE
                      WHEN LOAINV = 1 THEN 0.15*LUONG
                      WHEN LOAINV = 0 THEN 0.10*LUONG
                      END)
FROM NHANVIEN

SELECT NV.MANV, TEN, COUNT(*) AS SLBAY
FROM NHANVIEN NV LEFT JOIN PHANCONG PC ON NV.MANV = PC.MANV
GROUP BY NV.MANV, TEN

SELECT TEN, COUNT(MALOAI) AS KN
FROM NHANVIEN NV LEFT JOIN KHANANG KN ON NV.MANV = KN.MANV
WHERE LOAINV = '1'
GROUP BY NV.MANV, TEN

SELECT MALOAI, COUNT(MANV) AS SLPC
FROM KHANANG
GROUP BY MALOAI

SELECT MB.MALOAI, COUNT(*) AS SLBAY
FROM MAYBAY MB LEFT JOIN LICHBAY LB ON MB.MALOAI = LB.MALOAI
GROUP BY MB.MALOAI

SELECT SBDI, COUNT(*) AS SLCB
FROM CHUYENBAY CB LEFT JOIN LICHBAY LB ON LB.MACB = CB.MACB
WHERE NGAYDI = '2000/11/1'
GROUP BY SBDI

SELECT KH.MAKH, TEN, COUNT(MACB) SLDC100
FROM KHACHHANG KH LEFT JOIN DATCHO DC ON (KH.MAKH = DC.MAKH AND DC.MACB = '100')
GROUP BY KH.MAKH, TEN

SELECT NGAYDI, COUNT(MACB) AS SLA
FROM LICHBAY LB
LEFT JOIN MAYBAY MB ON LB.MALOAI = MB.MALOAI
JOIN LOAIMB L ON (MB.MALOAI = L.MALOAI AND HANGSX = 'AIRBUS')
GROUP BY NGAYDI

CREATE TABLE KHTX(
    MAKH NCHAR(15),
    TEN  NCHAR(50),
    SOLANDC INT,
    LOAIKH NVARCHAR(20),
    PRIMARY KEY (MAKH)
)

INSERT INTO KHTX
SELECT KH.MAKH, TEN, COUNT(MACB), ''
FROM KHACHHANG KH, DATCHO DC
WHERE KH.MAKH = DC.MAKH
GROUP BY KH.MAKH, KH.TEN
HAVING COUNT(MACB)>=2

UPDATE KHTX
SET LOAIKH = (CASE
              WHEN SOLANDC <= 4 THEN 'THUONG XUYEN'
              WHEN SOLANDC <=7 THEN 'THAN THIET'
              WHEN SOLANDC > 7 THEN 'VIP'
              END)

SELECT * FROM KHTX


run \| edit \| history \| help	0