TUAN 8_BTTL, Sql Server

Language: Layout:

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

View schema
Execution time: 0,23 sec, rows selected: 129, rows affected: 112, absolute service time: 0,44 sec