Bài giảng Hệ quản trị cơ sở dữ liệu (Oracle) - Chương 3: Truy vấn SQL - Nguyễn Thị Mỹ Dung

] LIKE: so sánh chuỗi 
  BETWEEN AND: so sánh trong khoảng 
  IS [NOT] NULL: tìm các bộ là (không) rỗng. 
  [NOT] IN: tìm trong/ ngoài danh sách 
 - Các ký tự so sánh đại diện đối với chuỗi: ‘%’ (nhiều 
ký tự), ‘_’ (một ký tự). 
 23 
 Truy vấn Select (tt) 
 Select nhiều bảng 
 SELECT * | 
 FROM [bídanh1] 
 [bídanh2] ON () 
 [WHERE[AND|OR] 
 Trong đó : 
 JOIN: Kết nối trong với cột chỉ định 
 LEFT OUTER JOIN: kết trái 
 RIGHT OUTER JOIN: kết phải 
 FULL OUTER JOIN: kết đầy đủ 
 NATURAL JOIN: kết tự nhiên 
 CROSS JOIN: kết liên bảng cho phép kết tất 
cả các bộ có thể có (tương tự phép kết tích đề-các), khi 
đó không cần bất kỳ điều kiện kết nối nào. 
 24 
 Truy vấn Select (tt) 
 Select trên tập hợp 
 UNION [ALL]() | INTERSECT () | MINUS(-) 
 SELECT 
 FROM 
 [WHERE ] 
 UNION | [Intersect] | [Minus] 
 SELECT 
 FROM 
 [WHERE ] 
 25 
 Bài tập select 
1. In ra mã số, họ tên của tất cả sinh viên 
2. In ra mã số, họ tên của tất cả sinh viên từ 30 tuổi trở 
 lên. 
3. Tìm họ tên sinh viên thực hiện tên đề tài, kết quả từ 
 9 trở lên. 
4. Tìm họ tên của tất cả sinh viên thực hiện đề tài (có 
 thể có sinh viên không thực hiện đề tài) gồm, họ tên 
 sinh viên, mã đề tài, kết quả. 
5. Tìm tất cả họ tên chủ nhiệm và sinh viên 
6. Tìm tất cả MASV có quê quán khác với nơi áp dụng 
 đề tài. 
7. Xóa tất cả những sinh viên chưa đăng ký đề tài nào. 
 26 
 Truy vấn Select (tt) 
 3. Update 
 UPDATE 
 SET = ,, 
 = , 
 [FROM ] 
 [WHERE ] 
Ví dụ: 
 UPDATE SINHVIEN 
 SET HOCLUC = 6.2, NAMSINH = 1991 
 WHERE MASV = ‘SV006’ 
 27 
 Truy vấn Select (tt) 
 4. Delete 
 Dạng 1: Xóa một số bộ từ bảng 
 DELETE FROM 
 -- Nếu đk có trong bảng khác 
 [FROM ] 
 [WHERE ] 
 Dạng 2: Xóa tất cả các bộ từ bảng 
 TRUNCATE TABLE 
 VD: Xóa những môn học dưới 20 tiết 
 DELETE FROM MON 
 WHERE SOTIET < 20 
 28 
 Delete (tt) 
 Lưu ý: 
 - Câu lệnh Delete sẽ xóa tất cả dữ liệu trên 
bảng FROM gần nó nhất. 
 - Nếu có nhiều điều kiện kết hợp nhiều bảng 
phải sử dụng truy vấn lồng. 
 VD: Xoá những sinh viên có học lực dưới 5 và kết 
quả thực hiện đề tài < 5. 
 DELETE FROM SINHVIEN 
 WHERE EXISTS (SELECT MASV FROM SVDT 
 WHERE SINHVIEN.MASV = SVDT.MASV AND 
 HOCLUC < 5 AND KETQUA < 5); 
 29 
 Truy vấn Select (tt) 
 Nhận xét: 
 - Các lệnh UPDATE, DELETE có thể gây ra vi 
phạm RBTV (không cho sửa, xóa, hoặc xóa luôn 
các dòng dữ liệu tham chiếu hoặc dữ liệu tham 
chiếu sẽ là Null). 
 - Điều kiện trong mệnh đề WHERE sẽ được 
thực hiện cho các dòng thỏa điều kiện, nếu không 
có mệnh đề WHERE toàn bộ bảng sẽ có ảnh hưởng. 
 - Tránh vi phạm RBTV khi xóa, nên xóa các dữ 
liệu bảng nhiều trước, sau đó xóa bảng một. 
 30 
 Bài tập Select 
 Tìm tên và địa chỉ liên lạc các chủ thầu thi công 
công trình ở Cần Thơ 
CGTRINH (STT_CTR, TEN_CTR, DIACHI_CTR, 
 TINH_THANH, KINH_PHI, TEN_CHU, TEN_THAU, 
 NGAY_BD) 
CHUTHAU (TEN_THAU, TEL, DCHI_THAU) 
SQL: 
Select TEN_THAU, DCHI_THAU 
From CGTRINH CT , CHUTHAU T 
Where CT.TEN_THAU = T.TEN_THAU and 
 TINH_THANH = ‘Cần Thơ '; 
 31 
 Bài tập Select (tt) 
 1. Tìm tên và địa chỉ liên lạc của các chủ thầu thi 
công công trình ở Cần Thơ do kiến trúc sư Lê Kim 
Dung thiết kế. 
 THIETKE (HOTEN_KTS, STT_CTR, THU_LAO) 
CGTRINH (STT_CTR, TEN_CTR, DIACHI_CTR, 
TINH_THANH, KINH_PHI, TEN_CHU, 
TEN_THAU, NGAY_BD) 
 CHUTHAU (TEN_THAU, TEL, DCHI_THAU) 
 SQL: ??? 
 32 
 Bài tập Select (tt) 
 2. Tìm nơi tốt nghiệp của kts thiết kế Khách sạn 
Quốc tế Cần Thơ? 
 KTRUCSU (HOTEN_KTS, NAMS_KTS, PHAI, 
NOI_TN, DCHI_LL_KTS) 
 THIETKE (HOTEN_KTS, STT_CTR, THU_LAO) 
 CGTRINH (STT_CTR, TEN_CTR, 
DIACHI_CTR, TINH_THANH, KINH_PHI, 
TEN_CHU, TEN_THAU, NGAY_BD) 
 SQL: ??? 
 33 
 Bài tập Select (tt) 
 3. Tìm tên, năm sinh, năm vào nghề của công nhân 
có chuyên môn hàn hoặc điện tham gia công trình của 
chủ thầu Lê Văn Sơn? 
 CONGNHAN (HOTEN_CN, NAMS_CN, 
NAM_VAO_N, CH_MON) 
 THAMGIA (HOTEN_CN, STT_CTR, NGAY_TGIA, 
SO_NGAY) 
 CGTRINH (STT_CTR, TEN_CTR, DIACHI_CTR, 
TINH_THANH, KINH_PHI, TEN_CHU, TEN_THAU, 
NGAY_BD) 
 SQL??? 
 34 
 Bài tập Select (tt) 
 4. Tìm tên công nhân tham gia công trình Khách 
sạn Quốc tế Cần Thơ ở TP. Cần Thơ trong thời gian 
từ 15/12/1994 đến 31/12/1994? 
 THAMGIA (HOTEN_CN, STT_CTR, 
NGAY_TGIA, SO_NGAY) 
 CGTRINH (STT_CTR, TEN_CTR, DIACHI_CTR, 
TINH_THANH, KINH_PHI, TEN_CHU, TEN_THAU, 
NGAY_BD) 
 SQL??? 
 35 
 Bài tập Select (tt) 
 5. Tìm tên và địa chỉ công trình mà công nhân 
Nguyễn Hồng Vân tham gia vào ngày 18/12/1994? 
 THAMGIA (HOTEN_CN, STT_CTR, 
NGAY_TGIA, SO_NGAY) 
 CGTRINH (STT_CTR, TEN_CTR, DIACHI_CTR, 
TINH_THANH, KINH_PHI, TEN_CHU, TEN_THAU, 
NGAY_BD) 
 SQL??? 
 36 
 Bài tập Select (tt) 
 6. Tìm tên và năm sinh của kiến trúc sư tốt 
nghiệp ở tp HCM và thiết kế ít nhất 1 công trình có 
kinh phí >400? 
 KTRUCSU (HOTEN_KTS, NAMS_KTS, PHAI, 
NOI_TN, DCHI_LL_KTS) 
 THIETKE (HOTEN_KTS, STT_CTR, THU_LAO) 
 CGTRINH (STT_CTR, TEN_CTR, DIACHI_CTR, 
TINH_THANH, KINH_PHI, TEN_CHU, TEN_THAU, 
NGAY_BD) 
 SQL??? 
 37 
 III. Truy vấn lồng 
 1. Truy vấn lồng 
 Truy vấn con là một câu lệnh SELECT được lồng 
vào bên trong một câu lệnh SELECT, INSERT, 
UPDATE, DELETE hoặc bên trong một truy vấn khác. 
 Cú pháp: 
 SELECT [ALL | DISTINCT] 
 FROM 
 WHERE AND | OR 
 (SELECT FROM 
 [WHERE ]) 
 38 
 Truy vấn lồng (tt) 
 Mục đích sử dụng: 
–Điều kiện tìm kiếm lấy từ kết quả của 
 câu select khác 
– Giúp trả lời các câu hỏi 
 • Tìm dự án thi công dài nhất? 
 • Tìm kiến trúc sư có thù lao cao nhất? 
 • Tìm các tác giả có nhiều bài viết nhất? 
 39 
 TRUY VẤN CON LỒNG NHAU (tt) 
 a. Sử dụng truy vấn con với toán tử IN 
 Khi cần thực hiện phép kiểm tra giá trị của một 
biểu thức có xuất hiện (không xuất hiện) trong tập 
các giá trị của truy vấn con, ta có thể sử dụng toán 
tử IN (NOT IN) như sau: 
 WHERE [NOT] IN 
() 
 Ví dụ: Tìm những sinh viên không thực hiện đề tài 
 SELECT HOTENSV, HOCLUC 
 FROM SINHVIEN 
 WHERE MASV NOT IN 
 (SELECT MASV FROM SV_DT) 
 40 
 TRUY VẤN CON LỒNG NHAU (tt) 
 b. Truy vấn con với EXISTS 
 Lượng từ EXISTS (NOT EXISTS) để kiểm tra xem 
một truy vấn con có trả về dòng kết quả nào hay không 
được sử dụng trong truy vấn con dưới dạng: 
 WHERE [NOT] EXISTS () 
 Ví dụ: cho biết họ tên của những sinh viên hiện chưa 
có đăng ký đề tài nào 
 SELECT hotensv 
 FROM SINHVIEN 
 WHERE NOT EXISTS (SELECT masv -- * 
 FROM SVDT 
 WHERE sinhvien.masv=svdt.masv) 
 41 
 TRUY VẤN CON LỒNG NHAU (tt) 
 c. Truy vấn con với mệnh đề HAVING 
 Một truy vấn con có thể được sử dụng trong mệnh 
đề HAVING của một truy vấn khác. Kết quả của truy vấn 
con được sử dụng để tạo điều kiện đối với các hàm gộp. 
 Ví dụ: Cho biết mã, tên và trung bình điểm thi của 
các môn học có trung bình lớn hơn trung bình điểm của 
tất cả các môn học. 
 SELECT KETQUA.MAMH,TENMH, AVG(DIEM) 
 FROM KETQUA,MONHOC 
 WHERE KETQUA.MAMH = MON.MAMH 
 GROUP BY KETQUA.MAMH,TENMH 
 HAVING AVG(DIEM) > --[All | Any| Some] 
 (SELECT AVG(DIEM) FROM KETQUA) 
 42 
TRUY VẤN CON LỒNG NHAU (tt) 
 d. Thực hiện phép chia với truy vấn lồng nhau 
 Sử dụng toán tử NOT EXISTS để thực hiện: 
 Cú pháp: 
 SELECT * 
 FROM R 
 WHERE NOT EXISTS (SELECT * 
 FROM S 
 WHERE NOT EXISTS (SELECT * 
 FROM R_S 
 WHERE R_S.C1 = S.C1 AND 
 R_S.C2 = R.C2)) 
 43 
 Phép chia với truy vấn lồng nhau (tt) 
 Ví dụ: Tìm thông tin sinh viên thực hiện tất cả các 
đề tài 
 SELECT * 
 FROM SINHVIEN 
 WHERE NOT EXISTS (SELECT * 
 FROM DETAI 
 WHERE NOT EXISTS (SELECT * 
 FROM SVDT 
 WHERE SVDT.MADT = DETAI.MADT 
 AND 
 SVDT.MASV = SINHVIEN.MASV)) 
 44 
 Truy vấn Select (tt) 
 2. Gom nhóm Group by 
 Tạo bảng tạm 
 WITH AS () 
 [, AS () [,]] 
 SELECT  
 FROM  
 WHERE ; 
 Mục đích: đơn giản trong sử dụng lại nhiều lần cùng 
một câu truy vấn trong select tổng hợp. 
 WITH t1 AS (select * from sinhvien) 
 Select * from t1 
 Lưu ý: các bảng tạm chỉ có ý nghĩa trong câu truy 
vấn ngay sau đó 
 45 
 Truy vấn lồng 
 Các hàm thống kê 
 Cú pháp: 
 SELECT [,] 
 FROM 
 [WHERE ] 
 [GROUP BY ] 
 [HAVING ] 
 Các hàm kết tập bao gồm: SUM, MAX, MIN, AVG 
và COUNT. 
 Đặt lại tên trường: Trong một số trường hợp tên 
trường của kết quả truy vấn không phù hợp ta cần đặt 
lại tên trường 
 Cú pháp: AS 
 46 
 Hàm thống kê (tt) 
 Cách sử dụng các hàm: 
 a. Hàm SUM (): Trả về tổng giá trị 
của tất cả các dòng của một trường kiểu số 
 Ví dụ: Tính tổng kinh phí của tất cả các đề tài 
 SELECT SUM (Kinhphi) as Tong_KP 
 FROM Detai; 
 b. Hàm MAX (): Trả về giá trị lớn 
nhất trong tất cả các dòng của một trường 
 Ví dụ: Tìm kinh phí lớn nhất của tất cả các đề tài 
 SELECT MAX(Kinhphi) AS KP_MAX 
 FROM Detai 
 47 
 Cách sử dụng hàm (tt) 
 c. Hàm MIN (): Trả về giá trị nhỏ 
nhất trong tất cả các dòng của một trường 
 Ví dụ: Tìm kinh phí nhỏ nhất của tất cả các đề tài 
 SELECT MIN(Kinhphi) AS KP_MIN 
 FROM Detai 
 d. Hàm AVG (): Trả về giá trị 
trung bình cộng của tất cả các dòng một trường kiểu 
số 
 Ví dụ: Tính trung bình cộng kinh phí của tất cả 
các đề tài 
 SELECT AVG(Kinhphi) AS TBC_KP 
 FROM Detai 
 48 
 Cách sử dụng hàm (tt) 
 e. Hàm COUNT (): Đếm các dòng 
trong bảng theo một hoặc một số trường nào đó 
 Ví dụ 1: In ra số lượng đề tài mà thầy Lê Đức Phúc 
làm chủ nhiệm 
 SELECT COUNT(MaDT) AS So_DT 
 FROM Detai 
 WHERE Chunhiem = ‘Lê Đức Phúc’ 
 Ví dụ 2: In ra họ tên của các giáo viên chủ nhiệm từ 
2 đề tài trở lên 
 SELECT Chunhiem, COUNT(MaDT) AS SoLG 
 FROM Detai 
 GROUP BY Chunhiem 
 HAVING COUNT(MaDT) >= 2 
 49 
 Gom nhóm (tt) 
 Group by trên nhóm 
 SELECT , <hàmkếttập 
 ()> 
 FROM 
 [ WHERE ] 
 GROUP BY 
 HAVING 
 50 
 Bài tập Group by 
 1. Tìm tổng kinh phí của tất cả các công trình 
theo từng chủ thầu 
 2. Họ tên KTS có tổng thù lao thiết kế công trình 
lớn hơn 25 triệu 
 3. Số lượng KTS có tổng thù lao thiết kế công 
trình lớn hơn 25 triệu 
 4. Tìm tên, địa chỉ công trình có nhiều công nhân 
nhất 
 51 
 GROUP BY (tt) 
 1. Tìm tổng kinh phí của tất cả các công trình 
theo từng chủ thầu 
CGTRINH (STT_CTR, TEN_CTR, DIACHI_CTR, 
TINH_THANH, KINH_PHI, TEN_CHU, 
TEN_THAU, NGAY_BD) 
 Select TEN_THAU, sum (KINH_PHI) As 
 TongKP 
 From CGTRINH 
 Group by TEN_THAU; 
 52 
 GROUP BY (tt) 
 2. Họ tên KTS có tổng thù lao thiết kế công 
 trình lớn hơn 25 triệu 
THIETKE (HOTEN_KTS, STT_CTR, THU_LAO) 
 Select HOTEN_KTS, sum (THU_LAO) As 
 TongTL 
 From THIETKE 
 Group by HOTEN_KTS 
 Having sum (THU_LAO) > 25; 
 53 
 GROUP BY (tt) 
 3. Số lượng KTS có tổng thù lao thiết kế công 
 trình lớn hơn 25 triệu 
 THIETKE (HOTEN_KTS, STT_CTR, THU_LAO) 
Select count(*) As Solg_KTS 
From (Select HOTEN_KTS, sum (THU_LAO) 
 As TongTL 
 From THIETKE Group by HOTEN_KTS 
 Having sum (THU_LAO) > 25); 
 54 
 GROUP BY (tt) 
 4. Tìm tên, địa chỉ công trình có nhiều công 
 nhân nhất 
 THAMGIA (HOTEN_CN, STT_CTR, NGAY_TGIA, 
 SO_NGAY) 
 CGTRINH (STT_CTR, TEN_CTR, DIACHI_CTR, 
 TINH_THANH, KINH_PHI, TEN_CHU, 
 TEN_THAU, NGAY_BD) 
with t1 as ( 
 Select STT_CTR, count(*) As TongSoCN 
 From THAMGIA 
 Group by STT_CTR) 
 55 
 GROUP BY (tt) 
with t1 as ( 
Select STT_CTR, count (*) As TongSoCN 
From THAMGIA 
Group by STT_CTR) 
Select TEN_CTR, DIACHI_CTR, TongSoCN 
From t1 a, CGTRINH b 
Where a.STT_CTR = b.STT_CTR and 
 TongSoCN = (Select Max (TongSoCN) 
 From t1 ); 
 56 
 IV. Các hàm xử lý dữ liệu 
 1. LENGTH (): Trả về chiều dài chuỗi 
 Select LENGTH ('lop DHCNTT') LEN 
 From Dual -- len = 10 
 2. INSTR(, , 
 ,) 
 Tìm vị trí chuỗi b trong chuỗi a bắt đầu từ vị trí n, 
 lần xuất hiện thứ m. 
 n<0: tìm từ phải sang trái 
 Select INSTR ('CORORATE FLOOR','OR',3,2) 
 From Dual -- vị trí 13 
 57 
 Các hàm xử lý dữ liệu (tt) 
 3. SUBSTR (, , 
 ) 
 Cắt lấy chuỗi con của chuỗi a, lấy từ vị trí n về 
 phải m ký tự, nếu không chỉ m thì lấy cho đến 
 cuối chuỗi . 
 n<0: vị trí cắt đếm từ phải sang trái 
 Select SUBSTR('ORACLE',1,3) Substring 
 From Dual --ORA 
 4. CONCAT (,): Nối 2 chuỗi a, b 
 Select CONCAT ('Lop DHCNTT',' Khoa SP Toan-
Tin') Substring 
 From Dual -- Lop DHCNTT Khoa SP Toan-Tin 
 58 
 Các hàm xử lý dữ liệu (tt) 
 5. SUBOWER/UPPER/INITCAPSTR () 
  Chuyển chuỗi thành chuỗi viết thường /hoa /ký 
 tự đầu các từ viết hoa. 
6. LTRIM/RTRIM(, ) 
  Loại bỏ chuỗi b ở bên trái/ phải chuỗi a 
 Select LTRIM(‘khoa su pham toan - tin’, ’khoa’) 
 From Dual; -- su pham toan - tin 
 Select RTRIM(‘LAST WORDx’, ’xy’) 
 FROM Dual; -- LAST WORD 
 59 
 Các hàm xử lý dữ liệu (tt) 
7. REPLACE (chuỗi a, chuỗi b [,chuỗi c]) 
 Thay tất cả các chuỗi b có trong chuỗi a bằng 
 chuỗi c 
SELECT JOB, REPLACE (JOB, ‘SALESMAN’, 
 ‘SALESPERSON’) 
FROM EMP 
WHERE DEPTNO =30 OR DEPTNO =20; 
 60 
 Các hàm xử lý dữ liệu (tt) 
 8. SYSDATE(chuỗi a, chuỗi b [,chuỗi c]) 
  Hàm lấy ngày tháng hiện hành của hệ thống 
 SELECT SYSDATE FROM DUAL 
 9. EXTRACT (YEAR|MONTH|DAY FROM <chuỗi 
ngày>) Tách lấy năm|tháng|ngày của 
 Select EXTRACT(Month FROM DATE 
 ‘2001-05-07’) 
 FROM Dual; -- 5 
 Select EXTRACT(DAY FROM Sysdate) 
 FROM Dual; 
 61 
 Các hàm xử lý dữ liệu (tt) 
10. MONTHS_BETWEEN(, ) 
 Cho biết số tháng giữa và 
Select MONTHS_BETWEEN 
 (DATE ‘1981-01-02’, DATE ‘1980-01-02’) 
 FROM Dual --12 
11. ADD_MONTHS (, n) Thêm n tháng 
vào 
Select ADD_MONTHS (DATE ’1981-01-02’, 2) 
FROM Dual; --3 
 62 
 Các hàm xử lý dữ liệu (tt) 
12. NEXT_DAY(, ) 
 Cho biết sau là ngày nào 
Select NEXT_DAY(DATE ‘2015-04-10’, 'friday') 
FROM Dual; --17-04-2015 
 13. LAST_DAY () Cho biết ngày cuối 
cùng trong tháng chỉ bởi 
Select LAST_DAY (DATE ‘2015-04-10’) 
FROM Dual; --30-04-2015 
 63 
 Các hàm xử lý dữ liệu (tt) 
 14. TO_CHAR(): Chuyển số sang chuỗi 
 TO_CHAR(, ):Chuyển 
ngày sang chuỗi theo định dạng 
 Select TO_CHAR(DATE'2011-09-20', 
 'DD-MM-YYYY') 
 FROM Dual; -- ‘20-09-2011’ 
 16. TO_NUMBER(): Chuyển chuỗi 
ký số sang dạng số 
 Select TO_NUMBER('123')+TO_NUMBER('34') 
 FROM Dual; => 157 
 64 
 Các hàm xử lý dữ liệu (tt) 
 17. TO_DATE(, <chuỗi 
định dạng>) 
 Chuyển chuỗi ngày sang dạng ngày theo định 
dạng 
 Select TO_DATE(’10-04-2015’,’DD-MM-YYYY’) 
 FROM Dual; -- 10-04-2015 
 65 
 Các hàm xử lý dữ liệu (tt) 
 CASE 
 WHEN THEN 
 [WHEN THEN 
 [ELSE ] 
 END 
 Đánh giá danh sách các điều kiện và trả về giá trị 
phù hợp 
 Select CASE 
 WHEN extract(day from sysdate)>15 
 THEN ‘Cuoi thang’ 
 ELSE ‘Dau thang’ END 
 From Dual; 
 66 
 TÓM TẮT NỘI DUNG 
 - Ngôn ngữ xử lý dữ liệu (Insert, Update, Delete) 
 - Truy vấn dữ liệu trên một và nhiều bảng (select 
 from where) 
 - Truy vấn dữ liệu tập hợp (UNION, INTERSECT, 
MINUS), truy vấn lồng nhau 
 - Truy vấn sử dụng hàm kết tập (SUM, MAX, MIN, 
AVG, COUNT) 
 - Truy vấn sắp xếp dữ liệu ORDER BY 
 - Truy vấn thống kê trên nhóm GROUP BY 
 - Các hàm xử lý dữ liệu (chuỗi, ngày, số, kiểu). 
 Biên soạn: Nguyễn Thị Mỹ Dung 67