TRUY VAN

--Q1:Trích ra danh sách nhân viên nam có Lương trên 30.000

 Select *

 From NhanVien

 Where Luong > 30000 and Phai = 'Nam'

--Q2:Trích ra danh sách nhân viên nữ ở Phòng nghiên cưú

SelectNV.*

From NhanVien NV, PhongBan PB

Where (NV.PHG = PB.MaPHG) and (NV.PHG = 5 and NV.Phai = 'Nu')

--Q3:Trích ra danh sách nhân viên nam có tên bắt đầu bằng chữ ‘Q’

Select *

From NhanVien

Where TenNV like 'Q%' and Phai = 'Nam'

--Q4.Trích ra danh sách nhân viên nam từ 20 đến 30 tuổi.

Select *

From NhanVien

Where ((year(getdate()) - year(NgSinh)) between 20 and 30) and Phai='Nam'

--Q5.Trích ra danh sách nhân viên có tham gia đề án 2

Select *

From PhanCong PC, NhanVien NV

Where NV.MaNV = PC.Ma_NVien and PC.SoDA = 2

--Q6.Trích ra danh sách nhân viên tham gia những đề án ở ‘Vũng Tàu’

Select NV.*

From PhanCong PC, NhanVien NV, DeAn DA

Where NV.MaNV = PC.Ma_NVien and PC.SoDA = MaDA and DA.DDiem_DA = 'VUNG TAU'

--Q7.Trích ra danh sách các đề án của phòng ‘Điều hành’

Select TenDA

From DeAn

Where Phong = 4

--Q8.Trích ra danh sách nhân viên tham gia đề án của phòng ‘Nghiên cứu’

Select NV.TenNV

From PhanCong PC, NhanVien NV, DeAn DA

Where NV.MaNV = PC.Ma_NVien and PC.SoDA = MaDA and DA.Phong = 5

--Q9.Trích ra danh sách các đề án mà nhân viên ‘Vũ’ tham gia với thời gian trên 20 giờ

Select DA.TenDA as [Ten De An]

From PhanCong PC, NhanVien NV, DeAn DA

Where PC.SoDA = DA.MaDA and PC.Ma_NVien = NV.MaNV and (NV.TenNV = 'Vu' and PC.ThoiGian >20)

--Q10.Trích ra danh sách nhân viên địa chỉ TPHCM tham gia đề án ở TPHCM

Select NV.*

From NhanVien NV, DeAn DA, DiaDiem_PHG DDP, PhanCong PC

Where PC.SoDA = DA.MaDA and PC.Ma_NVien = NV.MaNV and DDP.MaPHG = NV.PHG and (DDP.DiaDiem = 'TP HCM' and DA.DDiem_DA = 'TP HCM')

--Q12.Trích ra danh sách nhân viên không tham gia đề án nào.

Select MaNV

From NhanVien

Where MaNV Not In (Select Ma_NVien

From PhanCong)

--Q13.Trích ra danh sách nhân viên tham gia những đề án mà Đinh Bá Tiến tham gia

Select distinct NV.*

From PhanCong PC, NhanVien NV

Where PC.Ma_NVien = NV.MaNV and PC.SoDA in 

(Select PC.SoDA

From PhanCong PC, NhanVien NV

Where PC.Ma_NVien = NV.MaNV and (NV.HoNV = 'Dinh' and NV.TenLot = 'Ba' and NV.TenNV = 'Tien'))

--Q14.Trích ra danh sách nhân viên của phòng nghiên cứu có tham gia đề án

Select NV.*

From NhanVien NV, PhongBan PB

Where PB.MaPHG = NV.PHG and NV.MaNV in

(Select Ma_NVien

From PhanCong) and PB.TenPHG = 'Nghien cuu'

--Q15.Trích ra danh sách các đề án có trên 2 người tham gia

Select TenDA

From DeAn

Where MaDA in (Select PC.SoDA

From DeAn DA, PhanCong PC

Where PC.SoDA = DA.MaDA

Group by PC.SoDA

Having count(PC.SoDA) >= 2)

--Q16.Tính lương trung bình theo từng phòng, sắp thứ tự giảm dần theo lương trung bình

Select avg(Luong) as [Luong Trung Binh]

From NhanVien

Group by PHG

ORDER BY [Luong Trung Binh] desc

--Q17.Tính lương trung bình của nam nhân viên theo từng phòng

Select avg(Luong) as [Luong Trung Binh], PHG

From NhanVien

Where Phai = 'Nam'

Group by PHG

--Q18.Liệt kê các phòng có ít hơn 3 nữ nhân viên

Select PB.TenPHG

From PhongBan PB left join (Select PHG, count (MaNV)as SL

From NhanVien

Where Phai = 'Nu'

Group by PHG) as KQ on PB.MaPHG = KQ.PHG

Where  (KQ.SL < 3) or (KQ.SL is NULL) 

--Q19.Liệt kê các đề án có người trưởng phòng chủ trì họ ‘Đinh’

Select DA.TenDA

From PhanCong PC, NhanVien NV, DeAn DA, PhongBan PB

Where PC.SoDA = DA.MaDA and PC.Ma_NVien = NV.MaNV and NV.MaNV = PB.TrPHG and NV.TenNV like '%Đinh%'

--Q20.Trích ra danh sách nhân viên được ‘Nguyễn Thanh Tùng’ phụ trách trực tiếp

Select NV.*

From PhongBan PB, NhanVien NV

Where PB.MaPHG = NV.PHG and NV.Ma_NQL = (Select NV.MANV

From NhanVien NV, PhongBan PB

Where PB.MaPHG = NV.PHG and NV.HoNV like '%Nguyen%'and NV.Tenlot like '%Thanh%' and NV.TenNV like '%Tung%')

--Q22.Trích ra danh sách những phòng không có nữ nhân viên

--Cach 1

Select PB.TenPHG

From PhongBan PB left join (Select PHG, count (MaNV)as SL

From NhanVien

Where Phai = 'Nu'

Group by PHG) as KQ on PB.MaPHG = KQ.PHG

Where (KQ.SL is NULL) 

--Cach 2

Select TenPHG

From PhongBan

Where MaPHG not in (Select PHG 

From NhanVien

Where Phai = 'Nu')

--Q23.Trích ra danh sách những phòng đông nhân viên nhất

Select PHG

From NhanVien

Group by PHG

Having count(MaNV) >= all (Select count(MaNV)

From NhanVien

Group by PHG)

--Q24.Cho biết tên và địa chỉ các nhân viên phòng “Nghiên cứu”.

Select NV.HoNV + ' '+ NV.TenLot + ' '+ NV.TenNV as Ten, NV.DChi as [Dia Chi]

From NhanVien NV, PhongBan PB

Where NV.PHG = PB.MaPHG and PB.TenPHG = 'Nghien cuu'

--Q25.Với các đề án ở “HA NOI”, liệt kê các mã số đề án (MADA), mã số phòng ban chủ trì đề án (PHONG), họ tên trưởng phòng (HONV, TEN_NV) cũng như địa chỉ (DCHI) và ngày sinh của người ấy.

Select DA.MADA, DA.Phong, NV.TenNV, PB.TrPHG, NV.DChi, NV.NgSinh

From NhanVien NV, DeAn DA, PhongBan PB

Where NV.PHG = DA.Phong and NV.MaNV = PB.TrPHG and DA.DDiem_DA = 'HA NOI'

--Q26.Danh sách các đề án (MADA) có nhân viên với họ (HONV) là “Dinh”

Select PC.SoDA

From NhanVien NV, PhanCong PC

Where NV.MaNV = PC.Ma_NVien and NV.HoNV like '%Dinh%'

--Q27.Danh sách những nhân viên (HONV, TENNV) có trên 2 thân nhân.

Select HoNV +' '+ TenLot+ ' '+ TenNV

From NhanVien

Where MaNV in (Select Ma_NVien

From ThanNhan

Group by Ma_NVien

Having count(Ma_NVien) > 2)

--Q28.Danh sách những nhân viên (HONV, TENNV) không có thân nhân nào.

Select HoNV +' '+ TenLot+ ' '+ TenNV

From NhanVien

Where MaNV not in (Select Ma_NVien

From ThanNhan)

--Q29.Danh sách những trưởng phòng có tối thiểu một thân nhân.

Select distinct NV.HoNV +' '+ NV.TenLot+ ' '+ NV.TenNV as [Ten Truong Phong]

From ThanNhan TN, NhanVien NV

Where NV.MaNV = TN.Ma_NVien and Ma_NVien in (Select TrPHG

From PhongBan)

--Q30.Tên những nhân viên phòng số 5 có tham gia vào đề án San pham X với thời gian làm việc trên 20 giờ.

Select NV.HoNV +' '+ NV.TenLot+ ' '+ NV.TenNV as [Ten Nhan Vien]

From NhanVien NV, PhanCong PC, DeAn DA 

Where NV.MaNV = PC.Ma_NVien and PC.SoDA = DA.MaDA and DA.TenDA = 'San pham X' and DA.Phong = 5 and PC.ThoiGian > 20

--Q31.Danh sách những nhân viên (HONV, TENNV) có cùng tên với người thân.

Select NV.HoNV +' '+ NV.TenLot+ ' '+ NV.TenNV as [Ten Nhan Vien]

From NhanVien NV, ThanNhan TN

Where NV.MaNV = TN.Ma_NVien and TN.TenTN like NV.TenNV

--Q32.Với mỗi đề án, liệt kê tên đề án (TENDA) và tổng số giờ làm việc của tất cả các nhân viên tham gia đề án đó.

Select DA.TenDA, sum (PC.ThoiGian) as [Tong thoi gian]

From DeAn DA, PhanCong PC

Where PC.SoDA = DA.MaDA

Group by DA.TenDA

--Q33.Danh sách những nhân viên (HONV, TENNV) không làm việc cho một đề án nào ở ‘HA NOI’.

Select HoNV +' '+ TenLot+ ' '+ TenNV as [Ten Nhan Vien]

From NhanVien

Where MaNV not in ( Select distinct NV.MaNV

From NhanVien NV, DeAn DA

Where NV.PHG = DA.Phong and DA.DDiem_DA = 'HA NOI')

--Q34.Danh sách những nhân viên (HONV, TENNV) làm việc cho tất cả các đề án.

Select NV.HoNV + ' ' + NV.TenNV

From NhanVien NV, PhanCong PC

Where NV.MaNV = PC.Ma_NVien

Group by NV.HoNV + ' ' + NV.TenNV

Having count (PC.SoDA) = (Select count (MaDA)

 From DeAn)

--Q35.Tìm tên của các nhân viên làm việc cho tất cả các đề án mà phòng 5 chủ trì.

Select NV.HoNV + ' ' + NV.TenNV

From NhanVien NV, PhanCong PC, DeAn DA

Where NV.MaNV = PC.Ma_NVien and DA.Phong = 5

Group by NV.HoNV + ' ' + NV.TenNV

Having count (PC.SoDA) = (Select count (MaDA)

 From DeAn

 Where Phong = 5)

--Q36.Danh sách những nhân viên có mức lương trên mức lương trung bình của phòng “Nghiên cứu”

Select HoNV +' '+ TenLot+ ' '+ TenNV as [Ten Nhan Vien]

From NhanVien

Where PHG in (Select MaPHG

 From PhongBan 

 Where TenPHG = 'Nghien cuu') and Luong > (Select avg(Luong) as [Luong Trung Binh]

From NhanVien

Where PHG = (Select MaPHG

From PhongBan 

Where TenPHG = 'Nghien cuu')Group by PHG)

--Q37.Với các phòng ban có mức lương trung bình trên 30.000. Liệt kê tên phòng ban và số lượng nhân viên của phòng ban đó.

Select PB.TenPHG, count (NV.MaNV) as [So luong nhan vien]

From NhanVien NV, PhongBan PB

Where NV.PHG = PB.MaPHG and PB.MaPHG in (Select PHG

From NhanVien

Group by PHG

Having avg(Luong) > 30000)

Group by PB.TenPHG