Bài giảng Cơ sở dữ liệu - Chương 9: SQL nâng cao - Trịnh Hoàng Nam

9/3/2018
1
SQL NÂNG CAO
CƠ SỞ DỮ LIỆU
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
GIÁ TRỊ NULL
• Null là giá trị đặc biệt, không phải là hằng số, mà là giá trị không
biết
Định nghĩa
• Các phép toán số học liên quan tới Null cho giá trị là Null
• Các phép toán so sánh liên quan tới Null cho giá trị là Unknown
• Phép toán so sánh IS NULL và IS NOT NULL
Đặc trưng
• Liệt kê danh sách nhân viên không có lãnh đạo trực tiếp
Ví dụ
SELECT
FROM
WHERE
EMPLOYEE
supervisorSSN IS NULL
*
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
TOÁN TỬ AS
• Được sử dụng để đổi tên cột hiển thị trong phần kết
quả của câu truy vấn mà không ảnh hưởng đến bản
thân các quan hệ trong mệnh đề WHERE của câu
truy vấn đó
Toán tử AS trong SQL
• Liệt kê danh sách nhân viên và tên lãnh đạo trực tiếp
Ví dụ
SELECT E.EName AS ‘Tên nhân viên’, 
S.EName AS ‘Tên lãnh đạo trực tiếp’
FROM EMPLOYEE E, EMPLOYEE S
WHERE E.supervisorSSN=S.ESSN
9/3/2018
2
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
TRUY VẤN LỒNG
• Câu lệnh truy vấn chứa câu lệnh truy vấn khác bên
trong các thành phần của nó
Định nghĩa
• Kết quả trả về của câu truy vấn này được sử dụng
trong một mệnh đề của câu truy vấn khác
• Kết quả trả về của một câu truy vấn có thể là một giá
trị đơn hoặc một quan hệ với nhiều bộ, nhiều thuộc
tính
• Một số toán tử so sánh: IN, NOT IN, ANY, SOME, ALL
Đặc trưng
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
TRUY VẤN LỒNG
• Cho biết danh sách các dự án có sự tham gia của
nhân viên tên là ‘Lê Duy Anh’
Ví dụ
SELECT
FROM
WHERE
PROJECT p, EMPLOYEE e, WORKSON w
p.ProNum = w.PNum AND w.ESSN = e.ESSN
AND e.EName = N’Lê Duy Anh’
p.*
?
Độ phức tạp của truy vấn
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
TRUY VẤN LỒNG
• Cho biết danh sách các dự án có sự tham gia của
nhân viên tên là ‘Lê Duy Anh’
Ví dụ
?
Độ phức tạp của truy vấn
(SELECT w.PNum
FROM WORKSON w, EMPLOYEE e
WHERE w.ESSN=e. ESSN
AND e.EName = N’Lê Duy Anh’)
SELECT *
FROM PROJECT
WHERE PNumber IN
9/3/2018
3
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
TRUY VẤN LỒNG
• Cho biết danh sách các dự án có sự tham gia của
nhân viên tên là ‘Lê Duy Anh’
Ví dụ
?
Độ phức tạp của truy vấn
(SELECT w.PNum
FROM WORKSON w
WHERE w.ESSN IN
)
SELECT *
FROM PROJECT
WHERE PNumber IN
(SELECT e.ESSN
FROM EMPLOYEE e
WHERE e.EName = N’’)
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
TRUY VẤN LỒNG
• Được sử dụng để kiểm tra kết quả của câu truy vấn lồng
xem có rỗng hay không
Hàm EXISTS trong SQL
• Cho biết tên các nhân viên chưa tham gia bất kỳ dự án nào 
của công ty
Ví dụ
SELECTEName
FROM EMPLOYEE E
WHERE NOT EXISTS (SELECT *
FROM WOKRSON
WHERE E.ESSN=ESSN)
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
TRUY VẤN LỒNG
• Được sử dụng để kiểm tra kết quả của câu truy vấn lồng xem
có rỗng hay không
• Có thể được viết lại bằng cách sử dụng toán tử so sánh IN
Hàm EXISTS trong SQL
• Cho biết tên các nhân viên chưa tham gia bất kỳ dự án nào 
của công ty
Ví dụ
SELECTEName
FROM EMPLOYEE
WHERE ESSN NOT IN (SELECT ESSN
FROM WOKRSON)
9/3/2018
4
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
BẢNG KẾT TRONG SQL
• Phép kết thể hiện thông qua việc sử dụng
nhiều quan hệ trong mệnh đề FROM và các
điều kiện kết tương ứng trong mệnh đề
WHERE
• Phép kết được thực hiện ngay trong mệnh
đề FROM thông qua việc sử dụng các toán
tử INNER JOIN, OUTER JOIN đi kèm điều
kiện kết tương ứng
Giới thiệu
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
BẢNG KẾT
• Hiển thị tên và mức lương của các nhân viên trong
phòng ‘Nghiên cứu’
• Sử dụng phép nhân chéo và phép chọn:
• Sử dụng bảng kết:
Ví dụ
SELECTEName, ESalary
FROM EMPLOYEE, DEPARTMENT
WHERE DNum = DNumber AND DName=N’Phòng Nghiên cứu’
SELECTEName, ESalary
FROM EMPLOYEE JOIN DEPARTMENT ON DNum = DNumber
WHERE DName=N’Phòng Nghiên cứu’
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
BẢNG KẾT
Ví dụ
SELECTEName, ESalary
FROM EMPLOYEE, DEPARTMENT
WHERE DNum = DNumber 
AND DName=N’Phòng Nghiên cứu’
SELECTEName, ESalary
FROM EMPLOYEE JOIN DEPARTMENT
ON DNum = DNumber
WHERE DName=N’Phòng Nghiên cứu’
9/3/2018
5
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
BẢNG KẾT
• Với mỗi nhân viên hãy hiển thị tên của người đó và
tên của lãnh đạo trực tiếp của họ
• Sử dụng phép nhân chéo và phép chọn:
• Sử dụng bảng kết:
Ví dụ
SELECTe.EName as ‘Employee’, s.EName as ‘Supervisor’ 
FROM EMPLOYEE e, EMPLOYEE s
WHERE e.supervisorSSN = s.ESSN
SELECT e.EName as ‘Employee’, s.EName as ‘Supervisor’
FROM EMPLOYEE e JOIN EMPLOYEE s 
ON e.supervisorSSN = s.ESSN
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
BẢNG KẾT
Ví dụ
SELECTe.EName as ‘Employee’, 
s.EName as ‘Supervisor’ 
FROM EMPLOYEE e, EMPLOYEE s
WHERE e.supervisorSSN = s.ESSN
SELECT e.EName as ‘Employee’, 
s.EName as ‘Supervisor’
FROM EMPLOYEE e JOIN EMPLOYEE s 
ON e.supervisorSSN = s.ESSN
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
BẢNG KẾT
• Với mỗi nhân viên hãy hiển thị tên của người đó và
tên của lãnh đạo trực tiếp của họ
• Tuy nhiên, trong bảng EMPLOYEE có tất cả 21 bộ với
15 bộ có giá trị khác NULL tại thuộc tính supervisor
ứng với 15 bộ trả về bởi câu truy vấn
• Làm thế nào để hiển thị hết đầy đủ 21 bộ của quan hệ
EMPLOYEE theo yêu cầu đề bài?
Ví dụ
SELECT e.EName as ‘Employee’, 
s.EName as ‘Supervisor’
FROM EMPLOYEE e JOIN EMPLOYEE s 
ON e.supervisorSSN = s.ESSN
9/3/2018
6
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
BẢNG KẾT
• Với mỗi nhân viên hãy hiển thị tên của người đó và
tên của lãnh đạo trực tiếp của họ
• Sử dụng bảng kết trong:
• Sử dụng bảng kết ngoài
Ví dụ
SELECT e.EName as ‘Employee’, s.EName as ‘Supervisor’
FROM EMPLOYEE e JOIN EMPLOYEE s 
ON e.supervisorSSN = s.ESSN
SELECT e.EName as ‘Employee’, s.EName as ‘Supervisor’
FROM EMPLOYEE e LEFT JOIN EMPLOYEE s 
ON e.supervisorSSN = s.ESSN
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
BẢNG KẾT
Ví dụ
SELECT e.EName as ‘Employee’, 
s.EName as ‘Supervisor’
FROM EMPLOYEE e LEFT JOIN EMPLOYEE s 
ON e.supervisorSSN = s.ESSN
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
BẢNG KẾT
• Với mỗi dự án tại ‘TP. Hồ Chí Minh’, liệt kê số hiệu dự
án, số hiệu đơn vị quản lý dự án, và tên, ngày sinh
của trưởng đơn vị quản lý dự án
Ví dụ
SELECT PNumber, DNumber, EName, EBirthdate
FROM ((PROJECT JOIN DEPARTMENT ON dNum=DNumber)
JOIN EMPLOYEE ON mgrSSN=ESSN)
WHERE PLocation = N’TP Hồ Chí Minh’
9/3/2018
7
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
CÁC HÀM THỐNG KÊ TRONG SQL
• Các hàm số học được thực hiện theo từng nhóm
• Các hàm MAX, MIN, AVG, SUM thực hiện trên một thuộc tính
số học
• Hàm COUNT thực hiện trên một hoặc nhiều thuộc tính
Các hàm thống kê (kết hợp)
• Cho biết tổng lương, lương trung bình của nhân viên trong
công ty
Ví dụ
SELECTSUM(ESalary) AS ‘Tổng lương’, 
AVG(ESalary) AS ‘Lương trung bình’
FROM EMPLOYEE
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
CÁC HÀM THỐNG KÊ TRONG SQL
• Cho biết tổng lương, mức lương cao nhất, mức lương
thấp nhất, và của các nhân viên tham gia dự án có
mã số là 1
Ví dụ
SELECTSUM(E.ESalary), MAX(E.ESalary), MIN(E.ESalary) 
FROM (EMPLOYEE E JOIN WORKSON W ON E.ESSN=W.ESSN)
WHERE W.PNUM = 1
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
MỆNH ĐỀ GOM NHÓM
• Gom các bộ trong quan hệ thành từng nhóm theo một
(số) thuộc tính
• Các bộ có giá trị bằng nhau tại (các) thuộc tính gom
nhóm sẽ được xếp ở gần nhau
• Điều kiện để chọn các nhóm được đặt trong mệnh đề
HAVING
• Các hàm kết hợp được áp dụng cho các nhóm sau
khi đã gom
GROUP BY và HAVING trong SQL
9/3/2018
8
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
MỆNH ĐỀ GOM NHÓM
• Với mỗi phòng ban hãy cho biết mã số phòng ban, số
nhân viên thuộc quyền quản lý của phòng ban đó, và
mức lương trung bình của họ
Ví dụ
SELECT DNum, COUNT(*), AVG(ESalary)
FROM EMPLOYEE
GROUP BY DNum
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
MỆNH ĐỀ GOM NHÓM
• Với mỗi dự án, cho biết mã số dự án, tên dự án, số
nhân viên tham gia dự án đó
Ví dụ
SELECT p. PNumber, p. PName, COUNT(*)
FROM PROJECT p, WORKSON w
WHERE p.PNumber = w.PNum
GROUP BY p. PNumber, p.PName
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
MỆNH ĐỀ GOM NHÓM
• Với mỗi dự án có nhiều hơn hai nhân viên tham gia,
cho biết mã số dự án, tên dự án, và số nhân viên
tham gia dự án đó
Ví dụ
SELECT p.PNumber, p.PName, COUNT(*)
FROM PROJECT p, WORKSON w
WHERE p.PNumber = w.PNum
GROUP BY p.PNumber, p.PName
HAVING COUNT(*) > 2
9/3/2018
9
Ths. Trịnh Hoàng Nam, namth@buh.edu.vn
C
ơ
s
ở
d
ữ
l
i
ệ
u
–
T
ổ
n
g
q
u
a
n
h
ệ
t
h
ố
n
g
c
ơ
s
ở
d
ữ
l
i
ệ
u
CÂU TRUY VẤN SQL TỔNG QUÁT
Công thức chung của câu truy vấn SQL
SELECT 
FROM 
[WHERE ]
[GROUP BY ]
[HAVING ]
[ORDER BY ]