[Database-Tự Học SQL] COM2034_SQL Server - Lab 4: Điều Kiện & Vòng Lặp - P3 TRANSACTION (Transact-SQL)

LÝ THUYẾT:

TRANSACTION: là việc thực hiện một nhóm các câu lệnh, các câu lệnh này được thực thi một cách tuần tự và độc lập nhau. Nếu tất cả câc lệnh trong nhóm thực hiện thành công khi đó tất cả các sự thay đổi dữ liệu được thực hiện trong Transaction được lưu vào cơ sở dữ liệu (COMMIT). Tuy nhiên, nếu có một trong các lệnh của nhóm lệnh thực thi thất bại thì toàn bộ tiến trình sẽ thất bại, đồng nghĩa với việc dữ liệu phải ROLLBACK về trạng thái ban đầu.

Các tiêu chuẩn Transaction:

• Bảo toàn (Atomicity) - đảm bảo rằng tất cả các câu lệnh trong nhóm lệnh được thực thi thành công. Nếu không, transaction bị hủy bỏ tại thời điểm thất bại và tất cả các thao tác trước đó được khôi phục về trạng thái cũ.
• Nhất quán (Consistency) - đảm bảo rằng cơ sở dữ liệu thay đổi chính xác các trạng thái khi một transaction được thực thi thành công.
• Độc lập (Isolation) - cho phép các transaction hoạt động độc lập và minh bạch với nhau.
• Bền bỉ (Durability) - đảm bảo rằng kết quả của một transaction được commit vẫn tồn tại trong trường hợp lỗi hệ thống.

Transaction T-SQL tường minh có dạng (code mẫu đơn giản)

SET XACT_ABORT ON

BEGIN TRAN

BEGIN TRY
	--  Lệnh 1
	--  Lệnh 2
	--  Lệnh ...
 
COMMIT
END TRY
 
BEGIN CATCH
 
  ROLLBACK
 
END CATCH

Ý nghĩa các lệnh dùng để xử lý transaction.

• COMMIT - để lưu các thay đổi.
• ROLLBACK - để khôi phục lại các thay đổi.
• SAVEPOINT - tạo ra các điểm trong transaction để ROLLBACK.
• SET TRANSACTION - thiết lập các thuộc tính cho transaction.

Các lệnh điều khiển transaction chỉ được sử dụng với các lệnh thao tác dữ liệu DML như - INSERT, UPDATE và DELETE.

Noted: Chúng không thể được sử dụng trong lệnh CREATE TABLE hoặc DROP TABLE vì các hoạt động này được tự động được commit trong cơ sở dữ liệu.

Giao dịch lồng (nested transaction) là giao dịch được viết bên trong một giao dịch khác.

• Mỗi khi câu lệnh BEGIN TRAN được thực thi, hàm hệ thống @@TRANCOUNT được tăng thêm 1. 
• Khi thực thi câu lệnh COMMIT TRAN.
• Nếu @@TRANCOUNT > 1, các thay đổi sẽ không được commit. Thay vào đó @@TRANCOUNT giảm đi 1.
• Nếu @@TRANCOUNT = 1, mọi thay đổi đã được thực hiện trên CSDL trong suốt giao dịch sẽ được commit và @@TRANCOUNT được gán bằng 0. 
• Câu lệnh ROLLBACK TRAN roll-back toàn bộ các giao dịch đang hoạt động và thiết lập giá trị cho @@TRANCOUNT về 0

YÊU CẦU:

Bài 1: (Mục đích là làm quen với Transaction)

• Hiển thị mã phòng ban bằng 2, hiển thị Transaction count
• Thực hiện đổi tên (TENPHG) thành 'Phong KT 004'
• Thực hiện Rollback lại tên ban đầu (lúc chưa update ...set....)

Bài 2:

Yêu cầu sử dụng giao dịch kết hợp với try catch để chặn lỗi thực hiện 2 yêu cầu sau:

• Viết chương trình thêm một nhân viên mới. Nếu thêm nhân viên thành công thì thêm 1 thân nhân cho nhân viên vừa tạo.
• Viết chương trình thêm một công việc mới cho đề án số 1, đồng thời phân công nhân viên mói thêm ở câu trên đảm nhận công việc mới tạo.

THỰC HIỆN:

Bài 1:

• Hiển thị mã phòng ban bằng 2, hiển thị Transaction count
• Thực hiện đổi tên (TENPHG) thành 'Phong KT 004'
• Thực hiện Rollback lại tên ban đầu (lúc chưa update ...set....)

Code:

-- ban đầu chưa thay đổi (1)
SELECT * FROM PHONGBAN WHERE PHONGBAN.MAPHG = 2
SELECT @@TRANCOUNT AS dem_Tran

-- thực hiện sửa TENPHG thành 'Phong KT 004' (2)
BEGIN TRAN
	  UPDATE PHONGBAN
	  SET TENPHG = 'Phong KT 004'
	  WHERE MAPHG = 2

--COMMIT TRAN -- Sau khi commit, TRANCOUNT sẽ  = 0
SELECT * FROM PHONGBAN WHERE PHONGBAN.MAPHG = 2 -- kiểm sau khi thực hiện lệnh update... set..
SELECT @@TRANCOUNT AS dem_Tran -- kiểm tra biến đếm 

-- thực hiện Rollback và kiểm tra kết quả (3)
ROLLBACK TRAN -- RollBack thành không khi TRANCOUNT > 0 
SELECT * FROM PHONGBAN WHERE PHONGBAN.MAPHG = 2
SELECT @@TRANCOUNT AS dem_Tran

Kết quả:

• (1) Ban đầu TRANCOUNT = 0
• (2) Khi thực hiện thay đổi tên TRANCOUNT tăng lên 1 đơn vị
• (3) Thực hiện Rollback thì TRANCOUNT sẽ thiết lập giá trị là 0

Bài 2:

Yêu cầu sử dụng giao dịch kết hợp với try catch để chặn lỗi thực hiện 2 yêu cầu sau:

• Viết chương trình thêm một nhân viên mới. Nếu thêm nhân viên thành công thì thêm 1 thân nhân cho nhân viên vừa tạo.

Code mẫu đơn giản:

DECLARE @ma_nv NVARCHAR(9) = '2222'
SET XACT_ABORT ON

BEGIN TRAN

BEGIN TRY
	INSERT INTO NHANVIEN ( [HONV] ,[TENLOT] ,[TENNV] ,[MANV] ,[NGSINH] ,[DCHI] ,[PHAI] ,[LUONG] ,[MA_NQL] ,[PHG] )
					VALUES ('Van', 'Cong',  'Khanh',  @ma_nv, '2000/10/10','Binh Duong','Nam',30000,'005', 5)
	INSERT INTO THANNHAN ([MA_NVIEN] ,[TENTN],[PHAI],[NGSINH] ,[QUANHE]	)
					VALUES (@ma_nv, 'Nhan Nhan', 'Nam','2000/10/10','ban be') -- sửa @ma_nv thành 99999
	PRINT 'Them THANH CONG'

COMMIT -- thực hiện lưu nếu cả 2 dòng lệnh thêm thành công

END TRY
 
BEGIN CATCH
 
  ROLLBACK -- Nếu 1 trong 2 lệnh INSERT không thành công sẽ thông báo lỗi
  PRINT 'Them THAT BAI'
 
END CATCH

Noted: Với code trên khi thêm nhân viên mới chúng ta cần thay dòng thứ 1 là được, tuy nhiên để kiểm tra ROLLBACK thi chúng ta cố tình sửa dòng INTSERT trong bảng thân sai đi để kiểm tra. Ví dụ: thay đổi @ma_nv thành '99999' (dòng số 10)

Với cách trên nếu xảy lỗi chúng ta sẽ khó biết được lỗi xảy ra ở câu lệnh nào, nên chúng ta có thể thiết lập 1 cờ để kết hợp với IF... ESLE/CASE để việc xử lý được tường minh hơn.

Code tham khảo

DECLARE @is_error BIT = 0;
DECLARE @ma_nv NVARCHAR(9) = '77777';
SET XACT_ABORT ON
BEGIN TRAN
BEGIN TRY
	INSERT INTO NHANVIEN ( [HONV] ,[TENLOT] ,[TENNV] ,[MANV] ,[NGSINH] ,[DCHI] ,[PHAI] ,[LUONG] ,[MA_NQL] ,[PHG] )
					VALUES ('Van', 'Cong',  'Khanh',  @ma_nv, '2000/10/10','Binh Duong','Nam',30000,'005', 5)
	-- SELECT * FROM NHANVIEN WHERE MANV = @ma_nv
END TRY

BEGIN CATCH
	SET @is_error = 1;
END CATCH

IF @is_error = 0
	BEGIN
		BEGIN TRY
			INSERT INTO THANNHAN ([MA_NVIEN] ,[TENTN],[PHAI],[NGSINH] ,[QUANHE]	)
					VALUES (@ma_nv, 'Nhan Nhan', 'Nam','2000/10/10','ban be')
			PRINT 'Them THANH CONG'
		END TRY
		BEGIN CATCH
			ROLLBACK TRAN
			PRINT 'Them vao bang (THANNHAN) THAT BAI'
		END CATCH
	END
ELSE
BEGIN
	PRINT 'Them vao bang (NHANVIEN) THAT BAI'
END;

• Viết chương trình thêm một công việc mới cho đề án số 1, đồng thời phân công nhân viên mới thêm ở câu trên đảm nhận công việc mới tạo.

Chúng tôi thực với cú pháp đơn để các bạn dễ hiểu, các bạn có thể kết với RAISEERROR kết hợp với các mã lỗi (tham khảo ở phần 2 )

DECLARE 
	@ma_dean INT = 1
	,@so_tt INT = 12,
	@ma_nv NVARCHAR(9) = '111';
SET XACT_ABORT ON
BEGIN TRAN
BEGIN TRY
	
	INSERT INTO CONGVIEC (MADA, STT, TEN_CONG_VIEC) VALUES (@ma_dean, @so_tt, 'Tin Hoc Van Phong')
	INSERT INTO PHANCONG (MA_NVIEN, MADA, STT, THOIGIAN) VALUES (@ma_nv, @ma_dean, @so_tt,20)

	COMMIT TRAN
	PRINT 'Them THANH CONG'
END TRY
BEGIN CATCH
	ROLLBACK TRAN
	PRINT 'Them THAT BAI'
END CATCH

Tham khảo thêm tại đây

Xong!

[Database-Tự Học SQL] COM2034_SQL Server - Lab 4: Điều Kiện & Vòng Lặp - P2

Ôn lại lý thuyết:

WHILE: Vòng lặp WHILE là việc chạy/thực thi lặp đi lặp lại một đoạn CODE khi điều kiện cụ thể cho trước trả về giá trị là TRUE thì sẽ dừng. Đầu tiên, nó kiểm tra điều kiện và thực hiện một khối câu lệnh nếu điều kiện là đúng.

BREAK: 

• Dùng để thoát khỏi vòng lặp (BREAK không có bất kỳ đối số nào đi kèm)
• Nếu có nhiều WHILE lồng nhau thì BREAK sẽ thoát vòng lặp WHILE gần nó nhất

CONTINUE:

• Bỏ qua khối lệnh sau nó (khối lệnh trong bước lặp hiện tại), và thực hiện bước lặp tiếp theo
• CONTINUE không có bất kỳ đối số nào đi kèm

Cấu trúc lệnh WHILE

NỘI DUNG: Tìm về vòng lặp WHILE, TRY ... CATCH, RAISERROR

Bài 1:

Dùng vòng WHILE để viết chương trình đơn giản theo yêu cầu:

• Viết chương trình tính tổng các số chẵn từ 1 tới 10.
• Viết chương trình tính tổng các số chẵn từ 1 tới 10 nhưng bỏ số 4.

Bài 2:

Sử dụng cơ sở dữ liệu QLDA. Thực hiện các câu truy vấn sau, sử dụng vòng lặp

• Cho biết thông tin nhân viên (HONV, TENLOT, TENNV) có MaNV là số chẵn.
• Cho biết thông tin nhân viên (HONV, TENLOT, TENNV) có MaNV là số chẵn nhưng không tính nhân viên có MaNV là 4.

Bài 3:

Quản lý lỗi chương trình 

Thực hiện chèn thêm một dòng dữ liệu vào bảng PhongBan theo 2 bước 

• Nhận thông báo “Thêm dữ liệu thành công” từ khối Try
• Chèn sai kiểu dữ liệu cột MaPHG để nhận thông báo lỗi “Thêm dữ liệu thất bại” từ khối Catch

Viết chương trình khai báo biến @chia, thực hiện phép chia @chia cho số 0 và dùng RAISERROR để thông báo lỗi.

THỰC HIỆN:

Bài 1:

Dùng vòng WHILE để viết chương trình đơn giản theo yêu cầu:

• Viết chương trình tính tổng các số chẵn từ 1 tới 10.

-- Viết chương trình tính tổng các số chẵn từ 1 tới 10
DECLARE @tong INT = 0;
DECLARE @i INT = 1;

WHILE (@i <= 10)
BEGIN
	IF(@i % 2 = 0)
		SET @tong = @tong + @i
	SET @i = @i + 1
END
PRINT @tong

/*
Kết quả là 30 vì:
2
2 + 4 = 6
6 + 6 = 12
12 + 8 = 20
20 + 10 = 30
*/

• Viết chương trình tính tổng các số chẵn từ 1 tới 10, nếu số chẵn là số 8 thì kết thúc chương trình

Cách 1:

--  Viết chương trình tính tổng các số chẵn từ 1 tới 10, nếu số chẵn là số 8 thì kết thúc chương trình
DECLARE @tong_break INT = 0;
DECLARE @k INT = 1;

WHILE (@k <= 10)
BEGIN
	IF @k = 8
		BREAK

	IF(@k % 2 = 0)
		SET @tong_break = @tong_break + @k
	SET @k = @k + 1
END
PRINT @tong_break

/*
Kết quả là 12 vì:
2
2 + 4 = 6
6 + 6 = 12

Khi k = 8 thì BREAK có nghĩa sẽ kết thúc vòng lặp (vòng lặp gần nó nhất)

*/

Cách 2:

DECLARE @tong_break INT = 0;
DECLARE @k INT = 1;

WHILE (@k <= 10)
BEGIN
	

	IF(@k % 2 = 0)
		IF @k = 8
			BREAK
		ELSE
			SET @tong_break = @tong_break + @k
	SET @k = @k + 1
END
PRINT @tong_break
  

Các bạn đưa ra nhận xét gì về 2 cách trên

• Viết chương trình tính tổng các số chẵn từ 1 tới 10 nhưng bỏ số 4.

-- Viết chương trình tính tổng các số chẵn từ 1 tới 10 nhưng bỏ số 4.
DECLARE @tong_bo4 INT = 0;
DECLARE @j INT = 1;

WHILE (@j <= 10)
BEGIN
	IF @j = 4 
		BEGIN
			SET @j = @j + 1 -- phải tăng lên đếm lên 1, nếu không sẽ bị lặp vô hạng do biến đếm j = 4 hoài -> lặp tô tận 
			CONTINUE;
		END
	
	IF  (@j % 2= 0)
		SET @tong_bo4= @tong_bo4 + @j
	
	SET @j = @j + 1
END
PRINT @tong_bo4

/*
Kết quả là 26 vì:
Cách hoạt động cũng tương tự như tính tổng, tuy nhiên khi @j = 4, nó tăng lên 1 đơn vị và KHÔNG thực hiện phép cộng với giá trị = 4 này

*/

Bài 2:

Sử dụng cơ sở dữ liệu QLDA. Thực hiện các câu truy vấn sau, sử dụng vòng lặp

Cho biết thông tin nhân viên (HONV, TENLOT, TENNV) có MaNV là số chẵn.

Lấy thông tin và sắp xếp MANV theo thứ tự giảm dần

-- Lấy thông tin và sắp xếp MANV theo thứ tự giảm dần
SELECT MANV 
FROM NHANVIEN
ORDER BY MANV DESC;

Để đảm bảo MANV là INT thì chúng ta nên ép kiểu MANV sang INT, và lấy ra MANV cao nhất

-- Để đảm bảo MANV là INT thì chúng ta nên ép kiểu MANV sang INT, và lấy ra MANV cao nhất
SELECT TOP 1 
	CONVERT(INT, MANV)
FROM NHANVIEN
ORDER BY CONVERT(INT, MANV) DESC

Lấy MANV cao nhất gán vào biến @max_manv, và MANV nhấp nhất gán vào biến @min_manv (1)

-- Lấy MANV cao nhất gán vào biến @max_manv
DECLARE @max_manv INT
SET @max_manv = (SELECT TOP 1 
					CONVERT(INT, MANV)
				FROM NHANVIEN
				ORDER BY CONVERT(INT, MANV) DESC
				)

-- Tương tự lấy MANV nhấp nhất gán vào biến @min_manv
DECLARE @min_manv INT
SET @min_manv = (SELECT TOP 1 
					CONVERT(INT, MANV)
				FROM NHANVIEN
				ORDER BY CONVERT(INT, MANV) ASC
				)
-- HOẶC
DECLARE @min int = (select min(CAST(manv as int)) from NHANVIEN);
DECLARE @max int = (select max(CAST(manv as int)) from NHANVIEN);

Thực hiện theo yêu cầu (phải kết hợp với (1) để chạy nhé các bạn)

-- Thực hiện theo yêu cầu
WHILE (@min_manv < @max_manv)
BEGIN
	IF (@min_manv % 2 = 0)
		BEGIN
			SELECT MANV
				, HONV, TENLOT, TENNV
			FROM NHANVIEN
			WHERE CONVERT(INT, MANV) = @min_manv
		END
	SET @min_manv = @min_manv + 1
END

Noted 1: Với kiểu thực hiện vòng lặp như code trên chương trình chạy có nhiều khi xuất ra các dòng trắng vì MANV không tồn tại trong trường hợp MANV không liên tục,... 

Để khắc phục lỗi đó chúng ta có thể kiểm tra xem @min_manv có tồn trong bảng NHANVIEN thì mới thực hiện

-- Để khắc phục lỗi đó chúng ta có thể kiểm tra xem @min_manv có tồn trong bảng NHANVIEN thì mới thực hiện
WHILE (@min_manv < @max_manv)
BEGIN
	IF (@min_manv % 2 = 0)
		BEGIN
			IF EXISTS(SELECT CONVERT(INT, MANV) FROM NHANVIEN WHERE CONVERT(INT, MANV) = @min_manv) -- kiểm tra sự tồn tại của @min_manv trong bảng NHANVIEN
				SELECT MANV
					, HONV, TENLOT, TENNV
				FROM NHANVIEN
				WHERE (CONVERT(INT, MANV) = @min_manv)
		END
	SET @min_manv = @min_manv + 1
END;

Noted 2: Hai số chẵn kế nhau cách nhau 2 đơn vị, nếu chúng ta đã xác định được số đầu tiên rồi để tìm số chẵn tiếp theo thì chúng ta lấy số đó cộng thêm 2. => Với code trên các bạn xem và thay đổi sao cho hợp lý nhất để chương trình không cần kiểm tra các @min_manv là số lẻ. Như vậy lúc này vòng lặp chúng ta sẽ thực hiện lặp ít hơn,... mà vẫn cho ra kết quả không đổi.

Tham khảo cách KHÔNG DÙNG vòng lặp:

khong dung vong lap
SELECT MANV, TENLOT, TENNV 
FROM NHANVIEN
WHERE CONVERT(INT,MANV) %2 = 0

Cho biết thông tin nhân viên (HONV, TENLOT, TENNV) có MaNV là số chẵn nhưng không tính nhân viên có MaNV là 4.

Cách 1:

-- Cho biết thông tin nhân viên (HONV, TENLOT, TENNV) có MaNV là số chẵn nhưng không tính nhân viên có MaNV là 4
WHILE (@min_manv < @max_manv)
BEGIN
	IF (@min_manv = 4)
		BEGIN
			SET @min_manv = @min_manv + 1
			CONTINUE
		END
	IF (@min_manv % 2 = 0)
		BEGIN
			IF EXISTS(SELECT CONVERT(INT, MANV) FROM NHANVIEN WHERE CONVERT(INT, MANV) = @min_manv) -- kiểm tra sự tồn tại của @min_manv trong bảng NHANVIEN
				SELECT MANV
					, HONV, TENLOT, TENNV
				FROM NHANVIEN
				WHERE (CONVERT(INT, MANV) = @min_manv)
		END
	SET @min_manv = @min_manv + 1
END;

Cách 2:

-- HOẶC
WHILE (@min_manv < @max_manv)
BEGIN
	
	IF (@min_manv % 2 = 0)
		IF @min_manv = 4
			BEGIN
				SET @min_manv = @min_manv + 1
				CONTINUE
			END
		ELSE

			BEGIN
				IF EXISTS(SELECT CONVERT(INT, MANV) FROM NHANVIEN WHERE CONVERT(INT, MANV) = @min_manv) -- kiểm tra sự tồn tại của @min_manv trong bảng NHANVIEN
					SELECT MANV
						, HONV, TENLOT, TENNV
					FROM NHANVIEN
					WHERE (CONVERT(INT, MANV) = @min_manv)
			END
	SET @min_manv = @min_manv + 1
END;

Noted: Các bạn xem xem 2 cách trên, cách nào tối ưu hơn, vì sao

Bài 3:

Quản lý lỗi chương trình 

Thực hiện chèn thêm một dòng dữ liệu vào bảng PhongBan theo 2 bước 

• Nhận thông báo “Thêm dữ liệu thành công” từ khối Try
• Chèn sai kiểu dữ liệu cột MaPHG để nhận thông báo lỗi “Thêm dữ liệu thất bại” từ khối Catch

BEGIN TRY
	INSERT PHONGBAN ([TENPHG]
      ,[MAPHG]
      ,[TRPHG]
      ,[NG_NHANCHUC])
	VALUES ('keToan',2,'003','2020-06-02')
	 -- Nếu lệnh chèn thực thi thành công in ra dòng bên dưới
	PRINT 'SUCCESS: Them du lieu THANH CONG'
END TRY
-- Nếu có lỗi xảy ra khi chèn dữ liệu in ra dòng thông báo lỗi cùng với thông tin mã lỗi và thông báo lỗi
BEGIN CATCH
	PRINT 'FAILURE: Them du lieu THAT BAI'
	PRINT 'ERROR ' + CONVERT(varchar, ERROR_NUMBER()) 
		+ ': ' + ERROR_MESSAGE()
END CATCH

Kết quả:

Chạy lần 1 sẽ cho kết quả thành công

(1 row affected)

SUCCESS: Them du lieu THANH CONG

Completion time: 2022-05-25T10:50:40.1103666+07:00

Chạy lần 2 bị thông báo lỗi vì MAPHG đã tồn tại

(0 rows affected)

FAILURE: Them du lieu THAT BAI

ERROR 2627: Violation of PRIMARY KEY constraint 'PK_PhongBan'. Cannot insert duplicate key in object 'dbo.PHONGBAN'. The duplicate key value is (3).

Completion time: 2022-05-25T10:51:03.3769594+07:00

Viết chương trình khai báo biến @chia, thực hiện phép chia @chia cho số 0 và dùng RAISERROR để thông báo lỗi.

BEGIN TRY
	DECLARE @chia INT
	SET @chia = 55/0
END TRY
BEGIN CATCH
	DECLARE
		@ErrMessage NVARCHAR(2048), -- độ dài của chuỗi thông báo lỗi
		@ErrSeverity INT,
		@ErrState INT
		
	SELECT
		@ErrMessage = ERROR_MESSAGE(),
		@ErrSeverity = ERROR_SEVERITY(),
		@ErrState = ERROR_STATE()
		RAISERROR(@ErrMessage, @ErrSeverity, @ErrState) -- thực hiện hiển thị thông báo
END CATCH

Kết quả:

Msg 50000, Level 16, State 1, Line 15
Divide by zero error encountered.
Completion time: 2022-05-25T11:10:58.7629608+07:00

Xong!

[Database-Tự Học SQL] COM2034_SQL Server - Lab 4: Điều Kiện & Vòng Lặp - P1

NỘI DUNG:  Tìm hiểu chủ đề IF ... ELSE, IIF, CASE 

Bài 1:

• Viết câu truy vấn đếm số lượng nhân viên trong từng phòng ban, nếu số lượng nhân viên nhỏ hơn 3  thì hiển thị “Thiếu nhân viên”, ngược lại <5 hiển thị “Đủ Nhan Vien”, ngược lại hiển thị”Đông nhân viên”
• Viết câu truy vấn hiển thị TenNV và thêm cột thuế dựa vào mức lương: trong khoảng 0 and 25000 thì Thuế= LUONG*0.1, trong khoảng 25000 and 30000 thì LUONG*0.12, trong khoảng 30000 and 40000 thì LUONG *0.15, trong khoảng 40000 and 50000 thì LUONG *0.2, còn lại LUONG*0.25

Bài 2:

Sử dụng cơ sở dữ liệu QLDA. Thực hiện các câu truy vấn sau, sử dụng if…else và case

Viết chương trình xem xét có tăng lương cho nhân viên hay không. Hiển thị cột thứ 1 là TenNV, cột thứ 2 nhận giá trị

• “TangLuong” nếu lương hiện tại của nhân viên nhở hơn trung bình lương trong phòng mà nhân viên đó đang làm việc. 
• “KhongTangLuong “nếu lương hiện tại của nhân viên lớn hơn trung bình lương trong phòng mà nhân viên đó đang làm việc.

Viết chương trình phân loại nhân viên dựa vào mức lương.

• Nếu lương nhân viên nhỏ hơn trung bình lương mà nhân viên đó đang làm việc thì xếp loại “nhanvien”, ngược lại xếp loại “truongphong”

Viết chương trình hiển thị TenNV tùy vào cột phái của nhân viên, 

• nếu phái là Nữ thì ghi Ms. + TENNV, 
• Nam thì ghi Mr. + TENNV, 
• Ngược lại Khong_ro + TENNV

Viết chương trình tính thuế mà nhân viên phải đóng theo công thức:

• 0<luong<25000 thì đóng 10% tiền lương
• 25000<luong<30000 thì đóng 12% tiền lương
• 30000<luong<40000 thì đóng 15% tiền lương
• 40000<luong<50000 thì đóng 20% tiền lương
• Luong>50000 đóng 25% tiền lương

THỰC HIỆN:

Bài 1:

• Viết câu truy vấn đếm số lượng nhân viên trong từng phòng ban, nếu số lượng nhân viên nhỏ hơn 3  thì hiển thị “Thiếu nhân viên”, ngược lại <5 hiển thị “Đủ Nhan Vien”, ngược lại hiển thị”Đông nhân viên”

Đếm nhân viên theo từng phòng ban

-- thực hiện đếm nhân viên theo từng phòng ban trước
SELECT NHANVIEN.PHG
	, COUNT(NHANVIEN.MANV) AS 'So_nv'
FROM NHANVIEN
GROUP BY NHANVIEN.PHG

Dùng IFF thực hiện theo yêu cầu đề bài

SELECT NHANVIEN.PHG
	, COUNT(NHANVIEN.MANV) AS 'So_nv'
	, 'Ghi Chu' = IIF(COUNT(NHANVIEN.MANV) < 3, 'thieu -> can tuyen them',
					  IIF(COUNT(NHANVIEN.MANV) < 5, 'Du nhan vien', 'Dong nhan vien')
					 )
FROM NHANVIEN
GROUP BY NHANVIEN.PHG

Dùng CASE

--CASE
SELECT PHONGBAN.TENPHG
	, COUNT(NHANVIEN.PHG) So_luong_nhan_vien
	, Ghi_Chu = CASE 
		WHEN COUNT(NHANVIEN.PHG) < 3  THEN 'Thieu nhan vien'
		WHEN COUNT(NHANVIEN.PHG) < 5  THEN 'Du nhan vien'
	ELSE 'Dong nhan vien'
	END
FROM NHANVIEN 
	INNER JOIN PHONGBAN ON PHONGBAN.MAPHG = NHANVIEN.PHG
GROUP BY PHONGBAN.TENPHG

• Viết câu truy vấn hiển thị TenNV và thêm cột thuế dựa vào mức lương: 

- Trong khoảng 0 and 25000 thì Thuế= LUONG*0.1, 
- Trong khoảng 25000 and 30000 thì LUONG*0.12, 
- Trong khoảng 30000 and 40000 thì LUONG *0.15, 
- Trong khoảng 40000 and 50000 thì LUONG *0.2, 
- Còn lại LUONG*0.25

SELECT TENNV, LUONG, 
 THUE = CASE
			WHEN LUONG BETWEEN 0 AND 25000 THEN LUONG * 0.1
			WHEN LUONG BETWEEN 25000 AND 30000 THEN LUONG * 0.12
			WHEN LUONG BETWEEN 30000 AND 40000 THEN LUONG * 0.15
			WHEN LUONG BETWEEN 40000 AND 50000 THEN LUONG * 0.2
			ELSE LUONG * 0.25 
		END
FROM NHANVIEN

Bài 2:

Sử dụng cơ sở dữ liệu QLDA. Thực hiện các câu truy vấn sau, sử dụng if…else và case

Viết chương trình xem xét có tăng lương cho nhân viên hay không. Hiển thị cột thứ 1 là TenNV, cột thứ 2 nhận giá trị

• “TangLuong” nếu lương hiện tại của nhân viên nhở hơn trung bình lương trong phòng mà nhân viên đó đang làm việc. 
• “KhongTangLuong “nếu lương hiện tại của nhân viên lớn hơn trung bình lương trong phòng mà nhân viên đó đang làm việc.

Mức lương trung bình mỗi phòng ban

-- Mức lương trung bình mỗi phòng ban
SELECT NHANVIEN.PHG
	, AVG(NHANVIEN.LUONG)
FROM NHANVIEN
GROUP BY NHANVIEN.PHG

Lưu kết quả vào biến bảng (1)

-- Lưu kết quả vào biến bảng (1)
DECLARE @cau_1a TABLE (ma_phong INT, luong_TB FLOAT)
INSERT INTO @cau_1a
	SELECT NHANVIEN.PHG
		, AVG(NHANVIEN.LUONG)
	FROM NHANVIEN
	GROUP BY NHANVIEN.PHG

Thực hiện theo yêu cầu đề bài - Dùng IIF

-- thực hiện theo yêu cầu đề bài (phải kết nối bảng (1) với bảng nhân viên) 
-- dùng IIF

SELECT 
	NHANVIEN.TENNV
	, NHANVIEN.LUONG AS Luong_HienTai
	, Ghi_Chu_IIF = IIF (NHANVIEN.LUONG < bang_LTB.luong_TB, 'Tang luong', 'KHONG TANG luong')
FROM NHANVIEN
	INNER JOIN @cau_1a AS bang_LTB ON NHANVIEN.PHG = bang_LTB.ma_phong

Thực hiện theo yêu cầu đề bài - Dùng CASE

-- dùng CASE
SELECT 
	NHANVIEN.TENNV
	, NHANVIEN.LUONG AS Luong_HienTai
	, Ghi_Chu_CASE = CASE
		WHEN (NHANVIEN.LUONG < bang_LTB.luong_TB) THEN 'Tang luong'		
		ELSE 'KHONG TANG luong'
	END
FROM NHANVIEN
	INNER JOIN @cau_1a AS bang_LTB ON NHANVIEN.PHG = bang_LTB.ma_phong

Viết chương trình phân loại nhân viên dựa vào mức lương.

• Nếu lương nhân viên nhỏ hơn trung bình lương mà nhân viên đó đang làm việc thì xếp loại “nhanvien”, ngược lại xếp loại “truongphong”

Mức lương trung bình mỗi phòng ban

-- Mức lương trung bình mỗi phòng ban
SELECT NHANVIEN.PHG
	, AVG(NHANVIEN.LUONG)
FROM NHANVIEN
GROUP BY NHANVIEN.PHG

Lưu kết quả vào biến bảng (1)

-- Lưu kết quả vào biến bảng (1)
DECLARE @cau_1b TABLE (ma_phong INT, luong_TB FLOAT)
INSERT INTO @cau_1b
	SELECT NHANVIEN.PHG
		, AVG(NHANVIEN.LUONG)
	FROM NHANVIEN
	GROUP BY NHANVIEN.PHG

Thực hiện theo yêu cầu đề bài

-- thực hiện theo yêu cầu đề bài (phải kết nối bảng (1) với bảng nhân viên) 
SELECT 
	NHANVIEN.TENNV
	, NHANVIEN.LUONG AS Luong_HienTai
	, Ghi_Chu_IIF = IIF (NHANVIEN.LUONG < bang_LTB.luong_TB, 'Nhan Vien', 'Truong Phong')
FROM NHANVIEN
	INNER JOIN @cau_1b AS bang_LTB ON NHANVIEN.PHG = bang_LTB.ma_phong

Viết chương trình hiển thị TenNV tùy vào cột phái của nhân viên, 

• Nếu phái là Nữ thì ghi Ms. + TENNV, 
• Nam thì ghi Mr. + TENNV, 
• Ngược lại Khong_ro + TENNV

Simple CASE

-- Simple CASE 
SELECT 'Ten_nhanvien Simple CASE ' = CASE NHANVIEN.PHAI
	WHEN N'Nữ' THEN 'Ms. ' + NHANVIEN.TENNV
	WHEN N'Nam' THEN 'Mr. ' + NHANVIEN.TENNV
	ELSE 'Khong_ro ' + NHANVIEN.TENNV
	END
FROM NHANVIEN

Searched CASE

-- Searched CASE
SELECT 'ten nv' = CASE 
	WHEN NHANVIEN.PHAI LIKE N'Nữ' THEN 'Ms. ' + NHANVIEN.TENNV
	WHEN NHANVIEN.PHAI LIKE N'Nam' THEN 'Mr. ' + NHANVIEN.TENNV
	ELSE 'Khong_ro ' + NHANVIEN.TENNV
	END
FROM NHANVIEN

Viết chương trình tính thuế mà nhân viên phải đóng theo công thức:

• 0<luong<25000 thì đóng 10% tiền lương
• 25000<luong<30000 thì đóng 12% tiền lương
• 30000<luong<40000 thì đóng 15% tiền lương
• 40000<luong<50000 thì đóng 20% tiền lương
• Luong>50000 đóng 25% tiền lương

Thực hiện yêu cầu (Searched Case)

SELECT
	NHANVIEN.TENNV
	,NHANVIEN.LUONG
	,'Dong Thue' = CASE
			WHEN NHANVIEN.LUONG between 0 and 25000 THen NHANVIEN.LUONG * 0.1
			WHEN NHANVIEN.LUONG between 25000 and 30000 THen NHANVIEN.LUONG * 0.12
			WHEN NHANVIEN.LUONG between 30000 and 40000 THen NHANVIEN.LUONG * 0.15
			WHEN NHANVIEN.LUONG between 40000 and 50000 THen NHANVIEN.LUONG * 0.20
			ELSE NHANVIEN.LUONG * 0.25
	END
FROM NHANVIEN

Noted: Câu hỏi đặt ra ở câu lệnh trên là nếu mức lương 25000 thì thuế sẽ đóng là 10% hay 12% ? => Máy tính sẽ thực hiện từ trên xuống nếu lương 25000 thỏa mản điều kiện đầu nên thuế sẽ đóng là 10% (Thứ tự các câu lệnh trong trường hợp này các bạn cần để ý)

Xong!

[Database-Tự Học SQL] COM2034_SQL Server - Lab 3: Các Hàm Hệ Thống & Xử Lý Chuỗi

NỘI DUNG: 

Bài 1:

Với mỗi đề án, liệt kê tên đề án và tổng số giờ làm việc một tuần của tất cả các nhân viên tham dự đề án đó. 

• Xuất định dạng “tổng số giờ làm việc” kiểu decimal với 2 số thập phân.
• Xuất định dạng “tổng số giờ làm việc” kiểu varchar

Với mỗi phòng ban, liệt kê tên phòng ban và lương trung bình của những nhân viên làm việc cho phòng ban đó.

• Xuất định dạng “luong trung bình” kiểu decimal với 2 số thập phân, sử dụng dấu phẩy để phân biệt phần nguyên và phần thập phân.
• Xuất định dạng “luong trung bình” kiểu varchar. Sử dụng dấu phẩy tách cứ mỗi 3 chữ số trong chuỗi ra, gợi ý dùng thêm các hàm Right, Replace

Bài 2: 

Sử dụng các hàm toán học

Với mỗi đề án, liệt kê tên đề án và tổng số giờ làm việc một tuần của tất cả các nhân viên tham dự đề án đó.

• Xuất định dạng “tổng số giờ làm việc” với hàm CEILING
• Xuất định dạng “tổng số giờ làm việc” với hàm FLOOR
• Xuất định dạng “tổng số giờ làm việc” làm tròn tới 2 chữ số thập phân

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 (làm tròn đến 2 số thập phân) của phòng "Nghiên cứu"

Bài 3:

Sử dụng các hàm xử lý chuỗi

Danh sách những nhân viên (HONV, TENLOT, TENNV, DCHI) có trên 2 thân nhân, thỏa các yêu cầu

• Dữ liệu cột HONV được viết in hoa toàn bộ
• Dữ liệu cột TENLOT được viết chữ thường toàn bộ
• Dữ liệu chột TENNV có ký tự thứ 2 được viết in hoa, các ký tự còn lại viết thường (ví dụ: kHanh)
• Dữ liệu cột DCHI chỉ hiển thị phần tên đường, không hiển thị các thông tin khác như số nhà hay thành phố.

Cho biết 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, hiển thị thêm một cột thay thế tên trưởng phòng bằng tên “Fpoly”

Bài 4:

Sử dụng các hàm ngày tháng năm

• Cho biết các nhân viên có năm sinh trong khoảng 1960 đến 1965.
• Cho biết tuổi của các nhân viên tính đến thời điểm hiện tại.
• Dựa vào dữ liệu NGSINH, cho biết nhân viên sinh vào thứ mấy.
• Cho biết số lượng nhân viên, tên trưởng phòng, ngày nhận chức trưởng phòng và ngày nhận chức trưởng phòng hiển thi theo định dạng dd-mm-yy (ví dụ 25-04-2019)

Bài 5:  

Cho biết các nhân viên nhỏ tuổi nhất có tham gia dự án

Cho biết khoảng cách giữa lương cao nhất và lương thấp nhất là bao nhiêu. Thông tin hiển thị gồm: LuongCaoNhat, LuongThapNhat, ChenhLech. Định dạng lương theo mẫu (#,##0 triệu đồng).

 Hiển thị danh sách nhân viên gồm các thông tin: Mã nhân viên, họ và tên, Năm sinh, Độ tuổi

Được xét dựa trên tuổi: 

- Tuổi >50 : Cao tuổi

- Tuổi từ 25 – 50: Trung bình

- Tuổi <25: Nhỏ tuổi

THỰC HIỆN:

Bài 1:

Với mỗi đề án, liệt kê tên đề án và tổng số giờ làm việc một tuần của tất cả các nhân viên tham dự đề án đó. 

SELECT 
	DEAN.TENDEAN
	,SUM ( PHANCONG.THOIGIAN) AS 'Tong_thoi_gian'
FROM DEAN
	INNER JOIN CONGVIEC ON DEAN.MADA = CONGVIEC.MADA
	INNER JOIN PHANCONG ON CONGVIEC.MADA = PHANCONG.MADA
GROUP BY DEAN.TENDEAN

Xuất định dạng “tổng số giờ làm việc” kiểu decimal với 2 số thập phân.

Xuất định dạng “tổng số giờ làm việc” kiểu varchar

SELECT 
	DEAN.TENDEAN
	,SUM ( PHANCONG.THOIGIAN) AS 'Tong_thoi_gian'
	,CAST(SUM ( PHANCONG.THOIGIAN) AS DECIMAL (10,2)) AS 'Tong_thoi_gian_CAST_DEC_2_ThapPhan'
	,CONVERT( DECIMAL (10,2), SUM ( PHANCONG.THOIGIAN)) AS 'Tong_thoi_gian_CONVERT_DEC_2_ThapPhan'
FROM DEAN
	INNER JOIN CONGVIEC ON DEAN.MADA = CONGVIEC.MADA
	INNER JOIN PHANCONG ON CONGVIEC.MADA = PHANCONG.MADA
GROUP BY DEAN.TENDEAN

Với mỗi phòng ban, liệt kê tên phòng ban và lương trung bình của những nhân viên làm việc cho phòng ban đó.

SELECT PHONGBAN.TENPHG, 
	AVG(NHANVIEN.LUONG)
FROM PHONGBAN
	INNER JOIN NHANVIEN ON PHONGBAN.MAPHG = NHANVIEN.PHG
GROUP BY PHONGBAN.TENPHG

Xuất định dạng “luong trung bình” kiểu decimal với 2 số thập phân, sử dụng dấu phẩy để phân biệt phần nguyên và phần thập phân.

SELECT PHONGBAN.TENPHG, 
	CONVERT(DECIMAL(20,2),AVG(NHANVIEN.LUONG)) AS Decimal_2_ThapPhan,
	REPLACE(CONVERT(DECIMAL(20,2),AVG(NHANVIEN.LUONG)),'.',',') AS Decimal_2_ThapPhan_thay_CHAM_thanh_PHAY
FROM PHONGBAN
	INNER JOIN NHANVIEN ON PHONGBAN.MAPHG = NHANVIEN.PHG
GROUP BY PHONGBAN.TENPHG

Xuất định dạng “luong trung bình” kiểu varchar. Sử dụng dấu phẩy tách cứ mỗi 3 chữ số trong chuỗi ra, gợi ý dùng thêm các hàm Right, Replace

SELECT PHONGBAN.TENPHG, 
	CONVERT(VARCHAR,AVG(NHANVIEN.LUONG)) AS Kieu_Varchar,
	REPLACE(CONVERT(VARCHAR,AVG(NHANVIEN.LUONG)), /* chuyển lương trung bình sang VARCHAR */
			RIGHT(CONVERT(VARCHAR(10),AVG(NHANVIEN.LUONG)),3), /* Tìm chuỗi */
			',' + RIGHT(CONVERT(VARCHAR(10),AVG(NHANVIEN.LUONG)),3) ) /* Thay thế bằng chuỗi mới */
			AS Tach_nhom_3_chu_so
FROM PHONGBAN
	INNER JOIN NHANVIEN ON PHONGBAN.MAPHG = NHANVIEN.PHG
GROUP BY PHONGBAN.TENPHG

Noted: Yêu cầu chủ yếu để các bạn thực hành và hiểu về các sử dụng các hàm đã học. Các bạn cũng có thể sử dụng hàm FORMAT trong trường hợp này.

SELECT PHONGBAN.TENPHG
	,FORMAT(AVG(NHANVIEN.LUONG), '#,###.#0')
FROM PHONGBAN
	INNER JOIN NHANVIEN ON PHONGBAN.MAPHG = NHANVIEN.PHG
GROUP BY PHONGBAN.TENPHG

Ngoài ra hàm FORMAT cũng có thể dùng để định dạng ngày tháng năm. Ví dụ: FORMAT (GETDATE(), 'dd/mm/yyy hh:mm:ss') 

Các bạn có thể tìm hiểu hàm FORMAT tại đây

Bài 2: 

SỬ DỤNG CÁC HÀM TOÁN HỌC

Với mỗi đề án, liệt kê tên đề án và tổng số giờ làm việc một tuần của tất cả các nhân viên tham dự đề án đó.

SELECT DEAN.TENDEAN, 
	SUM(THOIGIAN) 
FROM DEAN
	INNER JOIN CONGVIEC ON dean.MADA = CONGVIEC.MADA
	INNER JOIN PHANCONG ON CONGVIEC.MADA = PHANCONG.MADA
GROUP BY DEAN.TENDEAN

Xuất định dạng “tổng số giờ làm việc” với hàm CEILING

Xuất định dạng “tổng số giờ làm việc” với hàm FLOOR

Xuất định dạng “tổng số giờ làm việc” làm tròn tới 2 chữ số thập phân

SELECT DEAN.TENDEAN, 
	CEILING(SUM(THOIGIAN)) AS Ham_CEILING,
	FLOOR(SUM(THOIGIAN)) AS Ham_FLOOR,
	ROUND(SUM(THOIGIAN),2) AS Ham_ROUND_2_So_ThapPhan
FROM DEAN
	INNER JOIN CONGVIEC ON dean.MADA = CONGVIEC.MADA
	INNER JOIN PHANCONG ON CONGVIEC.MADA = PHANCONG.MADA
GROUP BY DEAN.TENDEAN

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 (làm tròn đến 2 số thập phân) của phòng "Nghiên cứu"

Chuẩn bị truy vấn

-- chuẩn bị 
SELECT HONV, TENLOT, TENNV
	, LUONG
FROM NHANVIEN
WHERE LUONG > (SELECT AVG(LUONG) FROM NHANVIEN AS nvien, PHONGBAN AS pban
				WHERE nvien.PHG = pban.MAPHG and TENPHG = N'Nghiên cứu')

Dùng hàm ROUND để thực hiện yêu cầu

SELECT HONV, TENLOT, TENNV
	, ROUND(LUONG,2)
FROM NHANVIEN
WHERE LUONG > (SELECT AVG(LUONG) FROM NHANVIEN AS nvien, PHONGBAN AS pban
				WHERE nvien.PHG = pban.MAPHG and TENPHG = N'Nghiên cứu')

Bài 3: 

SỬ DỤNG CÁC HÀM XỬ LÝ CHUỖI

Danh sách những nhân viên (HONV, TENLOT, TENNV, DCHI) có trên 2 thân nhân, thỏa các yêu cầu (1)

-- chuẩn bị
SELECT THANNHAN.MA_NVIEN
	,HONV
	,TENLOT
	,TENNV
	,DCHI
	,COUNT(THANNHAN.MA_NVIEN)
FROM THANNHAN
	INNER JOIN NHANVIEN ON THANNHAN.MA_NVIEN = NHANVIEN.MANV
GROUP BY THANNHAN.MA_NVIEN,HONV, TENLOT, TENNV, DCHI
HAVING COUNT(THANNHAN.MA_NVIEN) > 2

Dữ liệu cột HONV được viết in hoa toàn bộ

Dữ liệu cột TENLOT được viết chữ thường toàn bộ

Dữ liệu chột TENNV có ký tự thứ 2 được viết in hoa, các ký tự còn lại viết thường (ví dụ: kHanh)

SELECT THANNHAN.MA_NVIEN
	, UPPER ( HONV)
	, LOWER(TENLOT)
	, LEFT(LOWER(TENNV),1) + UPPER (SUBSTRING(LOWER(TENNV), 2,1)) + RIGHT(LOWER(TENNV), LEN(LOWER(TENNV))-2)
	, NHANVIEN.DCHI 
	,COUNT(THANNHAN.MA_NVIEN)
FROM THANNHAN
INNER JOIN NHANVIEN ON THANNHAN.MA_NVIEN = NHANVIEN.MANV
GROUP BY THANNHAN.MA_NVIEN,HONV, TENLOT, TENNV, DCHI
HAVING COUNT(THANNHAN.MA_NVIEN) > 2

Dữ liệu cột DCHI chỉ hiển thị phần tên đường, không hiển thị các thông tin khác như số nhà hay thành phố.

Chúng ta sẽ dùng hàm substring trong trường hợp này, chạy với truy đơn giản trước (2)

SELECT DCHI
,SUBSTRING(DCHI,
		CHARINDEX(' ', DCHI) +1, 
		 /*chiều dài của địa chỉ, TRỪ vị trí khoảng trắng đầu tiên, TRỪ cho vị trị trí của dấu phẩy sau khi đảo ngược địa chỉ */
		LEN(DCHI) - CHARINDEX(' ', DCHI) - CHARINDEX(',', REVERSE( DCHI))) AS DIACHI
FROM  NHANVIEN

Hoặc

SELECT SUBSTRING (NHANVIEN.DCHI,
					CHARINDEX(' ',NHANVIEN.DCHI ) + 1, -- căn cứ vào khoảng trắng đầu tiên để xác định vị trí bắt đầu lấy
					CHARINDEX(',',NHANVIEN.DCHI)-1 - CHARINDEX(' ',NHANVIEN.DCHI )) -- xác định vị trí dấu phẩy, sau đó trừ đi vị trí khoảng trắng đầu tiên
FROM NHANVIEN

Thay vào câu (2) vào (1)

SELECT THANNHAN.MA_NVIEN
	,HONV
	,TENLOT
	,TENNV
	,SUBSTRING (NHANVIEN.DCHI,
				CHARINDEX(' ',NHANVIEN.DCHI ) + 1, 
				CHARINDEX(',',NHANVIEN.DCHI)-1 - CHARINDEX(' ',NHANVIEN.DCHI )) 
	,COUNT(THANNHAN.MA_NVIEN)
FROM THANNHAN
	INNER JOIN NHANVIEN ON THANNHAN.MA_NVIEN = NHANVIEN.MANV
GROUP BY THANNHAN.MA_NVIEN,HONV, TENLOT, TENNV, DCHI
HAVING COUNT(THANNHAN.MA_NVIEN) > 2

Cho biết 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, hiển thị thêm một cột thay thế tên trưởng phòng bằng tên “Fpoly”

Lấy ra phòng có số nhân viên đông nhất (chưa có tên trưởng phòng)

-- lấy phòng có số nhân viên đông nhất
SELECT TOP 1
	NHANVIEN.PHG
	, PHONGBAN.TENPHG
	, PHONGBAN.TRPHG
	, COUNT(NHANVIEN.MANV) AS so_nhanvien
FROM NHANVIEN
	INNER JOIN PHONGBAN ON NHANVIEN.PHG = PHONGBAN.MAPHG
GROUP BY NHANVIEN.PHG, PHONGBAN.TENPHG, PHONGBAN.TRPHG
ORDER BY so_nhanvien DESC

Hiển thị thêm tên trưởng phòng

-- PHONGBAN.TRPHG là mã số nhân viên, ngoài ra bảng nhân viên (manv) và bảng phòng ban (trphg) còn có quan hệ khác 
SELECT TOP 1
	NHANVIEN.PHG
	, PHONGBAN.TENPHG
	, PHONGBAN.TRPHG
	, nhan_vien2.TENNV
	, COUNT(NHANVIEN.MANV) AS so_nhanvien
FROM NHANVIEN
	INNER JOIN PHONGBAN ON NHANVIEN.PHG = PHONGBAN.MAPHG
	INNER JOIN NHANVIEN AS nhan_vien2 ON nhan_vien2.MANV = PHONGBAN.TRPHG
GROUP BY NHANVIEN.PHG, PHONGBAN.TENPHG, PHONGBAN.TRPHG, nhan_vien2.TENNV
ORDER BY so_nhanvien DESC

Thực hiện theo yêu cầu đề bài

-- Thực hiện thay thế theo yêu cầu
SELECT TOP 1
	NHANVIEN.PHG
	, PHONGBAN.TENPHG
	, PHONGBAN.TRPHG
    , nhan_vien2.TENNV
	, REPLACE( nhan_vien2.TENNV,  nhan_vien2.TENNV, 'Fpoly') AS Thay_the_ten_TruongPhong
	, COUNT(NHANVIEN.MANV) AS so_nhanvien
FROM NHANVIEN
	INNER JOIN PHONGBAN ON NHANVIEN.PHG = PHONGBAN.MAPHG
	INNER JOIN NHANVIEN AS nhan_vien2 ON nhan_vien2.MANV = PHONGBAN.TRPHG
GROUP BY NHANVIEN.PHG, PHONGBAN.TENPHG, PHONGBAN.TRPHG, nhan_vien2.TENNV
ORDER BY so_nhanvien DESC

Bài 4: 

SỬ DỤNG CÁC HÀM NGÀY THÁNG NĂM

Cho biết các nhân viên có năm sinh trong khoảng 1960 đến 1965.

SELECT NHANVIEN.MANV
	, NHANVIEN.TENNV
	, DATENAME(year, NHANVIEN.NGSINH) 
FROM NHANVIEN
WHERE 
	YEAR(NHANVIEN.NGSINH) BETWEEN 1960 AND 1965

Cho biết tuổi của các nhân viên tính đến thời điểm hiện tại.

SELECT 
	MANV, TenNV, YEAR(GETDATE()) - YEAR(NGSINH) as 'Tuoi'
FROM NHANVIEN;

Dựa vào dữ liệu NGSINH, cho biết nhân viên sinh vào thứ mấy.

SELECT NHANVIEN.MANV
	, NHANVIEN.TENNV
	, DATENAME(WEEKDAY, NHANVIEN.NGSINH) 
FROM NHANVIEN

Cho biết số lượng nhân viên, tên trưởng phòng, ngày nhận chức trưởng phòng và ngày nhận chức trưởng phòng hiển thi theo định dạng dd-mm-yy (ví dụ 25-04-2019)

Ban đầu chúng ta chỉ hiển thị mã trưởng phòng

-- Hiển thị mã trưởng phòng, dùng để nháp và lập luận
SELECT COUNT(NHANVIEN.MANV)
	, PHONGBAN.TRPHG
	, CONVERT(VARCHAR,NG_NHANCHUC,105)
FROM NHANVIEN
	INNER JOIN PHONGBAN ON NHANVIEN.PHG = PHONGBAN.MAPHG 
GROUP BY PHONGBAN.TRPHG, CONVERT(VARCHAR,NG_NHANCHUC,105)

Noted: Vì trưởng phòng cũng là một nhân viên, nên chúng ta có thể biết được tên nhân viên nhờ mã trưởng phòng (mã nhân viên) trong bảng NHANVIEN. 

SELECT COUNT(NHANVIEN.MANV)
	,PHONGBAN.TRPHG
	,nhan_vien2.TENNV
	, CONVERT(VARCHAR,NG_NHANCHUC,105)
FROM NHANVIEN
	INNER JOIN PHONGBAN ON NHANVIEN.PHG = PHONGBAN.MAPHG
	INNER JOIN NHANVIEN AS nhan_vien2 ON nhan_vien2.MANV = PHONGBAN.TRPHG 
GROUP BY PHONGBAN.TRPHG, nhan_vien2.TENNV, CONVERT(VARCHAR,NG_NHANCHUC,105)

Bài 5:  

Cho biết các nhân viên nhỏ tuổi nhất có tham gia dự án

Cho biết khoảng cách giữa lương cao nhất và lương thấp nhất là bao nhiêu. Thông tin hiển thị gồm: LuongCaoNhat, LuongThapNhat, ChenhLech. Định dạng lương theo mẫu (#,##0 triệu đồng).

 Hiển thị danh sách nhân viên gồm các thông tin: Mã nhân viên, họ và tên, Năm sinh, Độ tuổi

Được xét dựa trên tuổi: 

- Tuổi >50 : Cao tuổi

- Tuổi từ 25 – 50: Trung bình

- Tuổi <25: Nhỏ tuổi

??

Xong!

[Database-Tự Học SQL] COM2034_SQL Server - Lab 2: Sử Dụng Biến

NỘI DUNG

A. Chương trình tính diện tích, chu vi hình chữ nhật khi biết chiều dài và chiều rộng.

B. Dựa trên csdl QLDA thực hiện truy vấn, các giá trị truyền vào và trả ra phải dưới dạng sử dụng biến.

1. Cho biêt nhân viên có lương cao nhất
2. 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 "Nghiên cứu”
3. Với các phòng ban có mức lương trung bình trên 30,000, liệt kê tên phòng ban và số lượng nhân viên của phòng ban đó.
4. Với mỗi phòng ban, cho biết tên phòng ban và số lượng đề án mà phòng ban đó chủ trì

THỰC HIỆN

A. Chương trình tính diện tích, chu vi hình chữ nhật khi biết chiều dài và chiều rộng.

/*
Chu vi (C) = (dai + rong ) * 2
Dien tich (S) = dai * rong
*/

DECLARE @dai INT, @rong INT, @chu_vi INT, @dien_tich INT
SET @dai = 30
SET @rong = 20
SET @chu_vi = (@dai + @rong	) * 2
SET @dien_tich = @dai * @rong

PRINT @chu_vi
PRINT @dien_tich

-- hoặc
PRINT 'Chu vi: ' + CONVERT(CHAR(10),@chu_vi)
PRINT 'Dien tich: ' + CONVERT(CHAR(10),@dien_tich)

-- hoặc
SELECT @chu_vi AS Chu_vi, @dien_tich AS Dien_tich

Biến dùng để lưu trữ các giá trị tạm thời trong quá trình thực thi chương trình

B. Dựa trên csdl QLDA thực hiện truy vấn, các giá trị truyền vào và trả ra phải dưới dạng sử dụng biến.

1. Cho biêt nhân viên có lương cao nhất

-- chuẩn bị
SELECT  MAX(luong) FROM NHANVIEN 

SELECT * 
FROM NHANVIEN 
WHERE 
	LUONG = (SELECT  MAX(luong) FROM NHANVIEN )

-- dùng biến
DECLARE @max_luong INT
SET @max_luong = (SELECT  MAX(luong) FROM NHANVIEN)
                    
SELECT * 
FROM NHANVIEN 
WHERE 
	LUONG = @max_luong

2. 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 "Nghiên cứu”

-- chuẩn bị
SELECT  AVG(luong) 
FROM NHANVIEN, PHONGBAN 
WHERE 
		PHONGBAN.MAPHG = NHANVIEN.PHG 
	AND PHONGBAN.TENPHG LIKE N'Nghiên cứu' /* (1) */

SELECT CONCAT(HONV,' ', TENLOT,' ', TENNV) AS Ho_va_Ten 
FROM NHANVIEN 
WHERE 
	LUONG > /* (1) */ (SELECT  AVG(luong) 
				FROM NHANVIEN, PHONGBAN 
				WHERE 
						PHONGBAN.MAPHG = NHANVIEN.PHG 
					AND PHONGBAN.TENPHG LIKE N'Nghiên cứu')

-- dùng biến
DECLARE @avg_luong INT
/* gán (1) vào biến @avg_luong */
SET @avg_luong = (SELECT  AVG(luong) 
					FROM NHANVIEN, PHONGBAN 
					WHERE 
							PHONGBAN.MAPHG = NHANVIEN.PHG 
						AND PHONGBAN.TENPHG LIKE N'Nghiên cứu' /* (1) */
					)
SELECT CONCAT(HONV,' ', TENLOT,' ', TENNV) AS Ho_va_Ten, LUONG FROM NHANVIEN WHERE LUONG > @avg_luong

-- HOẶC, nói chung có nhiều cách giải nhá các bạn, chủ yếu là biết cách dùng biến thế nào, tạm thời chúng ta không xét phần data type trong chủ đề này
BEGIN
	DECLARE @avg_luong INT;
	
	SELECT @avg_luong = AVG(luong) FROM PHONGBAN 
	INNER JOIN NHANVIEN on PHONGBAN.MAPHG = NHANVIEN.PHG
	WHERE PHONGBAN.TENPHG = N'Nghiên cứu';
    SELECT * FROM NHANVIEN WHERE luong > @avg_luong;
	-- SELECT CONCAT(honv, tenlot) AS 'Họ Tên Lót', luong FROM NHANVIEN WHERE luong > @avg_luong;

-- có thể dùng thêm biến bảng
DECLARE @ket_qua TABLE (honv NVARCHAR(20), tenlot NVARCHAR(20), luong FLOAT);
	INSERT INTO @ket_qua
	SELECT HONV,TENLOT,LUONG FROM NHANVIEN WHERE luong > @avg_luong;

	SELECT CONCAT(honv, tenlot) AS 'Họ Tên Lót', luong FROM @ket_qua;

END;

-- hoặc

DECLARE @NGHIENCUU INT
SET @NGHIENCUU = (SELECT  PHONGBAN.MAPHG FROM PHONGBAN WHERE PHONGBAN.TENPHG = N'NGHIÊN CỨU')

-- SELECT * FROM  NHANVIEN WHERE NHANVIEN.PHG = 5;
-- SELECT AVG(NHANVIEN.LUONG) FROM  NHANVIEN WHERE NHANVIEN.PHG = @NGHIENCUU;

DECLARE @LUONGTB  FLOAT;

SET @LUONGTB = (SELECT AVG(NHANVIEN.LUONG) FROM  NHANVIEN WHERE NHANVIEN.PHG = @NGHIENCUU)

-- SELECT @LUONGTB

SELECT CONCAT(NHANVIEN.HONV, ' ', NHANVIEN.TENLOT, ' ', NHANVIEN.TENNV) AS [Họ và tên], LUONG as [Lương] FROM NHANVIEN WHERE LUONG > @LUONGTB

3. Với các phòng ban có mức lương trung bình trên 30,000, liệt kê tên phòng ban và số lượng nhân viên của phòng ban đó.

-- chuẩn bị
SELECT PHONGBAN.TENPHG, COUNT(NHANVIEN.MANV) FROM PHONGBAN,NHANVIEN 
WHERE PHONGBAN.MAPHG = NHANVIEN.PHG  
GROUP BY PHONGBAN.TENPHG

SELECT PHONGBAN.TENPHG, COUNT(NHANVIEN.MANV), AVG(Luong)  FROM PHONGBAN,NHANVIEN 
WHERE (PHONGBAN.MAPHG = NHANVIEN.PHG)  
GROUP BY PHONGBAN.TENPHG 
HAVING AVG(Luong) > 31000

-- dùng biến bảng
DECLARE @cau3 TABLE (ten_pb NVARCHAR(20), so_luong INT, avg_luong INT)

INSERT INTO @cau3
SELECT PHONGBAN.TENPHG, COUNT(NHANVIEN.MANV), AVG(Luong)  FROM PHONGBAN,NHANVIEN 
WHERE (PHONGBAN.MAPHG = NHANVIEN.PHG)  
GROUP BY PHONGBAN.TENPHG 
HAVING AVG(Luong) > 31000

SELECT * FROM @cau3

4. Với mỗi phòng ban, cho biết tên phòng ban và số lượng đề án mà phòng ban đó chủ trì

-- chuẩn bị bảng
SELECT * FROM DEAN

SELECT COUNT(mada) FROM DEAN GROUP BY phong

SELECT phongban.TENPHG, COUNT(mada) FROM DEAN, PHONGBAN WHERE DEAN.PHONG = PHONGBAN.MAPHG GROUP BY phongban.TENPHG

-- dùng biến
DECLARE @cau4 TABLE (ten_pb NVARCHAR(20), so_dean INT)
INSERT INTO @cau4
SELECT phongban.TENPHG, COUNT(mada) FROM DEAN, PHONGBAN WHERE DEAN.PHONG = PHONGBAN.MAPHG GROUP BY phongban.TENPHG

SELECT * FROM @cau4

Tham khảo bài subquery/truy vấn con/truy vấn lồng tại đây

Xong!

[Database-Tự Học SQL] Backup SQL Server - Script To Backup All SQL Server Databases - T-SQL

NỘI DUNG:

1. Thực hiện trên Microsoft SQL Server Management Studio

• Tạo đường dẫn để lưu các file backup  (1)
• Định nghĩa tên file  (2)
• Trích lọc chọn các database cần backup  (3)
• Thực hiện vòng lặp để duyệt hết list các database (3) cần backup
• Tạo script backup với các thông tin từ (1), (2), (3)
• Thực hiện backup test

2. Thực hiện trên SQL Server Configuration Manager

• Enable TCP/IP và gán port sẽ sử dụng cho SQL Server
• Restart SQL Server Service

3. Thực hiện trên Windows

• Allow inbound rules trên Windows Firewall và kiểm tra
• Tạo file script kết hợp SQL command line lưu với tên file OSQL_call_bk_SQL.bat
• Tạo file script tìm xóa chỉ giữ lại các file trong 3 ngày gần nhất  và lưu lại với tên forfile_del.bat
• Tạo file script có tên schedule_cmd.bat call và thực thi OSQL_call_bk_SQL.bat và forfile_del.bat
• Khởi tạo lập lịch back up định kỳ.

THỰC HIỆN

1. Thực hiện trên Microsoft SQL Server Management Studio

• Tạo đường dẫn để lưu các file backup
• Định nghĩa tên file 
• Trích lọc chọn các database cần backup
• Thực hiện vòng lặp để duyệt hết list các database (mục 3) cần backup
• Tạo script backup với các thông tin từ mục 1, mục 2, mục 3
• Thực hiện backup test

CODE:

Tạo file bk_QSL.sql

DECLARE @name NVARCHAR(256) -- ten database 
DECLARE @path NVARCHAR(512) -- duong dan de luu backup files  
DECLARE @fileName NVARCHAR(512) -- ten file backup  
DECLARE @fileDate NVARCHAR(40) -- lay ngay gio he thong luu vao bien nay ket hop voi fileName
 
-- (1) cai dat duong dan, ban phai dam bao folder dang ton tai
SET @path = 'C:\SQL_backups\BAKs' 
 
-- thuc hien lay ngay gio he thong luu vao bien
SELECT @fileDate = (SELECT FORMAT (getdate(), 'yyyymmdd_hhmmss'))
 
DECLARE db_cursor CURSOR READ_ONLY FOR  
SELECT [name] -- (3) thuc hien trich loc file can backup
FROM master.sys.databases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- khong lay cac database he thong
AND state = 0 -- cac database online
AND is_in_standby = 0 -- database is not read only for log shipping
AND [name] not like 'ReportServer%' -- databases he thong (tuy version ma 'ReportServer...' co database nay hay khong)

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
 
WHILE @@FETCH_STATUS = 0   -- lap duyet qua tat ca database
BEGIN   
   SET @fileName = @path + '\' + @fileDate + '_' + @name + '.BAK'  -- (2) ten file gom: duong dan + ngay gio + ten cua database
   BACKUP DATABASE @name TO DISK = @fileName  -- thuc hien backup FULL
   -- WITH DIFFERENTIAL -- co the thuc hien backup thay doi
   
	RESTORE VERIFYONLY -- kiem tra xem back up thanh cong hay khong, co doc duoc hay khong
	FROM DISK = @fileName
	

   FETCH NEXT FROM db_cursor INTO @name   
END   
 
CLOSE db_cursor   
DEALLOCATE db_cursor

Tùy nhu cầu, chúng ta có thể backup log với cấu trúc lệnh

BACKUP LOG [ten_database]
TO DISK = 'C:\SQL_backups\tenfile_canluu.trn'
GO

Hoặc tham khảo thêm các lựa chọn khác tại đây 

Noted: Ghi chú tiếng việt có dấu trong phần code T-SQL sẽ bị lỗi khi sử dụng lệnh osql nên chúng tôi ghi chú tiếng việt không dấu

CHẠY THỬ:

Processed 496 pages for database 'QLDA', file 'QLDA' on file 1.

Processed 1 pages for database 'QLDA', file 'QLDA_log' on file 1.

BACKUP DATABASE successfully processed 497 pages in 0.068 seconds (57.006 MB/sec).

Processed 432 pages for database 'QL_NhaTro', file 'QL_NhaTro' on file 1.

Processed 1 pages for database 'QL_NhaTro', file 'QL_NhaTro_log' on file 1.

BACKUP DATABASE successfully processed 433 pages in 0.061 seconds (55.351 MB/sec).

Processed 400 pages for database 'QLDA_COM2034', file 'QLDA' on file 1.

Processed 2 pages for database 'QLDA_COM2034', file 'QLDA_log' on file 1.

BACKUP DATABASE successfully processed 402 pages in 0.194 seconds (16.168 MB/sec).

Completion time: 2022-05-11T16:34:49.5008303+07:00

KẾT QUẢ:

2. Thực hiện trên SQL Server Configuration Manager

• Enable TCP/IP và gán port sẽ sử dụng cho SQL Server: Đảm bảo TCP/IP đã Enable, và thiết lập port cho SQL Server (ở đây chúng tôi dùng port 1433)

- SQL Server Configuration Manager -> Protocols for SQL...-> TCP/IP -> Enable

- SQL Server Configuration Manager -> Protocols for SQL...-> TCP/IP -> Properties -> Tab IP Addresses -> IPAll -> TCPPort điền 1433 -> OK

• Restart SQL Server Service: Khởi động lại SQL Server

Mở SQL Server Configuration Manager -> SQL Server Services -> SQL Server -> Restart

3. Thực hiện trên Windows

• Allow inbound rules trên Windows Firewall và kiểm tra

    Mở cmd run as Administrator

netsh advfirewall firewall add rule name="Allow TCP Port 1433" dir=in action=allow protocol=TCP localport=1433

Ngoài ra chúng ta cũng có thể thêm port vào Windows Firewall bằng giao diện có thể tham khảo ở link này (mục 1.3)

    Kiểm tra port đã lắng nghe trên máy chưa (Listening là đã lắng nghe)

netstat -ano | find "1433"

    Kết quả:

Có thể kiểm tra thêm bằng phần mềm tcping cho chắc ăn (đảm bảo các máy khác cũng có thể truy cập được SQL với port 1433)

tcping -h localhost 1433

    Kết quả: Nếu 1433 is Open -> ổn

Noted: có thể thay localhost bằng IP hoặc tên máy tính của các bạn


Download phần mềm TCPING tại đây

• Tạo file script kết hợp SQL command line lưu với tên file OSQL_call_bk_SQL.bat

Tạo file có tên OSQL_call_bk_SQL.bat

@echo off
set _DateTime=%date%%time%
set _DateTime=%_DateTime: =_%
set _DateTime=%_DateTime::=-%
set _DateTime=%_DateTime:/=-%
set _DateTime=%_DateTime:.=_%

set _srcScript="C:\SQL_backups\Script\bk_SQL.sql"
set _dstLogs="c:\SQL_Backups\Logs"

if NOT EXIST %_dstLogs% MD %_dstLogs%

::osql -E -S "localhost" -i "C:\SQL_backups\Script\bk_SQL.sql" 

osql -E -S "localhost" -i %_srcScript% -o "%_dstLogs%\%_DateTime%.txt"

@echo on

Noted: Tùy theo từng version mà sql command có thể là: iSQL, oSQL, SQLcmd,...... Đường dẫn đến file thường là (150 màu đỏ là tùy theo version mà chúng ta chọn cho hợp lý) C:\Program Files\Microsoft SQL Server\150\Tools\Binn

Tham khảo cách lấy ngày giờ hệ thống trên command tại link

• Tạo file script tìm xóa chỉ giữ lại các file trong 3 ngày gần nhất  và lưu lại với tên forfile_del.bat (tùy theo nhu cầu thực tế)

@echo off
set _DateTime=%date%%time%
set _DateTime=%_DateTime: =_%
set _DateTime=%_DateTime::=-%
set _DateTime=%_DateTime:/=-%
set _DateTime=%_DateTime:.=_%

set _path=C:\SQL_backups
set _dirfile=BAKs
set _logs=Logs

set _days=3
forfiles /p "%_path%\%_dirfile%" /s /m *.BAK /D -%_days% /C "cmd /c del /q @path&echo @path >>%_path%\%_logs%\%_DateTime%_DelFileList.txt"

@echo on 

Tham khảo tìm và xóa file với Forfiles tại link

• Tạo file script có tên schedule_cmd.bat call và thực thi OSQL_call_bk_SQL.bat và forfile_del.bat 

call C:\SQL_backups\Script\OSQL_call_bk_SQL.bat
call C:\SQL_backups\Script\forfile_del.bat

Noted: Các bạn đảm bảo rằng trong đĩa C:\ (đây là Lab nên chúng tôi lưu như vậy) có các folder và file như hình dưới:

• C:\SQL_backups\Logs: là nơi lưu trữ file log khi backup SQL và các file đã xóa file của lệnh forfiles
• C:\SQL_backups\BAKs: chứa các file .bak là định dạng file backup của SQL
• C:\SQL_backups\Script: chứa các file script

Các bạn có thể gọi thêm các script khác tùy theo nhu cầu, ví dụ: sau khi backup xong nén file log lại gửi đến email cho các bạn.

• Khởi tạo lập lịch back up định kỳ.

Task Schedule -> Create Basic Task... -> đặt tên -> Next -> Daily (Tùy các bạn chọn) -> Next

Cài đặt thời gian chạy file backup -> Next -> Start a program -> Next

Chọn đường dẫn đến nơi chứa file cần thực thi -> Next -> Finish

Kiểm tra lại Task Schedule vừa tạo

Kết quả thực thi

Link tham khảo

Xong!

[Database-Tự Học SQL] COM2012_MySQL Bài 6 P1: Subqueries/Truy Vấn Con/Truy Vấn Lồng Trong SQL

A. Subquery NON-correlated: truy vấn con độc lập với truy vấn bên ngoài. Truy vấn con được thi hành thi hành đầu tiên và một lần duy nhất cho toàn bộ câu lệnh. Kết quả của truy vấn con được điền vào truy vấn bên ngoài, và cuối cùng thi hành truy vấn bên ngoài

B. Subquery correlated: Truy vấn con không độc lập hay có liên quan với truy vấn bên ngoài. Truy vấn con sử dụng các giá trị từ truy vấn bên ngoài trong mệnh đề WHERE của nó. Quá trình thực hiện như sau: các truy vấn bên ngoài được thực hiện trước tiên và sau đó thi hành truy vấn con bên trong cho mỗi dòng kết quả của truy vấn bên ngoài

YÊU CẦU:

1. Hiển thị thông tin các nhân viên có mức lương cao nhất
2. Hiển thị thông tin nhân viên có mức lương lớn hơn mức lương trung bình của toàn công ty
3. Hiển thị thông tin nhân viên có mức lương lớn hơn tất cả các mức lương của nhân viên trong phòng PB001
4. Hiển thị thông tin nhân viên có mức lương lớn hơn một trong các mức lương của nhân viên trong phòng PB001
5. Hiển thị thông tin nhân viên thuộc các phòng ban có chữ "San Xuat" 
6. Hiển thị các phòng ban không có nhân viên nào
7. Hiển thị thông tin nhân viên mà nhân viên đó chưa được phân bổ phòng ban
8. Hiển thị thông tin các nhân viên trong nhóm có lương cao thứ 3

THỰC HIỆN

Sử dụng database của bài trước (bài 5)

1. Hiển thị thông tin các nhân viên có mức lương cao nhất

SELECT * FROM nhan_vien 
WHERE luong = (			
				SELECT MAX(luong) FROM nhan_vien);

2. Hiển thị thông tin nhân viên có mức lương lớn hơn mức lương trung bình của toàn công ty

SELECT * FROM nhan_vien 
WHERE luong > (			
				SELECT AVG(luong) FROM nhan_vien);

3. Hiển thị thông tin nhân viên có mức lương lớn hơn tất cả các mức lương của nhân viên trong phòng PB001

SELECT * FROM nhan_vien 
WHERE luong > ALL (			
					SELECT luong FROM nhan_vien WHERE phongban_id = "PB001" );	

4. Hiển thị thông tin nhân viên có mức lương lớn hơn một trong các mức lương của nhân viên trong phòng PB001

SELECT * FROM nhan_vien 
WHERE luong > ANY (			
					SELECT luong FROM nhan_vien WHERE phongban_id = "PB001" );

5. Hiển thị thông tin nhân viên thuộc các phòng ban có chữ "San Xuat" 

SELECT * FROM nhan_vien 
WHERE phongban_id IN ( 			
						SELECT id FROM phong_ban WHERE ten_pb LIKE "San Xuat%");

6. Hiển thị các phòng ban không có nhân viên nào

SELECT * FROM phong_ban 
WHERE NOT EXISTS ( 			
					SELECT * FROM nhan_vien WHERE phong_ban.id = nhan_vien.phongban_id);
-- có thể dùng LEFT JOIN để so sánh kết quả
SELECT * FROM phong_ban	
LEFT JOIN nhan_vien ON phong_ban.id = nhan_vien.phongban_id
WHERE nhan_vien.id IS NULL;

7. Hiển thị thông tin nhân viên mà nhân viên đó chưa được phân bổ phòng ban

SELECT * FROM nhan_vien 			
WHERE NOT EXISTS (			
					SELECT * FROM phong_ban WHERE phong_ban.id = nhan_vien.phongban_id); 
-- không dùng subquery, chúng ta có thể dùng câu này để so sánh kết quả
SELECT * FROM nhan_vien WHERE phongban_id IS NULL;

8. Hiển thị thông tin các nhân viên trong nhóm có lương cao thứ 3

SELECT * FROM nhan_vien
WHERE luong = (
	SELECT DISTINCT luong
	FROM nhan_vien
    ORDER BY luong DESC
    LIMIT 2, 1 -- bỏ qua 2 dòng và lấy 1 dòng
	)
;

Xong!

[Database-Tự Học SQL] COM2012_MySQL Bài 5: DQL-Data Query Language/Ngôn Ngữ Truy Vấn - Phần 1 - Truy Vấn 1 Bảng

DQL-Data Query Language/Ngôn Ngữ Truy Vấn: Bao gồm các lệnh cho phép truy vấn dữ liệu mà không làm thay đổi dữ liệu hoặc các đối tượng trong CSDL

NỘI DUNG

A. CHUẨN BỊ DỮ LIỆU

1. Tạo bảng (phong_ban, nhan_vien, du_an, quanly_duan)

2. Chèn dữ liệu mẫu

B. THỰC HIỆN TRUY VẤN

I. SELECT, CONCAT, AS

1. Truy vấn hiển thị toàn bộ nội dung trong bảng nhan_vien

2. Truy vấn hiển thị các cột: ho_nv, ten_nv, nam_sinh trong bảng nhan_vien

3. Truy vấn hiển thị các cột: ho_nv, ten_nv, nam_sinh trong bảng nhan_vien, gộp 2 côt ho_nv và ten_nv thành 1 cột

4. Truy vấn hiển thị các cột: ho_nv, ten_nv, nam_sinh trong bảng nhan_vien, gộp 2 côt ho_nv và ten_nv thành 1 cột và đặt bí danh cho cột là "Họ và Tên"

5. Truy vấn hiển thị cột: ten_nv trong bảng nhan_vien, và loại bỏ các dòng trùng nhau

II. ORDER BY

1. Hiển thị danh sách các nhân gồm: ho_nv, ten_nv, luong và lương sắp xếp giảm dần theo cột luong

2. Hiển thị danh sách các nhân gồm: ho_nv, ten_nv, luong và lương sắp xếp giảm dần theo cột luong, nếu lương bằng nhau thì sắp xếp tăng dần (A->Z) của cột ten_nv

III. WHERE

Hiển thị thông tin nhân viên có tên: "dan"

IV. OR

1. Hiển thị thông tin nhân viên có phongban_id là: PB001 hoặc PB002

2. Hiển thị thông tin nhân viên có luong là: 800, 1000, 1500

V. [NOT] IN

Hiển thị thông tin nhân viên có luong là: 800, 1000, 1500

VI. AND

Hiển thị thông tin nhân viên có phongban_id là: PB001 hoặc PB002 và luong > 1000

VII. [NOT] BETWEEN … AND

Hiển thị nhân viên có mức lương trong khoảng 1000 đến 2000

VIII. [NOT] LIKE

1. Hiển thị nhân viên có họ là: Tran

2. Hiển thị nhân viên có họ có chữ: Giap

3. Hiển thị nhân viên có tên có chiều dài là 3 ký tự và bắt đầu là chữ D

IX. IS [NOT] NULL

Hiện thị nhân viên chưa phân bổ bộ phận (căn cứ vào cột phongban_id)

X. AGGREGATE

1. Thống kê tổng số nhân viên

2. Thống kê tổng số nhân viên, và đổi tên cột đếm thành "Tổng số nhân viên"

3. Thống kê tổng số nhân viên nam/nữ

4. Tìm nhân viên có mức lương cao nhất

5. Tìm nhân viên có mức lương nhấp nhất

6. Tính mức lương trung bình của nhân toàn công ty

7. Tính tổng lương của nhân viên phòng ban có mã PB001

8. Hiển thị MIN, MAX, SUM, AVG của toàn thể nhân viên trên 1 dòng

XI. OPERATOR

1. Tính tuổi của nhân viên

2. Chỉ hiện thị các nhân viên có tuổi >30

3. Tăng thêm 200$ cho các nhân viên có mức lương thấp hơn 900$

XII. GROUP BY

Đếm số lượng nhân viên trong mỗi phòng

XIII. HAVING

1. Hiển thị tổng mức lương của mỗi phòng ban và chỉ hiển thị các nhóm/phòng có tổng lương lớn hơn 15000$

2. Tính tổng lương công ty phải trả cho mỗi phòng ban, chỉ nhóm hàng nào có cột lương >800$, chỉ hiển thị nhóm nào có tổng >10000$

XIV. LIMIT

1. Lấy ra 10 nhân viên có mức lương thấp nhất

2. Lấy ra 2 nhân viên có mức lương thấp nhất của phongban_id PB001, và lấy từ vị trí thứ 6

TỔNG HỢP

1. Hiển thị mã phòng ban (phongban_id) và lương trung bình tương ứng từng phòng, chỉ lấy các hàng có mức lương trên 800$, chỉ hiển thị các phòng có lương trung bình > 1200$ và sắp xếp theo thứ tự giảm dần theo cột phongban_id và chỉ lấy 3 dòng

  2. Hiển thị họ tên, giới tính của nhân viên, nếu giới tính = 1 thì hiển thị là nam, ngược lại là nữ 

THỨ TỰ THỰC HIỆN CÁC CÂU LỆNH TRONG SQL

THỰC HIỆN

A. CHUẨN BỊ DỮ LIỆU

1. Tạo bảng (phong_ban, nhan_vien, du_an, quanly_duan)

CREATE TABLE  phong_ban(
id  char(5) NOT NULL,
ten_pb  varchar(255) NOT NULL,
ma_tp  int DEFAULT NULL, 
PRIMARY KEY (id)
);

CREATE TABLE  nhan_vien(
id  int NOT NULL AUTO_INCREMENT,
ho_nv  varchar(255) NOT NULL,
ten_nv  varchar(10) NOT NULL,  
nam_sinh  date DEFAULT NULL,
dia_chi  varchar(255) DEFAULT NULL,
gioi_tinh  BOOLEAN  DEFAULT NULL,
luong  int DEFAULT NULL,
phongban_id  varchar(5) DEFAULT NULL, 
PRIMARY KEY (id),  
FOREIGN KEY (phongban_id) REFERENCES phong_ban (id)
);

CREATE TABLE  du_an(
id   char(5) NOT NULL,
ten_duan  varchar(255) DEFAULT NULL,
ngay_batdau   date DEFAULT NULL,  
ngay_ketthuc  date DEFAULT NULL,
PRIMARY KEY (id)
);

CREATE TABLE  quanly_duan(
duan_id  char(5) NOT NULL,
nhanvien_id   int NOT NULL,
ngay_thamgia  date DEFAULT NULL,  
ngay_ketthuc  date DEFAULT NULL,
sogio   int DEFAULT NULL,
vai_tro  varchar(255) DEFAULT NULL,  
PRIMARY KEY (duan_id, nhanvien_id), 
FOREIGN KEY (duan_id) REFERENCES du_an (id), 
FOREIGN KEY (nhanvien_id) REFERENCES nhan_vien (id))
;

Tìm hiểu cách tạo bảng ở bài 4

Sơ đồ sau khi tạo bảng

2. Chèn dữ liệu mẫu

INSERT INTO phong_ban VALUES('PB001', 'Nhan Su', '1');
INSERT INTO phong_ban VALUES('PB002', 'Ke Toan', '2');
INSERT INTO phong_ban VALUES('PB003', 'Kinh Doanh', '3');
INSERT INTO phong_ban VALUES('PB004', 'San Xuat', '4');
INSERT INTO phong_ban VALUES('PB005', 'Phat Trien Mau', '5');
INSERT INTO phong_ban VALUES('PB006', 'QA', '6');
INSERT INTO phong_ban VALUES('PB007', 'Thu Mua', '7');
INSERT INTO phong_ban VALUES('PB008', 'Ky Thuat', '8');
INSERT INTO phong_ban VALUES('PB009', 'LAB', '9');
INSERT INTO phong_ban VALUES('PB010', 'ME', '10');
INSERT INTO phong_ban VALUES('PB011', 'San Xuat 1', '11');
INSERT INTO phong_ban VALUES('PB012', 'San Xuat 2', '12');
INSERT INTO phong_ban VALUES('PB013', 'San Xuat 3', '13');
INSERT INTO phong_ban VALUES('PB014', 'San Xuat 4', '14');
INSERT INTO phong_ban VALUES('PB015', 'San Xuat 5', '15');

INSERT INTO nhan_vien VALUES('1', 'To At', 'Mui', '1981-1-27', '129, Thu Khoa Huan, Ha Noi', '0', '1700', 'PB003');
INSERT INTO nhan_vien VALUES('2', 'Nguyen At', 'Ty', '1984-12-24', '12, Bui Thi Xan, Long An', '1', '1700', 'PB008');
INSERT INTO nhan_vien VALUES('3', 'To Mau', 'Hoi', '1981-9-24', '197, Pham Van Dong, Ha Noi', '1', '700', 'PB004');
INSERT INTO nhan_vien VALUES('4', 'Le Mau', 'Ngo', '1985-5-15', '182, Thu Khoa Huan, Ha Noi', '0', '800', 'PB005');
INSERT INTO nhan_vien VALUES('5', 'Ho At', 'Tuat', '1983-2-27', '62, Le Duan, Dong Nai', '0', '1000', 'PB003');
INSERT INTO nhan_vien VALUES('6', 'Ly Canh', 'Thin', '1973-2-17', '30, Nguyen Thi Minh Khai, Long An', '1', '1000', 'PB001');
INSERT INTO nhan_vien VALUES('7', 'Nguyen Tan', 'Than', '1981-4-21', '23, Nguyen Hue, Cao Bang', '1', '850', 'PB007');
INSERT INTO nhan_vien VALUES('8', 'Thai Ky', 'Suu', '1984-9-18', '155, Nguyen Thi Minh Khai, TP. Ho Chi Minh', '0', '2500', 'PB001');
INSERT INTO nhan_vien VALUES('9', 'Nguyen Qui', 'Hoi', '1971-8-19', '84, Bui Thi Xan, TP. Ho Chi Minh', '0', '800', 'PB007');
INSERT INTO nhan_vien VALUES('10', 'Le Qui', 'Mui', '1985-11-9', '73, Le Van Tam, Dong Nai', '1', '900', 'PB001');
INSERT INTO nhan_vien VALUES('11', 'Le At', 'Suu', '1972-9-28', '70, Le Duan, Binh Duong', '1', '2000', 'PB008');
INSERT INTO nhan_vien VALUES('12', 'Ho Giap', 'Ti', '1995-9-23', '182, Nguyen Thi Minh Khai, Hau Giang', '1', '1000', 'PB006');
INSERT INTO nhan_vien VALUES('13', 'Nguyen Dinh', 'Meo', '1971-3-7', '165, Nguyen Thi Minh Khai, Binh Duong', '0', '2000', 'PB008');
INSERT INTO nhan_vien VALUES('14', 'Thai Canh', 'Dan', '1981-3-9', '142, Le Hong Phong, Cao Bang', '0', '2000', 'PB003');
INSERT INTO nhan_vien VALUES('15', 'Van Canh', 'Thin', '1984-9-6', '98, Le Van Tam, Ha Tay', '1', '800', 'PB001');
INSERT INTO nhan_vien VALUES('16', 'Hoang Ky', 'Ngo', '1971-7-6', '157, Le Hong Phong, Dong Nai', '0', '1700', 'PB005');
INSERT INTO nhan_vien VALUES('17', 'Nguyen Tan', 'Thin', '1997-3-18', '175, Le Hong Phong, Long An', '0', '900', 'PB003');
INSERT INTO nhan_vien VALUES('18', 'Ly Tan', 'Than', '1993-4-23', '64, Thu Khoa Huan, Hau Giang', '1', '2000', 'PB004');
INSERT INTO nhan_vien VALUES('19', 'Thai At', 'Ngo', '1973-2-6', '21, Pham Van Dong, Dong Nai', '0', '1000', 'PB005');
INSERT INTO nhan_vien VALUES('20', 'Nguyen Giap', 'Dau', '1984-3-8', '91, Nguyen Thi Minh Khai, Long An', '0', '900', 'PB004');
INSERT INTO nhan_vien VALUES('21', 'Thai Giap', 'Mui', '1974-9-18', '106, Le Duan, Da Nang', '0', '750', 'PB002');
INSERT INTO nhan_vien VALUES('22', 'Ho Dinh', 'Thin', '1997-8-5', '105, Nguyen Hue, Hau Giang', '1', '1000', 'PB001');
INSERT INTO nhan_vien VALUES('23', 'Thai Canh', 'Thin', '1971-3-3', '33, Le Van Tam, Ha Tay', '0', '750', 'PB006');
INSERT INTO nhan_vien VALUES('24', 'Ho At', 'Tuat', '1981-12-10', '187, Le Van Tam, TP. Ho Chi Minh', '0', '2500', 'PB003');
INSERT INTO nhan_vien VALUES('25', 'Ho Ky', 'Ti', '1971-12-13', '83, Le Van Tam, TP. Ho Chi Minh', '0', '2000', 'PB004');
INSERT INTO nhan_vien VALUES('26', 'Le Ky', 'Suu', '1985-10-17', '139, Pham Van Dong, Da Nang', '0', '800', 'PB006');
INSERT INTO nhan_vien VALUES('27', 'Ly Canh', 'Mui', '1973-12-8', '97, Bui Thi Xan, Ha Tay', '0', '1000', 'PB006');
INSERT INTO nhan_vien VALUES('28', 'To Tan', 'Meo', '1974-5-7', '102, Le Duan, Binh Duong', '0', '2500', 'PB002');
INSERT INTO nhan_vien VALUES('29', 'To Tan', 'Thin', '1981-8-21', '194, Ho Xuan Huong, Hau Giang', '0', '1700', 'PB007');
INSERT INTO nhan_vien VALUES('30', 'Van Ky', 'Tuat', '1984-8-18', '132, Bui Thi Xan, Binh Duong', '1', '2500', 'PB007');
INSERT INTO nhan_vien VALUES('31', 'Thai Giap', 'Suu', '1970-10-21', '117, Le Duan, Cao Bang', '1', '1500', 'PB001');
INSERT INTO nhan_vien VALUES('32', 'Van Dinh', 'Mui', '1994-7-11', '113, Ho Xuan Huong, Hau Giang', '0', '1000', 'PB001');
INSERT INTO nhan_vien VALUES('33', 'Tran Dinh', 'Suu', '1994-4-8', '192, Nguyen Thi Minh Khai, Da Nang', '0', '2000', 'PB006');
INSERT INTO nhan_vien VALUES('34', 'Thai Giap', 'Ti', '1996-8-24', '194, Le Hong Phong, Cao Bang', '0', '2500', 'PB005');
INSERT INTO nhan_vien VALUES('35', 'Van Tan', 'Than', '1971-7-16', '77, Thu Khoa Huan, Da Nang', '0', '800', 'PB006');
INSERT INTO nhan_vien VALUES('36', 'Nguyen Binh', 'Ty', '1993-3-5', '194, Le Hong Phong, Dong Nai', '1', '1700', 'PB007');
INSERT INTO nhan_vien VALUES('37', 'Ly Mau', 'Ty', '1973-9-19', '57, Nguyen Thi Minh Khai, Ha Tay', '1', '2000', 'PB003');
INSERT INTO nhan_vien VALUES('38', 'Tran Qui', 'Thin', '1996-3-21', '161, Bui Thi Xan, Cao Bang', '0', '900', 'PB008');
INSERT INTO nhan_vien VALUES('39', 'Ho At', 'Ngo', '1974-8-17', '144, Pham Van Dong, Hau Giang', '1', '2500', 'PB007');
INSERT INTO nhan_vien VALUES('40', 'Ly Ky', 'Tuat', '1993-11-10', '48, Bui Thi Xan, Cao Bang', '1', '1500', 'PB004');
INSERT INTO nhan_vien VALUES('41', 'Ho Dinh', 'Ngo', '1983-8-6', '138, Bui Thi Xan, Long An', '0', '1000', 'PB005');
INSERT INTO nhan_vien VALUES('42', 'Ho Nham', 'Suu', '1997-8-3', '18, Mac Van Khoa, Long An', '0', '2500', 'PB008');
INSERT INTO nhan_vien VALUES('43', 'Tran Canh', 'Ngo', '1993-4-16', '181, Thu Khoa Huan, Hau Giang', '1', '850', 'PB002');
INSERT INTO nhan_vien VALUES('44', 'Ly Binh', 'Than', '1982-4-14', '160, Le Hong Phong, Binh Duong', '1', '900', 'PB002');
INSERT INTO nhan_vien VALUES('45', 'Thai Ky', 'Suu', '1973-9-20', '64, Bui Thi Xan, Ha Noi', '0', '2500', 'PB002');
INSERT INTO nhan_vien VALUES('46', 'Truong Ky', 'Than', '1993-3-11', '61, Ho Xuan Huong, Long An', '1', '1500', 'PB002');
INSERT INTO nhan_vien VALUES('47', 'Ho Qui', 'Tuat', '1970-4-7', '169, Pham Van Dong, Dong Nai', '1', '900', 'PB006');
INSERT INTO nhan_vien VALUES('48', 'Ly Binh', 'Dau', '1972-12-15', '30, Nguyen Hue, Cao Bang', '1', '750', 'PB005');
INSERT INTO nhan_vien VALUES('49', 'Tran Ky', 'Ti', '1982-1-9', '30, Ho Xuan Huong, Binh Duong', '1', '1500', 'PB003');
INSERT INTO nhan_vien VALUES('50', 'Nguyen Nham', 'Ty', '1970-7-28', '133, Thu Khoa Huan, Long An', '1', '750', 'PB007');
INSERT INTO nhan_vien VALUES('51', 'Tran Dinh', 'Suu', '1994-3-13', '5, Nguyen Hue, TP. Ho Chi Minh', '0', '1700', 'PB008');
INSERT INTO nhan_vien VALUES('52', 'Nguyen At', 'Mui', '1985-7-20', '88, Le Duan, Cao Bang', '1', '700', 'PB007');
INSERT INTO nhan_vien VALUES('53', 'Van Ky', 'Dan', '1996-6-2', '51, Ho Xuan Huong, Cao Bang', '1', '2000', 'PB008');
INSERT INTO nhan_vien VALUES('54', 'Hoang Canh', 'Mui', '1982-11-21', '48, Ho Xuan Huong, Binh Duong', '0', '1700', 'PB007');
INSERT INTO nhan_vien VALUES('55', 'To Giap', 'Mui', '1996-9-1', '194, Pham Van Dong, Ha Noi', '1', '1500', 'PB003');
INSERT INTO nhan_vien VALUES('56', 'Truong At', 'Ty', '1971-9-22', '66, Ho Xuan Huong, Hau Giang', '0', '800', 'PB005');
INSERT INTO nhan_vien VALUES('57', 'Thai Canh', 'Hoi', '1972-2-2', '152, Bui Thi Xan, TP. Ho Chi Minh', '0', '800', 'PB004');
INSERT INTO nhan_vien VALUES('58', 'Thai Binh', 'Ngo', '1970-9-27', '76, Le Duan, Binh Duong', '0', '2500', 'PB006');
INSERT INTO nhan_vien VALUES('59', 'Van Giap', 'Than', '1997-10-12', '9, Ho Xuan Huong, Da Nang', '0', '1500', 'PB007');
INSERT INTO nhan_vien VALUES('60', 'Ly Mau', 'Tuat', '1995-8-27', '68, Nguyen Hue, Hau Giang', '0', '750', 'PB007');
INSERT INTO nhan_vien VALUES('61', 'Ho Nham', 'Thin', '1984-6-26', '5, Bui Thi Xan, TP. Ho Chi Minh', '1', '750', 'PB002');
INSERT INTO nhan_vien VALUES('62', 'Van Qui', 'Than', '1971-6-5', '140, Le Van Tam, Cao Bang', '1', '700', 'PB008');
INSERT INTO nhan_vien VALUES('63', 'Tran Dinh', 'Mui', '1985-2-25', '76, Le Van Tam, Hue', '0', '800', 'PB004');
INSERT INTO nhan_vien VALUES('64', 'Ho Nham', 'Meo', '1984-2-11', '95, Mac Van Khoa, Binh Duong', '1', '850', 'PB005');
INSERT INTO nhan_vien VALUES('65', 'Tran Ky', 'Suu', '1971-8-12', '96, Le Hong Phong, Binh Duong', '1', '1500', 'PB001');
INSERT INTO nhan_vien VALUES('66', 'Van Binh', 'Ngo', '1993-9-12', '105, Mac Van Khoa, Ha Noi', '0', '800', 'PB006');
INSERT INTO nhan_vien VALUES('67', 'Ho Ky', 'Mui', '1994-12-8', '110, Nguyen Hue, Long An', '1', '1700', 'PB004');
INSERT INTO nhan_vien VALUES('68', 'Truong Binh', 'Dan', '1982-9-28', '118, Nguyen Thi Minh Khai, Long An', '1', '1700', 'PB001');
INSERT INTO nhan_vien VALUES('69', 'Le Giap', 'Ngo', '1983-1-1', '93, Pham Van Dong, Binh Duong', '1', '850', 'PB006');
INSERT INTO nhan_vien VALUES('70', 'To At', 'Hoi', '1995-6-9', '154, Le Duan, Da Nang', '0', '1000', 'PB005');
INSERT INTO nhan_vien VALUES('71', 'Truong At', 'Thin', '1984-2-4', '141, Le Van Tam, Binh Duong', '0', '1500', 'PB004');
INSERT INTO nhan_vien VALUES('72', 'Ho Binh', 'Dau', '1983-6-19', '179, Thu Khoa Huan, Cao Bang', '0', '800', 'PB001');
INSERT INTO nhan_vien VALUES('73', 'Le Binh', 'Tuat', '1974-6-4', '57, Pham Van Dong, Binh Duong', '0', '1700', 'PB008');
INSERT INTO nhan_vien VALUES('74', 'Nguyen Canh', 'Ti', '1984-12-5', '123, Bui Thi Xan, Binh Duong', '1', '2000', 'PB005');
INSERT INTO nhan_vien VALUES('75', 'To Mau', 'Than', '1981-9-20', '179, Ho Xuan Huong, Binh Duong', '0', '1700', 'PB003');
INSERT INTO nhan_vien VALUES('76', 'Truong Tan', 'Suu', '1982-7-3', '14, Nguyen Hue, Ha Tay', '0', '750', 'PB005');
INSERT INTO nhan_vien VALUES('77', 'Le Canh', 'Mui', '1995-3-2', '69, Nguyen Hue, Da Nang', '1', '2000', 'PB001');
INSERT INTO nhan_vien VALUES('78', 'Tran Qui', 'Thin', '1994-9-20', '175, Nguyen Hue, Cao Bang', '0', '900', 'PB001');
INSERT INTO nhan_vien VALUES('79', 'Tran Mau', 'Dau', '1984-2-28', '79, Nguyen Hue, Long An', '0', '2500', 'PB006');
INSERT INTO nhan_vien VALUES('80', 'To Ky', 'Meo', '1982-12-4', '32, Le Hong Phong, Hue', '1', '2000', 'PB005');
INSERT INTO nhan_vien VALUES('81', 'Nguyen At', 'Suu', '1993-4-4', '117, Nguyen Hue, Ha Tay', '1', '1500', 'PB002');
INSERT INTO nhan_vien VALUES('82', 'Truong Dinh', 'Thin', '1971-1-19', '101, Pham Van Dong, Cao Bang', '0', '1000', 'PB005');
INSERT INTO nhan_vien VALUES('83', 'Van Nham', 'Mui', '1972-1-27', '74, Thu Khoa Huan, Long An', '0', '1000', 'PB008');
INSERT INTO nhan_vien VALUES('84', 'Nguyen Dinh', 'Suu', '1984-12-27', '52, Le Duan, Long An', '1', '850', 'PB002');
INSERT INTO nhan_vien VALUES('85', 'Ho Qui', 'Thin', '1994-6-28', '76, Nguyen Hue, Cao Bang', '0', '850', 'PB004');
INSERT INTO nhan_vien VALUES('86', 'To Dinh', 'Thin', '1993-7-21', '181, Le Hong Phong, Ha Tay', '0', '750', 'PB006');
INSERT INTO nhan_vien VALUES('87', 'Ho At', 'Dan', '1984-7-20', '174, Pham Van Dong, Da Nang', '0', '750', 'PB006');
INSERT INTO nhan_vien VALUES('88', 'Van Ky', 'Ngo', '1983-6-3', '2, Nguyen Thi Minh Khai, Dong Nai', '0', '700', 'PB008');
INSERT INTO nhan_vien VALUES('89', 'Tran Canh', 'Suu', '1995-8-25', '169, Le Duan, Binh Duong', '0', '1000', 'PB006');
INSERT INTO nhan_vien VALUES('90', 'Hoang Dinh', 'Ti', '1993-6-23', '103, Thu Khoa Huan, Ha Tay', '0', '1700', 'PB002');
INSERT INTO nhan_vien VALUES('91', 'Ly Tan', 'Tuat', '1993-1-19', '97, Pham Van Dong, Ha Tay', '0', '700', 'PB003');
INSERT INTO nhan_vien VALUES('92', 'Ho Tan', 'Mui', '1997-12-23', '15, Mac Van Khoa, Cao Bang', '1', '2500', 'PB006');
INSERT INTO nhan_vien VALUES('93', 'Le Tan', 'Dan', '1982-9-15', '184, Le Duan, Binh Duong', '0', '800', 'PB007');
INSERT INTO nhan_vien VALUES('94', 'Ly Dinh', 'Mui', '1985-1-6', '8, Bui Thi Xan, Hau Giang', '1', '1500', 'PB002');
INSERT INTO nhan_vien VALUES('95', 'Tran Qui', 'Thin', '1981-11-13', '65, Nguyen Hue, Cao Bang', '1', '700', 'PB002');
INSERT INTO nhan_vien VALUES('96', 'Truong Ky', 'Tuat', '1973-11-25', '153, Le Duan, Cao Bang', '0', '700', 'PB002');
INSERT INTO nhan_vien VALUES('97', 'Le Nham', 'Mui', '1981-12-27', '139, Le Duan, Hue', '0', '900', 'PB005');
INSERT INTO nhan_vien VALUES('98', 'Nguyen Qui', 'Dau', '1996-7-10', '166, Ho Xuan Huong, Ha Tay', '1', '850', 'PB007');
INSERT INTO nhan_vien VALUES('99', 'Van Dinh', 'Thin', '1997-8-12', '192, Le Van Tam, Ha Noi', '1', '2500', 'PB004');
INSERT INTO nhan_vien VALUES('100', '', 'Ty', '1973-2-21', '133, Pham Van Dong, Hue', '1', '2000', 'PB007');

INSERT INTO du_an VALUES('DA001', 'Resort Long Hai', '2001-02-24', '2012-05-11');
INSERT INTO du_an VALUES('DA002', 'Ho Tram', '2001-08-15', '2013-01-23');
INSERT INTO du_an VALUES('DA003', 'Khach San Sai Gon', '2010-04-14', '2017-07-01');
INSERT INTO du_an VALUES('DA004', 'Ba Ria - Vung Tau', '2001-02-20', '2016-01-19');
INSERT INTO du_an VALUES('DA005', 'Coco Bay Da Nang', '2006-10-22', '2012-03-26');
INSERT INTO du_an VALUES('DA006', 'The Sun City', '2005-03-01', '2016-02-12');
INSERT INTO du_an VALUES('DA007', 'Vi Thanh Hau Giang', '2003-01-01', '2021-10-24');
INSERT INTO du_an VALUES('DA008', 'Dong Ha', '2006-11-21', '2018-02-04');
INSERT INTO du_an VALUES('DA009', 'Hoi An Quang Nam', '2004-10-23', '2017-02-09');
INSERT INTO du_an VALUES('DA010', 'Resort Binh Chau 1', '2006-09-02', '2019-03-27');
INSERT INTO du_an VALUES('DA011', 'Resort Binh Chau 4', '2000-05-19', '2013-03-02');
INSERT INTO du_an VALUES('DA012', 'Ho Tram 1', '2002-08-19', '2013-04-15');
INSERT INTO du_an VALUES('DA013', 'Ho Tram 2', '2008-05-04', '2013-12-04');
INSERT INTO du_an VALUES('DA014', 'Ho Tram 3', '2002-08-05', '2013-09-14');
INSERT INTO du_an VALUES('DA015', 'Ho Tram 4', '2008-09-22', '2012-10-02');
INSERT INTO du_an VALUES('DA016', 'Khach San Sai Gon 1', '2000-12-17', '2012-03-25');
INSERT INTO du_an VALUES('DA017', 'Khach San Sai Gon 2', '2005-12-02', '2017-12-04');
INSERT INTO du_an VALUES('DA018', 'Khach San Sai Gon 3', '2006-02-20', '2015-11-18');
INSERT INTO du_an VALUES('DA019', '', '2005-12-19', '2012-12-26');
INSERT INTO du_an VALUES('DA020', '', '2002-10-04', '2021-03-15');

INSERT INTO quanly_duan VALUES('DA001', '19', '2001-2-24', '2012-5-11', '4094', 'Quan Ly');
INSERT INTO quanly_duan VALUES('DA002', '17', '2001-8-15', '2013-1-23', '4179', 'Worker');
INSERT INTO quanly_duan VALUES('DA003', '12', '2010-4-14', '2017-7-1', '2635', 'Tester');
INSERT INTO quanly_duan VALUES('DA004', '14', '2001-2-20', '2016-1-19', '5446', 'Nhan Vien Thiet Ket');
INSERT INTO quanly_duan VALUES('DA005', '11', '2006-10-22', '2012-3-26', '1982', 'Quan Ly');
INSERT INTO quanly_duan VALUES('DA006', '10', '2005-3-1', '2016-2-12', '4000', 'Tester');
INSERT INTO quanly_duan VALUES('DA007', '15', '2003-1-1', '2021-10-24', '6871', 'Tester');
INSERT INTO quanly_duan VALUES('DA008', '12', '2006-11-21', '2018-2-4', '4093', 'Quan Ly');
INSERT INTO quanly_duan VALUES('DA009', '11', '2004-10-23', '2017-2-9', '4492', 'Ky Su Trien Khai');
INSERT INTO quanly_duan VALUES('DA010', '14', '2006-9-2', '2019-3-27', '4589', 'Tester');
INSERT INTO quanly_duan VALUES('DA011', '16', '2000-5-19', '2013-3-2', '4670', 'Nhan Vien Thiet Ket');
INSERT INTO quanly_duan VALUES('DA012', '13', '2002-8-19', '2013-4-15', '3892', 'Ky Su Trien Khai');
INSERT INTO quanly_duan VALUES('DA013', '11', '2008-5-4', '2013-12-4', '2040', 'Tester');
INSERT INTO quanly_duan VALUES('DA014', '11', '2002-8-5', '2013-9-14', '4058', 'Quan Ly');
INSERT INTO quanly_duan VALUES('DA015', '12', '2008-9-22', '2012-10-2', '1471', 'Tester');
INSERT INTO quanly_duan VALUES('DA016', '20', '2000-12-17', '2012-3-25', '4116', 'Ky Su Trien Khai');
INSERT INTO quanly_duan VALUES('DA017', '16', '2005-12-2', '2017-12-4', '4385', 'Ky Su Trien Khai');
INSERT INTO quanly_duan VALUES('DA018', '15', '2006-2-20', '2015-11-18', '3558', 'Quan Ly');
INSERT INTO quanly_duan VALUES('DA019', '16', '2005-12-19', '2012-12-26', '2564', 'Worker');
INSERT INTO quanly_duan VALUES('DA020', '14', '2002-10-4', '2021-3-15', '6737', 'Nhan Vien Thiet Ket');

Tìm hiểu cách chèn dữ liệu ở bài 7

B. THỰC HIỆN TRUY VẤN

I. SELECT, CONCAT, AS, DISTINCT

1. Truy vấn hiển thị toàn bộ nội dung trong bảng nhan_vien

SELECT * 
  FROM nhan_vien;

2. Truy vấn hiển thị các cột: ho_nv, ten_nv, nam_sinh trong bảng nhan_vien

SELECT ho_nv, ten_nv, nam_sinh 
  FROM nhan_vien;

3. Truy vấn hiển thị các cột: ho_nv, ten_nv, nam_sinh trong bảng nhan_vien, gộp 2 côt ho_nv và ten_nv thành 1 cột

SELECT CONCAT(ho_nv, " ",ten_nv), nam_sinh 
  FROM nhan_vien;

CONCAT: Hàm thêm/nối hai hoặc nhiều chuỗi lại với nhau.

4. Truy vấn hiển thị các cột: ho_nv, ten_nv, nam_sinh trong bảng nhan_vien, gộp 2 côt ho_nv và ten_nv thành 1 cột và đặt bí danh cho cột là "Họ và Tên"

SELECT CONCAT(ho_nv, " ",ten_nv) AS "Họ và Tên", nam_sinh 
  FROM nhan_vien;

AS: Được sử dụng để đổi tên cột hoặc bảng bằng bí danh.

Bí danh chỉ tồn tại trong thời gian truy vấn.

5. Truy vấn hiển thị cột: ten_nv trong bảng nhan_vien, và loại bỏ các dòng trùng nhau

SELECT DISTINCT ten_nv 
  FROM nhan_vien;

SELECT DISTINCT: Chúng ta có thể hiểu là dùng để loại bỏ những kết quả trùng nhau hay được sử dụng để trả về các giá trị riêng biệt (khác nhau)

II. ORDER BY

ORDER BY Từ khóa được sử dụng để sắp xếp tập hợp kết quả theo thứ tự tăng dần ASC hoặc giảm dần DESC.

Noted: Nếu chúng ta ORDER BY <tên cột>  và sau đó không ghi ASC hay DESC thì mặc định cột đó sẽ sắp xếp theo kiểu tăng dần (A-Z)

1. Hiển thị danh sách các nhân gồm: ho_nv, ten_nv, luong và lương sắp xếp giảm dần theo cột luong

SELECT ho_nv, ten_nv, luong 
  FROM nhan_vien 
  ORDER BY luong DESC;

2. Hiển thị danh sách các nhân gồm: ho_nv, ten_nv, luong và lương sắp xếp giảm dần theo cột luong, nếu lương bằng nhau thì sắp xếp tăng dần (A->Z) của cột ten_nv

SELECT ho_nv, ten_nv, luong 
  FROM nhan_vien 
  ORDER BY luong DESC, ten_nv ASC;

III. WHERE

Hiển thị thông tin nhân viên có tên: "dan"

SELECT * 
  FROM nhan_vien 
  WHERE ten_nv ="dan";

IV. OR

1. Hiển thị thông tin nhân viên có phongban_id là: PB001 hoặc PB002

SELECT * 
  FROM nhan_vien 
  WHERE 
  		phongban_id ="PB001" 
  	 OR phongban_id ="PB002";

2. Hiển thị thông tin nhân viên có luong là: 800, 1000, 1500

SELECT * 
  FROM nhan_vien 
  WHERE luong = 800 
  	OR luong = 1000 
  	OR luong = 1500;

V. [NOT] IN

Hiển thị thông tin nhân viên có luong là: 800, 1000, 1500

SELECT * 
  FROM nhan_vien 
  WHERE luong IN (800, 1000,1500);

VI. AND

Hiển thị thông tin nhân viên có phongban_id là: PB001 hoặc PB002 và luong > 1000

SELECT * 
  FROM nhan_vien 
  WHERE (phongban_id ="PB001" OR phongban_id ="PB002") 
  	AND luong >1000;

VII. [NOT] BETWEEN … AND

Hiển thị nhân viên có mức lương trong khoảng 1000 đến 2000

SELECT * 
  FROM nhan_vien 
  WHERE luong BETWEEN 1000 AND 2000;

VIII. [NOT] LIKE

1. Hiển thị nhân viên có họ là: Tran

SELECT * 
  FROM nhan_vien 
  WHERE ho_nv LIKE "Tran%"; -- % đại diện cho MỘT CHUỖI ký tự bất kỳ

2. Hiển thị nhân viên có họ có chữ: Giap

SELECT * 
    FROM nhan_vien 
    WHERE ho_nv LIKE "%Giap%";

3. Hiển thị nhân viên có tên có chiều dài là 3 ký tự và bắt đầu là chữ D

SELECT * 
    FROM nhan_vien 
    WHERE ten_nv LIKE "D__"; -- _ đại diện cho MỘT KÝ TỰ bất kỳ

IX. IS [NOT] NULL

Hiện thị nhân viên chưa phân bổ bộ phận (căn cứ vào cột phongban_id)

SELECT * 
  FROM nhan_vien 
  WHERE phongban_id IS NULL;

X. AGGREGATE

1. Thống kê tổng số nhân viên

SELECT COUNT(id) 
    FROM nhan_vien;

2. Thống kê tổng số nhân viên, và đổi tên cột đếm thành "Tổng số nhân viên"

SELECT COUNT(id) AS "Tổng số nhân viên" 
    FROM nhan_vien;

3. Thống kê tổng số nhân viên nam/nữ

SELECT COUNT(id) 
    FROM nhan_vien 
    WHERE gioi_tinh = 1; -- tùy theo chúng ta quy định 0, 1 là nam hay nữ mà chúng ta chọn điều kiện cho thích hợp

4. Tìm nhân viên có mức lương cao nhất

SELECT MAX(luong) 
    FROM nhan_vien;

5. Tìm nhân viên có mức lương nhấp nhất

SELECT MIN(luong) 
    FROM nhan_vien;

6. Tính mức lương trung bình của nhân toàn công ty

SELECT AVG(luong) 
    FROM nhan_vien;

7. Tính tổng lương của nhân viên phòng ban có mã PB001

SELECT phongban_id, SUM(luong) 
    FROM nhan_vien 
    WHERE phongban_id = "PB001";

8. Hiển thị MIN, MAX, SUM, AVG của toàn thể nhân viên trên 1 dòng

SELECT MIN(luong), MAX(luong), SUM(luong), AVG(luong) 
    FROM nhan_vien;

XI. OPERATOR

1. Tính tuổi của nhân viên

SELECT ho_nv, ten_nv, YEAR(CURRENT_DATE()) - YEAR(nam_sinh) -- lấy năm hiện tại trừ đi năm sinh
    FROM nhan_vien;

2. Chỉ hiện thị các nhân viên có tuổi >30

SELECT ho_nv, ten_nv, YEAR(CURRENT_DATE()) - YEAR(nam_sinh) 
    FROM nhan_vien 
    WHERE YEAR(CURRENT_DATE()) - YEAR(nam_sinh) >30;

3. Tăng thêm 200$ cho các nhân viên có mức lương thấp hơn 900$

SELECT * , (luong + 200) 
  FROM nhan_vien 
  WHERE luong <=900;
  

XII. GROUP BY

Đếm số lượng nhân viên trong mỗi phòng

SELECT *, COUNT(id) 
  FROM nhan_vien 
  GROUP BY phongban_id;

XIII. HAVING

HAVING: Mệnh đề thường được sử dụng với mệnh đề GROUP BY lọc các nhóm dựa trên một danh sách các điều kiện được chỉ định.

1. Hiển thị tổng mức lương của mỗi phòng ban và chỉ hiển thị các nhóm/phòng có tổng lương lớn hơn 15000$

SELECT *, SUM(luong) AS tong_luong 
    FROM nhan_vien 
    GROUP BY phongban_id 
    HAVING tong_luong > 15000;

2. Tính tổng lương công ty phải trả cho mỗi phòng ban, chỉ nhóm hàng nào có cột lương >800$, chỉ hiển thị nhóm nào có tổng >10000$

SELECT *, SUM(luong) AS tong_luong 
    FROM nhan_vien 
    WHERE luong > 800 
    GROUP BY phongban_id 
    HAVING tong_luong > 10000;

XIV. LIMIT

     LIMIT offset, count

- offset: số dòng bỏ qua

- count: số dòng cần lấy

1. Lấy ra 10 nhân viên có mức lương thấp nhất

SELECT * 

    FROM nhan_vien  
    ORDER BY luong ASC 
    LIMIT 10;

Nếu chúng ta đưa vào 1 đối số, đối số này sẽ được gán cho count (số dòng cần lấy), trong trường hợp này offset = 0.

2. Lấy ra 2 nhân viên có mức lương thấp nhất của phongban_id PB001, và lấy từ vị trí thứ 6

SELECT * 
    FROM nhan_vien 
    WHERE phongban_id = "PB001" 
    ORDER BY luong ASC 
    LIMIT 5, 2;

TỔNG HỢP

1. Hiển thị mã phòng ban (phongban_id) và lương trung bình tương ứng từng phòng, chỉ lấy các hàng có mức lương trên 800$, chỉ hiển thị các phòng có lương trung bình > 1200$ và sắp xếp theo thứ tự giảm dần theo cột phongban_id và chỉ lấy 3 dòng

SELECT phongban_id, AVG(luong) AS tb_luong 
  FROM nhan_vien 
  WHERE luong > 800 
  GROUP BY phongban_id 
  HAVING tb_luong > 1200 
  ORDER BY phongban_id DESC 
  LIMIT 3;

1.      2. Hiển thị họ tên, giới tính của nhân viên, nếu giới tính = 1 thì hiển thị là nam, ngược lại là nữ 

SELECT 
    CONCAT(ho_nv, ' ', ten_nv) AS 'Họ và Tên',
    IF(gioi_tinh = 1, 'Nam', 'Nữ') AS 'Giới Tính'
FROM
    nhan_vien

THỨ TỰ THỰC HIỆN CÁC CÂU LỆNH TRONG SQL

Query Process Steps

1. Getting Data (From, Join)

2. Row Filter (Where)

3. Grouping (Group by)

4. Group Filter (Having)

5. Return Expressions (Select)

6. Order & Paging (Order by & Limit / Offset)

Xong!