Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
TUAN 8_BTTL
CREATE TABLE GIAOVIEN( MAGV NCHAR(10), HOTEN NVARCHAR(30), LUONG INT, PHAI NCHAR(3), NGSINH DATETIME, DIACHI NVARCHAR(50), GVQLCM NCHAR(10), MABM NCHAR(10), PRIMARY KEY(MAGV) ); CREATE TABLE KHOA( MAKHOA NCHAR(10), TENKHOA NVARCHAR(30), NAMTL INT, PHONG NCHAR(10), DIENTHOAI INT, TRUONGKHOA NCHAR(10), NGAYNHAMCHUC DATETIME, PRIMARY KEY (MAKHOA) ); CREATE TABLE BOMON( MABM NCHAR(10), TENBM NVARCHAR(30), PHONG NCHAR(10), DIENTHOAI INT, TRUONGBM NCHAR(10), MAKHOA NCHAR(10), NGAYNHAMCHUC DATETIME, PRIMARY KEY (MABM) ); CREATE TABLE GV_DT( MAGV NCHAR(10), DIENTHOAI INT, PRIMARY KEY(MAGV, DIENTHOAI) ); CREATE TABLE NGUOITHAN( MAGV NCHAR(10), TEN NVARCHAR(30), NGSINH DATETIME, PHAI NCHAR(3), PRIMARY KEY (MAGV, TEN) ); CREATE TABLE CHUDE( MACD NCHAR(10), TENCD NVARCHAR(30), PRIMARY KEY(MACD) ); CREATE TABLE DETAI( MADT NCHAR(10), TENDT NVARCHAR(50), CAPQL NVARCHAR(20), KINHPHI INT, NGAYBD DATETIME, NGAYKT DATETIME, MACD NCHAR(10), GVCNDT NCHAR(10), PRIMARY KEY(MADT) ); CREATE TABLE CONGVIEC( MADT NCHAR(10), SOTT INT, TENCV NVARCHAR(40), NGAYBD DATETIME, NGAYKT DATETIME, PRIMARY KEY(MADT, SOTT) ); CREATE TABLE THAMGIADT( MAGV NCHAR(10), MADT NCHAR(10), SOTT INT, PHUCAP INT, KETQUA NVARCHAR(20), PRIMARY KEY(MAGV, MADT, SOTT) ); ALTER TABLE BOMON ADD CONSTRAINT FK_BOMON_KHOA FOREIGN KEY (MAKHOA) REFERENCES KHOA(MAKHOA) ALTER TABLE BOMON ADD CONSTRAINT FK_BOMON_GIAOVIEN FOREIGN KEY (TRUONGBM) REFERENCES GIAOVIEN(MAGV) ALTER TABLE GV_DT ADD CONSTRAINT FK_GV_DT_GIAOVIEN FOREIGN KEY (MAGV) REFERENCES GIAOVIEN(MAGV) ALTER TABLE NGUOITHAN ADD CONSTRAINT FK_NGUOITHAN_GIAOVIEN FOREIGN KEY (MAGV) REFERENCES GIAOVIEN(MAGV) ALTER TABLE KHOA ADD CONSTRAINT FK_KHOA_GIAOVIEN FOREIGN KEY (TRUONGKHOA) REFERENCES GIAOVIEN(MAGV) ALTER TABLE THAMGIADT ADD CONSTRAINT FK_THAMGIADT_GIAOVIEN FOREIGN KEY (MAGV) REFERENCES GIAOVIEN(MAGV) ALTER TABLE THAMGIADT ADD CONSTRAINT FK_THAMGIADT_CONGVIEC FOREIGN KEY (MADT, SOTT) REFERENCES CONGVIEC(MADT, SOTT) ALTER TABLE CONGVIEC ADD CONSTRAINT FK_CONGIVEC_DETAI FOREIGN KEY (MADT) REFERENCES DETAI(MADT) ALTER TABLE DETAI ADD CONSTRAINT FK_DETAI_CHUDE FOREIGN KEY (MACD) REFERENCES CHUDE(MACD) ALTER TABLE DETAI ADD CONSTRAINT FK_DETAI_GIAOVIEN FOREIGN KEY (GVCNDT) REFERENCES GIAOVIEN(MAGV) ALTER TABLE GIAOVIEN ADD CONSTRAINT FK_GIAOVIEN_GIAOVIEN FOREIGN KEY (GVQLCM) REFERENCES GIAOVIEN(MAGV) ALTER TABLE GIAOVIEN ADD CONSTRAINT FK_GIAOVIEN_BOMON FOREIGN KEY (MABM) REFERENCES BOMON(MABM) INSERT INTO GIAOVIEN VALUES ('001', 'NGUYEN HOAI AN', '2000', 'NAM', '2/15/1973', 'TP HCM', NULL, NULL), ('002', 'TRAN TRA HUONG', '2500', 'NU', '6/20/1960', 'TP HCM', NULL, NULL), ('003', 'NGUYEN NGOC ANH','2200','NU','5/11/1975','TP HCM', '002', NULL), ('004', 'TRUONG NAM SON', '2300', 'NAM', '6/20/1959', 'BIEN HOA', NULL, NULL), ('005', 'LY HOANG HA', '2500', 'NAM', '10/23/1954', 'TP HCM', NULL, NULL), ('006','TRAN BACH TUYET','1500','NU','05/20/1980','MY THO',NULL,NULL), ('007','NGUYEN AN TRUNG','2100','NAM','06/05/1976','BIEN HOA',NULL,NULL), ('008','TRAN TRUNG HIEU','1800','NAM','08/06/1977','MY THO',NULL,NULL), ('009','TRAN HOANG NAM','2000','NAM','11/22/1975','TP HCM',NULL,NULL), ('010','PHAM NAM THANH','1500','NAM','12/12/1980','TP HCM',NULL,NULL) INSERT INTO KHOA VALUES ('CNTT', 'CONG NGHE THONG TIN', '1995', 'B11', '0838123456', '002', '2/20/2005'), ('HH', 'HOA HOC', '1980', 'B41', '0838456456', '001', '10/15/2001'), ('SH', 'SINH HOC', '1980', 'B31', '083845454', '004', '10/11/2000'), ('VL', 'VAT LY', '1976', 'B21', '0838223223', '005', '9/18/2003') INSERT INTO BOMON VALUES ('CNTT', 'CONG NGHE TRI THUC', 'B15', '0838126126', NULL, 'CNTT', NULL), ('HHC', 'HOA HUC CO', 'B44', '0838222222', NULL, 'HH', NULL), ('HL', 'HOA LY', 'B42', '0838878787', NULL, 'HH', NULL), ('HPT', 'HOA PHAN TICH', 'B43', '0838777777', '007', 'HH', '10/15/2007'), ('HTTT', 'HE THONG THONG TIN', 'B13', '0838125125', '002', 'CNTT', '9/20/2004'), ('MMT','MANG MAY TINH','B16','0838676767','001','CNTT','05/15/2005'), ('SH','SINH HOA','B33','0838898989',NULL,'SH',NULL), ('VLDT','VAT LY DIEN TU','B23','0838234234',NULL,'VL',NULL), ('VLUD','VAT LY UNG DUNG','B24','0838454545','005','VL','02/18/2006'), ('VS','VI SINH','B32','0838909090','004','SH','01/01/2007') INSERT INTO NGUOITHAN VALUES ('001', 'HUNG', '1/14/1990', 'NAM'), ('001', 'THUY', '12/8/1994', 'NU'), ('003', 'HA', '9/3/1998', 'NU'), ('003', 'THU', '9/3/1998', 'NU'), ('007','MAI','03/26/2003','NU'), ('007','VY','02/14/2000','NU'), ('008','NAM','05/06/1991','NAM'), ('009','AN','08/19/1996','NAM'), ('010','NGUYET','01/14/2006','NU') INSERT INTO GV_DT VALUES ('001', '0838912112'), ('001', '0903123123'), ('002', '0913454545'), ('003', '0838121212'), ('003', '0903656565') INSERT INTO CHUDE VALUES ('NCPT', 'NGHIEN CUU PHAT TRIEN'), ('QLGD', 'QUAN LY GIAO DUC'), ('UDCN', 'UNG DUNG CONG NGHE') INSERT INTO DETAI VALUES ('001', 'HTTT QUAN LY CAC TRUONG DAI HOC', 'DHQG', '20', '10/20/2007', '10/20/2008', 'QLGD', '002'), ('002', 'HTTT QUAN LY GIAO VU CHO 1 KHOA', 'TRUONG', '20', '10/12/2000', '10/12/2001', 'QLGD', '002'), ('003', 'NGHIEN CUU CHE TAO SOI NANO PLATIN', 'DHQG', '300', '5/15/2008', '5/15/2010', 'NCPT', '005'), ('004', 'TAO VAT LIEU SINH HOC BANG MANG OI NGUOI', 'NHA NUOC', '100','1/1/2007', '12/31/2009', 'NCPT', '004'), ('005', 'UNG DUNG HOA HOC XANH', 'TRUONG', '200', '10/10/2003', '12/10/2004', 'UDCN', '001'), ('006','NGHIEN CUU TE BAO GOC','NHA NUOC','4000','10/20/2006','10/20/2009','NCPT','004'), ('007','HTTT QUAN LY THU VIEN CAC TRUONG DAI HOC','TRUONG','20','05/10/2009','05/10/2010','QLGD','001') INSERT INTO CONGVIEC VALUES ('001', '1', 'KHOI TAO VA LAP KE HOACH', '10/20/2007', '12/20/2008'), ('001', '2', 'XAC DINH YEU CAU', '12/21/2008', '3/21/2008'), ('001', '3', 'PHAN TICH HE THONG', '3/22/2008', '5/22/2008'), ('001', '4', 'THIET KE HE THONG', '5/23/2008', '6/23/2008'), ('001', '5', 'CAI DAT THU NGHIEM', '6/24/2008', '10/20/2008'), ('002', '1', 'KHOI TAO VA LAP KE HOACH', '5/10/2009', '7/10/2009'), ('002', '2', 'XAC DINH YEU CAU', '7/11/2009', '10/11/2009'), ('002', '3', 'PHAN TICH HE THONG', '10/12/2009', '12/20/2009'), ('002', '4', 'THIET KE HE THONG', '12/21/2009', '3/22/2010'), ('002', '5', 'CAI DAT THU NGHIEM', '3/23/2010', '5/10/2010'), ('006','1','LAY MAU','10/20/2006','02/20/2007'), ('006','2','NUOI CAY','02/21/2007','08/21/2008') INSERT INTO THAMGIADT VALUES ('001', '002', '1', '0', NULL), ('001', '002', '2', '2', NULL), ('002', '001', '4', '2', 'DAT'), ('003', '001', '1', '1', 'DAT'), ('003', '001', '2', '0', 'DAT'), ('003','001','4','1','DAT'), ('003','002','2','0',NULL), ('004','006','1','0','DAT'), ('004','006','2','1','DAT'), ('006','006','2','1','DAT'), ('009','002','3','1','NULL'), ('009','002','4','1','NULL') UPDATE GIAOVIEN SET MABM='MMT' WHERE MAGV='001'; UPDATE GIAOVIEN SET MABM='HTTT' WHERE MAGV='002'; UPDATE GIAOVIEN SET MABM='HTTT' WHERE MAGV='003'; UPDATE GIAOVIEN SET MABM='VS' WHERE MAGV='004' OR MAGV='006'; UPDATE GIAOVIEN SET MABM='VLDT' WHERE MAGV='005'; UPDATE GIAOVIEN SET MABM='HPT' WHERE MAGV='007' OR MAGV='008' OR MAGV='010'; UPDATE GIAOVIEN SET MABM='MMT' WHERE MAGV='009'; SELECT G1.MAGV, G1.HOTEN, G2.HOTEN AS GVQL FROM GIAOVIEN AS G1 LEFT JOIN GIAOVIEN AS G2 ON G1.GVQLCM = G2.MAGV SELECT BM.MABM, BM.TENBM, GV1.HOTEN, COUNT(*) TONGSOGV FROM (BOMON AS BM LEFT JOIN GIAOVIEN AS GV1 ON BM.TRUONGBM = GV1.MAGV ) LEFT JOIN GIAOVIEN AS GV2 ON BM.MABM = GV2.MABM GROUP BY BM.MABM, BM.TENBM, GV1.HOTEN SELECT GV.HOTEN, GV.MAGV, CV.TENCV FROM GIAOVIEN AS GV LEFT JOIN THAMGIADT AS TG ON GV.MAGV = TG.MAGV JOIN CONGVIEC AS CV ON (TG.MADT = CV.MADT AND TG.SOTT = CV.SOTT) WHERE GV.PHAI = 'NAM' SELECT CV.TENCV, GV.HOTEN FROM CONGVIEC AS CV LEFT JOIN THAMGIADT AS TG ON (CV.MADT = TG.MADT AND CV.SOTT = TG.SOTT) JOIN GIAOVIEN AS GV ON (TG.MAGV = GV.MAGV) WHERE CV.MADT = '001' SELECT GV.HOTEN, CV.TENCV FROM GIAOVIEN AS GV LEFT JOIN THAMGIADT AS TG ON (GV.MAGV = TG.MAGV AND TG.MADT = '001') LEFT JOIN CONGVIEC AS CV ON (TG.MADT = CV.MADT AND TG.SOTT = CV.SOTT) SELECT MAGV, HOTEN FROM GIAOVIEN WHERE 2014 - YEAR(NGSINH) >= (CASE WHEN PHAI = 'NAM' THEN 60 WHEN PHAI = 'NU' THEN 55 END) SELECT GV.MAGV, HOTEN, (CASE WHEN GV.PHAI = 'NAM' THEN YEAR(NGSINH)+60 WHEN GV.PHAI = 'NU' THEN YEAR(NGSINH)+55 END) AS NAMVEHUU FROM GIAOVIEN GV, KHOA K WHERE K.TRUONGKHOA = GV.MAGV CREATE TABLE DANHSACHTHIDUA( MAGV NCHAR(10), SLDTDAT INT, DANHHIEU NVARCHAR(30), PRIMARY KEY (MAGV) ); INSERT INTO DANHSACHTHIDUA VALUES ('001','0','NULL'), ('002','0','NULL'), ('003','0','NULL'), ('004','0','NULL'), ('005','0','NULL'), ('006','0','NULL'), ('007','0','NULL'), ('008','0','NULL'), ('009','0','NULL'), ('010','0','NULL') UPDATE DANHSACHTHIDUA SET SLDTDAT = (SELECT COUNT(DISTINCT MADT) FROM THAMGIADT TG WHERE TG.MAGV = DANHSACHTHIDUA.MAGV GROUP BY MAGV) UPDATE DANHSACHTHIDUA SET DANHHIEU = (CASE WHEN SLDTDAT IS NULL THEN 'CHUA HOAN THANH NHIEM VU' WHEN SLDTDAT <=2 AND SLDTDAT>=1 THEN 'HOAN THANH NHIEM VU' WHEN SLDTDAT>=3 AND SLDTDAT<=5 THEN 'TIEN TIEN' WHEN SLDTDAT>=6 THEN 'LAO DONG XUAT SAC' END) SELECT * FROM DANHSACHTHIDUA SELECT MAGV, HOTEN, LUONG FROM GIAOVIEN GV, BOMON BM, KHOA K WHERE GV.MABM = BM.MABM AND BM.MAKHOA = K.MAKHOA AND GV.PHAI = 'NU' SELECT AVG(LUONG) AS AGV, MIN(LUONG) AS MIN, MAX(LUONG) AS MAX FROM (SELECT MAGV, HOTEN, LUONG FROM GIAOVIEN GV, BOMON BM, KHOA K WHERE GV.MABM = BM.MABM AND BM.MAKHOA = K.MAKHOA AND GV.PHAI = 'NU' ) AS TB; WITH TB AS (SELECT K.MAKHOA, K.TENKHOA, COUNT(GV.MAGV) AS SLGV FROM KHOA AS K LEFT JOIN BOMON AS BM ON K.MAKHOA = BM.MAKHOA JOIN GIAOVIEN AS GV ON GV.MABM = BM.MABM GROUP BY K.MAKHOA, K.TENKHOA) SELECT '' AS MAKHOA,'SO GV TB MOI KHOA' AS DANHMUC ,AVG(SLGV) AS SOGV FROM TB UNION SELECT '','SO GV NHO NHAT',MIN(SLGV) FROM TB UNION SELECT '','SO GV LON NHAT',MAX(SLGV) FROM TB UNION SELECT MAKHOA, TENKHOA, SLGV FROM TB; WITH TB AS ( SELECT TENCD, SUM(KINHPHI) AS TONGKP FROM CHUDE AS CD LEFT JOIN DETAI AS DT ON DT.MACD = CD.MACD GROUP BY TENCD ) SELECT 'KINH PHI LON NHAT' AS DANHMUC, MAX(TONGKP) AS SOTIEN FROM TB UNION SELECT 'KINH PHI NHO NHAT', MIN(TONGKP) FROM TB UNION SELECT TENCD, TONGKP FROM TB; WITH TB AS ( SELECT MADT, TENDT, KINHPHI, HOTEN FROM GIAOVIEN AS GV LEFT JOIN DETAI AS DT ON GVCNDT = MAGV ) SELECT '' AS MADT, 'TONG KINH PHI' AS TENDT, SUM(KINHPHI) AS KINHPHI, '' AS HOTEN FROM TB UNION SELECT '', 'TB KINH PHI', AVG(KINHPHI), '' FROM TB UNION SELECT MADT, TENDT, KINHPHI, HOTEN FROM TB ORDER BY HOTEN SELECT CAPQL, TENCD, COUNT(MADT) AS SLDT FROM CHUDE CD LEFT JOIN DETAI DT ON DT.MACD = CD.MACD GROUP BY CAPQL, TENCD WITH CUBE SELECT DISTINCT TENBM, PHAI, SUM(LUONG) AS TONGL FROM BOMON BM LEFT JOIN GIAOVIEN GV ON BM.MABM = GV.MABM GROUP BY TENBM, PHAI WITH ROLLUP SELECT TENBM, LUONG, COUNT(MAGV) AS SLGV FROM BOMON BM LEFT JOIN GIAOVIEN GV ON GV.MABM = BM.MABM WHERE MAKHOA = 'CNTT' GROUP BY TENBM, LUONG WITH CUBE
run
|
edit
|
history
|
help
0
a
Items
Q2_15min
Solution 2
StackOverflow_53753663
kuy09:admin09@pkms99.info
Workers information
Time packing with joins
sum vs count with multiple options in group by clause
Cross and outer apply