Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
Pro DB's project
bdLojaRoupas
t1
Jean
Self join producing wrong results -SQL
dynamic pivot
Task_6_Final
i need help
second Query
JSON with No root node - Format from XML output - SQL