BTVN_QLDT_TUAN 8, 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 HOTEN, TENBM
FROM GIAOVIEN GV LEFT JOIN BOMON BM ON BM.TRUONGBM = GV.MAGV

SELECT TENBM, HOTEN
FROM BOMON BM LEFT JOIN GIAOVIEN GV ON TRUONGBM = MAGV

SELECT HOTEN, TENDT
FROM GIAOVIEN GV LEFT JOIN DETAI DT ON GVCNDT = MAGV

SELECT HOTEN, MAGV, MUCLUONG = (CASE 
                              WHEN LUONG<=1800 THEN 'THAP'
                              WHEN LUONG<=2200 AND LUONG>1800 THEN 'TRUNG BINH'
                              ELSE 'CAO'
                              END)
FROM GIAOVIEN

SELECT HOTEN, MAGV, MUCLUONG = (CASE 
                              WHEN LUONG<=1800 THEN 'THAP'
                              WHEN LUONG<=2200 AND LUONG>1800 THEN 'TRUNG BINH'
                              ELSE 'CAO'
                              END)
FROM GIAOVIEN
ORDER BY LUONG DESC

SELECT GV.MAGV, HOTEN, THUNHAP = (CASE 
                         WHEN TENBM IS NOT NULL THEN LUONG+300
                         WHEN TENKHOA IS NOT NULL THEN LUONG+600
                         WHEN TENKHOA IS NOT NULL AND TENBM IS NOT NULL THEN LUONG+900
                         ELSE LUONG END), TENBM AS TRUONGBM, TENKHOA AS TRUONGKHOA
FROM GIAOVIEN GV LEFT JOIN BOMON BM ON BM.TRUONGBM = GV.MAGV
LEFT JOIN KHOA K ON K.TRUONGKHOA = GV.MAGV

SELECT HOTEN, NAMVEHUU = (CASE
                          WHEN PHAI = 'NAM' THEN YEAR(NGSINH)+60
                          WHEN PHAI = 'NU' THEN YEAR(NGSINH)+55
                          END)
FROM GIAOVIEN

View schema
Execution time: 0,14 sec, rows selected: 73, rows affected: 82, absolute service time: 0,3 sec