QUAN LY CHUYEN BAY, Sql Server

Language: Layout:

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'

SELECT MACB, NGAYDI, COUNT(*) AS SOLUONGNV
FROM PHANCONG
GROUP BY MACB, NGAYDI
ORDER BY SOLUONGNV DESC

SELECT MACB, NGAYDI, COUNT(*) AS SOLUONGHK
FROM DATCHO
GROUP BY MACB, NGAYDI
ORDER BY SOLUONGHK DESC

SELECT MACB, NGAYDI, SUM(LUONG) AS TONGLUONG
FROM PHANCONG, NHANVIEN
WHERE NHANVIEN.MANV = PHANCONG.MANV
GROUP BY MACB, NGAYDI
ORDER BY TONGLUONG ASC

SELECT AVG(LUONG) AS LUONGTB
FROM NHANVIEN
WHERE NHANVIEN.LOAINV = '0'

SELECT AVG(LUONG) AS LUONGTB
FROM NHANVIEN
WHERE NHANVIEN.LOAINV = '1'

SELECT MALOAI, COUNT(*) AS SOLUONGCB
FROM LICHBAY, CHUYENBAY
WHERE LICHBAY.MACB = CHUYENBAY.MACB
AND   CHUYENBAY.SBDEN = 'ORD'
GROUP BY MALOAI

SELECT SBDI, COUNT(*) AS SOLUONGXP
FROM CHUYENBAY
WHERE GIODI BETWEEN '10:00:00' AND '22:00:00'
GROUP BY SBDI
HAVING COUNT(*) > 2

SELECT TEN
FROM NHANVIEN, PHANCONG
WHERE NHANVIEN.MANV = PHANCONG.MANV
AND   NHANVIEN.LOAINV = '1'
GROUP BY TEN, NGAYDI
HAVING COUNT(*) > 1

SELECT MACB, NGAYDI, COUNT(*) AS SLHK
FROM DATCHO
GROUP BY MACB, NGAYDI
HAVING COUNT(*) < 3

SELECT SOHIEU, MALOAI, COUNT(*) AS SOLANDK
FROM PHANCONG, LICHBAY
WHERE LICHBAY.MACB   = PHANCONG.MACB
AND   LICHBAY.NGAYDI = PHANCONG.NGAYDI
AND   PHANCONG.MANV  = '1001'
GROUP BY MALOAI, SOHIEU
HAVING COUNT(*) > 2

SELECT HANGSX, COUNT(*) AS SLMAYBAY
FROM MAYBAY, LOAIMB
WHERE LOAIMB.MALOAI = MAYBAY.MALOAI
GROUP BY HANGSX

View schema
Execution time: 0,19 sec, rows selected: 80, rows affected: 109, absolute service time: 0,39 sec