TUAN 7_QLCB, Sql Server

TUAN 7_QLCB

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 MACB
FROM LICHBAY LB1
WHERE NOT EXISTS (SELECT MALOAI
                  FROM LOAIMB
                  WHERE HANGSX = 'BOEING'
                  EXCEPT
                  SELECT MALOAI
                  FROM LICHBAY LB2
                  WHERE LB2.MACB = LB1.MACB)
                  
SELECT NV.MANV, TEN
FROM NHANVIEN NV, KHANANG KN
WHERE KN.MANV = NV.MANV
AND NOT EXISTS (SELECT MALOAI
                FROM LOAIMB 
                WHERE HANGSX = 'AIRBUS'
                EXCEPT
                SELECT MALOAI
                FROM KHANANG KN1
                WHERE KN1.MANV = KN.MANV)
                
SELECT DISTINCT TEN
FROM NHANVIEN NV, PHANCONG PC
WHERE NV.LOAINV = '0'
AND  NV.MANV = PC.MANV
AND NOT EXISTS (SELECT NGAYDI, MACB
                FROM LICHBAY
                WHERE MACB = '100'
                EXCEPT
                SELECT NGAYDI, MACB
                FROM PHANCONG PC2
                WHERE PC2.MANV = PC.MANV)
                
SELECT NGAYDI
FROM LICHBAY LB1
WHERE NOT EXISTS (SELECT MALOAI
                  FROM LOAIMB
                  WHERE HANGSX = 'BOEING'
                  EXCEPT
                  SELECT MALOAI
                  FROM LICHBAY LB2
                  WHERE LB1.NGAYDI = LB2.NGAYDI)
                  
SELECT LICHBAY.MALOAI
FROM LICHBAY, LOAIMB
WHERE LOAIMB.MALOAI = LICHBAY.MALOAI
AND   LOAIMB.HANGSX = 'BOEING'
GROUP BY LICHBAY.MALOAI
HAVING COUNT(DISTINCT NGAYDI) = (SELECT COUNT(DISTINCT NGAYDI) FROM LICHBAY)

SELECT KH.MAKH, TEN
FROM KHACHHANG KH, DATCHO DC
WHERE KH.MAKH = DC.MAKH
GROUP BY KH.MAKH, TEN
HAVING COUNT(DISTINCT DC.NGAYDI) = (SELECT COUNT(DISTINCT NGAYDI) 
                                    FROM LICHBAY
                                    WHERE NGAYDI BETWEEN '10/31/2000' AND '11/1/2000')
                                    
SELECT MANV, TEN
FROM NHANVIEN
WHERE LOAINV = '1'
EXCEPT
SELECT NV.MANV, NV.TEN
FROM NHANVIEN NV, KHANANG KN, LOAIMB L
WHERE KN.MANV = NV.MANV
AND   KN.MALOAI = L.MALOAI
AND   L.HANGSX = 'AIRBUS'

SELECT DISTINCT SBDI
FROM CHUYENBAY CB, LICHBAY LB
WHERE CB.MACB = LB.MACB
AND NOT EXISTS (SELECT MALOAI
                FROM LOAIMB 
                WHERE HANGSX = 'BOEING'
                EXCEPT
                SELECT MALOAI
                FROM LICHBAY LB2
                WHERE LB2.MACB = LB.MACB
                AND LB2.NGAYDI = LB.NGAYDI)


run \| edit \| history \| help	0