Ngôn ngữ SQL Truy vấn có gom nhóm

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)

Chủ đề liên quan
Ngôn ngữ SQL Truy vấn có gom nhóm

Cùng chuyên mục

Xem nhiều hôm nay