--I.1 liet ke danh sach nhan vien gom ten dem, ten , gioi tinh

SELECT HoDem,Ten,GioiTinh FROM tbNhanVien

--I.2 liet ke danh sach nhan vien thuoc don vi co ten la 'Cong Ty 325'

SELECT a.* FROM tbNhanVien a,tbDonVi b

WHERE  b.MaSoDV = a.MaSoDV

AND b.TenDV = 'Cong ty 325'

--I.3 liet ke danh sach ten du an do don vi co ten la 'Hoc Vien Ngan Hang' thuc hien

SELECT a.TenDA FROM tbDuAn a, tbDonVi b

WHERE a.MaSoDV = b.MaSoDV

AND     b.TenDV ='Hoc Vien Ngan hang'

--I.4 liet ke danh sasch ten du an do ca hai nhan vien co ma so la 'nv001' va 'nv002' thuc hien

SELECT a.TenDA FROM tbDuAn a, tbNhanVien_DuAn b

WHERE a.MaSoDA = b.MaSoDA

AND b.MaSoNV IN('nv001','nv002')

--I.5 liet ke danh sach ten du an co nhan vien thuc hien co dia chi trung voi dia diem thuc hien

SELECT DISTINCT(a.TenDA) FROM tbDuAn a, tbNhanVien_DuAn b, tbNhanvien c

WHERE a.MaSoDA=b.MaSoDA

AND b.MaSoNV=c.MaSoNV

AND c.DiaChi LIKE a.DiaDiem

--I.6 liet ke danh sach ten du an do nhan vien co gioi tinh 'nam' thuc hien

SELECT DISTINCT  (a.TenDA) FROM tbDuAn a, tbNhanVien_DuAn b, tbNhanVien c

WHERE a.MaSoDA =b.MaSoDA

AND   b.MaSoNV = c.MaSoNV

AND   c.GioiTinh ='Nam'

--I.7 Liet ke danh sach ten don vi co nhung nhan vien thuc hien cac du an co dia chi cung dia diem tai 'Da Nang'

SELECT a.TenDV

FROM tbDonVi a, tbNhanVien b, tbDuAn c, tbNhanVien_DuAn d

WHERE a.MaSoDV = b.MaSoDV

AND  c.MaSoDA =d.MaSoDA

AND d.MaSoNV = b.MaSoNV

AND c.DiaDiem LIKE'Da Nang'

AND b.DiaChi LIKE 'Da Nang'

--I.8 liet ke danh sach ma nhan vien co luong lon hon 2500 thuoc don vi 'nha mayHai Van' Co so gio lon hon 10

SELECT a.MaSoNV FROM tbNhanVien a, tbDonVi b, tbDuAn c,tbNhanVien_DuAn d

WHERE a.MaSoDV = b.MaSoDV

AND   b.MaSoDV = c.MaSoDV

AND   c.MaSoDA = d.MaSoDA

AND   d.MaSoNV = a.MaSoNV

AND   a.Luong>2500

AND   b.TenDV ='Nha May Hai van'

AND  d.SoGio >10

--I.9 liet ke nhung ma nhan vien khong tham gia vao bat ky du an nao

SELECT HoDem, Ten

FROM tblNhanVien

EXCEPT

SELECT HoDem, Ten

FROM tblNhanVien a,tblNhanVien_DuAn b

WHERE a.MaSoNV=b.MaSoNV

--I.10 lietke danh sach nahn vien gom ho dem, ten,ngay sinh vua la nguoi quan ly don vi vua la nguoi tham gia du an

SELECT a.HoDem,a.Ten,a.NgaySinh FROM tbNhanVien a, tbNhanVien_DuAn b, tbDuAn c, tbDonVi d

WHERE a.MaSoNV = b.MaSoNV

AND b.MaSoDA = c.MaSoDA

AND a.MaSoDV = d.MaSoDV

AND d.MaSoNQL = b.MaSoNV

--------------------------------II.Truy Van Long----------------

--II.1 thuc hien cac cau truy van trog I.2-10 bang truy van long

--I.2 liet ke danh sach nhan vien thuoc don vi co ten la 'Cong Ty 325'

select a.* from tbNhanVien a

WHERE  a.MaSoDV In (select b.MaSoDV from tbDonVi b

   Where b.TenDV= 'Cong ty 325')

--I.3 liet ke danh sach ten du an do don vi co ten la 'Hoc Vien Ngan Hang' thuc hien

SELECT a.TenDA FROM tbDuAn a

where a.MaSoDV In ( select b.MaSoDV from tbDonVi b

   where  b.TenDV ='Hoc Vien Ngan hang')

--I.4 liet ke danh sasch ten du an do ca hai nhan vien co ma so la 'nv001' va 'nv002' thuc hien

SELECT DISTINCT(TenDA) FROM tbDuAn

where MaSoDA IN(select b.MaSoDA

from tbNhanVien_DuAn b

where b.MaSoNV IN('nv001','nv002'))

--I.5 liet ke danh sach ten du an co nhan vien thuc hien co dia chi trung voi dia diem thuc hien

/*

SELECT DISTINCT (a.TenDA) FROM tbDuAn a, tbNhanVien_DuAn b, tbNhanvien c

WHERE a.MaSoDA=b.MaSoDA

AND b.MaSoNV=c.MaSoNV

AND c.DiaChi = a.DiaDiem

select a.TenDA From tbDuAn a,

*/

--I.6 liet ke danh sach ten du an do nhan vien co gioi tinh 'nam' thuc hien

--I.7 Liet ke danh sach ten don vi co nhung nhan vien thuc hien cac du an co dia chi cung dia diem tai 'Da Nang'

SELECT DISTINCT(TenDV) FROM tbDonVi

WHERE MaSoDV In(select a.MaSoDV from tbDuAn a, tbNhanVien_DuAn b, tbNhanVien c

     where a.MaSoDA = b.MasoDA

     and b.MaSoNV = c.MaSoNv

     and a.DiaDiem = 'Da Nang'

     and c.Diachi ='Da Nang')

/*

--I.8 liet ke danh sach ma nhan vien co luong lon hon 2500 thuoc don vi 'nha may Hai Van' Co so gio lon hon 10

SELECT a.MaSoNV FROM tbNhanVien a, tbDonVi b, tbDuAn c,tbNhanVien_DuAn d

WHERE a.MaSoDV = b.MaSoDV

AND   b.MaSoDV = c.MaSoDV

AND   c.MaSoDA = d.MaSoDA

AND   d.MaSoNV = a.MaSoNV

AND   a.Luong>2500

AND   b.TenDV ='Nha May Hai van'

AND  d.SoGio >10

SELECT MaSoNV From tbNhanvien

where MaSoNV IN(select a.MaSoNV from tb

select MaSoNV from tbNhanVien

where

*/

--------------------------------------------------2.

SELECT MaSoNV

FROM tblNhanVien

WHERE DATEDIFF(mm,NgaySinh,GETDATE()) = (SELECT  MIN(DATEDIFF(mm,NgaySinh,GETDATE())) AS NgaySinh FROM tblNhanVien )

---3

SELECT MaSoDA

FROM tblNhanVien_DuAn

GROUP BY MaSoDA

HAVING SUM(SoGio) >= all(SELECT SUM(SoGio) AS TongGio FROM tblNhanVien_DuAn GROUP BY MaSoDA)

--III

---1

SELECT MaSoNV,COUNT(*) AS SoDuAn

FROM tblNhanVien_DuAn

GROUP BY MaSoNV

HAVING COUNT(*) >= all(SELECT COUNT(*) FROM tblNhanVien_DuAn GROUP BY MaSoNV)

---2

SELECT MaSoDV

FROM tblDuAn

GROUP BY MaSoDV

HAVING COUNT(*) >= all(SELECT COUNT(*) FROM tblDuAn GROUP BY MaSoDV)

---3

SELECT MaSoNV, SUM(SoGio) AS TongSoGio

FROM tblNhanVien_DuAn

GROUP BY MaSoNV