Sharing & Linking: [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

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!

Sharing & Linking

Pages

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

Để có những bài viết tốt hơn, hãy cho chúng tôi bình luận của bạn về bài viết này. Cám ơn các bạn!

No comments:

Post a Comment