SQL (Structured Query Language) là ngôn ngữ truy vấn cấu trúc được sử dụng rộng rãi trong cơ sở dữ liệu quan hệ. Đầu tiên, nó được IBM đề xuất trong dự án SEQUEL-XRM và System-R(1974-1977). Dường như ngay lập tức, những nhà sản xuất khác đã giới thiệu sản phẩm DBMS của mình dựa trên SQL, và bây giờ nó đã được chuẩn hoá chính thức. SQL tiếp tục phát triển đáp ứng những thay đổi trong lĩnh vực cơ sở dữ liệu. Chuẩn ANSI/ISO hiện tại của SQL được gọi là SQL-1999. Trong khi không phải tất cả sản phẩm DBMS đều hỗ trợ SQL-1999 đầy đủ, những nhà sản xuất vẫn đang tiếp tục làm việc để đạt đến điều này và hiện nay hầu hết các sản phẩm DBMS đều hỗ trợ những đặc trưng cơ bản của SQL-1999.
Làm thích hợp các chuẩn của SQL: SQL:1999 có tập hợp các tính năng gọi là Core SQL mà các nhà sản xuất phải thực hiện để thích hợp với chuẩn SQL-1999. Những đặc trưng còn lại được tổ chức trong các gói hỗ trợ.
Ví dụ, các gói được tổ chức như sau (các chương liên quan nằm trong dấu ngoặc đơn): kiểu ngày tháng nâng cao (enhanced date and time), quản lý ràng buộc nâng cao (enhanced integrity management) và các cơ sở dữ liệu hoạt động (active databases) (chương này), các giao diện ngôn ngữ phía ngoài (external language interfaces) (Chương 6), Xử lý các phân tích trực tuyến (OLAP) (Chương 25) và các đặc tính hướng đối tượng (object features) (Chương 23). Chuẩn SQL/MM thực hiện SQL-1999 bằng việc định nghĩa các gói bổ sung hỗ trợ Khai phá dữ liệu (data mining) (Chương 26), dữ liệu không gian (spatial data) (Chương 28) và văn bản (text documents) (Chương 27), hỗ trợ dữ liệu XML và các truy vấn tương lai.
Tổng quan
Ngôn ngữ SQL có một số khía cạnh:
- Ngôn ngữ thực hiện dữ liệu - The Data Manipulation Language (DML): Là tập hợp các lệnh của SQL cho phép người dùng đưa ra các truy vấn, thêm, sửa và xóa các dòng dữ liệu trong bảng. Những truy vấn sẽ được tập trung trình bày trong chương này. Chúng ta đã khám phá DML để thêm, sửa, xóa dữ liệu trong Chương 3.
- Ngôn ngữ định nghĩa dữ liệu - The Data Defination Language (DDL): Là tập hợp các câu lệnh của SQL hỗ trợ để tạo, xóa và sửa định nghĩa các bảng và các khung nhìn. Các ràng buộc tham chiếu có thể định nghĩa trên bảng, hoặc có thể định nghĩa sau khi bảng được tạo ra. Chúng ta đã khám phá các đặc trưng DDL trong chương 3. Mặc dù chuẩn SQL không đề cập tới các chỉ số, nhưng các thực hiện của DBMS thương mại cũng cung cấp lệnh để tạo và xóa các chỉ số.
- Trigger và Các ràng buộc toàn vẹn nâng cao (Triggers and Advanced Integrity Constraints): Chuẩn SQL:1999 mới có những hỗ trợ cho thực hiện triggers, đó là tập những thao tác được DBMS thực hiện khi có bất kỳ sự thay đổi nào trong cơ sở dữ liệu. Chúng ta sẽ khám phá trigger và các ràng buộc trong chương này.
- SQL nhúng và động (Embedded và dynamic SQL):Các đặc trưng của Embedded SQL cho phép mã lệnh SQL được gọi từ một ngôn ngữ lập trình như C và COBOL. Các đặc trưng của Dynamic SQL cho phép một truy vấn được xây dựng và thực hiện run-time.
- Truy vấn cơ sở dữ liệu từ xa và các thực hiện Client-Server (Client-Server Execution and Remote Database Access): Những lệnh này điều khiển cách thức các chương trình ứng dụng trên Client kết nối với cơ sở dữ liệu trên Server, hoặc truy cập dữ liệu trong cơ sở dữ liệu trên mạng. Chúng ta sẽ khám phá những lệnh này trong Chương 7.
- Quản lý giao dịch (Transaction Management): Một loạt các lệnh cho phép người dùng điều khiển các giao dịch.
- Bảo mật (Security): SQL cung cấp cơ chế để điều khiển các truy cập của người dùng tới các đối tượng dữ liệu như các bảng và các khung nhìn.
- Các đặc trưng nâng cao (Advanced features): Chuẩn SQL:1999 bao gồm các tính năng hướng đối tượng (Chương 23), các truy vấn lồng nhau (Chương 24), truy vấn hỗ trợ quyết định (Chương 25), khai phá dữ liệu (Chương 26), dữ liệu không gian (Chương 28) và quản lý dữ liệu XML và văn bản (Chương 27).
- Tổ chức chương
Các phần của chương được tổ chức như sau. Phần 2 trình bày về các truy vấn SQL cơ bản và Phần 3 giới thiệu các phép toán tập hợp. Phần 4 trình bày về các truy vấn lồng nhau. Phần 5.5 khám phá các phép toán nhóm. Chúng ta bàn tới các giá trị null trong Phần 6. Phần 7 xem xét các lệnh SQL được nhúng trong ngôn ngữ lập trình và trong Phần 8 bàn tới các bộ giá trị trong quan hệ được truy cập như thế nào bằng con trỏ. Phần 9 trình bày như thế nào truy vấn được xây dựng sử dụng dynamic SQL, và trong Phần 10 bàn về hai chuẩn kết nối tới DBMS là ODBC và JDBC. Phần 11 bàn về các ràng buộc toàn vẹn phức tạp được thực hiện trong SQL DDL, phần tiếp theo của những phần đã trình bày trong Chương 3; những ràng buộc mới cho phép chúng ta tận dụng đầy đủ khả năng của ngôn ngữ truy vấn SQL.
Cuối cùng, Phần 12 và 13 trình bày về khái niệm cơ sở dữ liệu hoạt động. Một cơ sở dữ liệu hoạt động có một tập các triggers do các DBA định nghĩa. DBMS điều khiển các cơ sở dữ liệu, phát hiện tình trạng hiện tại của cơ sở dữ liệu và gọi đến các trigger.
Các ví dụ:
Chúng ta sẽ xem xét một số ví dụ truy vấn sử dụng các bảng được định nghĩa sau:
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
Chúng ta sẽ cung cấp cho mỗi truy vấn một mã duy nhất, và sử dụng tiếp các truy vấn trong Chương 4. Truy vấn mới đầu tiên của chương này có mã số Q15. Truy vấn từ Q1 đến Q14 được định nghĩa trong Chương 4. Chúng ta sử dụng minh họa quan hệ S3 của Sailors, R2 của Reserves, và B1 của Boats giới thiệu trong Chương 4, hình 1, 2 và 3 để làm dữ liệu ví dụ cho các truy vấn.
Tất cả các bảng và truy vấn trong chương này có thể được tìm thấy trong Website:
http://www.cs.wisc.edu/~dbbook
Những vấn đề được chỉ ra bao gồm cách thức thực hiện câu lệnh SQL trong Oracle, IBM DB2, Microsoft SQL Server, và MySQL, và các scripts để tạo các bảng và các truy vấn ví dụ.
Dạng đơn giản của truy vấn SQL
Phần này trình bày cú pháp của truy vấn SQL đơn giản và giải thích ý nghĩa của nó thông qua chiếnlược thực hiện truy vấn ở mức khái niệm. Chiến lược thực hiện truy vấn ở mức khái niệm là cách để thực hiện truy vấn theo chiều hướng dễ hiểu, không tập trung vào hiệu năng. DBMS sẽ thực hiện truy vấn có thể theo một cách khác hiệu quả hơn cách người dùng định nghĩa.
Dạng đơn giản của truy vấn SQL như sau:
Tất cả các truy vấn phải có mệnh đề SELECT, chỉ ra những cột nào được đưa ra trong kết quả, và mệnh đề FROM xác định bảng chứa những cột liên quan trong câu lệnh. Mệnh đề WHERE xác định điều kiện chọn.
Chúng ta đã biết rằng một truy vấn liên quan tới các biểu thức trong đại số quan hệ, bao gồm phép chọn, phép chiếu, phép nhân chéo. Quan hệ chặt chẽ giữa đại số quan hệ và SQL là nền tảng để tối ưu hóa truy vấn trong DBMS quan hệ, chúng ta sẽ xem trong Chương 12 và 15. Kế hoạch thực hiện truy vấn SQL được biểu diễn sử dụng hàng loạt các biểu thức đại số quan hệ (Phần 15.1).
(Q15) Đưa ra tên và tuổi của tất cả các thủy thủ.
SELECT DISTINCT S.sname, S.age
FROM Sailors S
Hình 3 Minh họa B1 của Boats
Kết quả của truy vấn này trong trường hợp có và không có từ khóa DISTINCT dựa trên minh họa của Sailors chỉ ra trong Hình 4 và 5. Chỉ có sự khác nhau trong hai kết quả này là sự xuất hiện hay không xuất hiện của bộ giá trị có tên là Horatio và tuổi là 35.
Truy vấn tiếp theo có sử dụng đến phép chọn trong đại số quan hệ.
SELECT S.sid, S.sname, S.rating, S.age
FROM Sailors AS S
WHERE S.rating > 7
Như hai ví dụ minh họa ở trên, mệnh đề SELECT được sử dụng để thực hiện phép chiếu, trong khi phép chọn trong đại số quan hệ được biểu diễn sau mệnh đề WHERE! Chúng ta sẽ đề cập chi tiết đến các cú pháp của truy vấn SQL đơn giản:
- Cụm from-list sau mệnh đề FROM là danh sách tên các bảng. Tên bảng có thể được thay bằng một biến miền, biến miền hữu ích khi cùng một tên bảng xuất hiện nhiều hơn một lần trong from-list.
- Cụm select-list là danh sách tên các cột của bảng trong from-list. Các tên cột có thể có tiền tố là các biến miền.
- Qualification trong mệnh đề WHERE là một tổ hợp logic (tức là, biểu thức sử dụng kết nối logic AND, OR, NOT) của các điều kiện theo dạng Biểu_thứcopBiểu_thức, trong đó op là một trong những toán tử logic >, <, >=, <=, =. Một biểu thức là tên cột, hằng số, hoặc một biểu thức (toán học hoặc xâu ký tự).
- Từ khóa DISTINCT là từ khóa lựa chọn. Nó chỉ ra rằng bảng kết quả của truy vấn có nên chứa bộ giá trị trùng nhau không. Mặc định, những bộ giá trị trùng nhau không được phép loại bỏ.
Mặc dù, những quy tắc phía trên biểu diễn cú pháp của truy vấn SQL cơ bản, nhưng chúng không nói cho chúng ta biết ý nghĩa của một truy vấn. Kết quả của một truy vấn là một quan hệ bao gồm một tập các dòng! - những nội dung này có thể được hiểu bằng việc xem xét chiến lược thực hiện truy vấn ở mức khái niệm sau:
- Tìm kết quả của phép nhân chéo trên các bảng trong from-list.
- Xoá các dòng trong kết quả của phép nhân chéo không thoả mãn các điều kiện trong qualification.
- Xoá tất cả các cột không xuất hiện trong select-list.
- Nếu có từ khoá DISTINCT, kết quả của truy vấn sẽ bỏ đi những dòng trùng nhau.
Chiến lược thực hiện truy vấn ở mức khái niệm nói trên làm chúng ta nhìn thấy rõ ràng những dòng nào cần được xuất hiện trong kết quả. Tuy nhiên, nó dường như là không thực sự hiệu quả (tốc độ thực hiện truy vấn chậm). Chúng ta sẽ xem xét chi tiết về cách thực hiện một truy vấn như thế nào trong Chương 12 và 13; bây giờ, mục đích của chúng ta chỉ đơn giản là giải thích ý nghĩa của truy vấn. Chúng ta sử dụng truy vấn sau để minh hoạ chiến lược thực hiện truy vấn ở mức khái niệm:
(Q1) Tìm tên các thuỷ thủ đã phục vụ trên tàu mã số 103.
Câu lệnh SQL như sau:
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid = R.sid AND R.bid=103
Bước đầu tiên là xây dựng phép nhân chéo S4 × R3, kết quả chỉ ra trong Hình 8.
Bước thứ hai là áp dụng điều kiện S.sid=R.sid AND R.bid=103 (Ghi nhớ rằng phép so sánh đầu tiên là yêu cầu của phép nối). Bước này loại bỏ tất cả trừ dòng cuối cùng trong minh hoạ chỉ ra trong Hình 5.8. Bước thứ 3 là loại bỏ những cột không cần thiết, chỉ sname xuất hiện trong mệnh đề SELECT là cần được giữ lại. Bước này đưa chúng ta đến kết quả trong Hình 9, bảng chỉ có một cột và một dòng.
Hình 6. Minh hoạ R3 của Reserves
Bây giờ, chúng tôi trình bày một số ví dụ truy vấn, rất nhiều trong số này đã được trình bày trong phần đại số quan hệ và các phép toán logic (Chương 4). Ví dụ đầu tiên minh hoạ cách sử dụng các biến miền là biến không bắt buộc. Truy vấn Q1 bàn đến trong phần trước có thể được viết như sau:
SELECT sname
FROM Sailors S, Reserves R
WHERE S.sid = R.sid AND bid=103
SELECT sname
FROM Sailors, Reserves
WHERE Sailors.sid = Reserves.sid AND bid=103
(Q16) Tìm các sid của thuỷ thủ đã phục vụ trên tàu màu 'red'.
SELECT R.sid
FROM Boats B, Reserves R
WHERE B.bid = R.bid AND B.color = 'red'
(Q2) Tìm tên các thuỷ thủ đã phục vụ trên tàu màu 'red'.
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red'
Truy vấn này chứa phép nối giữa ba bảng, tiếp đến là điều kiện chọn màu của tàu. Kết nối với Sailors cho phép chúng ta tìm tên các thuỷ thủ.
(Q3) Tìm màu của các tàu đã được Lubber phục vụ.
SELECT B.color
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND S.sname = 'Lubber'
(Q4) Tìm tên các thuỷ thủ đã phục vụ trên ít nhất một tàu.
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid = R.sid
SQL hỗ trợ một phiên bản chung của select-list, trong đó không chỉ có danh sách các cột. Mỗi mục trong select-list có thể có dạng biểu-thức AS tên-cột, trong đó biểu-thức là một biểu thức toán hoặc một xâu ký tự của các cột (tiền tố có thể là một biến miền) và các hằng số. Nó có thể chứa hàm nhóm như sum và count, phần này chúng ta sẽ bàn trong Phần 5. Chuẩn SQL cũng hỗ trợ các biểu thức dạng ngày và tháng, phần này chúng ta sẽ không bàn ở đây. Mặc dù không phải là một thành phần của chuẩn SQL, rất nhiều các hệ thống thực thi cũng hỗ trợ sử dụng các hàm hỗ trợ như sqrt, sin và mod.
Biểu thức thông thường trong SQL: Phản ảnh tầm quan trọng của dữ liệu dạng ký tự, SQL: 1999 bao gồm một toán tử mạnh LIKE, còn gọi là SIMILAR. Toán tử này hỗ trợ mạnh mẽ việc tìm kiếm ký tự. Những biểu thức thông thường cũng được hệ điều hành Unix hỗ trợ phục vụ cho tìm kiếm xâu ký tự, mặc dù cú pháp có khác nhau một chút.
Đại số quan hệ và SQL: Tập các phép toán trong SQL có thể thực thi trong đại số quan hệ.
(Q17) Tăng rating lên 1 cho những người đã phục vụ trên hai tàu khác nhau trong một ngày.
SELECT S.sname, S.rating+1 AS rating
FROM Sailors S, Reserves R1, Reserves R2
WHERE S.sid = R1.sid AND S.sid = R2.sid
AND R1.day = R2.day AND R1.bid <> R2.bid
SELECT S1.sname AS name1, S2.sname AS name2
FROM Sailors S1, Sailors S2
WHERE 2*S1.rating = S2.rating-1
Thêm vào đó, SQL cung cấp khả năng so sánh mẫu thông qua toán tử LIKE, cùng với việc sử dụng ký hiệu % (thay thế cho số không hoặc nhiều ký tự bất kỳ) và _ (thay thế cho chính xác một ký tự bất kỳ). Vì thế '_AB%' là một mẫu phù hợp với tất cả các xâu có chứa ít nhất 3 ký tự, trong đó ký tự thứ 2 và thứ 3 là A và B tương ứng. Ghi nhớ rằng, không như những toán tử so sánh khác, ký tự trống cũng quan trọng trong toán tử LIKE. Vì thế, 'Jept' ='Jept ' là đúng, trong khi 'Jept' LIKE 'Jept' là sai. Một ví dụ sử dụng LIKE trong truy vấn như sau:
(Q18) Tìm tuổi các thuỷ thủ có tên bắt đầu và kết thúc bằng ký tự B và có ít nhất 3 ký tự.
SELECT S.age
FROM Sailors S
WHERE S.sname LIKE 'B_%B'
Hợp, giao và trừ
SQL cung cấp 3 cấu trúc thực hiện trên tập hợp, phần mở rộng của các dạng truy vấn cơ bản phía trước. Vì kết quả của một truy vấn là tập hợp các dòng nên việc xem xét đến các phép toán như hợp (UNION), giao (INTERSECT), trừ (EXCEPT) là rất tự nhiên. SQL hỗ trợ ba phép toán này thông qua từ khoá UNION, INTERSECT, and EXCEPT. SQL cũng cung cấp các phép toán tập hợp khác: IN (để kiểm tra một phần tử nào đó có ở trong tập hợp hay không), op ANY, op ALL (để so sánh một giá trị cùng với các phần tử trong một tập hợp nào đó, sử dụng phép so sánh op), và EXIST (để kiểm tra một tập hợp có rỗng hay không), Chúng ta khám phá các toán tử UNION, INTERSECT, và EXCEPT trong phần này, và các toán tử khác trong Phần 4.
Xem xét truy vấn sau đây:
(Q5) Tìm tên các thuỷ thủ đã phục vụ trên tàu màu đỏ hoặc màu xanh.
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid
AND (B.color = 'red' OR B.color = 'green')
(Q6) Tìm tên các thuỷ thủ đã phục vụ trên cả hai tàu màu đỏ và màu xanh.
Nếu chúng ta chỉ thay thế OR bằng AND, chúng ta sẽ nhận được tên các thuỷ thủ đã phục vụ trên một tàu có hai màu đỏ và xanh. Một ràng buộc tham chiếu chỉ ra rằng bid là khoá của Boats chứng tỏ rằng cùng một tàu không thể có hai màu, vì thế sự thay đổi AND cho OR trong truy vấn trước sẽ trả lại kết quả là một tập rỗng. Câu lệnh đúng của Q6 sử dụng toán tử AND như sau:
SELECT S.sname
FROM Sailors S, Reserves R1, Boats B1, Reserves R2, Boats B2
WHERE S.sid = R1.sid AND R1.bid = B1.bid
AND S.sid = R2.sid AND R2.bid = B2.bid
AND B1.color='red' AND B2.color = 'green'
Truy vấn trước rất khó để hiểu (và cũng không hiệu quả cho việc thực hiện). Giải pháp tốt cho hai truy vấn này là sử dụng UNION và INTERSECT.
Truy vấn Q5 có thể viết lại như sau:
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red'
UNION
SELECT S2.sname
FROM Sailors S2, Boats B2, Reserves R2
WHERE S2.sid = R2.sid AND R2.bid = B2.bid AND B2.color = 'green'
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red'
INTERSECT
SELECT S2.sname
FROM Sailors S2, Boats B2, Reserves R2
WHERE S2.sid = R2.sid AND R2.bid = B2.bid AND B2.color = 'green'
Như chúng ta đã quan sát trong chương 4, vấn đề này tồn tại bởi vì sname không xác định được một thuỷ thủ nào đó, vì sname không phải là khoá của Sailor. Nếu chúng ta chọn sid thay cho sname, chúng ta sẽ đưa ra được tập các thuỷ thủ đã phục vụ trên cả hai tàu màu đỏ và màu xanh. (Để đưa ra được tên các thuỷ thủ như yêu cầu của truy vấn, chúng ta sẽ phải sử dụng đến truy vấn lồng nhau, chúng ta sẽ bàn đến ví dụ này trong Phần 5.4.4)
Truy vấn tiếp theo minh hoạ phép toán tập hợp trong SQL là:
(Q19) Tìm sid của các thuỷ thủ đã phục vụ trên tàu màu đỏ, nhưng không phục vụ trên tàu màu xanh.
SELECT S.sid
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red'
EXCEPT
SELECT S2.sid
FROM Sailors S2, Reserves R2, Boats B2
WHERE S2.sid = R2.sid AND R2.bid = B2.bid AND B2.color ='green'
Ngoài ra, vì quan hệ Reserves chứa trường sid nên chúng ta không cần tìm trong quan hệ Sailors, và chúng ta có thể sử dụng một truy vấn đơn giản hơn.
SELECT R.sid
FROM Boats B, Reserves R
WHERE R.bid = B.bid AND B.color = 'red'
EXCEPT
SELECT R2.sid
FROM Boats B2, Reserves R2
WHERE R2.bid = B2.bid AND B2.color = 'green'
(Q20) Tìm tất cả các sid của thuỷ thủ có rating là 10 hoặc đã phục vụ trên tàu 104.
SELECT S.sid
FROM Sailors S
WHERE S.rating = 10
UNION
SELECT R.sid
FROM Reserves R
WHERE R.bid = 104
Truy vấn lồng nhau
Một trong các tính năng mạnh mẽ nhất của SQL là truy vấn lồng nhau. Một truy vấn lồng nhau là một truy vấn có một hay nhiều truy vấn khác bên trong nó, truy vấn bên trong được gọi là truy vấn con. Khi viết một truy vấn, đôi khi chúng ta cần biểu diễn một điều kiện, điều kiện đó liên quan đến kết quả của một truy vấn khác. Truy vấn con thông thường xuất hiện trong mệnh đề WHERE. Truy vấn con đôi khi cũng xuất hiện trong mệnh đề FROM hoặc HAVING (chúng tôi sẽ trình bày trong Phần 5)
Phần này chỉ bàn tới những truy vấn con xuất hiện trong mệnh đề WHERE. Cách làm truy vấn con trong các mệnh đề khác cũng tương tự. Những ví dụ về truy vấn con xuất hiện trong mệnh đề FROM sẽ được trình bày trong Phần 5.5.1.
Đại số quan hệ và SQL: Sự lồng nhau của các truy vấn là một tính năng không thể biểu diễn được trong đại số quan hệ, nhưng các truy vấn con có thể được chuyển vào đại số, chúng ta sẽ nhìn thấy trong Chương 15. Sự lồng nhau trong SQL được các phép toán logic trên quan hệ hỗ trợ nhiều hơn là được đại số quan hệ hỗ trợ. Trong mối liên quan với một số tính năng khác của SQL, như các toán tử hỗ trợ tập hợp và nhóm, lồng nhau là một cấu trúc thường xuyên được sử dụng.
Giới thiệu về các truy vấn lồng nhau
Để ví dụ, chúng ta cùng nhau viết lại truy vấn sau sử dụng truy vấn lồng nhau:
(Q1) Tìm tên các thuỷ thủ đã phục vụ trên tàu 103.
SELECT S.sname
FROM Sailors S
WHERE S.sid IN ( SELECT R.sid
FROM Reserves R
WHERE R.bid = 103 )
Truy vấn con tìm ra một tập các sid của thuỷ thủ đã phục vu trên tàu 103 (đó là 22, 31, và 74 trong minh hoạ R2 và S3), và truy vấn bên trên đưa ra tên các thuỷ thủ có sid nằm trong tập đó. Toán tử IN cho phép chúng ta kiểm tra một giá trị có nằm trong một tập các phần tử; một truy vấn SQL được sử dụng để để đưa ra tập các bộ giá trị thoả mãn điều kiện kiểm tra. Ghi nhớ rằng, nếu muốn đưa ra tập các thuỷ thủ không phục vụ trên tàu 103 thì rất đơn giản chỉ cần thay IN bằng NOT IN.
Cách tốt nhất để hiểu truy vấn lồng nhau là chỉ nghĩ đến nó trong ngữ cảnh đánh giá truy vấn ở mức khái niệm. Trong ví dụ của chúng ta, chiến lược thực hiện ở đây là: đầu tiên kiểm tra các dòng trong Sailors, sau đó ứng với từng dòng này, thực hiện truy vấn con trên quan hệ Reserves. Nói chung, chiến lược đánh giá khái niệm chúng ta đã sử dụng để biểu diễn việc định nghĩa một truy vấn có thể được mở rộng để biểu diễn truy vấn lồng nhau như sau: Xây dựng phép nhân chéo giữa các bảng trong mệnh đề FROM. Với mỗi dòng trong phép nhân chéo, khi kiểm tra điều kiện trong mệnh đề WHERE, tính toán (lại) truy vấn con. Tất nhiên, truy vấn con bản thân nó có thể chứa những truy vấn con khác, trong trường hợp này chúng ta tiếp tục áp dụng ý tưởng này.
Để biểu diễn truy vấn lồng nhau nhiều mức, chúng ta viết lại truy vấn Q2 như sau:
(Q2) Tìm tên các thuỷ thủ đã phục vụ trên tàu màu đỏ.
SELECT S.sname
FROM Sailors S
WHERE S.sid IN (SELECT R.sid
FROM Reserves R
WHERE R.bid IN (SELECT B.bid
FROM Boats B
WHERE B.color = 'red' )
Truy vấn trong cùng tìm ra tập các bid của tàu màu đỏ (102 và 104 trong minh hoạ B1). Truy vấn con phía trên tìm ra tập các thuỷ thủ đã phục vụ trên một trong những tàu này. Trong minh hoạ B1, R2 và S3, tập sid là 22, 31 và 64. Truy vấn trên cùng tìm ra tên các thuỷ thủ có sid nằm trong tập sid trên. Trong các minh hoạ này, kết quả có Dustin, Lubber, và Horatio.
Để tìm tên các thuỷ thủ không phục vụ trên tàu màu đỏ, chúng ta chỉ thay thế IN bằng NOT IN.
(Q21) Tìm tên các thuỷ thủ chưa từng phục vụ trên tàu nào có màu đỏ.
SELECT S.sname
FROM Sailors S
WHERE S.sid NOT IN ( SELECT R.sid
FROM Reserves R
WHERE R.bid IN (SELECT B.bid
FROM Boats B
WHERE B.color = 'red' )
Truy vấn này đưa ra tên các thuỷ thủ có sid không nằm trong tập 22, 31 và 64.
Ngược lại với truy vấn Q21, chúng ta có thể sửa truy vấn trước (phiên bản lồng nhau của Q2) bằng việc thay thế IN bằng NOT IN trong truy vấn con bên trong. Việc sửa này sẽ đưa ra tên các thuỷ thủ đã phục vụ trên tàu không phải màu đỏ. Hãy cùng chúng tôi xem xét cụ thể. Trong truy vấn con, chúng ta kiểm tra R.bid không phải là 102 và 104 (bid của những tàu màu đỏ). Truy vấn bên ngoài tìm ra những bộ giá trị trong Reserves có bid không phải là 102 và 104. Trên minh hoạ B1, R2 và S3, truy vấn ngoài trả lại kết quả là tập sid 22, 31, 64 và 74. Cuối cùng, chúng ta tìm ra tên các thuỷ thủ có sid nằm trong tập này.
Chúng ta có thể sửa truy vấn lồng nhau Q2 bằng việc thay thế cả hai từ khoá IN bằng NOT IN. Việc thay thế này sẽ giúp tìm ra tên các thuỷ thủ chưa từng phục vụ trên tàu không phải màu đỏ, tức là, họ chỉ phục vụ trên những tàu có màu đỏ. Trên minh hoạ B1, R2 và S3, truy vấn bên ngoài đưa ra tập các sid của thuỷ thủ không phải là 22, 31, 64 và 74. Tập này là 29, 32, 58, 71, 85, và 95. Chúng ta sau đó sẽ tìm ra tên tương ứng của từng thuỷ thủ.
Truy vấn lồng nhau liên kết
Trong các truy vấn lồng nhau chúng ta đã xem xét phía trước, truy vấn bên trong thực hiện độc lập với truy vấn bên ngoài. Thông thường, truy vấn bên trong có thể phụ thuộc vào bản ghi đang xét ở truy vấn bên ngoài (xét về khía cạnh kế hoạch thực hiện truy vấn). Hãy cùng chúng tôi viết lại truy vấn sau:
(Q1) Tìm tên các thuỷ thủ đã phục vụ trên tàu 103
SELECT S.sname
FROM Sailors S
WHERE EXISTS ( SELECT *
FROM Reserves R
WHERE R.bid = 103 AND R.sid = S.sid )
Toán tử EXISTS là toán tử so sánh trên tập hợp, giống như IN. Nó cho phép chúng ta kiểm tra một giá trị nào đó có tồn tại trong một tập hợp không. Vì thế, với mỗi thuỷ thủ trên dòng S, chúng ta kiểm tra giá trị này trên tập các dòng của Reserves (R) xem có rỗng không. Nếu không rỗng, tức là thuỷ thủ S đã phục vụ trên tàu 103 và chúng ta sẽ đưa ra tên của họ. Truy vấn con rõ ràng phụ thuộc vào dòng đang xét S và phải xem xét trên từng dòng của Sailors. Sự xuất hiện của S trong truy vấn con (có dạng S.sid) được gọi là liên kết, và truy vấn như thế này gọi là những truy vấn liên kết.
Trong ví dụ phía trước, bằng việc sử dụng NOT EXIST thay cho EXIST, chúng ta có thể đưa ra tên các thuỷ thủ chưa từng phục vụ trên tàu màu đỏ. Liên quan chặt chẽ đến EXISTS là xác nhận UNIQUE. Khi chúng ta áp dụng UNIQUE trong truy vấn con, nó sẽ trả lại True nếu không có dòng nào xuất hiện hai lần trong kết quả của truy vấn con, có nghĩa là, ở đó không có sự trùng nhau; trong trường hợp đặc biệt, nó trả lại True nếu kết quả truy vấn con rỗng (Và đây cũng là trường hợp của NOT UNIQUE)
Các toán tử so sánh trên tập hợp
Chúng ta đã xem xét một số toán tử trên tập hợp là EXISTS, IN và UNIQUE và trường hợp phủ định tương ứng. SQL cũng hỗ trợ op ANY và op ALL, trong đó op là một trong những phép so sánh >, <=, =, <, >= (SOME cũng có thể sử dụng, nhưng nó cùng nghĩa với ANY)
(Q22) Tìm những thuỷ thủ có rating lớn hơn của bất kỳ thuỷ thủ nào có tên là Horatio.
SELECT S.sid
FROM Sailors S
WHERE S.rating > ANY ( SELECT S2.rating
FROM Sailors S2
WHERE S2.sname = 'Horatio' )
Nếu chúng ta có một vài thuỷ thủ có tên là Horatio, truy vấn này tìm ra tất cả các thuỷ thủ có rating cao hơn một thủy thủ nào đó có tên Horatio. Trên minh hoạ S3, kết quả truy vấn này là các sid 31, 32, 58, 71, và 74. Nếu ở đó không có thuỷ thủ nào có tên là Horatio thì sao? Trong trường hợp này so sánh S.rating> ANY … được định nghĩa để trả về giá trị False, và truy vấn trên trả về tập kết quả là rỗng. Để hiểu những so sánh có chứa ANY, chúng ta cần phải biết rằng những so sánh được thực hiện so sánh lặp đi lặp lại. Trong ví dụ trên, S.rating được so sánh thành công với một số giá trị rating trong kết quả của truy vấn con. Truy vấn con phải trả lại dòng mà kết quả so sánh là True. So sánh trả về giá trị True nếu một trong số so sánh trên từng dòng trả lại giá trị True.
(Q23) Tìm các thuỷ thủ có rating lớn hơn của tất cả các thuỷ thủ có tên là Horatio.
Chúng ta có thể đạt được yêu cầu trên bằng việc sửa truy vấn Q22 một chút, chỉ cần thay thế ANY bằng ALL trong mệnh đề WHERE của truy vấn ngoài. Trong minh hoạ S3, chúng ta sẽ có sids là 58 và 71. Nếu ở đó không có thuỷ thủ nào có tên là Horatio, so sánh S.rating>ALL… sẽ trả về giá trị True. Sau đó, truy vấn sẽ đưa ra tên của tất cả các thuỷ thủ. Một lần nữa, chúng ta lại cần phải biết rằng việc thực hiện so sánh được lặp đi lặp lại. So sánh phải trả về giá trị True nếu tất cả các so sánh trên từng dòng trả lại giá trị True.
Một minh hoạ khác sử dụng ALL, xem xét truy vấn sau:
(Q24) Tìm tất cả các thuỷ thủ có rating cao nhất.
SELECT S.sid
FROM Sailors S
WHERE S.rating >= ALL ( SELECT S2.rating
FROM Sailors S2 )
Truy vấn con đưa ra tập rating của tất cả các thuỷ thủ. Điều kiện WHERE chỉ được thoả mãn S.rating lớn hơn hoặc bằng tất cả các giá trị rating trong truy vấn con, tức là, khi nó là giá trị rating lớn nhất. Trong minh họa S3, điều kiện thoả mãn với rating bằng 10, và các sid có rating này là 58 và 71.
Ghi nhớ rằng IN và NOT IN lần lượt tương đương với =ANY và <>ALL.
Một số ví dụ khác về truy vấn lồng nhau
Chúng ta cùng nhau xem lại một số truy vấn phía trên sử dụng toán tử INTERSECT.
(Q6) Tìm tên các thuỷ thủ đã phục vụ trên tàu màu 'red' và tàu màu 'green'
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red'
AND S.sid IN ( SELECT S2.sid
FROM Sailors S2, Boats B2, Reserves R2
WHERE S2.sid = R2.sid AND R2.bid = B2.bid
AND B2.color = 'green' )
Truy vấn này có thể được hiểu như sau: Tìm tất cả thuỷ thủ đã phục vụ trên tàu màu đỏ và thêm nữa, các sid này có trong tập các sid của thuỷ thủ đã phục vụ trên tàu màu xanh. Minh hoạ này chỉ ra cách thức một truy vấn sử dụng INTERSECT có thể được viết lại sử dụng IN, nó sẽ rất hữu ích nếu hệ thống của bạn không hỗ trợ toán tử INTERSECT. Những truy vấn sử dụng EXCEPT có thể viết lại tương đương sử dụng NOT IN. Để tìm những sid của thuỷ thủ đã phục vụ trên tàu màu đỏ nhưng chưa phục vụ trên tàu màu xanh, chúng ta chỉ cần thay thế từ khoá IN trong truy vấn trước bằng từ khoá NOT IN.
Viết lại truy vấn (Q6) sử dụng INTERSECT là phức tạp hơn vì chúng ta phải sử dụng các sid để xác định các thuỷ thủ và phải trả về tên của họ:
SELECT S3.sname
FROM Sailors S3
WHERE S3.sid IN (( SELECT R.sid
FROM Boats B, Reserves R
WHERE R.bid = B.bid AND B.color = `red' )
INTERSECT
(SELECT R2.sid
FROM Boats B2, Reserves R2
WHERE R2.bid = B2.bid AND B2.color = 'green' ))
Ví dụ tiếp theo của chúng ta minh hoạ cách biểu diễn SQL của phép chia trong đại số quan hệ.
(Q9) Tìm ra tên các thuỷ thủ đã phục vụ trên tất cả các tàu.
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS (( SELECT B.bid
FROM Boats B )
EXCEPT
(SELECT R.bid
FROM Reserves R
WHERE R.sid = S.sid ))
Ghi nhớ rằng truy vấn này là liên kết- với mỗi thuỷ thủ S, chúng ta kiểm tra để thấy rằng tập các tàu mà S đã phục vụ là một tập chứa tất cả các tàu. Cách khác để thực hiện truy vấn này mà không sử dụng EXCEPT như sau:
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS ( SELECT B.bid
FROM Boats B
WHERE NOT EXISTS ( SELECT R.bid
FROM Reserves R
WHERE R.bid = B.bid
AND R.sid = S.sid ))
Trong đó, mỗi thủy thủ chúng ta kiểm tra thì không có một tàu nào mà thuỷ thủ này chưa phục vụ.
SQL: 1999 Các hàm nhóm: Tập hợp các hàm nhóm được mở rộng rất nhiều trong chuẩn mới, bao gồm một số hàm thống kê như độ lệch chuẩn, hiệp phương sai. Tuy nhiên, các hàm nhóm mới được đóng gói trong SQL/OLAP không phải được tất cả các nhà sản xuất hỗ trợ.
Hàm nhóm
Khi truy vấn dữ liệu, chúng ta thường muốn thực hiện một số tính toán hoặc tổng kết trên những dữ liệu đó. Như chúng ta đã biết trong chương trước, SQL cho phép thực hiện các biểu thức toán học. Chúng ta sẽ xem xét khả năng tính toán trên các nhóm dữ liệu thông qua các hàm như MIN và SUM. Những đặc tính này là một trong những phần mở rộng của đại số quan hệ. SQL hỗ trợ năm hàm nhóm, có thể áp dụng trên bất kỳ cột nào, giả sử A, của một quan hệ:
1. COUNT([DISTINCT] A): Số các giá trị (khác nhau) tại cột A
2. SUM([DISTINCT] A): Tổng số của tất cả các giá trị (khác nhau) tại cột A.
3. AVG([DISTINCT] A): Giá trị trung bình của tất cả các giá trị (khác nhau) tại cột A.
4. MAX(A): Giá trị lớn nhất trên cột A
5. MIN(A): Giá trị lớn nhất trên cột A
Ghi nhớ rằng sẽ không có ý nghĩa nếu chỉ ra từ khoá DISTINCT đằng sau MIN hoặc MAX.
(Q25) Tìm tuổi trung bình của tất cả thuỷ thủ.
SELECT AVG (S.age)
FROM Sailors S
Trong minh hoạ S3, tuổi trung bình là 37.4. Tất nhiên, mệnh đề WHERE có thể được sử dụng để giới hạn bớt các thuỷ thủ tham gia vào hàm nhóm.
(Q26) Tìm tuổi trung bình của các thuỷ thủ có rating bằng 10
SELECT AVG (S.age) FROM Sailors S WHERE S.rating = 10Chỉ có hai thuỷ thủ thoả mãn điều kiện này, tuổi trung bình của họ là 25.5. MIN hoặc MAX có thể được sử dụng thay thế cho AVG trong truy vấn trên để tìm ra tuổi ít nhất và tuổi nhiều nhất của thuỷ thủ.
Tuy nhiên, tìm cả tên và tuổi của thuỷ thủ lớn tuổi nhất thì phức tạp hơn, như minh hoạ trong truy vấn tiếp theo.
(Q27) Tìm tên và tuổi của thuỷ thủ lớn tuổi nhất. Xem xét truy vấn sau và cố gắng đưa ra kết quả của truy vấn này.
SELECT S.sname, MAX (S.age)
FROM Sailors S
Mục đích của truy vấn này là trả lại chỉ tuổi lớn nhất và tên của người thuỷ thủ tương ứng. Tuy nhiên, truy vấn này không hợp lệ trong SQL - nếu mệnh đề SELECT sử dụng một hàm nhóm, thì nó phải sử dụng chỉ những hàm nhóm trừ khi truy vấn đó có chứa mệnh đề GROUP BY! (Cụ thể của hạn chế này sẽ được bàn đến trong mệnh đề GROUP BY ở Phần 5.5.1). Vì thế, chúng ta không thể sử dụng Max(S.age) cùng với S.name trong mệnh đề SELECT. Chúng ta phải sử dụng đến truy vấn con để thực hiện yêu cầu trong Q27.
SELECT S.sname, S.age
FROM Sailors S
WHERE S.age = ( SELECT MAX (S2.age)
FROM Sailors S2 )
Quan sát thấy rằng, chúng ta sử dụng kết quả của hàm nhóm trong truy vấn con như một đối số của phép so sánh. Nói đúng ra, chúng ta so sánh giá trị tuổi của tất cả các thuỷ thủ với kết quả của truy vấn con. Tuy nhiên, vì sử dụng hàm nhóm, truy vấn con đảm bảo sẽ trả về giá trị duy nhất trong một trường duy nhất, và SQL chuyển quan hệ thành một giá trị để có thể so sánh được. Sau đây là một truy vấn cho kết quả tương đương trong chuẩn SQL, tuy nhiên không nhiều hệ thống hỗ trợ kiểu truy vấn này.
SELECT S.sname, S.age
FROM Sailors S
WHERE (SELECT MAX (S2.age)
FROM Sailors S2 ) = S.age
Chúng ta có thể đếm số thuỷ thủ sử dụng mệnh đề COUNT. Ví dụ này minh hoạ việc sử dụng COUNT, nó hữu ích khi chúng ta muốn đếm xem có tất cả bao nhiêu thuỷ thủ.
(Q28) Đếm xem có tổng số bao nhiêu thuỷ thủ
SELECT COUNT (*)
FROM Sailors S
Chúng ta có thể nghĩ ký tự * như cách viết ngắn để thay thế tất cả các cột. Ngược lại với truy vấn này, truy vấn sau đây chỉ ra số các thuỷ thủ có tên khác nhau. (Ghi nhớ rằng sname không phải là khoá)
(Q29) Đếm số các thuỷ thủ có tên khác nhau
SELECT COUNT ( DISTINCT S.sname )
FROM Sailors S
Trong minh hoạ S3, kết quả của truy vấn Q28 là 10, trong khi kết quả của Q29 là 9 vì có hai thuỷ thủ có tên trùng nhau là Horatio. Nếu bỏ từ khóa DISTINCT, kết quả của Q29 là 10 vì tên Horatio được đếm hai lần. Vì thế, nếu không có DISTINCT thì Q29 và Q28 tương đương nhau. Tuy nhiên, sử dụng COUNT(*) là lựa chọn tốt khi có thể.
Hàm nhóm thường được sử dụng kết hợp với cấu trúc ANY và ALL. Ví dụ, xem xét truy vấn sau:
(Q30) Tìm tên các thủy thủ người lớn tuổi hơn người già nhất có rating bằng 10.
SELECT S.sname
FROM Sailors S
WHERE S.age > ( SELECT MAX ( S2.age )
FROM Sailors S2
WHERE S2.rating = 10 )
Trong minh họa S3, thủy thủ già nhất có rating bằng 10 là sid=58, tuổi là 35. Tên các thủy thủ lớn tuổi hơn người này là Bob, Dustin, Horatio, và Lubber. Nếu sử dụng ALL, truy vấn này có thể được viết như sau:
SELECT S.sname
FROM Sailors S
WHERE S.age > ALL ( SELECT S2.age
FROM Sailors S2
WHERE S2.rating = 10 )
Nếu thay ANY bằng ALL thì truy vấn sẽ đưa ra tên những người lớn tuổi hơn một người nào đó có rating=10. Việc này sẽ tương đương với việc thay MAX bằng MIN trong truy vấn trên.
Đại số quan hệ và SQL: Hàm nhóm là những hàm không thể biểu diễn được trong đại số quan hệ. Tương tự, cấu trúc nhóm trong SQL không thể biểu diễn được trong đại số.
Mệnh đề GROUP BY và HAVING
Phần trước, chúng ta đã áp dụng toán tử nhóm tới tất cả các dòng trong quan hệ. Thường thì chúng ta muốn áp dụng các toán tử nhóm tới một số các dòng trong quan hệ. Ví dụ, xem xét truy vấn sau:
(Q31) Tìm tuổi của các thủy thủ trẻ nhất ứng với từng mức rating.
Nếu chúng ta biết rằng rating là các số nguyên nằm trong khoảng từ 1 đến 10, chúng ta sẽ viết 10 truy vấn theo dạng:
SELECT MIN (S.age) FROM Sailors S WHERE S.rating = i
trong đó i=1,2, …, 10. Viết 10 truy vấn như thế thì rất nhàm chán và mệt mỏi. Quan trọng hơn, trên thực tế chúng ta có lẽ không biết có bao nhiêu mức rating tồn tại.
Để viết truy vấn kiểu này, chúng ta cần mở rộng các chức năng cơ bản của dạng câu lệnh SQL cơ bản, như mệnh đề GROUP BY. Trên thực tế, sự mở rộng này cũng bao gồm cả mệnh đề HAVING, là mệnh đề có thể lựa chọn để thêm vào điều kiện của nhóm (ví dụ, chúng ta có thể chỉ đưa ra những mức rating>6). Dạng chung của truy vấn SQL có mở rộng như sau:
SELECT [ DISTINCT ] select-list
FROM from-list
WHERE qualification
GROUP BY grouping-list
HAVING group-qualification
Sử dụng mệnh đề GROUP BY, chúng ta có thể viết Q31 như sau:
SELECT S.rating, MIN (S.age)
FROM Sailors S
GROUP BY S.rating
Hãy cùng chúng tôi xem xét một số điểm quan trọng liên quan đến các mệnh đề mới này:
- Cụm Select-list trong mệnh đề SELECT chứa (1) danh sách tên các cột và (2) danh sách các hàm nhóm có dạng aggop (colum-name) AS new-name. Lựa chọn AS new-name cung cấp cho cột này một tên mới trong kết quả của truy vấn. Bất kỳ toán tử nhóm nào cũng có thể được sử dụng trong aggop. Tất cả các cột xuất hiện trong (1) cũng phải xuất hiện trong grouping-list. Lý do là mỗi dòng trong kết quả tương ứng với một nhóm. Nếu một cột xuất hiện trong danh sách (1), nhưng không xuất hiện trong group-list, thì kết quả của truy vấn sẽ không rõ ràng.
- Các biểu thức xuất hiện trong group-qualification của mệnh đề HAVING phải có một giá trị duy nhất ứng với mỗi nhóm. Mệnh đề HAVING xác định những dòng kết quả nào của nhóm sẽ được đưa ra. Vì thế, cột xuất hiện trong group-qualification phải là đối số của một hàm nhóm, hoặc nó cũng phải xuất hiện trong grouping-list.
- Nếu mệnh đề GROUP BY bị bỏ đi, toàn bộ bảng được đề cập đến như là một nhóm. Chúng ta sẽ giải thích về mặt ngữ nghĩa của điều này thông qua ví dụ. Xem xét truy vấn:
(Q32) Tìm tuổi của thủy thủ trẻ nhất (ít nhất 18 tuổi) ứng với mỗi mức rating có ít nhất hai thủy thủ.
SELECT S.rating, MIN (S.age) AS minage
FROM Sailors S
WHERE S.age >= 18
GROUP BY S.rating
HAVING COUNT (*) > 1
Chúng ta sẽ đánh giá truy vấn này trong minh họa S3 của Sailors trong Hình 10. Mở rộng chiến lược thực hiện truy vấn ở mức khái niệm đã trình bày trong Phần 2, truy vấn này thực hiện như sau: Bước đầu tiên là thực hiện phép nhân chéo trên các bảng trong from-list. Vì chỉ có một quan hệ trong from-list trong Q23 nên kết quả của phép nhân chéo này chính là quan hệ trong Hình 10.
Bước thứ hai là thực hiện điều kiện trong mệnh đề WHERE, S.age>=18. Bước này loại đi dòng <71, Zorba, 10, 16.0>. Bước ba loại đi những cột không mong muốn. Chỉ những cột được đề cập trong mệnh đề SELECT, mệnh đề GROUP BY, hoặc mệnh đề HAVING là cần thiết, có nghĩa là chúng ta có thể bỏ đi sid và sname trong ví dụ của chúng ta. Kết quả chỉ ra trong Hình 11. Quan sát thấy rằng có hai dòng khác nhau với rating=3 và age=25, SQL không loại đi những bộ giá trị trùng nhau trừ khi chúng được yêu cầu bằng việc sử dụng từ khóa DISTINCT!
Bước thứ 4 là sắp xếp bảng tùy theo mệnh đề GROUP BY để xác định các nhóm. Kết quả của bước này chỉ ra trong Hình 12.
Bước thứ 5 là áp dụng group-qualification trong mệnh đề HAVING, đó là điều kiện COUNT(*)>1. Bước này loại đi những nhóm có rating bằng 1, 9 và 10. Quan sát thấy rằng thứ tự xem xét điều kiện trong mệnh đề WHERE và HAVING rất có ý nghĩa: Nếu mệnh đề WHERE không được xem xét đầu tiên, nhóm có rating=10 sẽ có mặt trong kết quả (mặc dù có một dòng không thỏa mãn điều kiện age>=18). Bước thứ 6 là đưa ra một dòng kết quả ứng một nhóm còn lại. Dòng kết quả ứng với một nhóm chứa tập con của các cột trong nhóm, cộng với một hoặc nhiều cột trong hàm nhóm. Trong ví dụ của chúng ta, mỗi dòng kết quả có cột rating và minage, cột đã được tính toán bằng cách áp dụng hàm MIN cho cột age. Kết quả của bước này chỉ ra trong Hình 13.
SQL:1999 đã giới thiệu hai hàm tập hợp mới, EVERY và ANY. Để minh họa cho những hàm này, chúng ta có thể thay thế mệnh đề HAVING trong ví dụ của chúng ta bằng
HAVING COUNT(*)>1 AND EVERY(S.age<=60)
Bước thứ 5 của quá trình thực hiện bị thay đổi do những thay đổi trong mệnh đề HAVING. Xem xét kết quả trong bước 4, chỉ ra trong Hình 12. Từ khóa EVERY yêu cầu tất cả các dòng trong nhóm phải thỏa mãn điều kiện trong group-qualification. Nhóm có rating=3 không thỏa mãn điều kiện và bị xóa, kết quả chỉ ra trong Hình 14.
Có thể thay thế truy vấn trên bằng truy vấn theo sau, điều kiện của age được đặt lên mệnh đề WHERE thay vì để ở HAVING:
SELECT S.rating, MIN (S.age) AS minage
FROM Sailors S
WHERE S.age >= 18 AND S.age<=60
GROUP BY S.rating
HAVING COUNT (*) > 1
Bây giờ, kết quả sau bước 3 của quá trình thực hiện không chứa dòng có age=63.5. Tuy nhiên, nhóm có rating=3 thỏa mãn điều kiện COUNT(*)>1, vì nó vẫn có hai dòng, và thỏa mãn điều kiện trong group-qualification được kiểm tra trong bước 5. Kết quả cuối cùng của truy vấn này chỉ ra trong Hình 15.
Các ví dụ khác của truy vấn sử dụng hàm nhóm
(Q33) Với mỗi tàu màu đỏ, tìm số lượng các phục vụ (reservation) cho từng tàu.
SELECT B.bid, COUNT (*) AS sailorcount
FROM Boats B, Reserves R
WHERE R.bid = B.bid AND B.color = 'red'
GROUP BY B.bid
Trên minh họa B1 và R2, kết quả của truy vấn này chứa hai bộ giá trị 102, 3 và 104, 2.
Quan sát thấy rằng cách viết của truy vấn phía sau đây sẽ không hợp lệ:
SELECT B.bid, COUNT (*) AS sailorcount
FROM Boats B, Reserves R
WHERE R.bid = B.bid
GROUP BY B.bid
HAVING B.color = 'red'
Cho dù là group-qualificationB.color='red' là giá trị duy nhất trên mỗi nhóm, vì thuộc tính được nhóm bid là khóa của Boats (vì thế xác định được duy nhất màu tương ứng), thì SQL cũng không cho phép truy vấn này. Chỉ những cột xuất hiện trong mệnh đề GROUP BY thì mới được phép xuất hiện trong mệnh đề HAVING, trừ khi nó xuất hiện như là đối số của một hàm nhóm trong mệnh đề HAVING.
(Q34) Tìm tuổi trung bình của các thủy thủ ứng với mỗi mức rating có ít nhất hai thủy thủ.
SELECT S.rating, AVG (S.age) AS avgage
FROM Sailors S
GROUP BY S.rating
HAVING COUNT (*) > 1
Sau khi xác định các nhóm dựa trên rating, chúng ta giữ lại chỉ những những nhóm có ít nhất hai thủy thủ. Kết quả của truy vấn áp dụng trên minh họa S3 được chỉ ra trong Hình 16.
Thêm vào một truy vấn con bên trong mệnh đề HAVING của Q34 (cách viết như với mệnh đề WHERE). Ghi nhớ rằng chúng ta có thể sử dụng S.rating bên trong truy vấn con của mệnh đề HAVING vì nó có một giá trị duy nhất cho nhóm hiện tại của các thủy thủ.
SELECT S.rating, AVG ( S.age ) AS avgage
FROM Sailors S
GROUP BY S.rating
HAVING 1 < ( SELECT COUNT (*)
FROM Sailors S2
WHERE S.rating = S2.rating )
(Q35) Tìm tuổi trung bình của các thủy thủ (ít nhất 18 tuổi) ứng với mỗi mức rating có ít nhất hai thủy thủ.
SELECT S.rating, AVG ( S.age ) AS avgage
FROM Sailors S
WHERE S. age >= 18
GROUP BY S.rating
HAVING 1 < ( SELECT COUNT (*)
FROM Sailors S2
WHERE S.rating = S2.rating )
Sự khác nhau đối với Q34, chúng ta loại đi những bộ giá trị có age<=18 và nhóm những bộ giá trị còn lại theo rating. Với mỗi nhóm, truy vấn con bên trong mệnh đề HAVING đưa ra số lượng các bộ giá trị của Sailors (không áp dụng điều kiện lựa chọn age<=18) có giá trị rating bằng với nhóm hiện tại đang xét. Nếu một nhóm có ít hơn hai thủy thủ, nó sẽ bị loại đi. Với mỗi nhóm còn lại, chúng ta đưa ra tuổi trung bình. Kết quả của truy vấn này áp dụng trên minh họa S3 chỉ ra trong Hình 15. Ghi nhớ rằng kết quả này tương đương với kết quả của truy vấn Q34, chỉ có sự khác nhau ở nhóm có rating = 10, chúng ta bỏ đi thủy thủ có age=16 trong khi tính tuổi trung bình.
(Q36) Tìm tuổi trung bình của các thủy thủ (ít nhất 18 tuổi), với mỗi mức rating chỉ ra trong kết quả phải có ít nhất hai thủy thủ.
SELECT S.rating, AVG ( S.age ) AS avgage
FROM Sailors S
WHERE S. age >18
GROUP BY S.rating
HAVING 1 < ( SELECT COUNT (*)
FROM Sailors S2
WHERE S.rating = S2.rating AND S2.age >= 18 )
Biểu diễn trên của truy vấn đã có sự thay đổi với Q35. Kết quả của truy vấn Q36 trên minh họa S3 chỉ ra trong Hình 16. Nó khác với kết quả của Q35 là không có bộ giá trị rating =10, vì chỉ có một bộ giá trị có rating=10 và age>=18.
Truy vấn Q36 thực sự tương tự với Q32, biểu diễn đơn giản như sau:
SELECT S.rating, AVG ( S.age ) AS avgage
FROM Sailors S
WHERE S. age > 18
GROUP BY S.rating
HAVING COUNT (*) > 1
Phát biểu của truy vấn Q36 chỉ ra thực tế rằng mệnh đề điều kiện trong mệnh đề WHERE được áp dụng trước điều kiện nhóm, vì thế, chỉ những thủy thủ có age>18 được đưa vào nhóm. Chúng ta sẽ xem xét cách khác để viết truy vấn này:
SELECT Temp.rating, Temp.avgage
FROM ( SELECT S.rating, AVG ( S.age ) AS avgage, COUNT (*) AS ratingcount
FROM Sailors S
WHERE S. age > 18
GROUP BY S.rating ) AS Temp
WHERE Temp.ratingcount > 1
Sự thay đổi này mang đến một số điểm đáng chú ý. Đầu tiên, mệnh đề FROM có thể chứa truy vấn con, theo chuẩn SQL. Thứ 2, mệnh đề HAVING không phải lúc nào cũng cần. Bất kỳ mệnh đề HAVING nào cũng có thể được viết lại mà không có nó, nhưng rất nhiều truy vấn sẽ đơn giản hơn nếu chúng ta sử dụng HAVING. Cuối cùng, khi một truy vấn con xuất hiện trong mệnh đề FROM, sử dụng từ khóa AS để cung cấp cho nó một tên mới khi cần thiết (vì nếu không thì chúng ta không thể biểu diễn được điều kiện Temp.ratingcount> 1 trong trường hợp này).
(Q37) Tìm những rating mà tuổi trung bình của thủy thủ bằng tuổi trung bình nhỏ nhất trên tất cả các mức rating.
Chúng ta sử dụng truy vấn này để minh họa rằng các hàm nhóm không thể được lồng nhau. Một cách viết như sau có thể được xem xét:
SELECT S.rating
FROM Sailors S
WHERE AVG (S.age) = ( SELECT MIN (AVG (S2.age))
FROM Sailors S2
GROUP BY S2.rating )
Suy nghĩ một chút cũng thấy rằng truy vấn này không thể thực hiện được cho dù ta giả sử biểu thức MIN(AVG(S2.age)) không hợp lệ được thực hiện. Trong truy vấn con, Sailors được chia vào các nhóm theo rating, và tuổi trung bình được tính trên mỗi nhóm. Với mỗi nhóm, áp dụng MIN trên các giá trị trung bình này sẽ trả lại cùng một giá trị!
Cách viết đúng của truy vấn trên như sau. Là cần thiết để đưa ra một bảng phụ lưu tuổi trung bình trên ứng với mỗi mức rating, sau đó tìm ra rating mà tuổi trung bình là nhỏ nhất.
SELECT Temp.rating, Temp.avgage
FROM ( SELECT S.rating, AVG (S.age) AS avgage,
FROM Sailors S
GROUP BY S.rating) AS Temp
WHERE Temp.avgage =(SELECT MIN (Temp.avgage) FROM Temp )
Kết quả của truy vấn này trên minh họa S3 là 10, 25.5.
Xem như là bài tập, người đọc nên xem xét xem truy vấn sau ra kết quả tương đương không, nếu không thì vì sao không:
SELECT Temp.rating, MIN ( Temp.avgage )
FROM ( SELECT S.rating, AVG (S.age) AS avgage,
FROM Sailors S
GROUP BY S.rating ) AS Temp
GROUP BY Temp.rating
Các giá trị NULL
Ở trên, chúng ta đã giả sử rằng giá trị các cột trong từng dòng đã được biết. Nhưng thực tế, ta sẽ gặp rất nhiều trường hợp mà giá trị này không có. Ví dụ, khi một thủy thủ, như Dan, tham gia một câu lạc bộ du thuyền, anh ấy có lẽ không cần đăng ký rating của mình.Vì định nghĩa của bảng có cột rating, nên giá trị rating của Dan không biết phải thêm gì vào đó. Điều cần làm ở đây là thêm vào đó một giá trị đặc biệt được gọi là giá trị chưa biết. Giả sử định nghĩa bảng Sailors được thêm vào một cột là maiden-name. Tuy nhiên, chỉ có những phụ nữ đã lập gia đình mới có họ của chồng (maiden-name). Với những phụ nữ độc thân và những người đàn ông, giá trị maiden-name sẽ không có. Một lần nữa, chúng ta phải thêm giá trị nào vào cột này cho Dan?
SQL cung cấp một giá trị cột đặc biệt gọi là null cho những trường hợp này. Sử dụng định nghĩa bảng Sailors, chúng ta sẽ nhập vào dòng 98; Dan; null; 39 để biểu diễn Dan, sự hiện diện của giá trị null làm nảy sinh nhiều vấn đề phức tạp, và chúng ta xem xét tác động của giá trị null đối với SQL trong phần này.
Các so sánh sử dụng giá trị Null
Xem xét một so sánh là rating=8. Nếu áp dụng so sánh này cho dòng của Dan, kết quả so sánh sẽ True hay False? Vì rating của Dan không biết, nó là nguyên nhân để có thể nói rằng phép so sánh này có kết quả là unknown. Trên thực tế, so sánh rating>8 và rating<8 sẽ thực hiện tốt. Nếu chúng ta so sánh hai giá trị null sử dụng >, <, = và nhiều hơn nữa, kết quả luôn luôn là unknown. Ví dụ, nếu chúng ta có null trong hai dòng phân biệt của quan hệ Sailor, bất kỳ so sánh nào cũng trả lại kết quả unknown.
SQL cũng cung cấp một phép so sánh đặc biệt IS NULL để kiểm tra giá trị của cột có null hay không; ví dụ, chúng ta có thể viết rating IS NULL, sẽ trả lại kết quả true trên dòng biểu diễn Dan. Chúng ta cũng có thể viết rating IS NOT NULL, sẽ trả lại kết quả false trên dòng biểu diễn Dan.
Các kết nối logic AND, OR, NOT
Bây giờ chúng ta tìm hiểu cách biểu diễn rating = 8 OR age < 40 và rating = 8 AND age < 40 có nghĩa là gì? Xem xét bản ghi Dan một lần nữa, vì age < 40, biểu thức đầu tiên trả lại giá trị True vì chúng ta không cần quan tâm đến rating, nhưng biểu thức thứ hai thì sao? Chúng ta chỉ có thể nói là unknown.
Nhưng ví dụ này đưa ra một điểm quan trọng- đó là chúng ta có các giá trị null, chúng ta phải định nghĩa các toán tử logic AND, OR, NOT mà kết quả trả về có ba giá trị true, false, và unknown. Chúng ta mở rộng cách hiểu bình thường của AND, OR, NOT để có thể bao được những trường hợp khi mà các đối số là unknown như trên. Biểu diễn NOT unknown được định nghĩa như unknown. OR của hai đối số cho kết quả là true nếu một trong hai đối số là true, và unknown nếu một trong các đối số là false và các trường hợp khác là unknown. (Nếu cả hai đối số là false, thì tất nhiên, kết quả sẽ là false). AND của hai đối số cho kết quả là false nếu một trong hai đối số là false, và unknown nếu một trong hai đối số là unknown và những trường hợp khác là true hoặc unknown. (Nếu cả hai đối số là true, kết quả là true).
Ảnh hưởng đến cấu trúc của SQL
Biểu thức logic xuất hiện trong rất nhiều câu lệnh SQL, và ảnh hưởng của giá trị null phải được nhận ra. Ví dụ, điều kiện trong mệnh đề WHERE loại đi những dòng (trong phép nhân chéo của các bảng mà tên có ở mệnh đề FROM) mà kết quả kiểm tra điều kiện không trả về giá trị true. Vì thế, nếu có sự hiện diện của giá trị null, bất kỳ dòng nào cho kết quả là false hoặc unknown đều bị loại bỏ. Loại bỏ các dòng có giá trị unknown có ảnh hưởng rất tinh tế và ý nghĩa trên các truy vấn, đặc biệt đối với các truy vấn con có mệnh đề EXISTS hoặc UNIQUE.
Vấn đề khác khi xuất hiện giá trị null là khi cần xác định hai dòng trong một minh hoạ quan hệ có trùng nhau hay không. SQL định nghĩa rằng hai dòng là trùng nhau nếu các cột tương đương có giá trị bằng nhau, hoặc cả hai đều chứa giá trị null. Đối lập với định nghĩa này là thực tế rằng nếu chúng ta so sánh hai giá trị null sử dụng =, kết quả sẽ là unknown! Trong ngữ cảnh của các dòng trùng nhau, so sánh này hoàn toàn được trả lại kết quả true, đó là một điều dị thường.
Như chúng ta mong đợi, các phép toán +, － , *, và / tất cả trả lại giá tri null nếu một trong các đối số là null. Tuy nhiên, nulls có thể gây ra một số điều không mong đợi khi thực hiện các phép toán trên tập hợp. COUNT(*) coi giá trị null giống như những giá trị khác, vì thế, giá trị này vẫn được đếm. Tất cả các toán tử nhóm khác (COUNT, SUM, AVG, MIN, MAX và những biến dạng của khi kết hợp với DISTINCT) hoàn toàn bỏ đi những giá trị null- vì thế SUM không được hiểu chỉ là phép cộng trên tất cả các giá trị; bước chuẩn bị ở đây là bỏ đi tất cả các giá trị null phải được đề cập tới. Như là một trường hợp đặc biệt, nếu một trong các toán tử này- không phải là COUNT- được áp dụng với chỉ những giá trị null, thì kết quả sẽ trả lại giá trị null.
Phép nối ngoài (Outer joins)
Một số những biến thể của phép nối dựa trên các giá trị null, gọi là phép nối ngoài, được SQL hỗ trợ. Xem xét việc nối hai bảng, giả sử Sailors 
c Reserves. Các bộ giá trị của Sailors không so sánh được với một dòng nào đó trong Reserves theo điều kiện nối sẽ không xuất hiện trong kết quả. Trong phép nối ngoài thì không phải như vậy.
Trên thực tế, có một vài dạng của phép nối ngoài. LEFT OUTER JOIN trả về tất cả các dòng có ở quan hệ Sailors, mặc dù các dòng ở Reserves không thỏa mãn phép so sánh. RIGHT OUTER JOIN trả về tất cả các dòng có ở quan hệ Reserves, mặc dù các dòng ở quan hệ Sailors không thỏa mãn phép so sánh. FULL OUTER JOIN trả về tất cả các dòng từ quan hệ Sailors và Reserves mặc dù không thỏa mãn điều kiện so sánh. (Tất nhiên, những dòng mà thoả mãn điều kiện so sánh luôn luôn xuất hiện trong kết quả, gọi là INNER JOINS, chúng ta đã trình bày trong chương 4).
SQL cho phép xác định các kiểu nối trong mệnh đề FROM. Ví dụ, truy vấn sau đưa ra danh sách sid, bid tương ứng với các thuỷ thủ và các tàu mà họ đã phục vụ:
SELECT Sailors.sid, Reserves.bid
FROM Sailors NATURAL LEFT OUTER JOIN Reserves R
Từ khoá NATURAL chỉ ra rằng điều kiện nối là phép nối bằng trên tất cả các thuộc tính chung (trong ví dụ này là sid), và mệnh đề WHERE không cần thiết (trừ khi chúng ta muốn thêm vào một điều kiện khác). Trên minh hoạ Sailors và Reserves trong Hình 6, truy vấn này đưa ra kết quả trong Hình 17.
Không cho phép các giá trị Null
Chúng ta có thể không cho phép các giá trị null bằng cách chỉ ra NOT NULL khi định nghĩa trường, ví dụ, sname CHAR(20) NOT NULL. Thêm nữa, các trường được định nghĩa là khoá chính cũng không cho phép các giá trị null. Vì thế, ràng buộc NOT NULL không cần phải liệt kê trong các trường là khoá chính.
Khám phá của chúng ta về giá trị null đã hoàn thành. Người đọc nên tìm hiểu thêm trong nhiều cuốn sách về SQL để có thể hiểu chi tiết hơn.
Các ràng buộc phức tạp trong SQL
Trong phần này chúng tôi sẽ trình bày về một số ràng buộc phức tạp, phần này được SQL hỗ trợ rất tốt. Các đặc trưng được bàn trong phần này bổ sung cho những ràng buộc tham chiếu của SQL đã trình bày ở Chương 3.
Các ràng buộc trên bảng đơn
Chúng ta có thể xác định những ràng buộc trên bảng đơn sử dụng các ràng buộc bảng, có dạng CHECK conditional-expression. Ví dụ, để đảm bảo rằng rating phải ở dạng số nguyên trong khoảng từ 1 đến 10, chúng ra có thể sử dụng:
CREATE TABLE Sailors
(sid INTEGER,
sname CHAR(10),
rating INTEGER,
age REAL,
PRIMARY KEY (sid),
CHECK ( rating >= 1 AND rating <= 10 ))
Để thiết đặt ràng buộc rằng, những tàu Interlake không được phục vụ, chúng ta có thể sử dụng:
CREATE TABLE Reserves
(sid INTEGER,
bid INTEGER,
day DATE,
FOREIGN KEY (sid) REFERENCES Sailors FOREIGN KEY (bid) REFERENCES Boats CONSTRAINT noInterlakeRes
CHECK ( 'Interlake' <>
(SELECT B.bname
FROM Boats B
WHERE B.bid = Reserves.bid )))
Khi một dòng được thêm vào Reserves hoặc sửa một dòng nào đó đang tồn tại, conditional expression trong ràng buộc CHECK được kiểm tra. Nếu kết quả trả về là false, lệnh bị từ chối thực hiện.
Các ràng buộc miền và các kiểu dữ liệu khác
Người dùng có thể định nghĩa miền mới sử dụng câu lệnh CREATE DOMAIN, và ràng buộc CHECK.
CREATE DOMAIN ratingval INTEGER DEFAULT 0
CHECK ( VALUE >= 1 AND VALUE <= 10 )
INTEGER là kiểu dữ liệu cơ bản của miền ratingval, và tất cả giá trị của ratingval phải thuộc kiểu này. Các giá trị trong ratingval được tiếp tục giới hạn sử dụng ràng buộc CHECK; trong định nghĩa ràng buộc này, chúng ta sử dụng từ khóa VALUE để tham chiếu tới giá trị trong miền. Bằng việc sử dụng tiện ích này, chúng ta có thể ràng buộc các giá trị trong miền này sử dụng sức mạnh của các truy vấn SQL. Khi một miền được định nghĩa, tên của miền có thể được sử dụng để giới hạn giá trị của các cột trong một bảng; chúng ta có thể sử dụng một dòng trong khai báo lược đồ, ví dụ:
rating ratingval
Lựa chọn từ khóa DEFAULT được sử dụng để hỗ trợ giá trị mặc định. Nếu miền ratingval được sử dụng trong một cột của quan hệ, và không có giá trị nào được nhập cho cột này khi thêm một bộ giá trị mới, giá trị mặc định 0 sẽ được sử dụng. (Nếu một giá trị mặc định được xác định cho một cột trong phần định nghĩa bảng, nó sẽ ưu tiên giá trị mặc định của miền giá trị). Đặc trưng này có thể được sử dụng để giảm thiểu lỗi trên dữ liệu nhập vào.
SQL hỗ trợ khái niệm giới hạn miền trong một khía cạnh quan trọng. Ví dụ, chúng ta có thể định nghĩa hai miền là Sailorid và Boatclass, mỗi miền sử dụng kiểu dữ liệu cơ bản là INTEGER. Mục đích là để thiết đặt một so sánh của giá trị Sailorid và Boatclass để nó luôn luôn bị lỗi (vì chúng được tạo ra từ các miền khác nhau); tuy nhiên, vì cả hai đều có cùng kiểu dữ liệu cơ bản, INTEGER, nên so sánh sẽ thành công trong SQL. Vấn đề này được làm rõ thông qua việc giới thiệu kiểu dữ liệu khác trong SQL:1999.
CREATE TYPE ratingtype AS INTEGER
Câu lệnh này định nghĩa một kiểu dữ liệu mới gọi là ratingtype, với INTEGER là kiểu gốc. Giá trị trong kiểu ratingtype có thể so sánh được với nhau, nhưng nó không thể so sánh được với giá trị của các kiểu khác. Đặc biệt, các giá trị của kiểu ratingtype được xem như một kiểu riêng khác với kiểu gốc-INTEGER, chúng không thể so sánh hoặc kết hợp được với INTEGER (ví dụ, cộng một giá trị kiểu Integer với một giá trị kiểu ratingtype). Nếu chúng ta muốn định nghĩa các phép toán trên kiểu mới, ví dụ, hàm Average, chúng ta phải tự làm điều này một cách tường minh. Chúng ta sẽ bàn đến cách định nghĩa các hàm trong Phần 23.4.1.
Các xác nhận (assertions): Các ràng buộc trên nhiều bảng
Các ràng buộc bảng liên quan đến một bảng đơn, mặc dù biểu thức điều kiện trong mệnh đề CHECK có thể tham chiếu sang các bảng khác. Các ràng buộc được yêu cầu hoạt động chỉ khi các bảng liên quan không rỗng. Vì thế, khi một ràng buộc có sự tham gia của một hoặc nhiều bảng, cơ chế ràng buộc bảng đôi khi làm cản trở và không thực sự được mong muốn. Để khống chế những tình trạng này, SQL hỗ trợ để tạo ra các assertions, là những ràng buộc không liên quan đến bất kỳ bảng cụ thể nào.
Như một ví dụ, giả sử rằng chúng ta hy vọng để thiết đặt ràng buộc là: số lượng của tàu cộng với số lượng của thủy thủ nên ít hơn 100. Chúng ta sẽ cố gắng tạo ràng buộc bảng sau:
CREATE TABLE Sailors
(sid INTEGER,
sname CHAR(10),
rating INTEGER,
age REAL,
PRIMARY KEY (sid),
CHECK ( rating >= 1 AND rating <= 10)
CHECK ( ( SELECT COUNT (S.sid) FROM Sailors S )
+ ( SELECT COUNT (B.bid) FROM Boats B ) < 100 ))
Giải pháp này có hai hạn chế. Nó liên quan đến Sailors, mặc dù nó bao gồm cả Boats trong định nghĩa. Quan trọng hơn, nếu một bảng Sailors rỗng, ràng buộc này được định nghĩa để luôn luôn hoạt động thậm chí cả khi chúng ta có nhiều hơn 100 dòng trên Boats! Chúng ta có thể mở rộng ràng buộc này để thêm vào một kiểm tra đảm bảo Sailors không rỗng, nhưng cách tiếp cận này gây nhiều cản trở. Giải pháp tốt nhất là tạo ra một assertion, như sau:
CREATE ASSERTION smallClub
CHECK (( SELECT COUNT (S.sid) FROM Sailors S )
+ ( SELECT COUNT (B.bid) FROM Boats B)
< 100 )
TRIGGERS và các cơ sở dữ liệu hoạt động
Trigger là một loại thủ tục đặc biệt được thực thi một cách tự động khi có một sự kiện thay đổi dữ liệu xảy ra như Update, Insert hay Delete. Trigger được dùng để đảm bảo ràng buộc dữ liệu, tính nhất quán, hoặc thực hiện các quy tắc dữ liệu phức tạp.
Trigger là đối tượng đặc biệt được tạo trên một bảng và là một phần của cơ sở dữ liệu. Trigger có thể truy vấn tới các bảng khác và có thể bao gồm các câu lệnh SQL phức tạp. Chúng ta có thể tạo trigger cho mỗi hành động trên bảng hoặc có thể kết hợp 2 hoặc 3 hành động trong một trigger.
Trigger được DBA xây dựng. Một cơ sở dữ liệu có một tập hợp các trigger được gọi là cơ sở dữ liệu hoạt động. Một trigger gồm 3 phần:
- Sự kiện: Những thay đổi ảnh hưởng tới cơ sở dữ liệu khi trigger hoạt động.
- Điều kiện: Một truy vấn hoặc một kiểm tra được thực hiện khi trigger hoạt động.
- Hoạt động: Một thủ tục được thực hiện khi trigger hoạt động và các điều kiện của nó là đúng.
Một trigger có thể được nghĩ như là một 'daemon' điều khiển một cơ sở dữ liệu, và thực hiện khi cơ sở dữ liệu có sự thay đổi ứng với sự kiện đã xác định.
Một điều kiện của một trigger có thể trả về giá trị True/False (ví dụ, tất cả các nhân viên có lương ít hơn $100,000) hoặc một truy vấn. Một truy vấn hiểu là true nếu tập kết quả không rỗng, và false nếu truy vấn không có kết quả trả về. Nếu phần điều kiện có giá trị true, những hành động của trigger được thực hiện.
Một hoạt động có thể kiểm tra kết quả của truy vấn trong một phần điều kiện của trigger, tham chiếu tới các giá trị cũ và mới của những bộ giá trị thay đổi sau khi thực hiện trigger, thực hiện các truy vấn mới và làm những thay đổi đối với cơ sở dữ liệu. Thực tế, một action có thể thực hiện một chuỗi các câu lệnh định nghĩa dữ liệu (ví dụ, tạo bảng mới, thay đổi quyền của người dùng) và các lệnh hướng-giao dịch (ví dụ, commit), hoặc gọi các thủ tục khác.
Những ví dụ của Trigger trong SQL
Một ví dụ chỉ ra trong Hình 5.20, cú pháp viết trên Oracle 7 để định nghĩa các triggers, minh họa những khái niệm cơ bản của trigger nói trên. (Cú pháp của SQL:1999 cho những trigger này tương tự như trong Oracle; chúng ta sẽ xem một ví dụ sử dụng cú pháp ngắn gọn của SQL:1999). Một trigger sử dụng init_count để khởi động một biến đếm trước khi thực hiện câu lệnh INSERT để thêm một bộ giá trị vào quan hệ Students. Trigger dùng biến tự động tăng incr_count để đếm khi có một bộ giá trị được thêm vào thỏa mãn điều kiện age <18.
CREATE TRIGGER init_count BEFORE INSERT ON Students /* Sự kiện */
DECLARE
count INTEGER;
BEGIN /* Hành động */
count := 0;
END
CREATE TRIGGER incr_count AFTER INSERT ON Students /* Sự kiện */
WHEN (new.age < 18) /* Điều kiện; 'new' là bộ giá trị mới được thêm vào */
FOR EACH ROW
BEGIN /* Hành động; một thủ tục nào đó trong Oracle's PL/SQL syntax */
count := count + 1;
END
Hình 20. Ví dụ minh hoạ Trigger
Một trong những trigger minh hoạ trong Hình 5.20 thực hiện trước khi câu lệnh thực thi, và minh hoạ khác thực hiện sau. Một trigger có thể cũng được lập lịch thực hiện thay thế cho một câu lệnh, hoặc trong cơ chế hoãn, ở cuối giao dịch ngăn lại một câu lệnh, hoặc trong cơ chế không đồng bộ, nó thực hiện như một phần của một giao dịch riêng biệt.
Trong Hình 20 minh hoạ một điểm khác về thực thi trigger: Người dùng phải xác định một trigger được thực hiện một lần mỗi khi sửa bản ghi hoặc một lần mỗi khi câu lệnh thực thi. Nếu một hoạt động phụ thuộc vào sự thay đổi của các bản ghi riêng lẻ, ví dụ, chúng ta phải kiểm tra trường age khi có một bản ghi Students được thêm vào để quyết định tăng biến đếm, sự kiện của trigger nên được định nghĩa xảy ra mỗi khi bản ghi được sửa; mệnh đề FOR EACH ROW được sử dụng để làm điều này. Kiểu trigger này được gọi là row-level trigger. Mặt khác, trigger init_count được thực hiện một lần với mỗi câu lệnh INSERT, không quan tâm đến số lượng các bản ghi được thêm vào, bởi vì chúng không có mệnh đề FOR EACH ROW. Trigger như thế này được gọi là statement-level trigger.
Trong Hình 20, từ khoá new tham chiếu tới một bộ giá trị mới được thêm vào. Nếu bộ giá trị đang tồn tại được sửa, từ khoá old và new có thể được sử dụng để tham chiếu tới các giá trị trước và sau khi có sự thay đổi. Bản SQL: 1999 phác thảo cũng cho phép hành động của trigger tham chiếu đến một tập các bản ghi đã thay đổi, không chỉ là thay đổi của một bản ghi. Ví dụ, thật hữu ích nếu có thể tham chiếu tới một tập các bản ghi mới thêm vào quan hệ Students, những bản ghi được thêm chỉ bằng một câu lệnh INSERT; chúng ta có thể đếm được số bản ghi mới có age<18 thông qua việc sử dụng truy vấn trên tập này. Như trigger được chỉ ra trong Hình 5.21, là trigger bổ sung cho các trigger trong Hình 5.20.
Định nghĩa trong hình 20 sử dụng cú pháp của SQL:1999, để minh hoạ sự tương đồng và sự khác nhau về cú pháp được sử dụng trong DBMS. Từ khoá NEW TABLE có thể được chúng ta sử dụng để cung cấp một tên mới cho tập các bộ giá trị mới được thêm. Mệnh đề FOR EACH STATEMENT chỉ ra trigger này là statement-level trigger và có thể bỏ qua vì nó là mệnh đề mặc định. Định nghĩa này không có mệnh đề WHEN; nếu có mệnh đề này, nó sẽ có mệnh đề FOR EACH STATEMENT theo sau-mệnh đề viết trước khi xác định các hành động.
Mỗi trigger được thực hiện một lần với mỗi câu lệnh SQL thêm các bộ giá trị vào bảng Students, và việc thêm từng bộ giá trị duy nhất vào bảng được thống kê lại. Hai trường đầu tiên của bộ giá trị chứa các hằng số (chỉ ra bảng được thay đổi là Students và loại thay đổi là Insert), và trường thứ ba là số lượng các bộ giá trị được thêm vào có age<18. (Trigger trong Hình 5.20 chỉ thực hiện việc đếm; trigger bổ sung được yêu cầu để thêm bộ giá trị phù hợp vào bảng thống kê).
CREATE TRIGGER set_count AFTER INSERT ON Students /* Event */
REFERENCING NEW TABLE AS InsertedTuples
FOR EACH STATEMENT
INSERT /* Action */
INTO StatisticsTable(ModifiedTable, ModificationType, Count)
SELECT 'Students', 'Insert', COUNT *
FROM InsertedTuples I
WHERE I.age < 18
Hình 20. Set-Oriented Trigger
Thiết kế cơ sở dữ liệu hoạt động
Trigger được coi là cơ chế mạnh mẽ để đối mặt với những thay đổi trên cơ sở dữ liệu, nhưng chúng phải được sử dụng hợp lý. Ảnh hưởng của tập hợp các trigger có thể rất phức tạp, và bảo trì các cơ sở dữ liệu hoạt động trở nên rất khó khăn. Thường thì, sử dụng sáng suốt những ràng buộc tham chiếu có thể thay thế được trigger.
Vì sao trigger khó hiểu
Trong một hệ thống cơ sở dữ liệu hoạt động, khi DBMS thực hiện một câu lệnh sửa đổi dữ liệu trong cơ sở dữ liệu, nó kiểm tra một số trigger được kích hoạt khi câu lệnh này thực hiện. DBMS thực hiện các trigger bằng việc xem xét các điều kiện của nó và sau đó thực hiện chúng (nếu kết quả kiểm tra điều kiện là True).
Nếu một câu lệnh làm kích hoạt nhiều hơn một trigger, DBMS sẽ lần lượt thực hiện chúng theo thứ tự ưu tiên. Một điểm quan trọng là trong quá trình thực hiện trigger có thể làm kích hoạt những trigger khác. Đặc biệt, khi một trigger thực hiện có thể kích hoạt chính bản thân nó, những trigger như thế gọi là các trigger đệ quy. Phản ứng dây truyền, và thứ tự không thể đoán trước được khi DBMS xử lý các trigger được kích hoạt làm cho những ảnh hưởng của tập các trigger trở nên khó hiểu.
Khác nhau giữa các ràng buộc và Triggers
Mục đích của trigger là đảm bảo sự nhất quán của cơ sở dữ liệu, và với mục đích này, chúng ta nên xem xét đến việc sử dụng các ràng buộc tham chiếu (ví dụ, ràng buộc khoá ngoại). Ràng buộc không tự động kích hoạt như trigger nên các ràng buộc dễ để hiểu và cũng cung cấp cho DBMS nhiều cơ hội để tối ưu hoá việc thực hiện. Ràng buộc cũng tránh cho dữ liệu trở nên thiếu nhất quán với bất kỳ loại câu lệnh nào, ngược lại trigger chỉ kích hoạt ứng với một loại câu lệnh xác định (ví dụ, câu lệnh Insert hoặc Delete). Một lần nữa, ta thấy giới hạn này làm cho ràng buộc dễ hiểu hơn.
Mặt khác, trigger cho phép chúng ta thực hiện các ràng buộc cơ sở dữ liệu bằng nhiều cách phức tạp, như những ví dụ minh sau đây:
- Giả sử rằng chúng ta có một bảng Orders có các trường itemid, quantity, customerid, và unitprice. Khi một khách hàng đặt một hoá đơn, giá trị ba trường đầu tiên được điền vào bằng người dùng (ví dụ là người bán hàng). Giá trị trường thứ tư có thể được lấy từ một bảng có tên là Items, nhưng nó cần thiết phải có mặt trong bảng Orders để có được một bản ghi hoàn thiện về sự kiện đặt hàng. Trong trường hợp giá của một Item sau đó thay đổi, chúng ta có thể định nghĩa một trigger để tìm giá trị này và đưa nó vào trường thứ tư khi có một bản ghi mới được thêm. Thêm nữa, việc giảm số lượng các trường mà người bán hàng phải nhập vào sẽ làm giảm nguy cơ dẫn đến không nhất quán dữ liệu, vì người bán hàng có thể nhập sai giá làm cho nó khác với giá lưu trong cơ sở dữ liệu.
- Tiếp tục với ví dụ trên, chúng ta có lẽ muốn thực hiện một số hoạt động khác khi một hoá đơn được nhận. Ví dụ, nếu chúng ta thanh toán qua tài khoản thì chúng ta muốn kiểm tra xem tổng số tiền cần phải trả có nhỏ hơn giới hạn thanh toán của thẻ không. Chúng ta có thể sử dụng trigger để kiểm tra; thực tế, chúng ta thậm chí có thể sử dụng ràng buộc CHECK. Tuy nhiên, sử dụng trigger cho phép chúng ta thực hiện nhiều ràng buộc phức tạp hơn. Trong trường hợp này, chúng ta có thể cho phép thanh toán số tiền vượt quá 10% khả năng của thẻ nếu khách hàng có quan hệ với công ty ít nhất một năm, và thêm khách hàng vào một bảng của các ứng cử viên cần được tăng thêm giới hạn thẻ.
Những khả năng khác của Triggers
Rất nhiều khả năng tiềm ẩn của Trigger dựa trên các ràng buộc dữ liệu. Trigger có thể được sử dụng để đáp ứng các yêu cầu không thường xuyên. Ví dụ, chúng ta có thể muốn kiểm tra những khách hàng đã mua hàng và thanh toán đầy đủ trong một tháng trước đây để khuyến mại giảm giá, nếu như thế, người bán hàng phải thông báo với khách hàng và yêu cầu cung cấp thông tin (các hoá đơn mua hàng). Chúng ta có thể thực hiện điều này bằng việc sử dụng một trigger để kiểm tra các hoá đơn thanh toán gần đây.
Các trigger có thể sinh ra một 'lịch sử' ghi lại các sự kiện để hỗ trợ các kiểm tra bảo đảm an toàn. Ví dụ, mỗi lần khách hàng đặt một hoá đơn, chúng ta tạo ra một bản ghi có Mã khách hàng và giới hạn thanh toán của thẻ, và thêm bản ghi này vào bảng Lịch sử khách hàng. Sau đó, phân tích bảng này để đưa ra đề nghị những khách hàng nào nên được tăng giới hạn thanh toán của thẻ (ví dụ, khách hàng không bao giờ thanh toán hoá đơn sai hạn và có ít nhất ba lần số tiền cần thanh toán vượt quá giới hạn của thẻ).
Trong ví dụ minh hoạ ở 8, chúng ta có thể sử dụng các trigger để tập hợp các thống kê trên bảng được truy cập và thay đổi. Một vài hệ thống cơ sở dữ liệu thậm chí sử dụng trigger bên trong như một phần cơ bản để quản lý các mô hình của quan hệ (Phần 22.11.1). Danh sách các khả năng của triggers không thể liệt kê thấu đáo, ví dụ, các trigger cũng thường được đề cập đến khi cần quản lý luồng công việc và thiết đặt các quy tắc quản lý.
Câu hỏi ôn tập
Câu trả lời của các câu hỏi tổng kết sau có thể được tìm thấy trong phần tương ứng:
- Các thành phần của SQL cơ bản? (Phần 2)
- Biến miền trong SQL là gì? Làm thế nào để bạn có thể đặt tên cho các cột của bảng kết quả truy vấn? Những hỗ trợ của SQL để so sánh các xâu ký tự? (Phần 2)
- Các phép toán SQL cung cấp trên tập (nhiều tập) các bộ giá trị, và chúng được sử dụng như thế nào khi viết truy vấn. (Phần 3)
- Truy vấn lồng nhau là gì? Truy vấn lồng nhau liên kết là gì? Sử dụng các toán tử IN, EXISTS, UNIQUE, ANY, và ALL được sử dụng như thế nào trong các truy vấn lồng nhau? Vì sao chúng hữu dụng? Minh hoạ câu trả lời của bạn bằng việc viết phép toán chia trong SQL. (Phần 4)
- Những hàm nhóm nào được SQL hỗ trợ? (Phần 2)
- Nhóm là gì? Giải thích đặc trưng này và thảo luận về cách thực hiện trong mệnh đề HAVING và WHERE. Đề cập những giới hạn mà các trường xuất hiện trong mệnh đề GROUP BY phải tuân theo. (Phần 5)
- Giá trị Null là gì? Chúng có được hỗ trợ trong mô hình quan hệ (trình bày trong Chương 3)? Chúng ảnh hưởng đến truy vấn thế nào? Trường là khoá chính của bảng có được phép chứa giá trị Null hay không? (Phần 6)
- Các loại ràng buộc SQL nào có thể được xác định bằng việc sử dụng ngôn ngữ truy vấn? Có thể biểu diễn các ràng buộc khoá chính sử dụng một trong các loại ràng buộc mới này. Nếu có thì tại sao SQL lại cung cấp cho ràng buộc khoá chính một cú pháp riêng. (Phần 7)
- Trigger là gì và ba phần của nó là gì? Sự khác nhau giữa các row-level triggers và statement-level triggers là gì? (Phần 8)
- Vì sao trigger khó hiểu? Giải thích sự khác nhau giữa trigger và ràng buộc và khi nào sử dụng trigger thay vì ràng buộc và ngược lại. Trigger được sử dụng cho những mục đích nào? (Phần 9)
Bài tập
Những vấn đề liên quan đến tất cả bài tập trong chương này có thể tìm thấy trên trang Web: http://www.cs.wisc.edu/~dbbook
Trang Web này chứa thông tin về các scripts để tạo bảng cho các ví dụ chạy trên các hệ quản trị cơ sở dữ liệu Oracle, IBM DB2, Microsoft SQL Server và MySQL.
Xem xét các lược đồ quan hệ sau: Student(snum: integer, sname: string, major: string, level: string, age: integer) Class(name: string, meets_ at: time, room: string, fid: integer)Enrolled(snum: integer, cname: string)Faculty(fid: integer, fname: string, deptid: integer)Ý nghĩa của các quan hệ này rất dễ hiểu, Enrolled (Đăng ký) có một bản ghi ứng với mỗi cặp Student-Class (Sinh viên-Lớp) khi sinh viên đăng ký học trong một lớp. Viết các truy vấn sau bằng SQL. Những bộ giá trị trùng nhau không được xuất hiện trong kết quả của truy vấn.
- Tìm tên của tất cả các sinh viên có (Level=JR) đã đăng ký lớp học do giáo viên I.Teach dạy.
- Tìm tuổi của sinh viên lớn tuổi nhất hoặc có Major là History hoặc đã đăng ký trong lớp học do I.Teach dạy.
- Tìm tên của tất cả các lớp hoặc có meet_at ở phòng R128 hoặc có trên năm sinh viên tham gia.
- Tìm tên của tất cả các sinh viên đã đăng ký tham gia trong hai lớp có giờ học trùng nhau.
- Đưa ra các level và tuổi trung bình của các sinh viên ở level này.
- Đưa ra các level và tuổi trung bình của các sinh viên ở level này, trừ level là JR.
- Tìm tên của các sinh viên đã đăng ký nhiều lớp học nhất.
- Tìm tên của sinh viên chưa đăng ký lớp học nào.
- Với mỗi giá trị tuổi (age) xuất hiện trong bảng Students, tìm giá trị xuất hiện level xuất hiện thường xuyên nhất. Ví dụ, nếu ứng với tuổi 18, có nhiều sinh viên ở level là FR hơn là ở mức SR, JR, hoặc SO, thì một cặp kết quả sẽ là (18, FR).
Trả lời: Câu trả lời của từng câu hỏi như sau:
1. SELECT DISTINCT S.Sname
FROM Student S, Class C, Enrolled E, Faculty F
WHERE S.snum = E.snum AND E.cname = C.name AND C.fid = F.fid AND F.fname = ‘I.Teach’ AND S.level = ‘JR’
2. SELECT MAX(S.age)
FROM Student S
WHERE (S.major = ‘History’)
OR S.snum IN ( SELECT E.snum
FROM Class C, Enrolled E, Faculty F
WHERE E.cname = C.name AND C.fid = F.fid
AND F.fname = ‘I.Teach’ )
3. SELECT C.name
FROM Class C
WHERE C.room = ‘R128’
OR C.name IN (SELECT E.cname
FROM Enrolled E
GROUP BY E.cname
HAVING COUNT (*) >= 5)
4. SELECT DISTINCT S.sname
FROM Student S
WHERE S.snum IN (SELECT E1.snum
FROM Enrolled E1, Enrolled E2, Class C1,Class C2
WHERE E1.snum = E2.snum AND E1.cname<> E2.cname
AND E1.cname = C1.name
AND E2.cname = C2.name
AND C1.meets_at=C2.meets_at)
5. SELECT DISTINCT F.fname
FROM Faculty F
WHERE NOT EXISTS (( SELECT *
FROM Class C )
EXCEPT
(SELECT C1.room
FROM Class C1 WHERE C1.fid = F.fid ))
6. SELECT DISTINCT F.fname
FROM Faculty F
WHERE 5 > ( SELECT COUNT (E.snum)
FROM Class C, Enrolled E
WHERE C.name = E.cname
AND C.fid = F.fid)
7. SELECT S.level, AVG(S.age)
FROM Student S
GROUP BY S.level
8. SELECT S.level, AVG(S.age)
FROM Student S
WHERE S.level <> ‘JR’
GROUP BY S.level
9. SELECT F.fname, COUNT(*) AS CourseCount
FROM Faculty F, Class C
WHERE F.fid = C.fid
GROUP BY F.fid, F.fname
HAVING EVERY ( C.room = ‘R128’ )
10. SELECT DISTINCT S.sname
FROM Student S
WHERE S.snum IN (SELECT E.snum
FROM Enrolled E
GROUP BY E.snum
HAVING COUNT (*) >= ALL (SELECT COUNT (*)
FROM Enrolled E2
GROUP BY E2.snum ))
11. SELECT DISTINCT S.sname
FROM Student S
WHERE S.snum NOT IN (SELECT E.snum
FROM Enrolled E )
12. SELECT S.age, S.level
FROM Student S
GROUP BY S.age, S.level,
HAVING S.level IN ( SELECT S1.level
FROM Student S1
WHERE S1.age = S.age
GROUP BY S1.level, S1.age
HAVING COUNT (*) >= ALL( SELECT COUNT (*)
FROM Student S2
WHERE s1.age = S2.age
GROUP BY S2.level, S2.age))
Xem xét các lược đồ quan hệ sau:
Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)
Quan hệ Danh mục (Catalog) liệt kê danh sách các giá các sản phẩm (Parts) của các nhà cung cấp (Suppliers). Viết các truy vấn sau bằng SQL:
- Tìm các pname của các sản phẩm có một vài nhà cung cấp.
- Tìm các sname của các nhà cung cấp cung cấp tất cả các sản phẩm.
- Tìm các sname của các nhà cung cấp đã cung cấp tất cả các sản phẩm màu đỏ.
- Tìm các pname của các sản phẩm được cung cấp chỉ bởi Acme Widget Suppliers.
- Tìm các sid của các nhà cung cấp giao sản phẩm với giá đắt hơn giá trung bình của tất cả các nhà cung cấp cung cấp sản phẩm đó.
- Với mỗi sản phẩm, tìm sname của các nhà cung cấp bán sản phẩm này với giá đắt nhất.
- Tìm các sid của nhà cung cấp chỉ cung cấp các sản phẩm màu đỏ.
- Tìm các sid của các nhà cung cấp cung cấp một sản phẩm màu đỏ và một sản phẩm màu xanh.
- Tìm sid của các nhà cung cấp cung cấp một sản phẩm màu đỏ hoặc một sản phẩm màu xanh.
- Với tất cả các nhà cung cấp chỉ cung cấp các sản phẩm màu xanh, in ra tên của các nhà cung cấp và tổng số sản phẩm mà nhà cung cấp này cung cấp.
- Với tất cả các nhà cung cấp cung cấp một sản phẩm màu đỏ và một sản phẩm màu xanh, in ra tên và giá sản phẩm đắt nhất mà nhà cung cấp đó cung cấp.
Trả lời: Dành cho độc giả
Các quan hệ sau đây lưu lại thông tin về các chuyến bay của hãng hàng không:
Flights(flno: integer, from: string, to: string, distance: integer,
departs: time, arrives: time, price: integer)
Aircraft(aid: integer, aname: string, cruisingrange: integer)
Certified(eid: integer, aid: integer)
Employees(eid: integer, ename: string, salary: integer)
Các quan hệ Employees mô tả các phi công và các loại nhân viên khác; tất cả các phi công được chứng nhận trên một số loại máy bay (aircraft) (nếu không có, cô/anh ấy không được coi là phi công), và chỉ những phi công được chứng nhận mới được phép bay. (Những truy vấn này sử dụng cùng một lược đồ đã được liệt kê ở Chương 4)
- Tìm tên của những loại máy bay mà tất cả các phi công điều hành chúng đều có thu nhập trên 80,000.
- Với mỗi phi công có chứng nhận bay trên nhiều hơn ba loại máy bay, tìm eid và cruisingrange lớn nhất của loại máy bay mà phi công đó có chứng nhận.
- Tìm tên của các phi công có lương ít hơn giá rẻ nhất của đường bay từ Los Angeles tới Honolulu.
- Với tất cả các loại máy bay có cruisingrange trên 1000 dặm (miles), tìm tên của loại máy bay và lương trung bình của tất cả các phi công được chứng nhận trên loại máy bay này.
- Tìm tên của các phi công được chứng nhận trên một vài loại máy Boeing.
- Tìm các aid của tất cả các hãng bay có thể được sử dụng trên đường bay từ Los Angeles tới Chicago.
- Xác định các chuyến bay có thể được điều khiển bằng tất cả các phi công có thu nhập trên $100,000.
- In ra ename của các phi công có thể điều khiển các loại máy bay có cruisingrange lớn hơn 3000 dặm, nhưng không được chứng nhận trên loại máy bay boeing.
- Hành khách muốn đi du lịch từ Madison tới New York mà không chuyển máy bay hơn hai lần. Liệt kê các lựa chọn thời gian khởi hành từ Madison nếu hành khách muốn tới New York lúc 6 p.m.
- Tính toán sự khác nhau giữa lương trung bình của một phi công và lương trung bình của tất cả các nhân viên (gồm cả phi công).
- In ra tên và lương của tất cả các nhân viên không phải là phi công, người mà có lương hơn lương trung bình của các phi công.
- In ra tên của các nhân viên có chứng nhận chỉ trên các loại máy bay có cruisingrange lớn hơn 1000 dặm.
- In ra tên của các nhân viên có chứng nhận chỉ trên các loại máy bay có cruisingrange lớn hơn 1000 dặm, nhưng trên ít nhất hai loại máy bay như vậy.
- In ra tên của các nhân viên có chứng nhận chỉ trên các loại máy bay có cruisingrange lớn hơn 1000 dặm và người đó được chứng nhận trên một vài loại máy bay Boeing.
Câu trả lời của từng câu hỏi như sau:
1. SELECT DISTINCT A.aname
FROM Aircraft A
WHERE A.Aid IN (SELECT C.aid
FROM Certified C, Employees E
WHERE C.eid = E.eid AND
NOT EXISTS ( SELECT *
FROM Employees E1
WHERE E1.eid = E.eid AND E1.salary<80000))
2. SELECT C.eid, MAX (A.cruisingrange)
FROM Certified C, Aircraft A
WHERE C.aid = A.aid
GROUP BY C.eid
HAVING COUNT (*) > 3
3. SELECT DISTINCT E.ename
FROM Employees E
WHERE E.salary < ( SELECT MIN (F.price)
FROM Flights F
WHERE F.from = ‘Los Angeles’ AND F.to = ‘Honolulu’ )
4. Quan sát rằng aid là khoá của Aircraft, nhưng đề bài yêu cầu aname; chúng ta đối mặt với sự phức tạp này bằng cách sử dụng một quan hệ Temp trung gian.
SELECT Temp.name, Temp.AvgSalary
FROM ( SELECT A.aid, A.aname AS name,
AVG (E.salary) AS AvgSalary
FROM Aircraft A, Certified C, Employees E
WHERE A.aid = C.aid AND
C.eid = E.eid AND A.cruisingrange > 1000
GROUP BY A.aid, A.aname ) AS Temp
5. SELECT DISTINCT E.ename
FROM Employees E, Certified C, Aircraft A
WHERE E.eid = C.eid AND
C.aid = A.aid AND
A.aname LIKE ‘Boeing%’
6. SELECT A.aid
FROM Aircraft A
WHERE A.cruisingrange > ( SELECT MIN (F.distance)
FROM Flights F
WHERE F.from = ‘Los Angeles’ AND F.to =‘Chicago’)
7. SELECT DISTINCT F.from, F.to
FROM Flights F
WHERE NOT EXISTS ( SELECT *
FROM Employees E
WHERE E.salary > 100000
AND
NOT EXISTS (SELECT *
FROM Aircraft A, Certified C
WHERE A.cruisingrange > F.distance
AND E.eid = C.eid AND A.aid = C.aid) )
8. SELECT DISTINCT E.ename
FROM Employees E
WHERE E.eid IN ( ( SELECT C.eid
FROM Certified C
WHERE EXISTS ( SELECT A.aid
FROM Aircraft A
WHERE A.aid = C.aid
AND A.cruisingrange > 3000 )
AND
NOT EXISTS ( SELECT A1.aid
FROM Aircraft A1
WHERE A1.aid = C.aid
AND A1.aname LIKE ‘Boeing%’ ))
9.SELECT F.departs
FROM Flights F WHERE F.flno IN (( SELECT F0.flno FROM Flights F0
WHERE F0.from = ‘Madison’ AND F0.to = ‘New York’
AND F0.arrives<‘18:00’ ) UNION
( SELECT F0.flno FROM Flights F0, Flights F1
WHERE F0.from = ‘Madison’ AND F0.to <> ‘New York’
AND F0.to = F1.from AND F1.to = ‘New York’
AND F1.departs > F0.arrives
AND F1.arrives < ‘18:00’ )
UNION ( SELECT F0.flno FROM Flights F0, Flights F1, Flights F2
WHERE F0.from = ‘Madison’
AND F0.to = F1.from AND F1.to = F2.from AND F2.to = ‘New York’
AND F0.to <> ‘New York’ AND F1.to <> ‘New York’ AND F1.departs > F0.arrives AND F2.departs > F1.arrives AND F2.arrives < ‘18:00’ ))
10. SELECT Temp1.avg- Temp2.avg FROM (SELECT AVG (E.salary) AS avg FROM Employees E
WHERE E.eid IN (SELECT DISTINCT C.eid
FROM Certified C )) AS Temp1,
(SELECT AVG (E1.salary) AS avg FROM Employees E1 ) AS Temp2
11. SELECT E.ename, E.salary FROM Employees E
WHERE E.eid NOT IN ( SELECT DISTINCT C.eid
FROM Certified C ) AND E.salary > ( SELECT AVG (E1.salary)
FROM Employees E1
WHERE E1.eid IN ( SELECT DISTINCT C1.eid FROM Certified C1 ) )
12.SELECT E.ename FROM Employees E, Certified C, Aircraft A
WHERE C.aid = A.aid AND E.eid = C.eid
GROUP BY E.eid, E.ename HAVING EVERY (A.cruisingrange > 1000)
13. SELECT E.ename
FROM3 Employees E, Certified C, Aircraft A
WHERE C.aid = A.aid AND E.eid = C.eid
GROUP BY E.eid, E.ename
HAVING EVERY (A.cruisingrange > 1000) AND COUNT (*) > 1
14. SELECT E.ename FROM Employees E, Certified C, Aircraft A
WHERE C.aid = A.aid AND E.eid = C.eid
GROUP BY E.eid, E.ename
HAVING EVERY (A.cruisingrange > 1000) AND ANY (A.aname = ’Boeing’)
Xem xét các lược đồ quan hệ sau. Một nhân viên có thể làm việc cho nhiều phòng; trường pct_ time của quan hệ Works chỉ ra tỷ lệ thời gian mà nhân viên đó làm việc cho các phòng khác nhau.
Emp(eid: integer, ename: string, age: integer, salary: real)
Works(eid: integer, did: integer, pct_time: integer)
Dept(did: integer, budget: real, managerid: integer)
Viết các truy vấn sau sử dụng SQL:
- In ra tên và tuổi của từng nhân viên làm việc cho cả hai phòng Hardware và Software.
- Với mỗi phòng có nhiều hơn 20 nhân viên yêu cầu làm việc toàn bộ thời gian, in ra did và số lượng nhân viên làm việc cho phòng đó.
- In ra tên của mỗi nhân viên có lương vượt quá budget (quỹ) của tất cả các phòng mà người đó làm việc.
- Tìm managerids của các người quản lý chỉ quản lý những phòng có budgets lớn hơn $1,000,000.
- Tìm ename của những người quản lý quản lý một phòng có budget lớn nhất.
- Nếu một người quản lý nào đó quản lý nhiều hơn một phòng, người đó điều khiển tổng số tất cả các budget của các phòng này. Tìm managerids của những người quản lý điều khiển nhiều hơn $5,000,000.
- Tìm managerids của các nhà quản lý điều khiển lượng budgets lớn nhất.
- Tìm ename của những người quản lý chỉ quản lý những phòng có budgets lớn hơn $1,000,000, nhưng có ít nhất một phòng có budgets nhỏ hơn $5,000,000.
Trả lời: Dành cho độc giả
Xem xét minh hoạ của quan hệ Sailors
- Viết các truy vấn SQL để đưa ra rating trung bình (sử dụng AVG); tổng số rating (sử dụng SUM), và số lượng các rating (sử dụng COUNT).
- Nếu bạn chia tổng số trên (sum) cho số lượng (count), kết quả có tương tự như tính trung bình không? Câu trả lời của bạn thay đổi như thế nào nếu ta áp dụng những tính toán trên trường age thay vì trường rating.
- Xem xét truy vấn sau: Tìm tên các thuỷ thủ có rating cao hơn tất cả các thuỷ thủ có age<21. Hai truy vấn SQL sau cố gắng để thực hiện yêu cầu này. Chúng có cùng một kết quả không? Nếu không, giải thích vì sao?
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS(SELECT *
FROM Sailors S2
WHERE S2.age < 21AND S.rating <= S2.rating )
SELECT *
FROM Sailors S
WHERE S.rating > ANY(SELECT S2.rating
FROM Sailors S2
WHERE S2.age < 21 )
- Xem xét minh hoạ Sailor. Cùng chúng tôi định nghĩa minh hoạ S1 của Sailors để chứa hai bộ giá trị đầu tiên, minh hoạ S2 để nó chứa hai bộ giá trị cuối cùng và S là minh hoạ trên.
- Chỉ ra kết quả thực hiện phép left outer join của S với chính bản thân nó, với điều kiện nối là sid=sid.
- Chỉ ra kết quả thực hiện phép right outer join của S với chính bản thân nó, với điều kiện nối là sid=sid.
- Chỉ ra kết quả thực hiện phép full outer join của S với chính bản thân nó, với điều kiện nối là sid=sid.
- Chỉ ra kết quả thực hiện phép left outer join của S1 với S2, với điều kiện nối là sid=sid.
- Chỉ ra kết quả thực hiện phép right outer join của S1 với S2, với điều kiện nối là sid=sid.
- Chỉ ra kết quả thực hiện phép full outer join của S1 với S2, với điều kiện nối là sid=sid.
Trả lời: Dành cho độc giả
Trả lời những câu hỏi sau:
- Giải thích cụm từ impedance mismatch trong ngữ cảnh nhúng các lệnh SQL trong ngôn ngữ lập trình trên máy trạm như C.
- Bằng cách nào giá trị của biến trong ngôn ngữ lập trình có thể được gán cho lệnh SQL nhúng trong đó.
- Giải thích cách sử dụng câu lệnh WHENEVER trong nắm bắt lỗi và trường hợp ngoại lệ.
- Giải thích sự cần thiết của con trỏ.
- Cung cấp ví dụ của một trường hợp cần thiết phải sử dụng câu lệnh SQL nhúng, vì nhiều khi chỉ sử dụng câu lệnh SQL không đủ và cần thiết phải sử dụng ngôn ngữ lập trình.
- Viết một chương trình C cùng với các câu lệnh SQL nhúng để cụ thể ví dụ của bạn trong những câu trả lời trước.
- Viết một chương trình C cùng với các câu lệnh SQL nhúng để tìm độ lệch chuẩn của tuổi các cầu thủ.
- Giải thích những cụm từ sau liên quan tới con trỏ: updatability, sensitivity, và scrol-lability
- Định nghĩa một con trỏ trên quan hệ Sailors có khả năng cập nhật (updatable), cuộn (scrollable), và trả về kết quả được sắp xếp theo age.
- Cung cấp một ví dụ về trường hợp cần thiết sử dụng dynamic SQL, đó là, thậm chí SQL nhúng không hiệu quả.
Trả lời: Dành cho độc giả
Xem xét lược đồ quan hệ sau và trả lời tóm tắt các câu hỏi:
Emp(eid: integer, ename: string, age: integer, salary: real)
Works(eid: integer, did: integer, pct_time: integer)
Dept(did: integer, budget: real, managerid: integer)
- Định nghĩa ràng buộc bảng trên Emp để đảm bảo tất cả các nhân viên có lương ít nhất $10,000.
- Định nghĩa ràng buộc bảng trên Dept để đảm bảo rằng tất cả người quản lý có age>30.
- Định nghĩa một xác nhận (assertion ) trên Dept để đảm bảo tất cả các người quản lý đều có age>30. So sánh xác nhận này với ràng buộc tương đương trên bảng. Giải thích cái nào tốt hơn.
- Viết các câu lệnh SQL để xoá tất cả các thông tin về các nhân viên có lương vượt quá lương của người quản lý. Đảm bảo rằng tất cả các ràng buộc liên quan phải thoả mãn sau những cập nhật của bạn.
Trả lời: Dành cho độc giả
Xem xét các lược đồ quan hệ sau:
Student(snum: integer, sname: string, major: string, level: string, age: integer)
Class(name: string, meets_ at: time, room: string, fid: integer)
Enrolled(snum: integer, cname: string)
Faculty(fid: integer, fname: string, deptid: integer)
Ý nghĩa của những lược đồ quan hệ này rất dễ hiểu; ví dụ; Enrolled có một bản ghi ứng với mỗi cặp Student-Class khi có một sinh viên đăng ký vào một lớp học.
- Viết các câu lệnh SQL để tạo các lược đồ quan hệ trên, bao gồm cả những yêu cầu ràng buộc khoá chính và khoá ngoại.
- Trình bày từng ràng buộc tham chiếu sau trong SQL nếu nó không là các ràng buộc khoá chính và khoá ngoại. Nếu ràng buộc không biểu diễn được bằng SQL, hãy trả lời tại sao.
- Tất cả các lớp có ít nhất 5 sinh viên và nhiều nhất 30 sinh viên đăng ký.
- Có ít nhất một lớp học trong một phòng.
- Tất cả các giáo viên phải dạy ít nhất hai khoá học.
- Chỉ những giáo viên trong bộ môn (Department) có deptid=33 dạy nhiều hơn ba khoá học.
- Tất cả sinh viên phải đăng ký khoá học có tên là Math 101.
- Lớp được lập lịch sớm nhất (tức là, lớp có giá trị meet_at nhỏ nhất) không nên cùng phòng với lớp được lập lịch muộn nhất.
- Hai lớp không được học cùng một phòng tại một thời điểm.
- Bộ môn có nhiều giáo viên nhất phải có số giáo viên gấp đôi bộ môn có ít giáo viên nhất.
- Không có bộ môn nào có nhiều hơn 10 giáo viên.
- Một sinh viên không được thêm nhiều hơn hai khoá học một lúc.
- Số lượng các chuyên ngành (majors) CS phải nhiều hơn chuyên ngành Math.
- Số lượng các khoá học khác nhau được sinh viên chuyên ngành CS đăng ký nhiều hơn số lượng các khoá học khác nhau được sinh viên chuyên ngành Math đăng ký.
- Tổng số các đăng ký trong các khoá học được dạy bằng giáo viên trong bộ môn có Depid=33 nhiều hơn số lượng của các sinh viên chuyên ngành Math.
- Phải có ít nhất một sinh viên chuyên ngành CS nếu như bảng Students có sinh viên.
- Các giáo viên của các bộ môn khác nhau không thể dạy trong cùng một phòng.
Trả lời: Dành cho độc giả
Thảo luận về ưu điểm và nhược điểm của cơ chế Trigger. Sự khác nhau giữa ràng buộc tham chiếu và trigger.
Trả lời: Dành cho độc giả
Xem xét các lược đồ quan hệ sau. Một nhân viên có thể làm việc cho nhiều phòng; trường pct_ time của quan hệ Works chỉ ra tỷ lệ thời gian mà nhân viên đó làm việc cho các phòng khác nhau.
Emp(eid: integer, ename: string, age: integer, salary: real)
Works(eid: integer, did: integer, pct_time: integer)
Dept(did: integer, budget: real, managerid: integer)
Viết các ràng buộc tham chiếu (miền, khoá, khoá ngoại, hoặc các ràng buộc kiểm tra; hoặc các xác nhận- assertions) hoặc các triggers để đảm bảo từng yêu cầu sau, các yêu cầu là độc lập.
- Nhân viên phải có mức lương nhỏ nhất là $1000.
- Tất cả các nhà quản lý phải là nhân viên.
- Tổng số tỷ lệ phần trăm thời gian của mỗi nhân viên trong các phòng khác nhau phải dưới 100%.
- Người quản lý phải có lương cao hơn bất kỳ nhân viên nào mà mình quản lý.
- Bất cứ khi nào một nhân viên được tăng lương, lương người quản lý cũng phải tăng ít nhất là bằng với mức này.
- Bất cứ khi nào một nhân viên được tăng lương, lương người quản lý cũng phải tăng ít nhất là bằng với mức này. Thêm nữa, bất cứ khi nào một nhân viên được tăng lương, qũy (budget) của phòng cũng phải tăng để lớn hơn tổng số lương của tất cả các nhân viên.
Trả lời dành cho độc giả
Bài tập lớn
Xác định tập con các truy vấn SQL được Minibase hỗ trợ.
Trà lời: Dành cho độc giả
Tài liệu tham khảo
Phiên bản gốc của SQL đã được phát triển như một ngôn ngữ truy vấn trong dự án IBM's System R, và sự phát triển ban đầu này được định hướng trong [107, 151]. Từ đó SQL đã được phát triển rộng rãi trong ngôn ngữ truy vấn quan hệ, và bây giờ nó đã trở thành một chuẩn quốc tế.
Một nghiên cứu toàn diện rất nên đọc về SQL-92 được Melton & Simon trình bày trong [524], và những tính năng chính của SQL: 1999 được trình bày trong [525]. Chúng tôi hướng người đọc tới hai quyển sách nghiên cứu về SQL. Một bản tóm lược về SQL: 1999 chuẩn được trình bày trong [237]. Những phân tích sâu sắc về SQL được trình bày trong [202]. Mặc dù có một vài vấn đề đã được SQL-92 giải quyết trong những phiên bản sau. Phần ngữ nghĩa của một số lượng lớn các câu lệnh truy vấn SQL được trình bày trong [560]. SQL: 1999 là chuẩn của tổ chức quốc tế ISO và ANSI. Melton là tác giả của ANSI và ISO SQL: 1999, tài liệu ANSI/ISO/IEC 9075 -:1999. Những tài liệu ISO liên quan là ISO/IEC 9075-: 1999. Một sự kế thừa, được đưa ra vào 2003, xây dựng trên SQL: 1999 là SQL: 2003. Bản nháp của SQL: 2003 có thể tìm được theo địa chỉ sau:
ftp://sqlstandards.org/SC32/
[774] chứa một tập các tài liệu trình bày về lĩnh vực cơ sở dữ liệu hoạt động. [794] chứa những giới thiệu sâu sắc về các luật tích cực (active rules), tìm hiểu về ngữ nghĩa, các ứng dụng và các vấn đề thiết kế.
[251] trình bày về những mở rộng SQL để phục vụ kiểm tra ràng buộc tích hợp thông qua các triggers. [123] cũng bàn về một cơ chế thủ tục, gọi là alerter, để quản lý cơ sở dữ liệu.
[185] là một văn bản gần đây đề cập đến vấn đề triggers có thể được tích hợp vào SQL mở rộng. Những kiểu cơ sở dữ liệu hoạt động có ảnh hưởng lớn bao gồm Ariel [366], HiPAC [516], ODE [18], Postgres [722], RDL [690], và Sentinel [36]. [147] so sánh kiến trúc của các hệ thống cơ sở dữ liệu hoạt động. [32] xem xét các điều kiện bên trong của tập các quy tắc tích cực, sự độc lập của thứ tự đánh giá. Những ngữ nghĩa của cơ sở dữ liệu được nghiên cứu trong [285] và [792]. Việc thiết kế và quản lý các hệ thống quy tắc tích cực được trình bày trong [60, 225]. [142] trình bày về quản lý các quy tắc sử dụng Chimera, một mô hình dữ liệu và ngôn ngữ của các hệ thống cơ sở dữ liệu hoạt động.