›
›

Bài giảng Cơ sở dữ liệu - Chương 3: Ngôn ngữ thao tác dữ liệu - Hồ Cẩm Hà

pdf 70 trang phuongnguyen 6480
Download
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Cơ sở dữ liệu - Chương 3: Ngôn ngữ thao tác dữ liệu - Hồ Cẩm Hà", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pdfbai_giang_co_so_du_lieu_chuong_3_ngon_ngu_thao_tac_du_lieu_h.pdf

Nội dung text: Bài giảng Cơ sở dữ liệu - Chương 3: Ngôn ngữ thao tác dữ liệu - Hồ Cẩm Hà

  1. Ch−¬ng 3 Ngôn ngữ thao tác dữ liệu
  2. Ngôn ngữ đại số quan hệ „ Ngôn ngữ đại số quan hệ là cơ sở quan trọng của một ngôn ngữ bậc cao được sử dụng để thao tác trên các quan hệ. „ Các phép toán của đại số quan hệ chia thành hai nhóm (tập hợp và đặc trưng) Hồ Cẩm Hà Chương 3 2
  3. Các phép toán tập hợp „ Phép hợp: r ∪s = {t⏐ t∈r hoặc t∈s} „ Phép giao: r ∩ s = {t⏐ t ∈ r và t ∈ s} „ Phép hiệu: r – s = {t⏐ t ∈ r và t ∉ s} r ∪ (s ∪ t) = (r ∪ s) ∪ t r ∩ (s ∩ t) = (r ∩ s) ∩ t Hồ Cẩm Hà Chương 3 3
  4. Các phép toán tập hợp „ Phép tích Descartes: r × s = {t⏐t=(a1, a2, ,an, b1, b2, ,bm), (a1, a2, ,an) ∈ r và (b1, b2, ,bm)∈s } Như vậy nếu r có k1 bộ, s có k2 bộ thì r × s có k1× k2 bộ. Hồ Cẩm Hà Chương 3 4
  5. Các phép toán tập hợp „ Phép chia: r r÷s ={t⏐t=(am+1,am+2, ,an): ∀(a1, a2, ,am) ∈ s, (a1, a2, ,am, am+1, ,an) ∈ r } Hồ Cẩm Hà Chương 3 5
  6. Các phép toán đặc biệt trên quan hệ Phép chọn σC(r) = {t/ t∈r, C(t) = TRUE} Ví du σ ((MA_DU_AN= D1) ∨ (LUONG ≥ 3500)) (PHAN_CONG) Hồ Cẩm Hà Chương 3 6
  7. Các phép toán đặc biệt trên quan hệ Phép chiếu ΠX(r) = {t[X]/ t ∈ r} Ví du ΠHOTEN, LUONG(PHAN_CONG) Hồ Cẩm Hà Chương 3 7
  8. Các phép toán đặc biệt trên quan hệ Phép kết nối (join) Khái niệm ” xếp cạnh nhau”: Cho bộ p =(p1, p2 , , pn) và bộ q =(q1, q2 , , qm). Xếp cạnh nhau của p và q : (p,q) = {p1, p2 , , pn, q1, q2 , , qm } r ⋈ Ai θ Bj s = {(t, u)/ t ∈ r, u ∈ s và t[Ai] θ u[Bj]} Hồ Cẩm Hà Chương 3 8
  9. Các phép toán đặc biệt trên quan hệ Phép kết nối (join) r ⋈ Ai θ Bj s = {(t, u)/ t ∈ r, u ∈ s và t[Ai] θ u[Bj]} Trường hợp θ là “=” gọi là kết nối bằng kết nối tự nhiên sử dụng kí hiệu r * s (hoặc r ⋈ s ) Ví dụ NHA_CUNG_CAP ⋈DIACHI≠NOI-SXUATSAN_PHAM Hồ Cẩm Hà Chương 3 9
  10. Các phép toán đặc biệt trên quan hệ Phép kết nối (join) kết nối tự nhiên sử dụng kí hiệu r * s (hoặc r ⋈ s ) Ví dụ NHA_CUNG_CAP * CUNG_UNG Hồ Cẩm Hà Chương 3 10
  11. Các phép toán đặc biệt trên quan hệ Phép kết nối nửa (semi join) r ⊲F s = ΠA(r ⋈ F s ) Ví du CUNG_UNG SAN_PHAM ⊲ F với F là điều kiện: (CUNG_UNG.MA_SP = SAN_PHAM.MA_SP) AND (SOLUONG>1500). Hồ Cẩm Hà Chương 3 11
  12. Các phép toán đặc biệt trên quan hệ Phép kết nối ngoài trái (left outer join) kết nối ngoài phải (right outer join) (Đọc tài liệu) Hồ Cẩm Hà Chương 3 12
  13. Các phép toán quan hệ bổ sung (Additional Relational Operations) Các hàm kết tập „ hàm tính tổng (SUM) „ hàm tính trung bình cộng (AVERAGE) „ hàm tính giá trị lớn nhất (MAX) „ hàm tính giá trị nhỏ nhất (MIN) „ hàm đếm các bộ giá trị (COUNT) „ hàm tính tổng (SUM) Hồ Cẩm Hà Chương 3 13
  14. Các phép toán quan hệ bổ sung (Additional Relational Operations) Các phép gộp nhóm f (r) Ví dụ MA_PHONG f COUNT MANV, AVERAGE LUONG (NHAN_VIEN) Hồ Cẩm Hà Chương 3 14
  15. Chú ý (1) Kết quả áp dụng một hàm kết tập là một quan hệ chứ không phải là một đại lượng vô hướng kể cả khi nó chỉ gồm một giá trị. Các bộ trùng lặp trong mỗi nhóm không bị loại bỏ khỏi nhóm khi thực hiện một hàm kết tập. (2) Một số phép toán có thể được biểu diễn theo các phép toán khác. Hồ Cẩm Hà Chương 3 15
  16. Dùng các biểu thức đại số quan hệ biểu diễn câu hỏi „ Câu hỏi 1 Tìm tên của dự án có mã số D4 ΠTEN_DA(σ(MA_DA=”D4” )(DU_AN)) „ Câu hỏi 2 Cho biết họ tên và lương của những nhân viên làm việc ở phòng “Nghiên cứu và phát triển”. ΠHOTEN, LUONG(NHAN_VIEN* (ΠMA-DV(σ(TEN-PHONG=”Nghiên cứu và phát triển )(PHONG)))) Hồ Cẩm Hà Chương 3 16
  17. Dùng các biểu thức đại số quan hệ biểu diễn câu hỏi „ Câu hỏi 3 Với mỗi dự án thực hiện ở “TT khí thượng thủy văn HN” hãy cho biết mã số dự án, đồng thời cho biết họ tên, ngày sinh của trưởng phòng quản lý dự án này. KQTG ← (ΠMA-DA, MA-DV(σ(DIA-DIEM-DA=”TT Khí tượng thủy văn HN’ )(DU_AN)) KETQUA ←ΠMA-DA, HO-TEN, NG-SINH(NHAN_VIEN⋈MA-NV=MA-TP (PHONG*KQTG)) Hồ Cẩm Hà Chương 3 17
  18. Dùng các biểu thức đại số quan hệ biểu diễn câu hỏi „ Câu hỏi 4 Tìm tên những nhân viên làm việc cho tất cả các dự án do phòng có mã số P4 quản lý. ΠHO-TEN((ΠMANV,MA-DA(CHAM_CONG)÷ ΠMA-DA(σ(MA-DV=P4)(DU_AN)))*NHAN_VIEN). Hồ Cẩm Hà Chương 3 18
  19. Dùng các biểu thức đại số quan hệ biểu diễn câu hỏi „ Câu hỏi 5 Tìm mã số những dự án có sự tham gia của một người là lãnh đạo phòng trực tiếp quản lý dự án này. KQTG ←ΠMA-DA, MA-DV(DU_AN) * ΠMA-DV, MA-TP(PHONG) KETQUA ← KQTG ⋈ ((KQTG.MA-DA=CHAM_CONG.MA-DA) AND (KQTG.MA-TP=CHAM_CONG.MA-NV)) CHAM_CONG Hồ Cẩm Hà Chương 3 19
  20. Ngôn ngữ tân từ (Các phép tính quan hệ) „ Cơ sở quan trọng của phần thao tác trong mô hình cơ sở dữ liệu quan hệ. „ Cơ sở toán học của phép tính quan hệ là logic tân từ cấp một. Có hai loại: ngôn ngữ tân từ biến bộ và ngôn ngữ tân từ biến miền. Hồ Cẩm Hà Chương 3 20
  21. Ngôn ngữ tân từ biến bộ (Tuple relational calculus) Một câu hỏi đơn giản trong ngôn ngữ tân từ biến bộ có dạng {t ⏐ ĐK(t) } Hồ Cẩm Hà Chương 3 21
  22. Ngôn ngữ tân từ biến bộ (Tuple relational calculus) {t ⏐ NHÂN_VIÊN (t) AND t.LUONG > 1700 000} {t.HO_TEN, t.MA_DA⏐NHÂN_VIÊN(t) AND t.LUONG > 1700 000} Hồ Cẩm Hà Chương 3 22
  23. Biểu thức của phép tính biến bộ Một cách tổng quát, một biểu thức của phép tính biến bộ có dạng {ti.A1, tj.A2, , tk.An ⏐ ĐK (t1, t2, , tn, tn+1, tn+2, , tn+m)} Hồ Cẩm Hà Chương 3 23
  24. Biểu thức của phép tính biến bộ Công thức của phép tính biến bộ được tạo nên từ các công thức nguyên tố. Một công thức nguyên tố thuộc một trong những dạng sau: (1) r(t) nói một cách khác đólàphát biểu (t ∈ r). (2) ti.A θ tj.B (3) ti.A θ c Mỗi công thức nguyên tố sẽ nhận một trong hai giá trị TRUE và FALSE đối với một tổ hợp các bộ (giá trị chân lý) Hồ Cẩm Hà Chương 3 24
  25. Biểu thức của phép tính biến bộ Các công thức nguyên tố tạo nên công thức của phép tính bộ theo các luật: (L1) Mỗi công thức nguyên tố là một công thức (L2) F, F1, F2 là công thức thì (F1 AND F2), (F1 OR F2), NOT(F) là công thức. (L3) F là một công thức thì (∃ t)(F) với t là một biến bộ cũng là một công thức (L4) F là một công thức thì (∀t)(F) với t là một biến bộ cũng là một công thức Hồ Cẩm Hà Chương 3 25
  26. Ví dụ Câu hỏi 1 Tìm tên của dự án có mã số D4 {t.TEN_DA⏐ (DU_AN(t) AND t.MA_DA=”D4”} Hồ Cẩm Hà Chương 3 26
  27. Ví dụ Câu hỏi 2 Cho biết họ tên và lương của những nhân viên thuộc phòng “Nghiên cứu và phát triển” {t.HO_TEN, t.LUONG ⏐∃u ((NHAN_VIEN(t) AND PHONG(u) AND u.TEN_PHONG = ”Nghiên cứu và phát triển” AND t.MA_DV = u.MA_DV } Hồ Cẩm Hà Chương 3 27
  28. Ví dụ Câu hỏi 3 Với mỗi dự án thực hiện ở “TT khí thượng thủy văn HN” hãy cho biết mã số dự án, đồng thời cho biết họ tên trưởng phòng quản lý dự án này {t.HO_TEN, t.LUONG ⏐∃u ((NHAN_VIEN(t) AND PHONG(u) AND u.TEN_PHONG = ”Nghiên cứu và phát triển” AND t.MA_DV = u.MA_DV } Hồ Cẩm Hà Chương 3 28
  29. Ví dụ Câu hỏi 4 Tìm tên những nhân viên làm việc cho tất cả các dự án do phòng có mã số P4 quản lý {t.HO_TEN ⏐ NHAN_VIEN(t) AND (∀u ((DU_AN(u) AND u.MA_DV = “P4”) ⇒ (∃v (CHAM_CONG(v) AND v.MANV = t.MANV AND t.MA_DA = u.MA_DA)))} Hồ Cẩm Hà Chương 3 29
  30. Ví dụ Câu hỏi 5 Tìm mã số của những dự án có sự tham gia của một người là lãnh đạo của phòng trực tiếp quản lý dự án này. t.MA_DA⏐∃p∃q ((DU_AN(t) AND PHONG(p) AND CHAM_CONG(q) AND (t.MA_DV = p.MA_DV) AND (p.MA_TP = q.MANV) AND (q.MA_DA = t.MA_DA))}. Hồ Cẩm Hà Chương 3 30
  31. Luật chuyển đổi công thức tương đương liên quan đến các lượng từ „ (∀x) (P(x)) ≡ (∃ x))(NOT(P(x))) „ (∃ x) (P(x)) ≡ NOT(∀x)(NOT(P(x))) „ (∀x) (P(x) AND Q(x)) ≡ (∃ x))(NOT(P(x)) OR NOT(Q(x))) „ (∀x) (P(x) OR Q(x)) ≡ (∃ x))(NOT(P(x)) AND NOT(Q(x))) „ (∃ x) (P(x) OR Q(x)) ≡ NOT(∀x) (NOT(P(x)) AND NOT(Q(x))) „ (∃ x) (P(x) AND Q(x)) ≡ NOT(∀x) (NOT(P(x)) OR NOT(Q(x))) „ (∀x) (P(x)) ⇒ (∃ x) (P(x)) „ (∃ x) (P(x))⇒ (∀x)(NOT (P(x))) Hồ Cẩm Hà Chương 3 31
  32. Ngôn ngữ tân từ biến miền Ví dụ Câu hỏi 1 Tìm tên của dự án có mã số D4 {tenX ⏐∃diadiemX ∃ma_dvX (DU_AN(“D4”,tenX, diadiemX, ma_dvX))} Hồ Cẩm Hà Chương 3 32
  33. Ngôn ngữ tân từ biến miền Ví dụ Câu hỏi 2 Cho biết họ tên và lương của những nhân viên thuộc phòng “Nghiên cứu và phát triển” {ho_tenX, luongX⏐ ∃ma_nvX∃ngay_sinhX∃gioi_tinhX ∃ ma_dvX ∃ ma_tpX (PHONG (ma_dvX, “Nghiên cứu và phát triển”, ma_tpX) AND NHAN_VIEN(ma_nvX,ho_tenX,ngay_sinhX,gioi_tinhX,ma_dvX,lu ongX))} Hồ Cẩm Hà Chương 3 33
  34. Ngôn ngữ tân từ biến miền Ví dụ Câu hỏi 3 Với mỗi dự án thực hiện ở “TT khí thượng thủy văn HN” hãy cho biết mã số dự án, đồng thời cho biết họ tên trưởng phòng quản lý dự án này {ma_daX, ho_tenX⏐ ∃ ten_daX ∃ ma_dvX ∃ten_phongX ∃ma_tpX ∃ngay_sinhX ∃gioi_tinhX ∃luongX ( DU_AN (ma_daX, ten_daX, “TT khí tượng thủy văn HN”, ma_dvX) AND PHONG (ma_dvX, ten_phongX, ma_tpX) AND NHAN_VIEN (ma_tpX, ho_tenX, ngay_sinhX, gioi_tinhX, ma_dvX, luongX)) } Hồ Cẩm Hà Chương 3 34
  35. Tính đầy đủ của NN hỏi „ Khả năng biểu thị câu hỏi của đại số quan hệ và ngôn ngữ tân từ là tương đương nhau về mặt này. „ Một ngôn ngữ hỏi L (của mô hình quan hệ) được gọi là đầy đủ nếu với L, chúng ta có thể biểu diễn được bất cứ một câu hỏi nào mà phép tính quan hệ biểu diễn được. „ Tính đầy đủ trở thành một tiêu chuẩn quyết định để so sánh khả năng biểu diễn câu hỏi của các ngôn ngữ hỏi bậc cao Hồ Cẩm Hà Chương 3 35
  36. NGÔN NGỮ SQL „ SQL đang được xem là ngôn ngữ chuẩn của các hệ cơ sở dữ liệu quan hệ. „ Ngôn ngữ SQL gồm nhiều phần. Nó cho phép định nghĩa dữ liệu (gồm DDL), cho phép cập nhật và tìm kiếm dữ liệu (gồm DML). „ cung cấp các khả năng khác như: định nghĩa khung nhìn (view), kiểm soát tính toàn vẹn dữ liệu, cấp phát quyền truy cập, điều khiển các giao tác, thao tác dữ liệu nhúng. Hồ Cẩm Hà Chương 3 36
  37. SQL Phần thao tác dữ liệu sẽ có đông đảo người dùng hơn, do chúng ta xem xét phần này trước phần định nghĩa dữ liệu của SQL. Hồ Cẩm Hà Chương 3 37
  38. SELECT FROM WHERE dùng để truy vấn dữ liệu trong CSDL
  39. NGÔN NGỮ SQL SELECT ten_da, ma_dv FROM du_an SELECT ma_da, ten_da, dia_diem_da, ma_dv FROM du_an hoặc SELECT * FROM du_an 39
  40. NGÔN NGỮ SQL Cho biết họ tên của mỗi nhân viên và lương mới của mỗi người nếu lương họ được tăng 10% SELECT ho_ten, luong + luong*0.10 FROM nhan_vien Trong bảng kết quả, nếu muốn có thể đặt tên cho cột thứ ba, chẳng hạn cột thứ ba được đặt tên là luong_moi, với câu lệnh sau SELECT ho_ten, luong + luong*0.10 AS luong_moi FROM nhan_vien 40
  41. NGÔN NGỮ SQL Cho biết danh sách nhân viên có lương trên 2 300 000, với mỗi nhân viên như vậy các thông tin đưa ra gồm: mã nhân viên, họ tên, mã đơn vị và lương. SELECT manv, ho_ten, ma_dv, luong FROM nhan_vien WHERE luong > 2 300 000 41
  42. NGÔN NGỮ SQL Tìm tên các nhân viên có mã đơn vị là 'P4' và lương thấp hơn 2 500 000. Câu truy vấn đólà SELECT ho_ten, ma_dv, luong FROM nhan_vien WHERE ma_dv = ‘P4’ AND luong < 2 500 000 42
  43. NGÔN NGỮ SQL Có thể tìm biết tên và mã các dự án mà địa điểm có chứa từ ‘UBND’ bằng câu sau: SELECT ma_da, ten_da, dia_diem_da FROM du_an WHERE dia_diem_da LIKE ‘%UBND%’ 43
  44. NGÔN NGỮ SQL Để liệt kê các nhân viên nữ theo thứ tự tăng dần của lương, có thể dùng: SELECT * FROM nhan_vien WHERE gioi_tinh = ’nu’ ORDER BY luong 44
  45. NGÔN NGỮ SQL Ví dụ 4.10 SELECT * FROM cham_cong WHERE ma_da IN (‘D1’, ‘D2’, ‘D4’) ORDER BY so_gio DESC, ma_da ASC 45
  46. NGÔN NGỮ SQL Câu hỏi: “Có bao nhiêu nhân viên lương cao hơn 3 000 000 ?” có thể viết như sau: SELECT COUNT(*) AS vip FROM nhan_vien WHERE luong > 3000 000 46
  47. NGÔN NGỮ SQL Ví dụ 4.13 Câu hỏi: “ Tìm lương cao nhất, lương thấp nhất và trung bình cộng lương các nhân viên” được viết như sau: SELECT MIN(luong)AS min, MAX(luong) AS max, AVG(luong) AS tb FROM nhan_vien 47
  48. NGÔN NGỮ SQL Câu hỏi: “ Cho biết lương cao nhất và tổng lương của mỗi đơn vị”sẽ được viết là SELECT ma_dv, MAX(luong) AS max_dv, SUM(luong) AS tong_dv FROM nhan_vien GROUP BY ma_dv 48
  49. NGÔN NGỮ SQL Để đưa ra lương cao nhất và tổng lương của những đơn vị có tổng lương trên 4 triệu, câu truy vấn là SELECT ma_dv, MAX(luong) AS max_dv, SUM(luong) AS tong_dv FROM nhan_vien GROUP BY ma_dv HAVING SUM(luong) > 4 000 000 49
  50. NGÔN NGỮ SQL Để tìm mã số và tên những nhân viên phòng ‘Kỹ thuật’ có thể dùng câu truy vấn sau SELECT manv, ho_ten FROM nhan_vien WHERE ma_dv = (SELECT ma_dv FROM phong WHERE ten_phong = ‘Kỹ thuật’); 50
  51. NGÔN NGỮ SQL Xét câu hỏi: ‘Tìm tên của những nhân viên có lương đạt trên mức lương trung bình của tất cả các nhân viên, đồng thời cho biết luôn mức chênh lệch này’. Chúng ta có thể dùng truy vấn sau: SELECT ho_ten , luong – (SELECT AVG(luong) FROM nhan_vien) AS hieu FROM nhan_vien WHERE luong > (SELECT AVG(luong) FROM nhan_vien); 51
  52. NGÔN NGỮ SQL Câu ORDER BY không được sử dụng trong một câu truy vấn con mặc dù nó có thể được dùng trong câu truy vấn ngoài cùng. Danh sách các mục được liệt kê bởi câu truy vấn con SELECT phải chứa tên của một cột hoặc một biểu thức trừ phi câu truy vấn con này dùng từ khóa EXISTS. 52
  53. NGÔN NGỮ SQL Theo ngầm định, các tên cột trong câu truy vấn con tham chiếu đến tên bảng trong câu FROM của truy vấn con này, hoặc có thể tham chiếu đến bảng trong câu FROM của truy vấn ngoài bằng việc xác định tên cột đó. Khi một câu truy vấn con là một trong hai toán hạng của một biểu thức so sánh thì truy vấn con này phải xuất hiện ở vế phải của so sánh. 53
  54. NGÔN NGỮ SQL Muốn biết thông tin về những nhân viên làm việc cho đơn vị có địa điểm là ’23 Lý Thường Kiệt’, có thể dùng truy vấn như sau SELECT * FROM nhan_vien WHERE ma_dv IN (SELECT ma_dv FROM dia_diem_dv WHERE dia_diem = ’23 Lý Thường Kiệt); 54
  55. NGÔN NGỮ SQL Cần phân biệt mục đích của câu WHERE và câu HAVING. Mặc dù có cú pháp tương tự: WHERE lọc lấy một số bộ trong bảng đưa vào bảng kết quả, HAVING lọc lấy một số nhóm nào đó để đưa vào bảng kết quả 55
  56. NGÔN NGỮ SQL Điều kiện chọn (lọc) trong câu HAVING luôn chứa ít nhất một hàm gộp, nếu không thì điều kiện này có thể chuyển vào câu WHERE. Nên nhớ rằng hàm gộp không được dùng trong câu WHERE 56
  57. NGÔN NGỮ SQL Trường hợp câu truy vấn có cả câu WHERE và câu HAVING (dĩ nhiên có cả câu GROUP BY), thì trước tiên điều kiện chọn ở câu WHERE được áp dụng. các bộ thỏa điều kiện này được tạo thành các nhóm do câu GROUP BY. Tiếp theo, câu HAVING loại đi những nhóm không thỏa điều kiện đặt sau từ HAVING. Các nhóm còn lại được câu SELECT sử dụng để tạo ra các bộ là kết quả của câu truy vấn. SELECT ma_dv, MAX(luong) AS max_dv, SUM(luong) AS tong_dv FROM nhan_vien GROUP BY ma_dv HAVING SUM(luong) > 4 000 000 57
  58. NGÔN NGỮ SQL Các truy vấn lồng nhau Để tìm mã số và tên những nhân viên phòng ‘Kỹ thuật’ có thể dùng câu truy vấn sau SELECT manv, ho_ten FROM nhan_vien WHERE ma_dv IN (SELECT ma_dv FROM phong WHERE ten_phong = ‘Kỹ thuật’); 58
  59. NGÔN NGỮ SQL Các truy vấn lồng nhau Xét câu hỏi: ‘Tìm tên của những nhân viên có lương đạt trên mức lương trung bình của tất cả các nhân viên, đồng thời cho biết luôn mức chênh lệch này’ SELECT ho_ten , luong – (SELECT AVG(luong) FROM nhan_vien) AS hieu FROM nhan_vien WHERE luong > (SELECT AVG(luong) FROM nhan_vien); 59
  60. NGÔN NGỮ SQL Các truy vấn lồng nhau Câu ORDER BY không được sử dụng trong một câu truy vấn con mặc dù nó có thể được dùng trong câu truy vấn ngoài cùng. Danh sách các mục được liệt kê bởi câu truy vấn con SELECT phải chứa tên của một cột hoặc một biểu thức trừ phi câu truy vấn con này dùng từ khóa EXISTS. 60
  61. NGÔN NGỮ SQL Các truy vấn lồng nhau Theo ngầm định, các tên cột trong câu truy vấn con tham chiếu đến tên bảng trong câu FROM của truy vấn con này, hoặc có thể tham chiếu đến bảng trong câu FROM của truy vấn ngoài bằng việc xác định tên cột đó. Khi một câu truy vấn con là một trong hai toán hạng của một biểu thức so sánh thì truy vấn con này phải xuất hiện ở vế phải của so sánh. 61
  62. NGÔN NGỮ SQL Các truy vấn lồng nhau Muốn biết thông tin về những nhân viên làm việc cho đơn vị có địa điểm là ’23 Lý Thường Kiệt’, có thể dùng truy vấn như sau SELECT * FROM nhan_vien WHERE ma_dv IN (SELECT ma_dv FROM dia_diem_dv WHERE dia_diem = ’23 Lý Thường Kiệt); 62
  63. NGÔN NGỮ SQL Các truy vấn lồng nhau “ Tìm tên và lương của những người có lương cao hơn lương mọi nhân viên thuộc đơn vị mã P2 “. SELECT ho_ten FROM nhan_vien WHERE luong > ALL (SELECT luong FROM nhan_vien WHERE ma_dv = ’P2’); 63
  64. NGÔN NGỮ SQL Các truy vấn lồng nhau Câu hỏi “ Cho biết thông tin về những nhân viên có lương cao hơn ít nhất một nhân viên làm ở phòng kỹ thuật” SELECT ho_ten FROM nhan_vien WHERE luong > SOME (SELECT luong FROM nhan_vien WHERE ma_dv IN (SELECT ma_dv FROM phong WHERE ten_phong = ‘Kỹ thuật’)64 );
  65. NGÔN NGỮ SQL Các truy vấn lồng nhau “Cho biết tên phòng có trung bình lương cao nhất” sẽ được viết là SELECT ten_phong FROM phong WHERE ma_dv IN (SELECT ma_dv FROM nhan_vien GROUP BY ma_dv HAVING AVG(luong) >= ALL (SELECT AVG(luong) FROM nhan_vien GROUP BY ma_dv) ); 65
  66. NGÔN NGỮ SQL Dùng các biến bộ Để tìm thông tin về tên và lương của trưởng phòng nghiên cứu phát triển SELECT nv.ho_ten, nv.luong FROM nhan_vien nv, phong p WHERE nv.manv = p.ma_tp AND p.ten_phong = ‘Nghiên cứu và phát triển’ Các biến bộ rất hữu ích trong việc so sánh hai bộ trong cùng một quan hệ. 66
  67. NGÔN NGỮ SQL Dùng các biến bộ Có thể tìm nhân viên có lương cao hơn ít nhất lương của một nữ nhân viên bằng câu lệnh sau : SELECT DISTINCT nv.ho_ten, nv.luong FROM nhan_vien a, nhan_vien b WHERE a.luong > b.luong AND b.gioi_tinh = ‘nu’ 67
  68. NGÔN NGỮ SQL Các quan hệ được dẫn xuất và khung nhìn SQL-92 cho phép dùng câu hỏi con trong câu FROM, trong đóbảng kết quả của câu hỏi con phải được đặt tên và các cột được phép đổi tên. Một kết quả như vậy được gọi là một quan hệ (bảng) được dẫn xuất. 68
  69. NGÔN NGỮ SQL Các quan hệ được dẫn xuất và khung nhìn “Tìm lương trung bình của các phòng có lương trung bình lớn hơn 2 100 000”, trong SQL (chuẩn 92) có thể viết như sau: SELECT ma_phong, luong_tb FROM (SELECT ma_dv, AVG(luong) FROM nhan_vien GROUP BY ma_dv) AS ket_qua (ma_phong, luong_tb) WHERE luong_tb > 2 100 000 69
  70. Xin cảm ơn !