TUAN 6 QUANLYDETAI, Sql Server

TUAN 6 QUANLYDETAI

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)

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', '001', 'HH', '10/15/2007'),
('HTTT', 'HE THONG THONG TIN', 'B13', '0838125125', '002', 'CNTT', '9/20/2004')

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')

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')

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')

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')

UPDATE GIAOVIEN
SET MABM='CNTT'
WHERE MAGV='001';

UPDATE GIAOVIEN
SET MABM='HTTT'
WHERE MAGV='002';

UPDATE GIAOVIEN
SET MABM='HTTT'
WHERE MAGV='003';

UPDATE GIAOVIEN
SET MABM='CNTT'
WHERE MAGV='004';

UPDATE GIAOVIEN
SET MABM='HL'
WHERE MAGV='005';

SELECT DISTINCT LUONG 
FROM GIAOVIEN
WHERE LUONG >= ALL (
    SELECT LUONG 
    FROM GIAOVIEN)
    
SELECT DISTINCT *
FROM GIAOVIEN
WHERE LUONG >= ALL (
    SELECT LUONG 
    FROM GIAOVIEN)

SELECT DISTINCT LUONG
FROM GIAOVIEN
WHERE MABM = 'HTTT'
AND LUONG >= ALL (
    SELECT LUONG 
    FROM GIAOVIEN 
    WHERE MABM = 'HTTT')
    
SELECT *
FROM GIAOVIEN
WHERE MABM = 'HTTT'
AND NGSINH <= ALL (
    SELECT NGSINH 
    FROM GIAOVIEN 
    WHERE MABM = 'HTTT')

SELECT HOTEN
FROM (SELECT GV.*
      FROM GIAOVIEN GV, BOMON BM, KHOA K
      WHERE GV.MABM = BM.MABM
      AND   BM.MAKHOA = K.MAKHOA
      AND   K.TENKHOA = 'CONG NGHE THONG TIN') AS GV_K_CNTT
WHERE NGSINH >= ALL (SELECT NGSINH
                     FROM GIAOVIEN GV, BOMON BM, KHOA K
                     WHERE GV.MABM = BM.MABM
                     AND   BM.MAKHOA = K.MAKHOA
                     AND   K.TENKHOA = 'CONG NGHE THONG TIN')

SELECT TENKHOA, HOTEN
FROM KHOA K, GIAOVIEN GV, BOMON BM
WHERE GV.MABM = BM.MABM
AND   BM.MAKHOA = K.MAKHOA
AND   GV.LUONG >= ALL (SELECT LUONG 
                       FROM GIAOVIEN)
                       
SELECT GV.*
FROM GIAOVIEN GV
WHERE GV.LUONG >= ALL (SELECT LUONG
                   FROM GIAOVIEN GV_SS
                   WHERE GV.MABM = GV_SS.MABM)
                   
SELECT DT.*
FROM DETAI DT
WHERE DT.MADT NOT IN (SELECT MADT 
                          FROM THAMGIADT TGDT_W, GIAOVIEN GV_W
                          WHERE TGDT_W.MAGV = GV_W.MAGV
                          AND   GV_W.HOTEN = 'NGUYEN HOAI AN')

SELECT TENDT, HOTEN
FROM DETAI DT, GIAOVIEN GV
WHERE DT.MADT NOT IN (SELECT MADT 
                          FROM THAMGIADT TGDT_W, GIAOVIEN GV_W
                          WHERE TGDT_W.MAGV = GV_W.MAGV
                          AND   GV_W.HOTEN = 'NGUYEN HOAI AN')
AND DT.GVCNDT = GV.MAGV

SELECT HOTEN, MAGV
FROM (SELECT GV_F.*
      FROM GIAOVIEN GV_F, BOMON BM_F, KHOA K_F
      WHERE GV_F.MABM = BM_F.MABM
      AND   BM_F.MAKHOA = K_F.MAKHOA) AS GV_K_CNTT
WHERE MAGV NOT IN (SELECT MAGV
                   FROM THAMGIADT)

SELECT HOTEN, MAGV
FROM GIAOVIEN GV
WHERE MAGV NOT IN (SELECT MAGV
                   FROM THAMGIADT)

SELECT GV.*
FROM GIAOVIEN GV
WHERE LUONG > ALL (SELECT LUONG
                    FROM GIAOVIEN GV_W
                    WHERE GV_W.HOTEN = 'NGUYEN HOAI AN')
                    
SELECT GV_TR_BM.*
FROM (SELECT GV_FR.*
      FROM GIAOVIEN GV_FR, BOMON BM_FR
      WHERE BM_FR.TRUONGBM = GV_FR.MAGV) AS GV_TR_BM
WHERE GV_TR_BM.MAGV IN (SELECT MAGV
                        FROM THAMGIADT)
      
SELECT GV.*
FROM GIAOVIEN GV
WHERE GV.LUONG > ANY (SELECT LUONG
                    FROM GIAOVIEN GV_W, BOMON BM_W, KHOA K_W
                    WHERE GV_W.MABM = BM_W.MABM
                    AND   BM_W.TENBM = 'CONG NGHE PHAN MEM')

SELECT GV.HOTEN, GV.LUONG
FROM GIAOVIEN GV
WHERE GV.LUONG > ALL(SELECT DISTINCT GV_W.LUONG
                     FROM GIAOVIEN GV_W, BOMON BM_W
                     WHERE GV_W.MABM = BM_W.MABM
                     AND   BM_W.TENBM = 'HE THONG THONG TIN')
              
             
SELECT TOP 1 K_BM_GV.TENKHOA, K_BM_GV.TONGSOGV
FROM (SELECT K.TENKHOA, SUM(TONGSOGV) AS TONGSOGV
      FROM KHOA K, (SELECT BM1.MAKHOA, BM1.MABM, COUNT(*) AS TONGSOGV
              FROM BOMON BM1, GIAOVIEN GV1
              WHERE BM1.MABM = GV1.MABM
              GROUP BY BM1.MAKHOA, BM1.MABM ) AS BM_GV
      WHERE K.MAKHOA = BM_GV.MAKHOA
      GROUP BY BM_GV.MAKHOA, K.TENKHOA) AS K_BM_GV
ORDER BY TONGSOGV DESC

SELECT TOP 1 HOTEN
FROM (SELECT GV.HOTEN, DT.GVCNDT, COUNT(*) AS TSDT
      FROM GIAOVIEN GV, DETAI DT
      WHERE GV.MAGV = DT.GVCNDT
      GROUP BY GV.HOTEN, DT.GVCNDT) GV_DT
ORDER BY GV_DT.TSDT DESC

SELECT TOP 1 MABM
FROM (SELECT MABM, COUNT(*) AS TSGV
      FROM GIAOVIEN
      GROUP BY MABM) BM_GV
ORDER  BY TSGV DESC

SELECT TOP 1 HOTEN, MABM
FROM (SELECT GV.HOTEN, GV.MABM, TG.MAGV, COUNT(*) AS TSDT
      FROM GIAOVIEN GV, THAMGIADT TG
      WHERE GV.MAGV = TG.MAGV
      GROUP BY GV.HOTEN, GV.MABM, TG.MAGV) AS GV_TGDT
ORDER BY TSDT DESC

SELECT TOP 1 HOTEN, MABM
FROM (SELECT GV.HOTEN, GV.MABM, TG.MAGV, COUNT(*) AS TSDT
      FROM GIAOVIEN GV, THAMGIADT TG
      WHERE GV.MAGV = TG.MAGV
      AND   GV.MABM = 'HTTT'
      GROUP BY GV.HOTEN, GV.MABM, TG.MAGV) AS GV_TGDT
ORDER BY TSDT DESC

SELECT TOP 1 HOTEN, MABM
FROM (SELECT GV.HOTEN, GV.MABM, COUNT(*) AS TSNT
      FROM NGUOITHAN NT, GIAOVIEN GV
      WHERE GV.MAGV = NT.MAGV
      GROUP BY GV.HOTEN, GV.MABM, GV.MAGV) AS GV_NT
ORDER BY TSNT DESC

SELECT TOP 1 HOTEN, TSDT
FROM (SELECT GV.HOTEN, GV.MAGV, COUNT(*) AS TSDT
      FROM BOMON BM, GIAOVIEN GV, DETAI DT
      WHERE BM.TRUONGBM = GV.MAGV
      AND   GV.MAGV = DT.GVCNDT
      GROUP BY GV.HOTEN, GV.MAGV) AS TBM_DT
ORDER BY TSDT DESC


run \| edit \| history \| help	0