TUAN 7_QLDT, Sql Server

TUAN 7_QLDT

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 DISTINCT TG1.MAGV
FROM THAMGIADT TG1
WHERE NOT EXISTS (SELECT MADT FROM DETAI
                  EXCEPT
                  (SELECT TG2.MADT 
                   FROM THAMGIADT TG2
                   WHERE TG2.MAGV = TG1.MAGV))
                   
SELECT HOTEN
FROM GIAOVIEN GV, DETAI DT, CHUDE CD, THAMGIADT TG
WHERE GV.MAGV = TG.MAGV
AND   TG.MADT = DT.MADT
AND   DT.MACD = CD.MACD
GROUP BY HOTEN, GV.MAGV
HAVING COUNT(DISTINCT CD.MACD) = (SELECT COUNT(*) FROM CHUDE)

SELECT DISTINCT TENDT
FROM DETAI DT1, THAMGIADT TG1
WHERE TG1.MADT = DT1.MADT
AND NOT EXISTS (SELECT MAGV
                FROM GIAOVIEN GV2, BOMON BM2
                WHERE GV2.MABM = BM2.MABM
                AND   BM2.TENBM = 'HE THONG THONG TIN'
                EXCEPT (
                    SELECT MAGV
                    FROM THAMGIADT TG3
                    WHERE TG3.MADT = TG1.MADT))

SELECT HOTEN
FROM GIAOVIEN GV, THAMGIADT TG, DETAI DT
WHERE GV.MAGV = TG.MAGV
AND   TG.MADT = DT.MADT
AND   DT.MACD = 'QLGD'
GROUP BY HOTEN
HAVING COUNT(DISTINCT TG.MADT) = (SELECT COUNT(*) FROM DETAI WHERE MACD = 'QLGD')

SELECT DISTINCT HOTEN
FROM GIAOVIEN GV, THAMGIADT TG
WHERE GV.MAGV = TG.MAGV
AND   GV.HOTEN != 'TRAN TRA HUONG'
AND NOT EXISTS (SELECT TG1.MADT
                FROM THAMGIADT TG1, GIAOVIEN GV1
                WHERE TG1.MAGV = GV1.MAGV
                AND   GV1.HOTEN = 'TRAN TRA HUONG'
                EXCEPT (
                    SELECT TG2.MADT
                    FROM THAMGIADT TG2
                    WHERE TG.MAGV = TG2.MAGV))

SELECT DISTINCT TENDT
FROM THAMGIADT TG, DETAI DT
WHERE TG.MADT = DT.MADT
AND NOT EXISTS (SELECT MAGV
                 FROM GIAOVIEN GV1, BOMON BM1
                 WHERE GV1.MABM = BM1.MABM
                 AND   BM1.MABM = 'HOA HUU CO'
                 EXCEPT
                 (SELECT MAGV 
                  FROM THAMGIADT TG2
                  WHERE TG2.MADT = TG.MADT))
                  
SELECT DISTINCT HOTEN
FROM GIAOVIEN GV, THAMGIADT TG
WHERE TG.MAGV = GV.MAGV
AND NOT EXISTS (SELECT MADT, SOTT
                FROM CONGVIEC
                WHERE MADT = '006'
                EXCEPT (
                    SELECT MADT, SOTT
                    FROM THAMGIADT TG2
                    WHERE TG2.MAGV = TG.MAGV))
                 
                
SELECT MAGV
FROM THAMGIADT TG
WHERE NOT EXISTS (SELECT MADT
                  FROM CHUDE CD1, DETAI DT1
                  WHERE CD1.MACD = DT1.MACD
                  AND   CD1.TENCD = 'UNG DUNG CONG NGHE'
                  EXCEPT
                  (SELECT MADT
                   FROM THAMGIADT TG2
                   WHERE TG.MADT = TG2.MADT))
                   
SELECT DISTINCT GV.HOTEN
FROM GIAOVIEN GV, THAMGIADT TG
WHERE GV.MAGV = TG.MAGV
AND NOT EXISTS (SELECT DT1.MADT 
                FROM DETAI DT1, GIAOVIEN GV1
                WHERE DT1.GVCNDT = GV1.MAGV
                AND   GV1.HOTEN = 'TRAN TRA HUONG'
                EXCEPT
                SELECT TG2.MADT
                FROM THAMGIADT TG2
                WHERE TG2.MAGV = TG.MAGV)
                
SELECT TENDT
FROM DETAI DT, THAMGIADT TG
WHERE DT.MADT = TG.MADT
AND NOT EXISTS (SELECT GV1.MAGV
                FROM GIAOVIEN GV1, BOMON BM1
                WHERE GV1.MABM = BM1.MABM
                AND   BM1.MAKHOA = 'CNTT'
                EXCEPT
                SELECT MAGV
                FROM THAMGIADT TG2
                WHERE TG2.MADT = TG.MADT)
                
SELECT DISTINCT HOTEN
FROM GIAOVIEN GV, THAMGIADT TG
WHERE TG.MAGV = GV.MAGV
AND NOT EXISTS (SELECT CV1.MADT, SOTT
                FROM CONGVIEC CV1, DETAI DT1
                WHERE DT1.MADT = CV1.MADT
                AND   DT1.TENDT = 'NGHIEN CUU TE BAO GOC'
                EXCEPT
                SELECT TG2.MADT, TG2.SOTT
                FROM THAMGIADT TG2
                WHERE TG.MAGV = TG2.MAGV)
                
SELECT HOTEN
FROM THAMGIADT TG, GIAOVIEN GV
WHERE TG.MAGV = GV.MAGV
AND NOT EXISTS (SELECT MADT
                FROM DETAI DT1
                WHERE DT1.KINHPHI >= 100
                EXCEPT 
                SELECT MADT
                FROM THAMGIADT TG2
                WHERE TG2.MAGV = GV.MAGV)
                
SELECT DISTINCT TENDT
FROM DETAI DT, THAMGIADT TG
WHERE DT.MADT = TG.MADT
AND NOT EXISTS (SELECT MAGV
                FROM GIAOVIEN GV1, BOMON BM1, KHOA K1
                WHERE GV1.MABM = BM1.MABM
                AND BM1.MAKHOA = K1.MAKHOA
                AND K1.TENKHOA = 'SINH HOC'
                EXCEPT
                SELECT MAGV
                FROM THAMGIADT TG2
                WHERE TG2.MADT = TG.MADT)
                
SELECT DISTINCT HOTEN, GV.MAGV, NGSINH
FROM GIAOVIEN GV, THAMGIADT TG
WHERE GV.MAGV = TG.MAGV
AND NOT EXISTS (SELECT CV1.MADT, CV1.SOTT
                FROM CONGVIEC CV1, DETAI DT1
                WHERE DT1.TENDT = 'UNG DUNG HOA HOC XANH'
                AND DT1.MADT = CV1.MADT
                EXCEPT
                SELECT TG2.MADT, TG2.SOTT
                FROM THAMGIADT TG2
                WHERE TG2.MAGV = TG.MAGV)
AND EXISTS (SELECT CV3.*
            FROM CONGVIEC CV3, DETAI DT3 
            WHERE CV3.MADT = DT3.MADT                            
            AND   DT3.TENDT = 'UNG DUNG HOA HOC XANH')
            
SELECT GV.HOTEN, BM.TENBM, GV1.HOTEN
FROM GIAOVIEN GV, GIAOVIEN GV1, THAMGIADT TG, BOMON BM
WHERE GV.MAGV = TG.MAGV
AND   GV.GVQLCM = GV1.MAGV
AND   GV.MABM = BM.MABM
AND NOT EXISTS (SELECT MADT
                FROM DETAI DT1, CHUDE CD1
                WHERE DT1.MACD = CD1.MACD
                AND CD1.TENCD = 'NGHIEN CUU PHAT TRIEN'
                EXCEPT
                SELECT MADT
                FROM THAMGIADT TG2
                WHERE TG2.MAGV = TG.MAGV)


run \| edit \| history \| help	0