Chương 7: Ngôn ngữ SQL (Structured Query Language)

Chapter 7Ngôn ngữ SQL(Structured Query Language)1Ví dụCho một cơ sở dữ liệu gồm các quan hệ Sailors, Reserves và Boats.Sailors(sid: integer, sname: string, rating: integer , age: real)Boats(bid: integer, bname: string, color: string)Reserves(sid: integer , bid: integer , day: date)2Ví dụ3Câu truy vấn cơ bảnCú phápSELECT: chỉ định các cột xuất hiện trong kết quả.FROM: xác định các bảng trong câu truy vấnWHERE: tùy chọn, chỉ định điều kiện chọn ra các dòng trên các bảng trong mệnh đề FROM.4Câu truy vấn cơ bảnFrom-list: tên của các quan hệ.Select-list: danh sách các thuộc tính của các quan hệ trong from-listQualification: Các phép so sánh , =, ≤, ≥,  , AND, OR, NOT.DISTINCT: loại bỏ các giá trị trùng trong kết quả.5Câu truy vấn cơ bảnConceptual Evaluation Strategy: Semantics of an SQL query defined in terms of the following conceptual evaluation strategy:Compute the cross-product of relation-list.Discard resulting tuples if they fail qualifications.Delete attributes that are not in target-list.If DISTINCT is specified, eliminate duplicate rows.6Câu truy vấn cơ bảnA Note on Range VariablesReally needed only if the same relation appears twice in the FROM clause. 7SELECT S.snameFROM Sailors S, Reserves RWHERE S.sid=R.sid AND bid=103SELECT snameFROM Sailors, Reserves WHERE Sailors.sid=Reserves.sid AND bid=103Câu truy vấn cơ bảnExamples of Basic SQL QueriesFind the names and ages of all sailors.Find the names and ages of all sailors8Câu truy vấn cơ bảnFind all sailors with a rating above 7.When you want to retrieve all columns:9Câu truy vấn cơ bảnFind the sids of sailors who have reserved a red boat.Find the names of sailors who have reserved a red boat.10Câu truy vấn cơ bảnFind the colors of boats reserved by LubberFind the names of sailors who have reserved at least one boat.115.2 The Form of A Basic SQL QueryExpressions and Strings in the SELECT CommandSelect-list: Can be of the form expression AS column name, where expression is any arithmetic or string expression over column names and constants.It can also contain aggregates such as sum and count.12Câu truy vấn cơ bảnEx: Compute increments for the ratings of persons who have sailed two different boats on the same day135.2 The Form of A Basic SQL QueryFind triples (of ages of sailors and two fields defined by expressions) for sailors whose names begin and end with B and contain at least three characters.14SELECT S.age, age1=S.age-5, 2*S.age AS age2FROM Sailors SWHERE S.sname LIKE ‘B_%B’Câu truy vấn cơ bảnString ComparisonsLIKE is used for string matching. “_” stands for any one character and “%”stands for 0 or more arbitrary charactersEx: Find the ages of sailors whose name begins and ends with B and has at least three characters.15Union, Intersect, And ExceptUNION: s1 UNION s2, result rows either in s1 or s2.INTERSECT: s1 INTERSECT s2, result rows in s1 and s2.EXCEPT: s1 EXCEPT s2, result rows in s1 but not in s2. (Some system recognize ‘MINUS’ for EXECPT)IN, ANY, ALL, EXISTS to be covered in ‘Nested Queries’.16Union, Intersect, And ExceptFind the names of sailors who have reserved a red or a green boat17Union, Intersect, And ExceptFind the names of sailors who have reserved both a red and a green boat18Union, Intersect, And ExceptThe OR query previous can be rewritten as follows19Union, Intersect, And ExceptThe AND query previous can be rewritten as follows20Union, Intersect, And ExceptFind the sids of all sailors who have reserved red boats but not green boats.21Union, Intersect, And ExceptWe can use the following simpler query22Union, Intersect, And ExceptNote that UNION , INTERSECT ,and EXCEPT can be used on any two tables that are union-compatible, that is, have the same number of columns and the columns, taken in order, have the same types.Ex: Find all sids of sailors who have a rating of 10 or have reserved boat 10423Nested QueriesA nested query là một query chứa một query khác, query được chứa bên trong gọi là subquery .Subquery thường xuất hiện trong mệnh đề WHERE của query.Ngoài ra Subqueries cũng có thể xuất hiện trong mênh đề FROM hoặc HAVING.24Nested QueriesVí dụ: Find the names of sailors who have reserved boat 10325Nested QueriesFind the names of sailors who have reserved a red boat.26Nested QueriesFind the names of sailors who have not reserved a red boat.27Nested QueriesTương quan của nested queries:Trong nested query, việc hoàn thành của subquery phụ thuộc vào query bên ngoàiVí dụ: Find the names of sailors who have reserved boat number 103.28Nested QueriesSet-Comparison OperatorsThe output of a subquery returning a single constant can be compared using the normal operators =, , >, , =, , >=, ALL , respectively.32Nested QueriesMore Examples of Nested QueriesFind the names of sailors who have reserved both a red and a green boat.33Nested QueriesWriting this previous query using INTERSECT34Nested QueriesThe Division operation in relational algebra can be expressed in SQL.Find the names of sailors who have reserved all boats.35Nested QueriesAn alternative way to do this query without using EXCEPT follows36Aggregate OperatorsSQL supports five aggregate operations, which can be applied on any column, say A, of a relation.COUNT([DISTINCT] A): The number of (unique) values in the A column.SUM([DISTINCT] A): The sum of all (unique) values in the A column.AVG([DISTINCT] A): The average of all (unique) values in the A column.MAX (A): The maximum value in the A column.MIN (A): The minimum value in the A column.37Aggregate OperatorsFind the average age of all sailors.Find the average age of sailors with a rating of 10.38Aggregate OperatorsFind the name and age of the oldest sailor.We have to use a nested query39 Aggregate OperatorsCount the number of sailors.Count the number of different sailor names40 Aggregate OperatorsFind the names of sailors who are older than the oldest sailor with a rating of 10.41Aggregate OperatorsUsing ALL , this query could alternatively be written as follows:42Aggregate OperatorsThe GROUP BY and HAVING ClausesSo far, we’ve applied aggregation operators to all tuples. Sometimes, we want to apply them to each of several groups of tuples in a relation.Syntax:43Aggregate OperatorsThe select-list in the SELECT clause consists of a list of column names and a list of terms having the form aggop (column-name ) AS new-name.The grouping-list: a list of column names in SELECT clause.The group-qualification: is then applied to eliminate groups that do not satisfy this condition. The expressions must have a single value per group44Aggregate OperatorsEx: Find the age of the youngest sailor for each rating level45Aggregate OperatorsFind the age of the youngest sailor who is eligible to vote (i.e., is at least 18 years old) for each rating level with at least two such sailors.46Aggregate Operators47Aggregate OperatorsMore Examples of Aggregate QueriesFor each red boat, find the number of reservations for this boat.48Aggregate OperatorsIt is interesting to observe that the following version of the above query is illegal49Aggregate OperatorsFind the average age of sailors for each rating level that has at least two sailors.50Aggregate OperatorsThe HAVING clause can have a nested subquery.51Aggregate OperatorsFind the average age of sailors who are of voting age for each rating level that has at least two sailors.52Aggregate OperatorsFind the average age of sailors who are of voting age for each rating level that has at least two such sailors.53 Aggregate OperatorsThe following simpler formulation shows54Aggregate OperatorsThe fact that the WHERE clause is applied before grouping is done; thus, only sailors with age > 18 are left when grouping is done.55Aggregate OperatorsFind those ratings for which the average age of sailors is the minimum over all ratings.56Aggregate OperatorsA correct version of the above query follows.57NULL VALUESNull values:Field values in a tuple can be unknown, SQL provides a special column value called null to use in such situationsNull is used when the column value is either unknown or inapplicable.Null is not a constant that can be explicitly used as an argument of some expression.Null values need to be taken into account when evaluating conditions in the WHERE clause.58NULL VALUESComparisons Using Null ValuesThe presence of null complicates many issues:Special operators needed to check if value is/is not null. Logical Connectives AND, OR, and NOTThe logical operators AND , OR and NOT using a three-valued logic in which expressions evaluate to true, false or unknown .59NULL VALUESRules for NULL values:An arithmetic operator with (at least) one NULL argument always returns NULL.The comparison of a NULL value to any second value returns a result of UNKNOWN.60NULL VALUESOuter Joins: are join variants that do not loose any information from the input tables: LEFT OUTER JOIN includes all dangling tuples from the left input table with NULL values filled in for all attributes of the right input table.RIGHT OUTER JOIN includes all dangling tuples from the right input table with NULL values filled in for all attributes of the left input table.FULL OUTER JOIN includes all dangling tuples from both input tables.615.7 Integrity Constraints An Integrity Constraint describes conditions that every legal instance of a relation must satisfy.Inserts/deletes/updates that violate IC’s are disallowed.Can be used to ensure application semantics or prevent inconsistencies62Integrity Constraints Constraints over a Single Table:CHECK constraint:To ensure that rating must be an integer in the range 1 to 10, we could use:63Integrity Constraints Domain Constraints: Field values must be of right type. Always enforcedINTEGER is the base type for the domain ratingvalThe optional DEFAULT keyword is used to associate a default value with a domain64