Ngôn Ngữ SQL Truy Vấn Có Gom Nhóm | Thư Viện Hướng Dẫn

A. Các hàm tính toán

SQL sử dụng các hàm sau: Count, Max, Min, Sum, Avg.

Hàm Count dùng đối số * có nghĩa là đếm tất cả các mẫu tin thỏa điều kiện đếm mà không cần quan tâm đến bất kỳ cột nào.

. Có tất cả bao nhiêu sinh viên trong lớp th01

SELECT COUNT(*) FROM SINHVIEN WHERE LOP='TH01'

B.Mệnh đề group by

Dùng để gom nhóm dữ liệu, thường dùng kết hợp với một hàm tính toán kể trên.

.Tính điểm trung bình của từng sinh viên, biết rằng điểm số lưu trong bảng KETQUA(MASV, MAMH, DIEM)

SELECT MASV, AVG(DIEM) FROM KETQUA GROUP BY MASV

.cho biết lương lớn nhất trong từng phòng ban

NHANVIEN(MANV, TENNV, PHAI, LUONG, PHG)

PHONGBAN(MAPB, TENPB, TRPHG)

THANNHAN(MA_NVIEN, TENTN, PHAI, QUANHE)

SELECT PHG, MAX (LUONG) FROM NHANVIEN GROUP BY PHG

C. Mệnh đề having

Mệnh đề HAVING thường được sử dụng cùng với mệnh đề GROUP BY.

Sau HAVING là biểu thức điều kiện. Biểu thức điều kiện này không tác động vào toàn bảng được chỉ ra ở mệnh đề from mà chỉ tác động lần lượt từng nhóm các mẫu tin đã chỉ ra trong mệnh đề group by.

.Cho biết các sinh viên có điểm trung bình lớn hơn hoặc bằng 8.0

SELECT MASV, AVG (DIEM) FROM KETQUA GROUP BY MASV HAVING AVG(DIEM) >= 8.0

Truy vấn lồng

A. Tìm kiếm có lượng từ EXISTS, ANY và ALL

Cho danh sách các nhân viên có ít nhất 1 thân nhân.

SELECT MANV, TENNV FROM NHANVIEN WHERE EXISTS (SELECT TENTN FROM THANNHAN WHERE THANNHAN.MA_NVIEN= NHANVIEN.MANV)

HOẶC CÓ THỂ VIẾT CÁCH KHÁC NHƯ SAU:

SELECT MANV, TENNV FROM NHANVIEN WHERE ( SELECT COUNT (*) FROM THANNHAN WHERE THANNHAN.MA_NVIEN=NHANVIEN.MANV) >0

LƯU Ý = ANY TƯƠNG ĐƯƠNG VỚI TOÁN TỬ IN . CHO BIẾT NHÂN VIÊN CÓ LƯƠNG LỚN NHẤT

SELECT MANV, LUONG FROM NHANVIEN WHERE LUONG >=ALL (SELECT LUONG FROM NHANVIEN)

HOẶC CÓ THỂ VIẾT CÁCH KHÁC NHƯ SAU:

SELECT MANV, LUONG FROM NHANVIEN WHERE LUONG = (SELECT MAX (LUONG) FROM NHANVIEN)

. CHO BIẾT SINH VIÊN CÓ ĐIỂM TRUNG BINH LỚN NHẤT

SELECT MASV, AVG (DIEM) FROM KQTQUA GROUP BY MASV WHERE AVG(DIEM) >= ALL (SELECT AVG(DIEM) FROM KETQUA WHERE KETQUA GROUP BY MASV)

CÓ 2 LOẠI TRUY VẤN LỒNG

B LOẠI 1: LỒNG PHÂN CẤP

Mệnh đề WHERE của truy vấn con không tham chiếu đến thuộc tính của các quan hệ trong mệnh đề FROM ở truy vấn cha

Khi thực hiện, câu truy vấn con sẽ được thực hiện trước

Ví dụ:

Cho biết các nhân viên cùng phòng với nhân viên “Nguyễn Văn A”

SELECT MANV, TENNV FROM NHANVIEN WHERE PHG IN (SELECT PHG FROM NHANVIEN WHERE TENNV= 'Nguyễn Văn A')

. Tìm những nhân viên có lương lớn hơn lương của tất cả nhân viên ở phòng 4.

SELECT MANV, TENNV FROM NAHNVIEN WHERE LUONG > ( SELECT MAX(LUONG) FROM NHANVIEN WHERE PHG=4)

.phòng ban có đông nhân viên nhất (gom nhóm + truy vấn lồng phân cấp)

SELECT PG.TENPHG, COUNT(*) AS SOLUONG FROM NHANVIEN NV, PHONGBAN PB WHERE NV.PHG=PB.MAPHG HAVING COUNT(*) >= ALL( SELECT COUNT(*) FROM NHANVIEN GROUP BY PHG)

.Cho biết họ tên nhân viên (HONV, TENLOT, TENNV) có mức lương lớn hơn mức lương của một nhân viên nào đó của phòng “Nghiên cứu”

SELECT HONV, TENLOT, TENNV, LUONG,PHG FROM NHANVIEN WHERE LUONG > ANY ( SELECT NV.LUONG FROM NAHNVIEN NV, PHONGBAN PB WHERE NV.PHG=PB.MAPHG AND PB.TENPHG=N'Nghiên cứu')

C. Loại 2: Lồng tương quan

Mệnh đề WHERE của truy vấn con tham chiếu ít nhất một thuộc tính của Các quan hệ trong mệnh đề FROM ở truy vấn cha.

Khi thực hiện, câu truy vấn con sẽ được thực hiện nhiều lần, mỗi lần tương ứng với một bộ của truy vấn cha.

Ví dụ:

Tìm những nhân viên không có thân nhân nào:

SELECT MANV, TENNV FROM NHANVIEN N WHERE NOT EXISTS (SELECT * FROM THANNHAN T WHERE T.MANV=N.MANV)

.Tìm tất cả các nhân viên làm việc ở phòng nghiên cứu

SELECT MANV, TENNV FROM NHANVIEN N WHERE EXISTS (SELECT * FROM PHONGBAN P WHERE TENPG= 'Nghiên cứu' AND P.MAPHG=N.PHG) ### `III. Phép chia ` Có 2 cách thực hiện: Cách 1: Sử dụng NOT EXISTS + NOT IN hoặc NOT EXISTS + NOT EXISTS Cách 2: Sử dụng mệnh đề GROUP BY + HAVING VD: Tìm nhân viên được phân công làm việc trong tất cả các đề án do Phòng Nghiên cứu quản lí Cách 1: Sử dụng NOT EXISTS + NOT IN ```SQL SELECT N.MANV, N.TENNV FROM NHANVIEN N WHERE NOT EXISTS ( SELECT * FROM PHONGBAN PB, DEAN D WHERE D.PHONG=PB.MAPHG AND PB.TENPHG = 'Nghiên cứu' AND D.MADA NOT IN ( SELECT PC.MADA FROM PHANCONG PC WHERE PC.MA_NVIEN = N.MANV) )

SỬ DỤNG NOT EXISTS + NOT EXISTS

SELECT N.MANV, N.TENNV FROM NHANVIEN N WHERE NOT EXISTS ( SELECT * FROM PHONGBAN PB, DEAN D WHERE D.PHONG= PB.MAPHG AND PB.TENPHG ='Nghiên cứu' AND NOT EXISTS ( SELECT * FROM PHANCONG PC WHERE PC.MA_NVIEN =N.MANV AND PC.MADA = D.MADA ) )

Sử dụng GROUP BY + HAVING

SELETC N.MANV, N.TENNV FROM NHANVIEN N, PHANCONG PC. PHONGBAN PB1, DEAN D1 WHERE N.MANV =PC.MA_NVIEN AND PC.MADA =D1=MADA AND D1.PHONG =PB1.MAPHG AND PB1.TENPHG = 'Nghiên cứu' GROUP BY N.MANV, N.TENNV HAVING COUNT (DISTINCT PB.MADA) = (SELECT COUNT (DISTINCT D2.MADA) WHERE DEAN D2, PHONGBAN PB2 WHERE D2.PHONG = PB2. MAPHG AND PB2.TENPHG = 'Nghiên cứu')

IV. Các loại truy vấn khác

A. Truy vấn con ở mệnh đề SELECT

Với mỗi nhân viên, cho biết họ, tên nhân viên và số thân nhân của họ

SELECT HONV, TENLOT, TENNV, ( SELECT COUNT(*) FROM THANNHAN WHERE MA_NVIEN = NV.MANV) AS SOTN FROM NHANVIEN NV

B. Truy vấn con ở mệnh đề FROM

KẾt quả trả về của một câu truy vấn phụ là một bảng

• Bảng trung gian trong quá trình truy vấn

• Không có lưu trữ thật sự

VÍ DỤ

Cho biết những phòng ban (TENPHG) có lương trung bình của các nhân viên lớn lơn 20000

SELECT TENPHG, TEMP.LUONG_TB FROM PHONGBAN, (SELECT PHG, AVG(LUONG) AS LUONG_TB FROM NHANVIEN GROUP BY PHG HAVING AVG(LUONG)>2000) AS TEMP WHERE MAPHG=TEMP.PHG

C. Điều kiện kết ở mệnh đề FROM

Tìm mã và tên các nhân viên làm việc tại phòng ‘Nghien cuu’

SELECT * FROM NHANVIEN INNER JOIN PHONGBAN ON PHG=MAPHG WHERE TENPHG='Nghiên cứu'

Tìm họ tên các nhân viên và tên các đề án nhân viên tham gia nếu có

SELECT * FROM (PHANCONG PC JION DEAN DA O SODA=MADA) RIGHT JION NHANVIEN NV ON PC.MA_NVIEN=NV.MANV) #### `Cấu trúc Case ` Cho biết họ tên các nhân viên đã đến tuổi về hưu (nam 60 tuổi, nữ 55 tuổi) ```SQL SELECT * FROM NHANVIEN WHERE YEAR (GETDATE()) -YEAR(NGSINH)>= ( CASE PHAI WHEN 'Nam' THEN 60 WHEN 'Nu' THEN 55 END)