THCSDLNC
create database CONGTY
use CONGTY
-- CREATE TABLE tblDonVi --
CREATE TABLE tblDonVi
(MaSoDV int,CONSTRAINT pk_MaSoDV PRIMARY KEY(MaSoDV),
TenDV varchar(30) not null,
MaSoNQL char(5) not null,
NgayBatDau char(10) not null
)
GO
-- CREATE TABLE tblNhanVien --
CREATE TABLE tblNhanVien
(MaSoNV char(5), CONSTRAINT pk_MaSoNV PRIMARY KEY(MaSoNV),
HoDem varchar(20),
Ten varchar(10) not null,
NgaySinh char(10) not null,
DiaChi varchar(30) not null,
GioiTinh char(3) not null,
Luong int not null,
MaSoDV int, CONSTRAINT fk_MaSoDV_tblNhanVien FOREIGN KEY(MaSoDV) REFERENCES tblDonVi(MaSoDV))
GO
-- CREATE TABLE tblDuAn --
CREATE TABLE tblDuAn
(MaSoDA int, CONSTRAINT pk_MaSoDA PRIMARY KEY(MaSoDA),
TenDA varchar(20) not null,
DiaDiem varchar(30) not null,
MaSoDV int, CONSTRAINT fk_MaSoDV_tblDuAn FOREIGN KEY(MaSoDV) REFERENCES tblDonVi(MaSoDV))
GO
-- CREATE TABLE tblNhanVien_DuAn --
CREATE TABLE tblNhanVien_DuAn
(MaSoNV char(5), CONSTRAINT fk_MaSoNV FOREIGN KEY(MaSoNV) REFERENCES tblNhanVien(MaSoNV),
MaSoDA int, CONSTRAINT fk_MaSoDA FOREIGN KEY(MaSoDA) REFERENCES tblDuAn(MaSoDA),
SoGio int)
--INSERT DATA INTO TABLE tblDonVi--
INSERT INTO tblDonVi VALUES
(5,'Cong ty 325','nv001','15/9/2000')
INSERT INTO tblDonVi VALUES
(4,'Nha may Hai Van','nv014','124/6/1997')
INSERT INTO tblDonVi VALUES
(1,'Hoc vien Ngan Hang','nv061','25/2/1992')
INSERT INTO tblDonVi VALUES
(7,'Hoc vien Ngan','nv073','25/2/1992')
--INSERT DATA INTO TABLE tblNhanvien--
INSERT INTO tblNhanVien VALUES
('nv001','Le','Van','02/12/1979','Ha Noi','nam',3000,5)
INSERT INTO tblNhanVien VALUES
('nv002','Tran Duc','Nam','14/02/1996','Tam Ky','nam',4000,5)
INSERT INTO tblNhanVien VALUES
('nv010','Hoang','Thanh','08/05/1979','Da Nang','nu',2500,4)
INSERT INTO tblNhanVien VALUES
('nv014','Pham','Bang','26/06/1952','Da Nang','nam',4300,4)
INSERT INTO tblNhanVien VALUES
('nv016','Nguyen','Son','14/08/1973','Hue','nam',3800,5)
INSERT INTO tblNhanVien VALUES
('nv018','Vu Huong','Giang','26/03/1983','Da Nang','nu',2500,5)
INSERT INTO tblNhanVien VALUES
('nv025','Tran Le','Hoa','15/03/1980','Hue','nu',2500,4)
INSERT INTO tblNhanVien VALUES
('nv061','Hoang','Giap','05/12/1957','Hue','nam',5000,1)
INSERT INTO tblNhanVien VALUES
('nv073','Hoang','Ha','05/12/1957','Hue','nam',5000,1)
--INSERT DATA INTO TABLE tblDuAn--
INSERT INTO tblDuAn VALUES
(1,'Cau song Han','Da Nang',5)
INSERT INTO tblDuAn VALUES
(2,'Nha xe Bach Khoa','Hoa Khanh',5)
INSERT INTO tblDuAn VALUES
(3,'San van dong','Hoa Khanh',5)
INSERT INTO tblDuAn VALUES
(10,'Vuon hoa','DH Kinh Te',4)
INSERT INTO tblDuAn VALUES
(20,'San bong','DH Hue',1)
INSERT INTO tblDuAn VALUES
(30,'Thu vien','Quang Nam',4)
--INSERT DATA INTO TABLE tblNhanVien_DuAn--
INSERT INTO tblNhanVien_DuAn VALUES
('nv001',1,32)
INSERT INTO tblNhanVien_DuAn VALUES
('nv010',1,7)
INSERT INTO tblNhanVien_DuAn VALUES
('nv016',3,40)
INSERT INTO tblNhanVien_DuAn VALUES
('nv018',1,20)
INSERT INTO tblNhanVien_DuAn VALUES
('nv018',2,20)
INSERT INTO tblNhanVien_DuAn VALUES
('nv002',1,10)
INSERT INTO tblNhanVien_DuAn VALUES
('nv002',3,10)
INSERT INTO tblNhanVien_DuAn VALUES
('nv002',10,10)
INSERT INTO tblNhanVien_DuAn VALUES
('nv002',20,10)
INSERT INTO tblNhanVien_DuAn VALUES
('nv010',30,30)
INSERT INTO tblNhanVien_DuAn VALUES
('nv010',10,10)
INSERT INTO tblNhanVien_DuAn VALUES
('nv025',10,35)
INSERT INTO tblNhanVien_DuAn VALUES
('nv025',30,5)
INSERT INTO tblNhanVien_DuAn VALUES
('nv014',30,20)
INSERT INTO tblNhanVien_DuAn VALUES
('nv014',20,15)
INSERT INTO tblNhanVien_DuAn VALUES
('nv061',20,NULL)
-- CAC CAU LENH TRUY VAN --
--I.Truy van dung phep toan quan he
--1.Liet ke danh sach nhan vien gom Ho dem, ten va gioi tinh --
SELECT HoDem,Ten,GioiTinh FROM tblNhanVien
--2.Liet ke danh sach nhan vien thuoc don vi co ten "Cong ty 325" --
SELECT HoDem,Ten FROM tblNhanVien,tblDonVi
WHERE tblNhanVien.MaSoDV = tblDonVi.MaSoDV AND tblDonVi.TenDV LIKE 'Cong ty 325'
--3.Liet ke danh sach ten du an do don vi co ten la "Hoc vien Ngan Hang" thuc hien --
SELECT TenDA FROM tblDuAn,tblDonVi
WHERE tblDuAn.MaSoDV = tblDonVi.MaSoDV AND tblDonVi.TenDV LIKE 'Hoc vien Ngan Hang'
--4.Liet ke danh sach ten du an do ca 2 nhan vien nv001 va nv002 thuc hien --
SELECT TenDA FROM tblDuAn,(SELECT * FROM tblNhanVien_DuAn Hav WHERE MaSoNV = 'nv001') as T,(SELECT * FROM tblNhanVien_DuAn Hav WHERE MaSoNV = 'nv002') as K
WHERE tblDuAn.MaSoDA = T.MaSoDA AND tblDuAn.MaSoDA = K.MaSoDA
--5.Liet ke danh sach ten du an co dia chi nhan vien thuc hien du an trung voi dia diem cua du an
SELECT DISTINCT TenDA FROM tblDuAn,tblNhanVien,tblNhanVien_DuAn
WHERE (tblDuAn.DiaDiem LIKE tblNhanVien.DiaChi)
AND (tblNhanVien.MaSoNV = tblNhanVien_DuAn.MaSoNV)
AND (tblNhanVien_DuAn.MaSoDA = tblDuAn.MaSoDA)
--6.Liet ke danh sach ten du an do nam gioi lam ma ko co nu gioi tham gia--
(select distinct tblDuAn.TenDA from tblNhanVien,tblNhanVien_DuAn,tblDuAn
where
tblNhanVien.GioiTinh ='nam'
and tblNhanVien.MaSoNV = tblNhanVien_DuAn.MaSoNV
)
except
(select distinct tblDuAn.TenDA from tblNhanVien_DuAn,tblNhanVien,tblDuAn
where
tblDuAn.MaSoDA = tblNhanVien_DuAn.MaSoDA
and tblNhanVien_DuAn.MaSoNV = tblNhanVien.MaSoNV
and tblNhanVien.GioiTinh like 'nu')
--7.Liet ke danh sach ten Don vi co nhung nhan vien thuc hien cac du an co dia chi cung o Da Nang--
select distinct tblDonVi.TenDV from tblDonVi,tblNhanVien,tblDuAn,tblNhanVien_DuAn
where
tblNhanVien.DiaChi like 'Da Nang'
and tblNhanVien.MaSoNV = tblNhanVien_DuAn.MaSoNV
and tblDuAn.MaSoDA = tblNhanVien_DuAn.MaSoDA
and tblDuAn.DiaDiem like 'Da Nang'
and tblNhanVien.MaSoDV = tblDonVi.MaSoDV
--8.Liet ke danh sach ma nhan vien co luong tren 2500 thuoc don vi Nha may Hai Van da tham gia du an co so gio tren 10 gio--
select distinct tblNhanVien.MaSoNV from tblNhanVien,tblNhanVien_DuAn,tblDonVi
where
tblNhanVien.MaSoNV = tblNhanVien_DuAn.MaSoNV
and tblNhanVien.Luong > 2500
and tblDonVi.TenDV like 'Nha may Hai Van'
and tblDonVi.MaSoDV = tblNhanVien.MaSoDV
and tblNhanVien_DuAn.SoGio > 10
--9.Liet ke ma nhan vien khong tham gia du an nao--
select MaSoNV from tblNhanVien
except
select distinct MaSoNV from tblNhanVien_DuAn
--10.Liet ke danh sach nhan vien gom Ho, ten dem, ngay sinh vua la quan li va co tham gia du an--
select distinct HoDem, Ten, NgaySinh from tblNhanVien,tblNhanVien_DuAn,tblDonVi
where
tblNhanVien.MaSoNV = tblNhanVien_DuAn.MaSoNV
and tblNhanVien.MaSoNV = tblDonVi.MaSoNQL
--II.Truy van dung cau lenh long nhau
--A/Truy van long cau I.2-10
--2.Liet ke danh sach nhan vien thuoc don vi co ten "Cong ty 325" --
SELECT HoDem,Ten FROM tblNhanVien
WHERE tblNhanVien.MaSoDV = (SELECT MaSoDV FROM tblDonVi WHERE TenDV LIKE 'Cong ty 325')
--3.Liet ke danh sach ten du an do don vi co ten la "Hoc vien Ngan Hang" thuc hien --
SELECT TenDA FROM tblDuAn
WHERE tblDuAn.MaSoDV IN (SELECT MaSoDV FROM tblDonVi WHERE TenDV LIKE 'Hoc vien Ngan Hang')
--4.Liet ke danh sach ten du an do ca 2 nhan vien nv001 va nv002 thuc hien --
SELECT TenDA FROM tblDuAn
WHERE tblDuAn.MaSoDA
IN (SELECT MaSoDA FROM tblNhanVien_DuAn WHERE MaSoNV = 'nv001'
AND MaSoDA in (SELECT MaSoDA FROM tblNhanVien_DuAn WHERE MaSoNV = 'nv002'))
--5.Liet ke danh sach ten du an co dia chi nhan vien thuc hien du an trung voi dia diem cua du an
select TenDA from tblDuAn where DiaDiem in (select distinct DiaChi from tblNhanVien where MaSoNV in(select MaSoNV from tblNhanVien_DuAn))
and MaSoDA in (select MaSoDA from tblNhanVien_DuAn)
--6.Liet ke danh sach ten du an do nam gioi lam ma ko co nu gioi tham gia--
select distinct tblDuAn.TenDA from tblDuAn
where
tblDuAn.MaSoDA IN
(select distinct tblNhanVien_DuAn.MaSoDA from tblNhanVien,tblNhanVien_DuAn
where tblNhanVien.GioiTinh ='nam' and tblNhanVien.MaSoNV = tblNhanVien_DuAn.MaSoNV
and tblNhanVien_DuAn.MaSoDA != ALL(
select distinct tblDuAn.MaSoDA from tblNhanVien_DuAn,tblNhanVien,tblDuAn
where
tblDuAn.MaSoDA = tblNhanVien_DuAn.MaSoDA
and tblNhanVien_DuAn.MaSoNV = tblNhanVien.MaSoNV
and tblNhanVien.GioiTinh like 'nu'))
--7.Liet ke danh sach ten Don vi co nhung nhan vien thuc hien cac du an co dia chi cung o Da Nang--
select TenDV from tblDonVi
where
TenDV IN (select TenDV from tblDonVi where MaSoDV
IN (select MaSoDV from tblNhanVien where DiaChi like 'Da Nang' and MaSoNV
IN (select MaSoNV from tblNhanVien_DuAn where MaSoDA
IN (select MaSoDA from tblDuAn where DiaDiem like 'Da Nang'))))
--8.Liet ke danh sach ma nhan vien co luong tren 2500 thuoc don vi Nha may Hai Van da tham gia du an co so gio tren 10 gio--
select MaSoNV from tblNhanVien
where
MaSoNV in (select MaSoNV from tblNhanVien where Luong > 2500 and MaSoDV
= (select MaSoDV from tblDonVi where TenDV like 'Nha may Hai Van')
and MaSoNV in (select MaSoNV from tblNhanVien_DuAn where SoGio > 10 ))
--9.Liet ke ma nhan vien khong tham gia du an nao
select MaSoNV from tblNhanVien
where
MaSoNV not in (select distinct MaSoNV from tblNhanVien_DuAn)
--10.Liet ke danh sach nhan vien gom Ho, ten dem, ngay sinh vua la quan li va co tham gia du an
select HoDem, Ten, NgaySinh from tblNhanVien
where MaSoNV in (select MaSoNV from tblNhanVien_DuAn where MaSoNV in (select MaSoNQL from tblDonVi))
--B/Liet ke ma nhan vien co tuoi nho nhat
select TOP 1 MaSoNV from tblNhanVien
order by datediff(d,convert(datetime,tblNhanVien.NgaySinh,103),convert(datetime,getdate(),103)) ASC
--C/Liet ke ma du an co so gio lon nhat
select MaSoDA from tblDuAn
where MaSoDA in (select MaSoDA from tblNhanVien_DuAn
where SoGio = (select max(SoGio) from tblNhanVien_DuAn))
--III.Truy van thong ke
--1.Liet ke ma nhan vien tham gia nhieu du an nhat
select TOP 1 MaSoNV,count(MaSoNV) as "MAX" from tblNhanVien_DuAn
group by MaSoNV
order by count(MaSoNV) DESC
--2.Cho biet ma don vi quan li nhieu du an nhat
select TOP 1 tblDonVi.MaSoDV,count(tblDonVi.MaSoDV) as TongDA from tblDonVi,(select MaSoNV,count(MaSoNV) as soDA from tblNhanVien_DuAn group by MaSoNV) as T,tblNhanVien
where tblDonVi.MaSoDV = tblNhanVien.MaSoDV and tblNhanVien.MaSoNV = T.MaSoNV
group by tblDonVi.MaSoDV
order by count(tblDonVi.MaSoDV) DESC
--3.Liet ke ma nhan vien va tong so gio ma nhan vien nay tham gia du an
select MaSoNV,sum(SoGio) as TongSoGio from tblNhanVien_DuAn
group by MaSoNV
Bạn đang đọc truyện trên: AzTruyen.Top