on_thi_cuoi_ky, Sql Server

on_thi_cuoi_ky

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 count(*) as slgv, sum(luong) as tongluong
from giaovien

select bm.mabm, count(magv) as tonggv, avg(luong) as luongtb
from giaovien gv, bomon bm
where bm.mabm = gv.mabm
group by bm.mabm

select tencd, count(distinct madt) as sldt
from chude cd, detai dt
where cd.macd = dt.macd
group by tencd

select gv.magv, gv.hoten, count(ten) as slnt
from giaovien gv, nguoithan nt
where gv.magv = nt.magv
group by gv.magv, gv.hoten

select hoten
from giaovien gv
where ngsinh <= all (select ngsinh from giaovien)

select hoten, tenkhoa
from giaovien gv, bomon bm, khoa k
where 
gv.mabm = bm.mabm and
bm.makhoa = k.makhoa and
luong >= all (select luong from giaovien)

select hoten, tenbm
from giaovien gv1, bomon bm
where
gv1.mabm = bm.mabm and
luong >= all (select luong from giaovien gv2 where gv1.mabm = gv2.mabm)

select hoten
from giaovien gv, bomon bm, khoa k
where 
gv.mabm = bm.mabm and
bm.makhoa = k.makhoa and
k.tenkhoa = N'CONG NGHE THONG TIN' and
not exists (select magv from thamgiadt tg where tg.magv = gv.magv)

select hoten
from giaovien gv
where 
luong > all(select luong from giaovien gv1, bomon bm
            where gv1.mabm = bm.mabm
            and bm.tenbm = N'VI SINH'
            )
            
select mabm
from  giaovien gv
group by mabm
having count(magv) >= all(select count(magv) from giaovien group by mabm)

select hoten, gv.magv
from bomon bm, giaovien gv, thamgiadt tg, detai dt
where 
bm.truongbm = gv.magv and
gv.magv = tg.magv and
dt.gvcndt = gv.magv
group by hoten, gv.magv
having count(dt.madt) >= all(select count(tg.madt) 
                            from bomon bm1, giaovien gv1, thamgiadt tg1, detai dt1
                            where 
                            bm1.truongbm = gv1.magv and
                            gv1.magv = tg1.magv and
                            dt1.gvcndt = gv1.magv
                            group by hoten, gv1.magv
                          )
                          
select distinct tendt
from detai dt1, thamgiadt tg1
where dt1.madt = tg1.madt and
not exists (select magv
            from giaovien gv2
            where gv2.mabm = 'HTTT'
            except
            select magv from thamgiadt tg3
            where tg3.madt = tg1.madt)
            
select distinct hoten 
from thamgiadt tg1, giaovien gv1
where
tg1.magv = gv1.magv and
gv1.hoten != N'TRAN TRA HUONG' and
not exists (select madt 
            from thamgiadt tg2, giaovien gv2
            where tg2.magv = gv2.magv
            and gv2.hoten = N'TRAN TRA HUONG'
            except
            select madt
            from thamgiadt tg3
            where tg3.magv = tg1.magv)
            
select tendt
from thamgiadt tg1, detai dt1
where tg1.madt = dt1.madt and
not exists (select magv
            from giaovien gv2, bomon bm2, khoa k2
            where gv2.mabm = bm2.mabm
            and bm2.makhoa = k2.makhoa
            and k2.tenkhoa = N'CONG NGHE THONG TIN'
            except
            select magv
            from thamgiadt tg3
            where tg3.madt = tg1.madt)
            
select gv1.magv, gv1.hoten, gv2.hoten
from giaovien gv1 left join giaovien gv2 on gv1.gvqlcm = gv2.magv

select bm.mabm, tenbm, gv2.hoten, count(gv1.magv) as slgv
from bomon bm 
left join giaovien gv1 on bm.mabm = gv1.mabm
left join giaovien gv2 on bm.truongbm = gv2.magv
group by bm.mabm, tenbm, gv2.hoten

create table danhsachthidua(
    magv nchar(10),
    sodtdat int,
    danhhieu nchar(30),
    primary key (magv)
    )
insert into danhsachthidua values
('001',null,null),
('002',null,null),
('003',null,null),
('004',null,null),
('005',null,null),
('006',null,null),
('007',null,null),
('008',null,null),
('009',null,null),
('010',null,null)

update danhsachthidua
set sodtdat = (select count(distinct madt)
               from thamgiadt tg
               where tg.magv = danhsachthidua.magv
               and tg.ketqua = N'DAT'
               )
               
update danhsachthidua
set danhhieu = (case
                when sodtdat = 0 then 'chua hoan thanh nhiem vu'
                when sodtdat <=2 then 'hoan thanh nhiem vu'
                when sodtdat <=5 then 'tien tien'
                when sodtdat >= 6 then 'lao dong xuat sac'
                end)

select * from danhsachthidua

select tencd, capql, count(madt) as sldt
from chude cd, detai dt
where cd.macd = dt.macd
group by tencd, capql with cube

select tenbm, phai, sum(luong) as tongluong, 
grouping (phai) 'rgp'
from giaovien gv right join bomon bm on gv.mabm = bm.mabm
group by tenbm, phai with rollup


run \| edit \| history \| help	0