TRƯỜNG ĐẠI HỌC TÀI NGUYÊN VÀ MÔI TRƯỜNG HÀ NỘI
KHOA CÔNG NGHỆ THÔNG TIN

BÁO CÁO BÀI TẬP LỚN
ĐỀ SỐ 6
XÂY DỰNG CƠ SỞ DỮ LIỆU CHO BÀI TOÁN QUẢN LÝ
NHÂN SỰ

Sinh viên thực hiện : Nguyễn Minh Chiến
Mã sinh viên

: 1711060246

Lớp

: ĐH7C5

Hà Nội, tháng 12/2021

1


BÀI TẬP LỚN
Học Phần: Ngơn ngữ sql
Họ và tên:
Nguyễn Minh
Chiến

•

Mã

Phịng thi:
SV:1711060246 B.397

Lớp quản lý:
ĐH7C5

Cơ sở dữ liệu cho bài toán quản lý nhân sự gồm 4 bảng:
- PHONG gồm 4 cột MAPHONG, TENPHONG (khố chính), DIACHI,
-

DIEN THOAI.
DANHMUCNGOAINGU gồm 2 cột MANN (khố chính),
TENNN.
NHANSU gồm 7 cột MANS (khố chính), HOTEN, GIOI TINH,

-

NGAYSINH, LUONG, MAPHONG, SDT.
TRINHDONN gồm 3 cột MANS (khoá chính), MANN (khố chính),
TRINHDO.
BÀI LÀM

Create Database Qly_NhanSu
go
use Qly_NhanSu
go
---Tạo các bảng---Create Table PHONG(
MAPHONG Char(3) NOT NULL,
TENPHONG Nvarchar(50) NOT NULL,
DIACHI Nvarchar(200) NOT NULL,

DIENTHOAI Char(10),
Constraint pk_maphong primary key(MAPHONG)
);
go
Create Table DANHMUCNGOAINGU(
MANN Char(2) NOT NULL,
TENNN Nvarchar(50) NOT NULL,
Constraint pk_mann primary key(MANN)
);
2


go
Create Table NHANSU(
MANS Char(5) NOT NULL,
HOTEN Nvarchar(50) NOT NULL,
GIOITINH NChar(5),
NGAYSINH Date,
LUONG int,
MAPHONG Char(3) NOT NULL,
SDT Char(10),
Constraint pk_mans primary key(MANS)
);
go
Create Table TRINHDONN(
MANS Char(5) NOT NULL,
MANN Char(2) NOT NULL,
TRINHDO Char(1),
Constraint pk_trinhdo primary key(MANS, MANN)
);

go

---INSERT dữ liệu vào các bảng
INSERT INTO PHONG VALUES('HCA', N'Hành chính tổng hợp', N'172, Phú
Diễn, Bắc Từ Liêm, Hà Nội', '0333666881')
INSERT INTO PHONG VALUES('KDA', N'Kinh doanh', N'172, Phú Diễn, Bắc Từ
Liêm, Hà Nội', '0333666882')
INSERT INTO PHONG VALUES('KTA', N'Kỹ thuật', N'172, Phú Diễn, Bắc Từ
Liêm, Hà Nội', '0333666883')
INSERT INTO PHONG VALUES('QTA', N'Quản trị', N'172, Phú Diễn, Bắc Từ
Liêm, Hà Nội', '0333666884')
go

INSERT INTO DANHMUCNGOAINGU VALUES('01', 'Anh')
INSERT INTO DANHMUCNGOAINGU VALUES('02', 'Nga')
INSERT INTO DANHMUCNGOAINGU VALUES('03', 'Pháp')
INSERT INTO DANHMUCNGOAINGU VALUES('04', 'Nhật')
INSERT INTO DANHMUCNGOAINGU VALUES('05', 'Trung Quốc')
3


INSERT INTO DANHMUCNGOAINGU VALUES('06', 'Hàn Quốc')
go
INSERT INTO NHANSU VALUES('HC001', N'Nguyễn Minh Chiến', N'Nam', '0131-1999', 1500, 'HCA', '0984748654')
INSERT INTO NHANSU VALUES('HC002', N'Trần Văn Nam', N'Nam', '03-241999', 1700, 'HCA', '0124587485')
INSERT INTO NHANSU VALUES('HC003', N'Nguyễn Thanh Huyền', N'Nữ', '0105-1999', 1500, 'HCA', '0984741111')
INSERT INTO NHANSU VALUES('KD001', N'Lê Tuyết Anh', N'Nữ', '12-12-1999',
2500, 'KDA', '0333374854')
INSERT INTO NHANSU VALUES('KD005', N'Nguyễn Anh Tú', N'Nam', '04-251999', 1800, 'KDA', '0980034554')
INSERT INTO NHANSU VALUES('KD003', N'Phạm An Thái', N'Nam', '07-111999', 2200, 'KDA', '03544148748')

go
INSERT INTO TRINHDONN VALUES('HC001', '01', 'A')
INSERT INTO TRINHDONN VALUES('HC001', '02', 'B')
INSERT INTO TRINHDONN VALUES('HC002', '01', 'C')
INSERT INTO TRINHDONN VALUES('HC002', '03', 'C')
INSERT INTO TRINHDONN VALUES('HC003', '01', 'D')
INSERT INTO TRINHDONN VALUES('KD001', '01', 'C')
INSERT INTO TRINHDONN VALUES('KD001', '02', 'B')
INSERT INTO TRINHDONN VALUES('KD002', '01', 'D')
INSERT INTO TRINHDONN VALUES('KD002', '02', 'A')
INSERT INTO TRINHDONN VALUES('KD003', '01', 'B')
INSERT INTO TRINHDONN VALUES('KD003', '02', 'C')
INSERT INTO TRINHDONN VALUES('KD004', '01', 'C')
INSERT INTO TRINHDONN VALUES('KD004', '04', 'A')
Go

4


Mơ hình quan hệ

5


--1. Đưa ra thơng tin của nhân sự có mã số HC001-Select * from NHANSU
where MANS = 'HC001'
go

--2. Đưa ra danh sách các nhân sự Nữ-select * from NHANSU
where GIOITINH = N'Nữ'

go

--3. Tìm nhân viên có họ Nguyễn-select * from NHANSU
where HOTEN like N'Nguyễn%'
go

6


--4. Đưa ra thơng tin nhân sự có tuổi dưới 30? (Đưa ra thông tin tuổi dưới kết
quả)
select *, YEAR(GETDATE()) - YEAR(NGAYSINH) AS N'TUỔI' from NHANSU
where YEAR(GETDATE()) - YEAR(NGAYSINH) < 30
go

--5. Đếm số lượng nhân sự có trình độ A
Select COUNT(*) AS N'Số lượng nhân sự' from NHANSU, TRINHDONN
where NHANSU.MANS = TRINHDONN.MANS
AND TRINHDONN.TRINHDO = 'A'
go

--6. Đưa ra danh sách nhân sự theo chiều tăng dần của lương
select * from NHANSU ORDER BY LUONG ASC
go

7


--7. Tính lương trung bình của Phịng HCA
select AVG(LUONG) AS 'Lương trung bình' From NHANSU

where MAPHONG = 'HCA'
go

--8. Cho biết các phịng có tổng lương lớn hơn 4000
select SUM(NHANSU.LUONG) AS 'Tổng lương', PHONG.TENPHONG from
NHANSU, PHONG
where NHANSU.MAPHONG = PHONG.MAPHONG
Group by PHONG.TENPHONG
having SUM(NHANSU.LUONG) > 4000
go

--9. Lấy tất cả dữ liệu ở cột TRINHDO trong bảng TRINHDONN không được
trùng nhau
select DISTINCT TRINHDO AS N'Trình độ' from TRINHDONN
go

8


--10. Tạo view có tên v_VIEW bao gồm các thơng tin:
Mã nhân sự, họ tên, giới tính, ngày sinh, sđt, tên phòng, địa chỉ
CREATE VIEW v_VIEW as
select a.MANS, a.HOTEN, a.GIOITINH, a.NGAYSINH, a.SDT,
b.TENPHONG, b.DIACHI
from NHANSU as a, PHONG as b
where a.MAPHONG=b.MAPHONG
go

--11. Viết procedure Insert dữ liệu cho bảng PHONG đã thiết kế ---Create Procedure sp_PHONG(
@MAPHONG char(3),

@TENPHONG nvarchar(50),
@DIACHI nvarchar(200),
@DIENTHOAI char(10))
AS
Insert Into PHONG(MAPHONG, TENPHONG, DIACHI, DIENTHOAI)
values(@MAPHONG, @TENPHONG, @DIACHI, @DIENTHOAI)
go
sp_PHONG @MAPHONG = 'ITA',
@TENPHONG = N'Giám sát',
@DIACHI = N'172, Phú Diễn, Bắc Từ Liêm, Hà Nội',
@DIENTHOAI = '0333666885'
go
select * from Phong where MAPHONG = 'ITA'

9


---12. Viết procedure hiển thị thông tin nhân sự khi biết tên phòng
Create Procedure sp_TimKiem(@name as nvarchar(200))
AS
BEGIN
Select n.MANS, n.HOTEN, n.GIOITINH, n.NGAYSINH,
p.TENPHONG, p.DIACHI
from NHANSU as n, PHONG as p
where n.MAPHONG = p.MAPHONG
AND p.TENPHONG LIKE '%' + @name + '%'
END
go
Exec sp_TimKiem @name=N'Kinh doanh'
go


--13. Viết procedure sửa thông tin Tên, lương, sđt của nhân sự khi biết mã nhân
sự
Create Procedure sp_Update( @name as nvarchar(200),
@Hoten as nvarchar(50),
@Luong as int,
@Sdt as char(10)
)
AS
BEGIN
Update NHANSU SET HOTEN=@Hoten, LUONG=@Luong, SDT=@Sdt
Where MANS=@name
END
go
Exec sp_Update @name='HC002',
@Hoten=N'Test',
@Luong=1200,
@Sdt='0987635241'
Select *From NHANSU Where HOTEN='Test'

10


--14. Tạo Trigger khi insert dữ liệu vào bảng TRINHDONN có TRINHDO = A Thì
Lương của Bảng NHANSU sẽ tăng thêm 50
Create Trigger tg_Insert
ON TRINHDONN FOR INSERT
AS
BEGIN
DECLARE @Trinhdo nvarchar(50);

Select @Trinhdo=TRINHDONN.TRINHDO From NHANSU, TRINHDONN
Where NHANSU.MANS = TRINHDONN.MANS
IF @Trinhdo = 'A'
BEGIN
UPDATE NHANSU SET LUONG = LUONG + 50 From NHANSU,
TRINHDONN Where NHANSU.MANS = TRINHDONN.MANS
END
select* from NHANSU
END
go
INSERT INTO TRINHDONN VALUES('KD002', '03', 'B')
go

--15. Truy vấn lồng Lấy ra thơng tin nhân sự khi trình độ ngoại ngữ là A
Select * From NHANSU
where MANS IN(
Select MANS from TRINHDONN Where TRINHDO = 'A')
Order By HOTEN
11


--16. Truy vấn lồng Lấy ra thông tin nhân sự khi trình độ ngoại ngữ khơng phải là
A
Select * From NHANSU
where MANS NOT IN(
Select MANS from TRINHDONN Where TRINHDO = 'A')
Order By HOTEN
go

--17. Truy vấn lồng Lấy ra thông tin nhân sự khi trình độ ngơn ngữ là tiếng Nga

Select * From NHANSU
where MANS IN
(
Select MANS from TRINHDONN Where MANN
12


IN
(
Select MANN from DANHMUCNGOAINGU where TENNN = N'Nga'
)
)
go

--18. Thêm cột TONGNS vào bảng PHONG và Cập nhập chính xác tổng số nhân
sự
---Thêm cột TONGNS vào bảng PHONG
Alter Table PHONG ADD TONGNS int
---Cập nhập chính xác tổng số nhân sự
UPDATE PHONG
SET TONGNS = (Select COUNT(*) From NHANSU Where NHANSU.MAPHONG
= PHONG.MAPHONG)
From PHONG
Select * From PHONG
go

---19 Viết trigger để khi thêm Nhân sự vào bảng Thì sẽ tăng cột TONGNS lên 1
Create Trigger tg_ThemNS
ON NHANSU
AFTER INSERT

13


AS
SET NOCOUNT ON
UPDATE PHONG
SET TONGNS = TONGNS + s.dem
From PHONG p JOIN (Select MAPHONG, COUNT(*) AS dem from
inserted Group By MAPHONG) s
ON p.MAPHONG = s.MAPHONG
go
INSERT INTO NHANSU VALUES('HC008', N'Phạm ', N'Nam', '07-11-1998', 2200,
'ITA', '0354414874')
Select*from PHONG
go

--20. Viết trigger để khi Xoá nhân ở bảng thì sẽ giảm cột TONGNS lên 1
Create Trigger tg_XoaNS
ON NHANSU
AFTER DELETE
AS
14


SET NOCOUNT ON
UPDATE PHONG
SET TONGNS = TONGNS - s.dem
From PHONG p JOIN (Select MAPHONG, COUNT(*) AS dem from deleted
Group By MAPHONG) s
ON p.MAPHONG = s.MAPHONG

go
DELETE NHANSU Where MANS='HC008'
Select*from PHONG
go

15