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!