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