-- 1.1. Nhập vào MaNV cho biết tuổi của nhân viên này
CREATE OR ALTER FUNCTION Tuoi_NV (@maNV NVARCHAR(9))
RETURNS INT
AS
BEGIN
RETURN (SELECT YEAR(GETDATE()) - YEAR(NgSinh)
FROM NhanVien
WHERE MaNV = @maNV);
END
GO
-- cách gọi hàm kiểm tra tuổi
SELECT dbo.Tuoi_NV('005') AS Tuoi;
GO
-- hoặc
PRINT 'Tuoi cua nhan vien: ' + CAST(DBO.Tuoi_NV('005') AS NVARCHAR(20))
GO
-- 1.2. Nhập vào Manv cho biết số lượng đề án nhân viên này đã tham gia
CREATE OR ALTER FUNCTION SoLuong_DeAnDaThamGia (@maNV NVARCHAR(9))
RETURNS INT
AS
BEGIN
RETURN (SELECT COUNT(*)
FROM PhanCong
WHERE Ma_NVien = @maNV);
END
GO
-- gọi hàm
SELECT dbo.SoLuong_DeAnDaThamGia('003') AS SoLuong_DeAnDaThamGia;
GO
-- 1.3. Truyền tham số vào phái nam hoặc nữ, xuất số lượng nhân viên theo phái
CREATE OR ALTER FUNCTION SoLuong_NhanVien_TheoPhai (@phai NVARCHAR(4))
RETURNS INT
AS
BEGIN
RETURN (SELECT COUNT(*)
FROM NhanVien
WHERE Phai = @phai);
END
GO
-- Gọi hàm
SELECT dbo.SoLuong_NhanVien_TheoPhai(N'Nữ') AS SoLuong_NhanVien;
GO
SELECT dbo.SoLuong_NhanVien_TheoPhai(N'Nam') AS SoLuong_NhanVien;
GO
-- 1.4. Truyền tham số đầu vào là tên phòng, tính mức lương trung bình của phòng đó, Cho biết họ tên nhân viên (HONV, TENLOT, TENNV) có mức lương trên mức lương trung bình của phòng đó
CREATE OR ALTER FUNCTION LuongNV_TrenLuongTB (@tenPhG NVARCHAR(15))
RETURNS @nV TABLE (HoTen NVARCHAR(45))
AS
BEGIN
DECLARE @luongTB FLOAT = (SELECT AVG(Luong)
FROM NhanVien
WHERE PhG = (SELECT MaPhG FROM PhongBan WHERE TenPhG = @tenPhG));
INSERT INTO @nV
SELECT CONCAT(HoNV, ' ', TenLot, ' ', TenNV) -- hoặc dùng concat để nối chuỗi
FROM NhanVien
WHERE Luong > @luongTB;
RETURN;
END
GO
-- Gọi hàm
SELECT *
FROM dbo.LuongNV_TrenLuongTB(N'CNTT');
GO
-- hoặc
CREATE OR ALTER FUNCTION fn_Cau_14 (@tenPhG NVARCHAR(15))
RETURNS TABLE
AS
RETURN
(
SELECT honv, tenlot, tennv
FROM nhanvien
WHERE luong > (
SELECT AVG(luong)
FROM nhanvien
WHERE phg = (SELECT maphg FROM PhongBan WHERE tenPhG = @tenPhG)
)
)
GO
--Gọi Hàm
SELECT *
FROM fn_Cau_14('CNTT')
GO
-- 1.5. Tryền tham số đầu vào là Mã Phòng, cho biết tên phòng ban, họ tên người trưởng phòng và số lượng đề án mà phòng ban đó chủ trì
CREATE OR ALTER FUNCTION Tim_PhongBan (@maPhg INT)
RETURNS TABLE
AS
RETURN (SELECT TenPhG, CONCAT(HoNV, ' ', TenLot, ' ', TenNV) AS HoTenNV, COUNT(MaDA) AS SoLuong_DeAn
FROM PhongBan
JOIN NhanVien ON PhongBan.TrPhG = NhanVien.MaNV
JOIN DeAn ON PhongBan.MaPhG = DeAn.Phong
WHERE MaPhG = @maPhg
GROUP BY TenPhG, HoNV, TenLot, TenNV);
GO
-- Gọi hàm
SELECT *
FROM dbo.Tim_PhongBan(5);
GO
-- 2.1. Hiển thị thông tin HoNV, TenNV, TenPHG, DiaDiemPhg
CREATE OR ALTER VIEW View_ThongTinNhanVien
AS
SELECT HoNV, TenNV, TenPhG, DiaDiem
FROM NhanVien
JOIN PhongBan ON NhanVien.PhG = PhongBan.MaPhG
JOIN DiaDiem_PhG ON PhongBan.MaPhG = DiaDiem_PhG.MaPhG;
GO
-- Thực hiện select dữ liệu trong view vừa tạo
SELECT *
FROM View_ThongTinNhanVien;
GO
-- 2.2. Hiển thị thông tin TenNv, Lương, Tuổi
CREATE OR ALTER VIEW View_ThongTinNhanVien_Tuoi
AS
SELECT TenNV, Luong, YEAR(GETDATE()) - YEAR(NgSinh) AS Tuoi
FROM NhanVien
GO
SELECT *
FROM View_ThongTinNhanVien_Tuoi;
GO
-- 2.3. Hiển thị tên phòng ban và họ tên trưởng phòng của phòng ban có đông nhân viên nhất
CREATE OR ALTER VIEW View_PhongBan_MaxNV
AS
SELECT TOP(1) WITH TIES COUNT(NhanVien.PhG) AS SLNV
, TenPhG
, CONCAT(NV.HoNV, ' ', NV.TenLot, ' ', NV.TenNV) AS HoTenTP
FROM NhanVien
JOIN PhongBan ON NhanVien.PhG = PhongBan.MaPhG
JOIN NhanVien NV ON PhongBan.TrPhG = NV.MaNV
GROUP BY NhanVien.PhG, TenPhG, NV.HoNV, NV.TenLot, NV.TenNV
ORDER BY SLNV DESC
GO
SELECT *
FROM View_PhongBan_MaxNV;
GO
Hướng dẫn giải lab 3.
Xong!
Thực hiện lệnh (new query – execute): EXEC sys.sp_cycle_errorlog;
tại màn hình Select Configuration Task chọn option
Set up Database Mail > ấn next
Tại màn hình New Profile nhấn Add để thêm account
Điền thông tin SMTP server bạn > nhấn OK
|
Outgoing Mail Server (SMTP) |
AMTP Authentication
|
|
E-mail address: |
Basic authentication |
|
Reply e-mail: |
User name: |
|
Server name: |
Password: |
|
Port number: |
Confirm password: |
Nhấn next
Tại màn hình Manage Profile Security chọn tab public profile,
trong phần Default Profile chọn Yes ấn > next.
Nhấn Next
nhấn Finish
Nhấn Close để hoàn thành cài đặt
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO 
Log ghi nhận trên hệ thống
Chọn SQL Server Agent > Operations > Admin-SQL >
Properties > History
-- test alert 9002 đầy log
USE master;
GO
CREATE DATABASE TestAlertDB -- tạo database
ON (
NAME = TestAlertDB
, FILENAME = 'd:\Data\TestAlertDB.mdf'
, MAXSIZE = 20MB)
LOG ON (
NAME = TestAlertDB_log
, FILENAME = 'd:\Data\TestAlertDB_log.ldf'
, MAXSIZE = 20MB) ; -- giới hạng dung lượng 20MB
GO
-- tạo mới bảng dữ liệu
USE TestAlertDB;
CREATE TABLE testtable (col1 text);
-- chèn dữ liệu để test
USE TestAlertDB;
GO
SET NOCOUNT ON;
WHILE 1 = 1
BEGIN
INSERT INTO testtable (col1)
VALUES('Test data!');
END;
GOVào SQL
Server Agent > Alerts > Test Full Transaction Log > Properties > History
