/*auto readmore*/ /*auto readmore*/ /* an hien script*/ // an hien password /*an hien ma chuong trinh cong tru */ /*Scrollbox thanh cuon*/ /***Nhung CODE***/ /* dòng xanh dòng trắng */ /* https://cdnjs.com/libraries/prism lay thu vien, can vao ten file ma goi 1. copy link vao vi du:prism-python.min.js 2. ten ngon nua la python */ /*=== New posts ===*/ /*header slider*/ /*=== bai viet lien quan===*/ /*===tabcode===*/
Showing posts with label COM2034. Show all posts
Showing posts with label COM2034. Show all posts

[Database-Tự Học SQL] COM2034_SQL Server - Lab 7: SQL Function & View

NỘI DUNG:
Sử dụng hàm do người dùng định nghĩa và tạo khung nhìn view.
➢ Khai báo, tạo function
➢ Thực thi các function
➢ Tạo và sử dụng khung nhìn view

YÊU CẦU:

Bài 1:
Viết các hàm:
Nhập vào MaNV cho biết tuổi của nhân viên này.
Nhập vào Manv cho biết số lượng đề án nhân viên này đã tham gia
Truyền tham số vào phái nam hoặc nữ, xuất số lượng nhân viên theo phái
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 đó.
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ì.


Bài 2: 
Tạo các view:
Hiển thị thông tin HoNV,TenNV,TenPHG, DiaDiemPhg.
Hiển thị thông tin TenNv, Lương, Tuổi.
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


THỰC HIỆN:

Bài 1:
Viết các hàm:
➢ Nhập vào MaNV cho biết tuổi của nhân viên này.
-- 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

➢ Nhập vào Manv cho biết số lượng đề án nhân viên này đã tham gia
-- 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

➢ Truyền tham số vào phái nam hoặc nữ, xuất số lượng nhân viên theo phái
-- 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

➢ 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 đó.
-- 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
-- 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

➢ 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ì.

-- 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


Bài 2: 
Tạo các view:
➢ Hiển thị thông tin HoNV,TenNV,TenPHG, DiaDiemPhg.
-- 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

➢ Hiển thị thông tin TenNv, Lương, Tuổi.
-- 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


➢ 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
-- 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

Xong!

[Database-Tự Học Quản Trị SQL Server] SOA101 - LAB5 - QUẢN LÝ THÔNG TIN ĐĂNG NHẬP, NGƯỜI DÙNG VÀ PHÂN QUYỀN



MỤC TIÊU:
Kết thúc bài thực hành này bạn có thể biết cách:
  • Chuyển đổi authentication mode
  • Tạo user login
  • Quản lý và cấp quyền server level role
  • Tạo và quản lý database user
  • Cấp quyền và xem lại các quyền đã cấp


YÊU CẦU:
BÀI 1
Tạo mới login và user với các yêu cầu:
Tên người dùng là AW_Admin với vai trò là nhà quản trị CSDL, phân quyền cho user này toàn quyền thao tác trên CSDL AdventureWorks2019
Tạo người dùng khác tên là AW_backup, phân quyền cho người dùng này chỉ được phép thao tác backup CSDL AdventureWorks2019

BÀI 2
Tạo mới login và user với các yêu cầu:
Một người dùng thông thường tên AW_UserThuong cho phép user này thấy bảng dữ liệu “Customer” và chỉ được phép thực hiện các câu lệnh select, insert, delete trên bảng này.
Thu hồi quyền delete của user “AW_UserThuong” trên bảng “Customer”


THỰC HIỆN:
Video bài viết


BÀI 1
Tạo mới login và user với các yêu cầu:
Tên người dùng là AW_Admin với vai trò là nhà quản trị CSDL, phân quyền cho user này toàn quyền thao tác trên CSDL AdventureWorks2019
Tạo người dùng khác tên là AW_backup, phân quyền cho người dùng này chỉ được phép thao tác backup CSDL AdventureWorks2019

Quản lý server-level security
Tại SSMS, ở cửa sổ Object explorer, phải chuột tên server -> chọn properties -> chọn Security -> SQL Server and Windows Authentication mode -> OK


Tại cửa sổ phải Object explorer, phải chuột tên server SQL -> chọn Restart -> OK để khởi động lại SQL Server


a. Tạo Login
Security -> Logins -> New Login …

Tại cửa sổ Login-new , trong phần login name nhập AW_Admin -> chọn option SQL server authentication -> nhập password 2 lần -> bỏ checkbox Enforce password policy, trong phần Default database chọn AdventureWorks2019-> OK


b.   Ngoài ra chúng ta có thể tạo login AW_backup bằng T-SQL

USE master
GO
CREATE LOGIN AW_backup
	WITH PASSWORD=N'AW_backup', 
	CHECK_EXPIRATION=OFF, 
	CHECK_POLICY=OFF
GO


Xem thông tin Logins đã tạo Security -> Logins


c. Tạo user


d. Tạo user bằng T-SQL
USE AdventureWorks2019
GO
CREATE USER AW_backup 
	FOR LOGIN AW_backup
GO

e. Gán quyền cho user AW_Admin

f. Gán quyền cho user AW_backup bằng T-SQL

USE AdventureWorks2019
GO
	ALTER ROLE db_backupoperator 
	ADD MEMBER AW_backup
GO

BÀI 2
Tạo mới login và user với các yêu cầu:
Một người dùng thông thường tên AW_UserThuong cho phép user này thấy bảng dữ liệu “Customer” và chỉ được phép thực hiện các câu lệnh select, insert, delete trên bảng này.
Thu hồi quyền delete của user “AW_UserThuong” trên bảng “Customer”

a. Tạo mới LogIn và User
USE master;
GO
CREATE LOGIN AW_UserThuong
	WITH PASSWORD = 'AW_UserThuong',
	CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF,
	DEFAULT_DATABASE = AdventureWorks2019;
GO

USE AdventureWorks2019
GO
CREATE USER AW_UserThuong 
	FOR LOGIN AW_UserThuong
GO

b. Phân quyền được phép thực hiện các câu lệnh: Select, Insert, Delete
USE AdventureWorks2019
GO
GRANT SELECT, INSERT, DELETE
ON Customer TO AW_UserThuong;
GO

c.  Kiểm tra quyền đã gán



d. Thu hồi quyền delete của user AW_UserThuong trên bảng dữ liệu Customer
USE AdventureWorks2019
GO
REVOKE DELETE
ON Customer
FROM AW_UserThuong;
GO




Xong!

[Database-Tự Học Quản Trị SQL Server] SOA101 - LAB3 - KẾ HOẠCH - CHIẾN LƯỢC SAO LƯU VÀ PHỤC HỒI CSDL (GUI)

MỤC TIÊU:
Kết thúc bài thực hành này bạn có thể thực hiện sao lưu/backup database và phục hồi/restore database SQL server, lên kế hoạch backup databse định kỳ cho SQL Server nhằm hạn chế tối đa việc mất dữ liệu, duy trì hệ thống làm việc an toàn và ổn định
  • Tạo backup device
  • Backup database, các kiểu backup (backup type): Backup Full, backup Differential, Backup Transaction log
  • Kiểm tra sau khi backup
  • Lập lịch backup database định kỳ (schedule backup)
  • Phục hồi database
YÊU CẦU:
Bài 1:
a. Tạo các backup device backup Full, backup Differential, Backup Transaction log
b. Thực hiện backup Full, backup Differential (nên tạo thêm bảng mới để tạo ra sự thay đổi trong CSDL), Backup Transaction log với các device backup đã tạo ở bài 1.a
c. Kiểm tra kết quả sau khi backup tương ứng với từng kiểu backup

Bài 2:
a. Lập lịch backup định kỳ cho CSDL với các yêu cầu:
  • Backup Full: Tối thứ 7 hằng tuần lúc vào lúc 11 giờ 30 khuya
  • Backup Differential: Các ngày thứ 2, 3, 4, 5, 6 vào lúc 11 giờ 30 khuya
  • Backup Transaction Log: Hằng ngày và lặp lại 1 giờ l lần bắt đầu từ 8:00AM - 5:00PM
b. Phục hồi dữ liệu với các bản sao đã thực hiện ở bài 1.b

THỰC HIỆN:

Bài 1:

a. Tạo các backup device backup Full, backup Differential, Backup Transaction log
Tạo backup device cho Backup Full



Tương tự tạo backup device cho Backup Differential



Tương tự tạo backup device cho Backup Transaction log


Đã tạo thành công 3 backup devices


b. Thực hiện backup Full, backup Differential, Backup Transaction log với các device backup đã tạo ở bài 1.a
Thực hiện backup Full cho CSDL

Chọn DemoDB_01 (CSDL cần thực hiện backup) -> chuột phải -> Tasks -> Back Up …



Chọn Backup type Full (4) > remove đường dẫn hiện tại (5)

Add (6) > chọn Backup devices Full (7) đã tạo ra ở bước trên

Backup thành công -> OK


Thực hiện Backup Differential
Chuẩn bị: Giả lập dữ liệu phát sinh sau khi backup full, chúng ta nên tạo mới bản và thêm dữ liệu vào CSDL
Chọn CSDL DemoDB_01 -> Table ->click chuột phải -> New -> Table…



Tạo các trường dữ liệu MaSoSinhVien, TenSinhVien, Diem và lưu lại (Ví dụ: Table_1)



Thực hiện:
Chọn CSDL DemoDB_01 -> Tasks -> Back Up…

Ở mục Source chọn Backup type -> chọn Differential -> mục Destination chọn Add -> Backup device chọn DemoDB_01_Backup_Differential -> OK -> OK

Backup thành công -> OK



DemoDB_01_Backup_Transaction_Log
Thực hiện tương tự như backup full



nhưng phần backup type (bước 3) chọn Transaction Log và backup device chọn Transaction Log




Kết quả sau thực hiện


c. Kiểm tra kết quả sau khi backup tương ứng với từng kiểu backup
Trong server Objects chọn Backup Devices -> phải chuột vào DemoDB_01_Backup_Differential -> properties -> Media Contents




Để xem report backup, phải chuột vào database muốn xem chọn Reports -> Standard reports -> Backup and Restore Events. 





Bài 2
a. Lập lịch backup định kỳ cho CSDL
Khởi đội dịch vụ SQL Server Agent


Nhấn Yes để khởi động service SQL SERVER AGENT 


Thực hiện lập lịch backup
Tại SQL server chọn Management, Phải chuột vào Maintenance Plans chọn Maintenance Plan Wizard.


Màn hình SQL server maintenance plan Wizard ấn next


Đặt tên và chọn option Separate schedules for each task -> next



Tại màn hình Select Maintenance tasks chọn các loại backup cần thực hiện (Full, Diferential, Transaction Log) -> next.



Lập lịch Backup Full: 
Tại cửa sổ define Back up database (full) -> Next


Chọn tab General, trong phần Database(s) click vào mũi tên để chọn database cần backup -> OK.


tab Destination, trong phần Backup database across one or more files ấn Add -> Chọn backup devive → OK



tab Option, chọn vào checkbox Verify backup integrity -> ấn nút Change


Đặt tên cho job backup ở phần name, trong phần Frequency chọn Occurs là Weekly và chỉ chọn vào Saturday, Occurs once at: 11:30 PM (backup hàng tuần vào ngày thứ bảy vào lúc 11 giờ 30 khuya) -> Ok.

Nhấn next để tạo lịch backup differential.


Lập lịch backup Differential
Tương tự thực hiện lặp lại từ bước 8 đến bước 14
Bước 15 chọn DemoDB_01_Backup_Differential



Tiếp tục lặp lại từ bước 16 – 19
Đặt tên cho job backup ở phần name, trong phần Frequency chọn Occurs là Weekly và chỉ chọn các ngày từ Monday đến Friday, Occurs once at: 12:00 AM (backup hàng tuần vào các ngày T2, 3, 4, 5, 6 vào lúc 12 giờ khuya) -> Ok.



Lập lịch backup Transaction Log
Tương tự thực hiện lặp lại từ bước 8 đến bước 14
Bước 15 chọn DemoDB_01_Backup_Transaction_Log


Tiếp tục lặp lại từ bước 16 - 19
Đặt tên cho job backup ở phần name, trong phần Frequency chọn Occurs là Daily Trong phần Daily Frequency chọn Occurs every chọn 1 hours và Starting at là 8:00AM – Ending at là 5:00PM(backup hàng ngày, lập lại 1 giờ l lần bắt đầu từ 8 giờ sáng đến 5 giờ chiều) -> Ok


Check vào Write a report to a text file, trong phần folder location click nút … Chọn nơi lưu trữ report à OK -> Next.

Nhấn Finish hoàn tất


Chờ cho đến khi các tiến trình hoàn tất → Close

Sau khi thực hiện xong vào Maintenance Plans sẽ thấy các lịch đã tạo


vào SQL Server Agent chọn Jobs sẽ thấy các job backup.


Chúng ta thể phải chuột vào từng job chọn properties để xem lại các thông tin đã cấu hình



b. Phục hồi sữ liệu với các bản sao đã thực hiện ở bài 1.b
Giả lập xóa CSDL
SSMS phải chuột vào Database cần xóa -> delete -> Delete Object ấn OK



Thực hiện phục hồi/Restore Backup Full
Phải chuột vào database -> Restore database


Tại cửa sổ Restore Database, chọn option Device ấn nút …


Tại cửa sổ Select backup device, trong phần Backup media type -> Backup Device, Trong phần Backup media -> nhấn Add -> chọn DemoDB_01_Backup_Full -> OK

Nhấn OK 2 lần để thực hiện phục hồi dữ liệu



Kết quả thành công -> OK


Thực hiện phục hồi /Restore Differential backup
Tương tự như backup Full bước 5 chúng ta chọn DemoDB_01_Backup_Full &
DemoDB_01_Backup_Differential





Kết quả thành công -> OK


Thực hiện phục hồi /Restore Transaction log
Tương tự chúng ta chọn DemoDB_01_Backup_Full, DemoDB_01_Backup_Differential, DemoDB_01_Backup_Transaction_Log




Kết quả thành công -> OK




Xong!
/*header slide*/