QUAN LY MAYBAY, Sql Server

QUAN LY MAYBAY

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 SBDEN, COUNT(*) AS SOLUONGCHUYENBAY
FROM CHUYENBAY
GROUP BY SBDEN

SELECT SBDI, COUNT(*) AS SOLUONGCHUYENBAY
FROM CHUYENBAY
GROUP BY SBDI

SELECT SBDI, NGAYDI, COUNT(*) AS SOLUONGCHUYENBAY
FROM CHUYENBAY, LICHBAY
WHERE LICHBAY.MACB = CHUYENBAY.MACB
GROUP BY SBDI, NGAYDI

SELECT SBDEN, NGAYDI, COUNT(*) AS SOLUONGCHUYENBAY
FROM CHUYENBAY, LICHBAY
WHERE LICHBAY.MACB = CHUYENBAY.MACB
GROUP BY SBDEN, NGAYDI

SELECT PHANCONG.NGAYDI, PHANCONG.MACB, COUNT(PHANCONG.MANV) AS SOLUONGTIEPVIEN
FROM NHANVIEN, PHANCONG, LICHBAY
WHERE NHANVIEN.MANV = PHANCONG.MANV
AND   LICHBAY.NGAYDI = PHANCONG.NGAYDI
AND   LICHBAY.MACB   = PHANCONG.MACB
AND   NHANVIEN.LOAINV = '0'
GROUP BY PHANCONG.NGAYDI, PHANCONG.MACB

SELECT COUNT(*) AS SOLUONGCHUYENBAY
FROM LICHBAY, CHUYENBAY
WHERE LICHBAY.MACB = CHUYENBAY.MACB
AND   CHUYENBAY.SBDI = 'MIA'
AND   LICHBAY.NGAYDI = '2000/11/1'


run \| edit \| history \| help	0