Bài này lâu rùi mà mình chưa làm xong 
. Nhưng thấy box này trống chạy vô xong đất. Nếu ai làm xong rùi thì post bài full lên cho mn xem nha 
. Nhưng thấy box này trống chạy vô xong đất. Nếu ai làm xong rùi thì post bài full lên cho mn xem nha - Code:
use master
go
if exists(select * from SysDatabases where name = 'QLNV') drop database QLNV
go
create database QLNV
go
use QLNV
go
create table NHANVIEN
(
MaNV nvarchar (5) not null,
HoNV nvarchar (10) not null,
TenLot nvarchar (10) not null,
TenNV nvarchar (10) not null,
NgaySinh datetime not null,
DiaChi nvarchar (50) not null,
Phai nvarchar (3) not null,
Luong float not null,
MaPB nvarchar (3) not null
)
create table DEAN
(
MaDA nvarchar (5) not null,
TenDA nvarchar (30) not null,
DiaDiemDA nvarchar (30) not null,
MaPB nvarchar (3) not null
)
create table DIADIEMPB
(
MaPB nvarchar (3) not null,
DiaDiem nvarchar (20) not null,
)
create table PHONGBAN
(
MaPB nvarchar (3) not null,
TenPB nvarchar (30) not null,
TruongPhong nvarchar (5) not null,
NgayNhanChuc datetime not null
)
create table PHANCONG
(
MaNV nvarchar (5) not null,
MaDA nvarchar (5) not null,
ThoiGian float not null
)
create table THANNHAN
(
MaNV nvarchar (5) not null,
TenTN nvarchar (20) not null,
Phai nvarchar (4) not null,
NgaySinhTN datetime not null,
QuanHe nvarchar (30) not null
)
--- tao khoa chinh
alter table NHANVIEN add constraint pknv primary key (MaNV)
alter table DEAN add constraint pkda primary key (MaDA)
alter table PHONGBAN add constraint pkpb primary key (MaPB)
alter table THANNHAN add constraint pktn primary key (MaNV, TenTN)
alter table DIADIEMPB add constraint pkdd primary key (MaPB, DiaDiem)
alter table PHANCONG add constraint pkpc primary key (MaNV, MaDA)
--- tao khoa ngoai
alter table NHANVIEN add constraint fknvpb foreign key (MaPB) references PHONGBAN (MaPB)
alter table DIADIEMPB add constraint fkddpb foreign key (MaPB) references PHONGBAN (MaPB)
alter table PHONGBAN add constraint fkpbnv foreign key (TruongPhong) references NHANVIEN (MaNV)
alter table DEAN add constraint fkdapb foreign key (MaPB) references PHONGBAN (MaPB)
alter table THANNHAN add constraint fktnnv foreign key (MaNV) references NHANVIEN (MaNV)
alter table PHANCONG add constraint fkpcnv foreign key (MaNV) references NHANVIEN (MaNV)
alter table DEAN add constraint fkpcda foreign key (MaDA) references DEAN (MaDA)
--- nhap du lieu
--- truoc khi nhap du lieu nen xoa 1 khoa ngoai
alter table NHANVIEN drop constraint fknvpb
--- nhap du lieu cho NHANVIEN
insert into NHANVIEN values ('NV1', 'DINH' , 'BA', 'TIEN', '1/9/65', '731 Tran Hung Dao q1 tphcm', 'NAM' , '30000', 'P3')
insert into NHANVIEN values ('NV2', 'VO', 'THANH', 'TUNG', '12/8/55', '638 Tran Hung Dao', 'NAM', '40000', 'P3')
insert into NHANVIEN values ('NV3', 'TRAN', 'THANH', 'TAM', '7/31/72', '853 Mai Thi Luu q1 tphcm', 'NAM', '25000', 'P2')
insert into NHANVIEN values ('NV4', 'VO', 'MANH', 'HUNG', '9/15/61', '975 Ba Ria, Vung Tau', 'NAM', '38000', 'P2')
insert into NHANVIEN values ('NV5', 'LE', 'NGOC', 'QUYEN', '10/10/77', '450 Trung Vuong, Ha Noi', 'NU', '55000', 'P1')
insert into NHANVIEN values ('NV6', 'TRAN', 'MINH', 'KHANG', '6/24/62', 'Tran Binh Trong tphcm', 'NAM', '58000', 'P1')
insert into NHANVIEN values ('NV7', 'LE', 'THI', 'NHAN', '6/20/61', '291 Ho Van Hue QPN tphcm', 'NU', '43000', 'P3')
insert into NHANVIEN values ('NV8', 'TRAN', 'HONG', 'QUAN', '3/29/79','980 Le Hong Phong q10 tphcm', 'NAM', '25000', 'P2')
insert into NHANVIEN values ('NV9', 'BUI', 'THUY', 'VU', '7/19/58', '332 Nguyen Thai Hoc q1 tphcm', 'NU', '25000', 'P1')
--- nhap du lieu cho PHONG BAN
insert into PHONGBAN values ('P1', 'QUAN LY', 'NV5', '6/19/01')
insert into PHONGBAN values ('P2', 'DIEU HANH', 'NV8', '1/1/85')
insert into PHONGBAN values ('P3', 'NGHIEN CUU', 'NV2', '5/22/98')
--- nhap du lieu cho DIA DIEM PHONG BAN
insert into DIADIEMPB values ('P1', 'TPHCM')
insert into DIADIEMPB values ('P2', 'HA NOI')
insert into DIADIEMPB values ('P3', 'NHA TRANG')
insert into DIADIEMPB values ('P3', 'TPHCM')
insert into DIADIEMPB values ('P3', 'VUNG TAU')
--- nhap du lieu cho DE AN
insert into DEAN values ('DA1', 'San Pham X', 'VUNG TAU', 'P3')
insert into DEAN values ('DA2', 'San Pham Y', 'NHA TRANG', 'P3')
insert into DEAN values ('DA3', 'San Pham Z', 'TPHCM', 'P3')
insert into DEAN values ('DA4', 'Tin Hoc Hoa', 'HA NOI', 'P2')
insert into DEAN values ('DA5', 'Cap Quang', 'TPHCM', 'P1')
insert into DEAN values ('DA6', 'Dao Tao', 'HA NOI', 'P2')
--- nhap du lieu cho PHAN CONG
insert into PHANCONG values ('NV1', 'DA1', '32.5')
insert into PHANCONG values ('NV1', 'DA2', '7.5')
insert into PHANCONG values ('NV1', 'DA3', '10')
insert into PHANCONG values ('NV2', 'DA3', '40')
insert into PHANCONG values ('NV2', 'DA4', '20')
insert into PHANCONG values ('NV2', 'DA5', '20')
insert into PHANCONG values ('NV3', 'DA1', '20')
insert into PHANCONG values ('NV3', 'DA2', '20')
insert into PHANCONG values ('NV4', 'DA3', '10')
insert into PHANCONG values ('NV5', 'DA4', '10')
insert into PHANCONG values ('NV5', 'DA5', '10')
insert into PHANCONG values ('NV7', 'DA6', '30')
insert into PHANCONG values ('NV8', 'DA4', '10')
insert into PHANCONG values ('NV8', 'DA6', '20')
insert into PHANCONG values ('NV9', 'DA4', '15')
insert into PHANCONG values ('NV9', 'DA6', '35')
--- nhap du lieu cho THAN NHAN
insert into THANNHAN values ('NV1', 'CHAU', 'NU', '12/31/78', 'con gai')
insert into THANNHAN values ('NV1', 'DUY', 'NAM', '1/1/28', 'cha me')
insert into THANNHAN values ('NV1', 'PHUONG', 'NU', '5/5/57', 'vo chong')
insert into THANNHAN values ('NV2', 'DUONG', 'NU', '5/3/48', 'vo chong')
insert into THANNHAN values ('NV2', 'KHANG', 'NAM', '10/25/73', 'con trai')
insert into THANNHAN values ('NV2', 'QUANG', 'NU', '4/5/76', 'con gai')
insert into THANNHAN values ('NV7', 'DANG', 'NAM', '2/29/32', 'vo chong')
--- show cac table
select *from NHANVIEN
select *from PHANCONG
select *from DEAN
select *from PHONGBAN
select *from DIADIEMPB
select *from THANNHAN
--Thuc hien Bai tap
--- cau 1: cho biet ten tuoi tung nhan vien
select HoNV, TenLot, TenNV , 'Tuoi' = YEAR(GETDATE()) - YEAR(NgaySinh)
from NHANVIEN
--- cau 2: cho biet nhan vien nam co dia chi "Tran Hung Dao"
select HoNV, TenLot, TenNV, Phai, DiaChi
from NHANVIEN
where Phai='NAM' and DiaChi like '%Tran Hung Dao%'
--- cau 3: cho biet ng co ho 'Le' va ten bat dau bang 'N'
select HoNV, TenLot, TenNV
from NHANVIEN
where HoNV='Le' and TenNV like 'N%'
--- cau 4: cho biet ng co ngay sinh 07/1958
select HoNV, TenLot, TenNV, NgaySinh
from NHANVIEN
where MONTH(NgaySinh)=7 and YEAR(NgaySinh)=1958
--- cau 5: cho biet nhung ng co ngay sinh trong quy 3
select HoNV, TenLot, TenNV, NgaySinh
from NHANVIEN
where MONTH(NgaySinh) in (7,8,9)
--- cau 6: cho biet so DA dc phan cong cua tung NV (ma so, tan DA, so luong)
select nv.MaNV, TenNV, 'SLDA' = COUNT (pc.MaDA)
from NHANVIEN nv, PHANCONG pc
where nv.MaNV = pc.MaNV
group by nv.MaNV, TenNV
--- cau 7: ten NV co Than Nhan >=2
select nv.MaNV, TenNV, 'SLTN' = COUNT (tn.TenTN)
from NHANVIEN nv, THANNHAN tn
where nv.MaNV = tn.MaNV
group by nv.MaNV, TenNV
having COUNT(tn.TenTN)>=2
--- cau 8: ten NV co tuoi >=30 dc phan cong DA o tphcm
select TenNV
from NHANVIEN nv
where YEAR(getdate()) - YEAR(NgaySinh)>=30
and nv.MaNV in
(
select MaNV
from DEAN da inner join PHANCONG pc on da.MaDA = pc.MaDA
where da.DiaDiemDA = 'TPHCM'
)
--- cau 9: cho biet co bao nhieu DA o tphcm
select 'SL' = COUNT (DiaDiemDA)
from DEAN
where DiaDiemDA = 'TPHCM'
--- cau 10: ten va SLDA / thanh pho
select DiaDiemDA, 'SL' = COUNT (DiaDiemDA)
from DEAN
group by DiaDiemDA
--- cau 11: so nam tham nien cua tung truong phong
select TenNV, TenPB, 'tham nien' = YEAR(GETDATE())-YEAR( NgayNhanChuc)
from PHONGBAN pb inner join NHANVIEN nv on pb.TruongPhong = nv.MaNV
--- cau 12: so luong dia diem tung phong ban
select TenPB, 'SLDD' = COUNT (ddpb.DiaDiem)
from PHONGBAN pb inner join DIADIEMPB ddpb on pb.MaPB = ddpb.MaPB
group by ddpb.MaPB, pb.TenPB
--- cau 13: co bao nhieu ten DA la 'san pham'
select 'SL' = COUNT (TenDA)
from DEAN
where TenDA like 'San Pham%'
--- cau 14: ten phong,dd phong,ten de an, dd de an
select TenPB,DiaDiem, TenDA, DiaDiemDA
from DEAN da, PHONGBAN pb, DIADIEMPB ddpb
where pb.MaPB=ddpb.MaPB and da.MaPB=pb.MaPB and da.DiaDiemDA=ddpb.DiaDiem
---cau 15: ten cua truong phong co luong thap hon nhan vien
--cach 1:
create view v15r1 as
select MaNV, TenNV, pb.MaPB, Luong
from NHANVIEN nv, PHONGBAN pb
where nv.MaNV=pb.TruongPhong
create view v15r2 as
select nv.MaPB,'LTB'=AVG(Luong)
from NHANVIEN nv
where MaNV not in(
select TruongPhong
from PHONGBAN
)
group by MaPB
select TenNV
from v15r1 r1,v15r2 r2
where r1.MaPB=r2.MaPB and Luong
--cach 2:
select MaNV, TenNV, pb.MaPB, Luong
from NHANVIEN nv, PHONGBAN pb
where nv.MaNV=pb.TruongPhong and
Luong <(
select AVG(LUONG)
from NHANVIEN
where MaNV not in(
select TruongPhong
from PHONGBAN
) and MaPB=pb.MaPB
)
---cau 16:
---cau 17:ma nhan vien NV5 co luong lon nhant hay khong, co hoac k
select 'Nhan vien 005 co luong lon nhat k?'
=case when exists(
select Luong
from NHANVIEN nv
where nv.MaNV='005'
and Luong = (
select MAX(nv.Luong)
from NHANVIEN nv
)
)then 'Yes' else 'No' end
---cau 18: ten nhan vien da co gia dinh nhung chua co con
select nv.MaNV,nv.TenNV
from NHANVIEN nv, THANNHAN tn
where nv.MaNV=tn.MaNV and nv.MaNV not in (
select MaNV
from THANNHAN
where QuanHe like 'con%'
)
---cau 19: cho biet ten nhan vien, ten de an, so gio lam viec doi voi tung de an
--select TenNV,TenDA,ThoiGian
--from NHANVIEN nv,DEAN da,PHANCONG pc
--where
---cau 23:ten phong ban, tong so nam, tong so nu
--cach 1:
select pb.MaPB,'SoNam'=COUNT(MaNV)
from NHANVIEN nv left join PHONGBAN pb on nv.MaPB=pb.MaPB
where Phai='NAM'
group by pb.MaPB
select MaPB, 'SoNu'=COUNT(MaNV)
from NHANVIEN
where Phai='NU'
group by MaPB
--cach 2:
---cau 32:
select MaPB
from DEAN
where MaPB NOT IN (
select MaPB
from DEAN
where DiaDiemDA <> 'HA NOI'
)
--28
select NV.MaNV, PC.MaDA, DiaDiemDA
from NHANVIEN NV, PHANCONG PC, DEAN DA
where NV.MaNV=PC.MaNV AND
PC.MaDA=DA.MaDA AND
DiaDiemDA NOT IN (
SELECT DiaDiem
FROM DIADIEMPB DDPB
where DDPB.MaPB=NV.MaPB
)
--34
select TENPB, 'TENTP'=TP.TENNV, 'SLNU'=COUNT(NV.MANV)
from NHANVIEN NV, PHONGBAN PB, NHANVIEN TP
where NV.PHAI='NU' AND
NV.MAPB=PB.MAPB AND
PB.TRUONGPHONG=TP.MANV
group by PB.MaPB, TenPB, TP.TenNV
having COUNT(NV.MaNV) >= ALL(
select COUNT(MaNV)
from NHANVIEN
where Phai='NU'
group by MaPB
)
--36
select TENDA, TENNV, THOIGIAN
from NHANVIEN NV, PHANCONG PC, DEAN DA
where NV.MANV=PC.MANV AND
PC.MADA=DA.MADA AND
THOIGIAN = (
select MAX(THOIGIAN)
FROM PHANCONG
where MADA=PC.MADA
)
select * FROM DEAN
select MaNV, 'SLDA'=COUNT(MaDA)
from PHANCONG
GROUP BY MaNV
HAVING COUNT(MaDA) = (
select COUNT(MaDA)
from DEAN
)
select *
from NHANVIEN A
where NOT EXISTS (
select *
from DEAN C
where MAPB='P1' AND
MADA NOT IN (
select MADA
from PHANCONG B
where B.MANV=A.MANV
)
)
--[url=http://cntt.1forum.biz/] [b] ALL
--CHO BIET CAC NHAN VIEN LAM VIEC ALL DE AN CUA PHONG P1
