----[L]--- Hot hot hot 40 câu SQL quản lý đề án

CREATE DATABASE QLDEAN
GO
USE QLDEAN
GO
CREATE TABLE PHONGBAN
(
   TENPHG NVARCHAR(100),
   MAPHG INT PRIMARY KEY,
    TRPHG CHAR(5),
   NG_NHANCHUC DATETIME DEFAULT(GETDATE())
)
Go
ALTER TABLE PHONGBAN
ADD CONSTRAINT FK_PHONGBAN FOREIGN KEY (TRPHG) REFERENCES NHANVIEN(MANV)
Go
CREATE TABLE NHANVIEN
(
   HONV NVARCHAR(10) NOT NULL,
   TENLOT NVARCHAR(15) NOT NULL,
   TENNV NVARCHAR(15) NOT NULL,
   MANV CHAR(5) PRIMARY KEY,
   NGSINH DATETIME,
   DCHI NVARCHAR(50),
   PHAI NCHAR(5) CHECK(PHAI IN(N'NAM',N'NU')),
   LUONG FLOAT CHECK(LUONG BETWEEN 10000 AND 100000),
   MA_NQL CHAR(5),
   PHG INT FOREIGN KEY(PHG) REFERENCES PHONGBAN(MAPHG),
   CONSTRAINT FK_NHANVIEN FOREIGN KEY (MA_NQL) REFERENCES NHANVIEN(MANV)
)
GO
CREATE TABLE DIADIEM_PHG
(
   MAPHG INT,
   DIADIEM NVARCHAR(20),
   CONSTRAINT PK_DIADIEM_PHG PRIMARY KEY(MAPHG,DIADIEM),
   CONSTRAINT FK_DIADIEM_PGH FOREIGN KEY (MAPHG) REFERENCES PHONGBAN(MAPHG)
)
GO
CREATE TABLE DEAN
(
   TENDA NVARCHAR(20),
   MADA INT PRIMARY KEY,
   DDIEM_DA NVARCHAR(20),
   PHONG INT FOREIGN KEY (PHONG) REFERENCES PHONGBAN(MAPHG)
)
GO
CREATE TABLE PHANCONG
(
   MA_NVIEN CHAR(5),
   SODA INT,
   THOIGIAN DECIMAL(3,1) CHECK(THOIGIAN <=40),
   CONSTRAINT PK_PHANCONG PRIMARY KEY(MA_NVIEN,SODA),
   CONSTRAINT FK_PHANCONG FOREIGN KEY (MA_NVIEN) REFERENCES NHANVIEN(MANV),
   CONSTRAINT FK_PHANCONG2 FOREIGN KEY (SODA) REFERENCES DEAN(MADA)
)
GO
CREATE TABLE THANNHAN
(
   MA_NVIEN CHAR(5),
   TENTN NVARCHAR(15),
   PHAI NCHAR(5) CHECK (PHAI IN(N'NAM',N'NU')),
   NGSINH DATETIME,
   QUANHE NVARCHAR(20),
   CONSTRAINT PK_THANNHAN PRIMARY KEY (MA_NVIEN,TENTN),
   CONSTRAINT FK_THANNHAN FOREIGN KEY (MA_NVIEN) REFERENCES NHANVIEN(MANV)
)
GO

SET DATEFORMAT DMY

--PHONGBAN
INSERT INTO PHONGBAN VALUES(N'QUAN LY',1,NULL,NULL)
INSERT INTO PHONGBAN VALUES(N'DIEU HANH',4,NULL,NULL)
INSERT INTO PHONGBAN VALUES(N'NGHIEN CUU',5,NULL,NULL)

--NHAN VIEN
INSERT INTO NHANVIEN VALUES(N'DINH',N'BA',N'TIEN','123','09/01/1955',N'731 TRAN HUNG DAO Q1 TPHCM',N'NAM',30000,NULL,5)
INSERT INTO NHANVIEN VALUES(N'NGUYEN',N'THANH',N'TUNG','333','08/12/1945',N'638 NGUYEN VAN CU Q5 TPHCM',N'NAM',40000,NULL,5)
INSERT INTO NHANVIEN VALUES(N'TRAN',N'THANH',N'TAM','453','31/07/1962',N'543 MAI THI LUU BA DINH HA NOI',N'NAM',25000,NULL,5)
INSERT INTO NHANVIEN VALUES(N'NGUYEN',N'MANH',N'HUNG','666','15/09/1952',N'975 LE LAI P3 VUNG TAU',N'NAM',38000,NULL,5)
INSERT INTO NHANVIEN VALUES(N'VUONG',N'NGOC',N'QUYEN','888','10/10/1927',N'450 TRUNG VUONG MY THO TG',N'NU',55000,NULL,1)
INSERT INTO NHANVIEN VALUES(N'LE',N'THI',N'NHAN','987','20/06/1931',N'291 HO VAN HUE Q.PN TPHCM',N'NU',43000,NULL,4)
INSERT INTO NHANVIEN VALUES(N'TRAN',N'HONG',N'QUANG','777','29/03/1959',N'980 LE HONG PHONG VUNG TAU',N'NAM',25000,NULL,4)
INSERT INTO NHANVIEN VALUES(N'BUI',N'THUY',N'VU','999','19/07/1958',N'332 NGUYEN THAI HOC QUY NHON',N'NAM',25000,NULL,4)


--UPDATE PHONGBAN
UPDATE PHONGBAN SET TRPHG='888', NG_NHANCHUC='19/06/1971' WHERE MAPHG=1
UPDATE PHONGBAN SET TRPHG='777', NG_NHANCHUC='01/01/1985' WHERE MAPHG=4
UPDATE PHONGBAN SET TRPHG='333', NG_NHANCHUC='22/05/1978' WHERE MAPHG=5

--UPDATE NHANVIEN
UPDATE NHANVIEN SET MA_NQL='333' WHERE MANV='123'
UPDATE NHANVIEN SET MA_NQL='888' WHERE MANV='333'
UPDATE NHANVIEN SET MA_NQL='333' WHERE MANV='453'
UPDATE NHANVIEN SET MA_NQL='333' WHERE MANV='666'

UPDATE NHANVIEN SET MA_NQL='888' WHERE MANV='987'
UPDATE NHANVIEN SET MA_NQL='987' WHERE MANV='777'
UPDATE NHANVIEN SET MA_NQL='987' WHERE MANV='999'

--DIADIEM_PHG
INSERT INTO DIADIEM_PHG VALUES(1,N'TP HCM')
INSERT INTO DIADIEM_PHG VALUES(4,N'HA NOI')
INSERT INTO DIADIEM_PHG VALUES(5,N'NHA TRANG')
INSERT INTO DIADIEM_PHG VALUES(5,N'TP HCM')
INSERT INTO DIADIEM_PHG VALUES(5,N'VUNG TAU')

GO
--DEAN
INSERT INTO DEAN VALUES(N'SAN PHAM X',1,N'VUNG TAU',5)
INSERT INTO DEAN VALUES(N'SAN PHAM Y',2,N'NHA TRANG',5)
INSERT INTO DEAN VALUES(N'SAN PHAM Z',3,N'TP HCM',5)
INSERT INTO DEAN VALUES(N'TIN HOC HOA',10,N'HA NOI',4)
INSERT INTO DEAN VALUES(N'CAP QUANG',20,N'TP HCM',1)
INSERT INTO DEAN VALUES(N'DAO TAO',30,N'HA NOI',4)

GO
--PHAN CONG
INSERT INTO PHANCONG VALUES('123',1,22.5)
INSERT INTO PHANCONG VALUES('123',2,7.5)
INSERT INTO PHANCONG VALUES('123',3,10)
INSERT INTO PHANCONG VALUES('333',10,10)
INSERT INTO PHANCONG VALUES('333',20,10)
INSERT INTO PHANCONG VALUES('453',1,20)
INSERT INTO PHANCONG VALUES('453',2,20)
INSERT INTO PHANCONG VALUES('666',3,40)
INSERT INTO PHANCONG VALUES('888',20,0)
INSERT INTO PHANCONG VALUES('987',20,15)
INSERT INTO PHANCONG VALUES('987',30,20)
INSERT INTO PHANCONG VALUES('777',10,35)
INSERT INTO PHANCONG VALUES('777',30,5)
INSERT INTO PHANCONG VALUES('999',10,10)
INSERT INTO PHANCONG VALUES('999',30,30)

GO
--THAN NHAN
INSERT INTO THANNHAN VALUES('123',N'CHAU',N'NU','31/12/1978',N'CON GAI')
INSERT INTO THANNHAN VALUES('123',N'DUY',N'NAM','01/01/1978',N'CON TRAI')
INSERT INTO THANNHAN VALUES('123',N'PHUONG',N'NU','05/05/1957',N'VO CHONG')
INSERT INTO THANNHAN VALUES('333',N'DUONG',N'NU','03/05/1948',N'VO CHONG')
INSERT INTO THANNHAN VALUES('333',N'TUNG',N'NAM','25/10/1973',N'CON TRAI')
INSERT INTO THANNHAN VALUES('333',N'QUANG',N'NU','05/04/1976',N'CON GAI')
INSERT INTO THANNHAN VALUES('987',N'DANG',N'NAM','29/02/1932',N'VO CHONG') 

*Q1

select *
from nhanvien
where phai='nam'
and luong>30000

//=======================================
*Q2

select *
from nhanvien nv,phongban pb
where nv.phg=pb.maphg
and phai='nu'
and tenphg='nghien cuu'

//===========================================================
*Q3

select *
from nhanvien
where phai='nam'
and tennv like 'q%'

//==============================================================================

*Q4

select honv, tenlot,tennv,year(getdate())-year(ngsinh) as tuoi
from nhanvien
where phai='nam'
and year(getdate ())-year(ngsinh) between 40 and 50

//============================================================
*Q5

select *                                                                                                                                                                                                                             
from nhanvien n,phancong p
where n.manv=p.ma_nvien
and soda=2

//==========================================================
*Q6

select *                                                                                                                                                                                                                             
from nhanvien n,phancong p, dean d
where n.manv=p.ma_nvien and p.soda=d.mada
and ddiem_da='vung tau'

//=========================================================
*Q7

select *
from dean d,phongban p
where d.phong=p.maphg
and tenphg='dieu hanh'

//============================================================================

*Q8

select distinct manv,honv,tenlot,tennv                                                                                                                                                                                                                             
from nhanvien n,phancong p, dean d, phongban pb
where n.manv=p.ma_nvien and p.soda=d.mada and d.phong=pb.maphg
and tenphg='nghien cuu'

//==============================================================
*Q9

select *
from dean d,phancong p,nhanvien n
where d.mada=p.soda
and p.ma_nvien=n.manv
and thoigian>20
and tennv='vu'

//=======================================================

*Q10

select manv, honv, tenlot, tennv
from nhanvien n,phancong p,dean d
where n.manv=p.ma_nvien
and p.soda=d.mada
and dchi like'%tphcm'
and ddiem_da='tp hcm'

//===================================================
 
*Q11


//=============================================

*Q12

select *
from nhanvien
where manv not in(select ma_nvien from phancong)

//===============================================
*Q13

select distinct manv, honv, tenlot, tennv
from nhanvien n, phancong p
where n.manv=p.ma_nvien
and p.soda in(
select soda
from phancong p,nhanvien n
where p.ma_nvien=n.manv
and honv='dinh' and tenlot='ba' and tennv='tien')
and tennv<>'tien'

//============================================================================

*Q14
select *
from nhanvien n
where manv in
(select manv
from nhanvien n,phongban p,phancong c
where n.manv=c.manvien
and  n.phg=p.maphg
and thoigian>0
and phg=5
)
//===========================================================


*Q15
select soda,,count(*) as sl
from phancong
group by soda
having count(*)>2
//==================================================
*Q16

select phg,avg(luong) as luongtb
from nhanvien
group by phg
order by  luongtb desc
//======================================================

*Q17
select phg,avg(luong) as luongtb
from nhanvien
where phai='nam'
group by phg
//==================================================================

*Q18

select phg, count (*) as sonvnu
from nhanvien
where phai='nu'
group by phg
having count (*)<3
//===================================================================

*Q19

select *
from nhanvien n,dean d,phancong p
where n.manv=p.manvien
and p.soda=d.mada
and honv='dinh'
//=====================================================================
*Q20

select *
from nhanvien
where ma_nql=(
select manv
from nhanvien
where tennv='tung')
//=================================================================

*Q21

select manv, tennv, phong
from nhanvien n,phancong p,dean d
where n.manv=p.ma_nvien
and p.soda=d.mada
and ddiem_da='tp hcm' and d.phong not in(
select maphg
from diadiem_phg
where diadiem='tp hcm')




//=======================================================================
*Q22

select phg
from nhanvien
where phai not in
(select phai
from nhanvien
where phai='nu'
)
//=================================================================
*Q23

select phg
from nhanvien
where manv=(select max(manv)
from nhanvien
)

select maphg, tenphg, count(manv) as sonhanvien
from phongban pb, nhanvien nv
where pb.maphg = nv.phg
group by maphg, tenphg
having count(manv) >=all (
select count(manv)
from nhanvien
group by phg)

select top 1 maphg, tenphg, count(manv) as sonhanvien
from phongban pb, nhanvien nv
where pb.maphg = nv.phg
group by maphg, tenphg
order by sonhanvien desc

//===============================================================================

*Q24

select honv,tenlot,tennv,manv,dchi,phg,tenphg
from nhanvien n,phongban p
where n.phg=p.maphg
and phg=5
//================================================================================================
*Q25

select honv+' '+tennv as hotenv,mada,phong,dchi,ngsinh
from nhanvien n,dean d
where n.phg=d.phong
and phong=4
//================================

*Q26

select honv,mada
from nhanvien n,dean d
where n.phg=d.phong
and honv='dinh'
//===================================================
*Q27

select honv,tennv,manv,count(tentn)as sotn
from nhanvien n,thannhan t
where n.manv=t.manvien
group by honv,tennv,manv
having count(tentn)>2

//=======================================================================
*Q28
select manv, tennv
from nhanvien nv left outer join thannhan tn on nv.manv = tn.manvien
where tn.manvien is null

select manv, tennv
from nhanvien
where manv <> all(select manvien from thannhan)

//================================================
*Q29
select honv,tennv,manv,count(tentn)as sotn
from nhanvien n,phongban b,thannhan t
where n.phg=b.maphg
and n.manv=t.manvien
group by honv,tennv,manv
having count(tentn)>=1
//===============================================================
*Q30

select *
from nhanvien
where manv  in
(select manv
from nhanvien n,dean d,phancong p
where n.manv=p.manvien
and p.soda=d.mada
and mada=1
and phg=5
and thoigian>20

)
//==================================================================
*Q31

select honv,tennv
from nhanvien n,thannhan t
where n.manv=t.manvien
and  manv=333

//=========================================================
*Q32

select mada,tenda, sum(thoigian) as tongtg
from dean d,phancong p
where d.mada=p.soda
group by mada,tenda

//=========================================================

*Q33

select * from nhanvien
where manv not in(
select manv
from nhanvien n,phancong p
where n.manv=p.ma_nvien
and p.soda in(
select mada
from dean
where ddiem_da='ha noi'))

//=========================================================

*Q34

select honv,tennv, count(soda) as sodean
from nhanvien n,phancong p
where n.manv=p.ma_nvien
group by honv,tennv
having count(soda)=(
select count(*) as sodean
from dean)


//=========================================================

*Q35

select * from nhanvien
where manv not in(
select manv
from nhanvien n,phancong p
where n.manv=p.ma_nvien
and p.soda not in(
select mada
from dean
where phong=5))


select distinct manv,tennv
from nhanvien n,phancong p
where n.manv=p.ma_nvien
and p.soda in(
select mada
from dean
where phong=5)

/=========================================================
*Q36

select manv,honv,tenlot,tennv,luong
from nhanvien n, phongban p
where n.phg=p.maphg and p.tenphg='nghien cuu'
and luong>(
select avg(luong) as luongtb
from nhanvien)

//=========================================================

*Q37

select maphg, tenphg, avg(luong) as luongtb, count(manv) as sonv
from phongban p, nhanvien n
where p.maphg=n.phg
group by maphg, tenphg
having avg(luong)>30000

//=========================================================
*Q38

select manv,honv,tenlot,tennv, count(soda) as sodean, sum(thoigian) as tongtg
from nhanvien n,phancong p
where n.manv=p.ma_nvien
group by manv,honv,tenlot,tennv
having count(soda)>1 and sum(thoigian)>35

//=========================================================

*Q39

select manv,honv,tenlot,tennv,luong
from nhanvien
where luong>(
select avg(luong) as luongtb
from nhanvien)


//=========================================================
*Q40

select maphg,tenphg, avg(year(getdate())-year(ngsinh)) as tuoitb
from phongban p,nhanvien n
where p.maphg=n.phg
group by maphg,tenphg

select maphg,tenphg, year(getdate())-year(ngsinh) as tuoitb
from phongban p,nhanvien n
where p.maphg=n.phg

//=========================================================