1 sql. 2 introduction structured query language (sql) is the most widely used commercial relational...
TRANSCRIPT
2
IntroductionIntroduction
• Structured Query Language (SQL) is the most widely used commercial relational database language.
• It was originally developed at IBM in the SEQUEL-XRM and System-R projects (1974-1977).
• Almost immediately, other vendors introduced DBMS products based on SQL, and it is now a de facto standard.
• The SQL continues to evolve in response to the changing need.
3
• The SQL language has several aspects to it:– The Data Manipulation Language (DML)
• The subset of SQL allows users to pose queries and to insert, delete, and modify rows.
– The Data Definition Language (DDL)• The subset of SQL supports the creation, deletion,
and modification to definitions for tables and views.
– Triggers and Advanced Integrity Constraints• The feature was introduced in SQL:1999. The
standard includes support for triggers, which are action executed by the DBMS whenever changes to the database meet conditions specified in the trigger
4
– Embedded and Dynamic SQL• Embedded SQL features allow SQL code to be
called from a host language such as C or COBOL.• Dynamic SQL features allow a query to be
constructed and executed at run-time.
– Client-Server Execution and Remote Database Access
• Client application can connect to an SQL server.• Access data from a database over a network.
– Transaction Management• Control how transaction are executed.
– Security• Provides mechanisms to control users’ access to
data.
5
Basic SQL QueryBasic SQL Query
• The basic form of SQL
– relation-list • A list of relation names (possibly with a range-
variable after each name).
– target-list • A list of attributes of relations in relation-list.
SELECT [DISTINCT] target-listFROM relation-listWHERE qualification
6
– qualification • Comparisons (Attr op const or Attr1 op Attr2, where
op is one of ) combined using AND, OR and NOT.
– DISTINCT • an optional keyword indicating that the answer
should not contain duplicates. Default is that duplicates are not eliminated.
,,,,,
SELECT [DISTINCT] target-listFROM relation-listWHERE qualification
7
• SELECT clause– specifies columns to be retained in the result.
• FROM clause– specifies a cross-product of tables.
• WHERE clause (optional)– specifies selection conditions on the tables mentioned
in the FROM clause.
• An SQL query intuitively corresponds to a relational algebra expression involving selections, projections, and cross-products.
9
• Example: Find the names of all branches in the loan relation.
SELECT branch-name
FROM Loan
Loan Result
11
• 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.
(This strategy is probably the least efficient way to compute a query! An optimizer will find more efficient strategies to compute the same answers.)
12
• Example (Q1, p.137): Find the names of sailors who have reserved boat number 103.
sid bid day
22 101 10/10/96
58 103 11/12/96
sid sname rating age
22 dustin 7 45.0
31 lubber 8 55.5
58 rusty 10 35.0
Instance R3 of ReservesInstance S4 of Sailors
13
SELECT S.snameFROM Sailors S, Reserves RWHERE S.sid=R.sid AND R.bid=103
sid sname rating age sid bid day
22 dustin 7 45.0 22 101 10/10/96
22 dustin 7 45.0 58 103 11/12/96
31 lubber 8 55.5 22 101 10/10/96
31 lubber 8 55.5 58 103 11/12/96
58 rusty 10 35.0 22 101 10/10/96
58 rusty 10 35.0 58 103 11/12/96
S4 X R3Row remains afterselection.
Result
sid bid day
22 101 10/10/96
58 103 11/12/96
sid sname rating age
22 dustin 7 45.0
31 lubber 8 55.5
58 rusty 10 35.0
14
• A Note on Range Variables– Really needed only if the same relation appears
twice in the FROM clause. The previous query can also be written as:
or
SELECT S.snameFROM Sailors S, Reserves RWHERE S.sid=R.sid AND bid=103
SELECT snameFROM Sailors, Reserves WHERE Sailors.sid=Reserves.sid AND bid=103
It is good style,however, to userange variablesalways!
15
More ExamplesMore Examples
• Given the following schema:Sailors(sid: integer, sname: string, rating:integer, age: real)
Boats(bid: integer, bname; string, color: string)
Reserves(sid: integer, bid: integer, day: date)
sailors sid sname rating
Boats bid bname
age
color
Reserves sid bid day
16
Example: Find the sids of sailors who have reserved a red boat.
Example: Find the names of sailors who have reserved a red boat.
SELECT R.sidFROM Boat B, Reserves RWHERE B.bid = R.bid AND B.color = ‘red’
sailors sid sname rating
Boats bid bname
age
color
Reserves sid bid day
SELECT S.snameFROM Sailors S, Reserves R, Boat B WHERE S.sid = R.sid AND R.bid = B.bid AND
B.color = ‘red’
17
Example: Find the colors of boats reserved by Lubber.
(In general, there may be more than one sailor called Lubber. In this case, it will return the colors of boats reserved by some Lubber).
sailors sid sname rating
Boats bid bname
age
color
Reserves sid bid day
SELECT B.colorFROM Sailors S, Reserves R, Boat B WHERE S.sid = R.sid AND R.bid = B.bid AND
S.name = ‘Lubber’.
18
Example: Find the names of sailors who have reserved at least one boat.
(If a sailor has not made a reservation, the second step in the conceptual evaluation strategy would eliminate all rows in the cross-product that involve this sailor).
sailors sid sname rating
Boats bid bname
age
color
Reserves sid bid day
SELECT S.nameFROM Sailors S, Reserves RWHERE S.sid = R.sid
19
Expressions and StringsExpressions and Strings
• Illustrates use of arithmetic expressions and string pattern matching: Find 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.
• AS and = are two ways to name fields in result.• LIKE is used for string matching. `_’ stands for any one
character and `%’ stands for 0 or more arbitrary characters.
SELECT S.age, age1=S.age-5, 2*S.age AS age2FROM Sailors SWHERE S.sname LIKE ‘B_%B’
20
Union, Intersect, and ExceptUnion, Intersect, and Except
• SQL provides three set-manipulation constructs that extend the basic query form presented earlier.– Union ()– Intersection ()– Except ()
(many systems recognize the keyword MINUS for EXCEPT)
21
sailors sid sname rating
Boats bid bname
age
color
Reserves sid bid day
Example: Example: Find sid’s of sailors who’ve reserved a red Find sid’s of sailors who’ve reserved a red oror a green a green boatboat
• UNION: Can be used to compute the union of any two union-compatible sets of tuples (which are themselves the result of SQL queries).
• If we replace OR by AND in the first version, what do we get?
• Also available: EXCEPT (What do we get if we replace UNION by EXCEPT?)
SELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND (B.color=‘red’ OR B.color=‘green’)
SELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’UNIONSELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’
22
sailors sid sname rating
Boats bid bname
age
color
Reserves sid bid day
Example: Example: Find sid’s of sailors who’ve reserved a red Find sid’s of sailors who’ve reserved a red andand a green a green boatboat
• INTERSECT: Can be used to compute the intersection of any two union-compatible sets of tuples.
• Included in the SQL/92 standard, but some systems don’t support it.
SELECT S.sidFROM Sailors S, Boats B1, Reserves R1, Boats B2, Reserves R2WHERE 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’)
SELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’INTERSECTSELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’
23
sailors sid sname rating
Boats bid bname
age
color
Reserves sid bid day
Example: Example: Find sid’s of all sailors who’ve reserved red boat but Find sid’s of all sailors who’ve reserved red boat but not green boat.not green boat.
SELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’EXCEPTSELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’
Indeed, since the Reserves relation contains sid information, there is no need to look at the Sailors relation.
SELECT R.sidFROM Boats B, Reserves RWHERE R.bid=B.bid AND B.color=‘red’EXCEPTSELECT R.sidFROM Boats B, Reserves RWHERE R.bid=B.bid AND B.color=‘green’
24
sailors sid sname rating
Boats bid bname
age
color
Reserves sid bid day
Example: Example: Find sid’s of all sailors who have a rating of 10 or Find sid’s of all sailors who have a rating of 10 or reserved boat 104reserved boat 104
SELECT S.sidFROM Sailor SWHERE S.rating = 10UNIONSELECT R.sidFROM Reserves RWHERE R.bid=104
25
Nested QueriesNested Queries
• A nested query is a query that has another query embedded within it.
• The embedded query is called a subquery.
• The embedded query can be a nested query itself.– Queries may have very deeply nested
structures.
26
Example: Find names of sailors who’ve reserved boat #103:
SELECT S.snameFROM Sailors SWHERE S.sid IN (SELECT R.sid FROM Reserves R WHERE R.bid=103)• A very powerful feature of SQL: a WHERE clause can itself
contain an SQL query! (Actually, so can FROM and HAVING clauses.)
• To find sailors who’ve not reserved #103, use NOT IN.
• To understand semantics of nested queries, think of a nested loops evaluation: For each Sailors tuple, check the qualification by computing the subquery.
27
Correlated Nested QueriesCorrelated Nested Queries
• In the previous example, the inner subquery has been completely independent of the outer query.
• In general, the inner subquery could depend on the row currently being examined in the outer query.
28
Example: Find names of sailors who’ve reserved boat #103:
SELECT S.snameFROM Sailors SWHERE EXISTS (SELECT * FROM Reserves R WHERE R.bid=103 AND S.sid=R.sid)
• EXISTS is another set comparison operator, which allows us to test whether a set is nonempty.
• If UNIQUE is used, and * is replaced by R.bid, finds sailors with at most one reservation for boat #103. (UNIQUE checks for duplicate tuples; * denotes all attributes. Why do we have to replace * by R.bid?)
• Illustrates why, in general, subquery must be re-computed for each Sailors tuple.
29
Set-comparison OperatorsSet-comparison Operators
• We’ve already seen IN, EXISTS and UNIQUE. We can also use NOT IN, NOT EXISTS and NOT UNIQUE.
• Also available: op ANY, op ALL
– Where op is one of the arithmetic comparison operators
– SOME is also available, but it is just a synonym for ANY.
• Example: Find sailors whose rating is greater than that of some sailor called Horatio:
,,,,,
SELECT *FROM Sailors SWHERE S.rating > ANY (SELECT S2.rating FROM Sailors S2 WHERE S2.sname=‘Horatio’)
30
sailors sid sname rating
Boats bid bname
age
color
Reserves sid bid day
Example:Example: Find sailors whose rating is better than every sailor Find sailors whose rating is better than every sailor called Horatio.called Horatio.
SELECT *FROM Sailors SWHERE S.rating > ALL (SELECT S2.rating FROM Sailors S2 WHERE S2.sname=‘Horatio’)
Example:Example: Find the sailors with the highest rating. Find the sailors with the highest rating.
SELECT *FROM Sailors SWHERE S.rating >= ALL (SELECT S2.rating FROM Sailors S2)
31
• Rewriting INTERSECT queries using IN
Example: Find sid’s of sailors who’ve reserved both a red and a green boat:
SELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE 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’)
• Similarly, EXCEPT queries can be re-written using NOT IN.
• To find names (not sid’s) of Sailors who’ve reserved both red and green boats, just replace S.sid by S.sname in SELECT clause. (What about INTERSECT query? [ see p. 150 of the textbook])
32
Division in SQLDivision in SQL
Example: Find sailors who’ve reserved all boats.
SELECT S.snameFROM Sailors SWHERE NOT EXISTS ((SELECT B.bid FROM Boats B) EXCEPT (SELECT R.bid FROM Reserves R WHERE R.sid=S.sid))
sailors sid sname rating
Boats bid bname
age
color
Reserves sid bid day
Note that this query is correlated – for each sailor S, we check to see if the set of boats reserved by S includes every boat.
All boats
All boats reserved by S
33
An Alternative way to write the previous query without using EXCEPT
Intuitively, for each sailor we check that there is no boat that has not been reserved by this sailor.
sailors sid sname rating
Boats bid bname
age
color
Reserves sid bid day
SELECT S.snameFROM Sailors SWHERE 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))
Boats haven’t beenreserved by sailor s
Boat b will be returned if it has beenreserved by sailor s; otherwise the resultwill be empty.
34
Aggregate OperatorsAggregate Operators
• SQL allows the use of arithmetic expressions.• SQL supports five aggregate operations, which
can be applied on any column of a relation.
COUNT([DISTINCT] A) The number of (unique) value 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.
35
sailors sid sname rating
Boats bid bname
age
color
Reserves sid bid day
Example: Example: Find the average age of all sailorsFind the average age of all sailors
SELECT AVG (S.age)FROM Sailors S
Example: Example: Find the average age of sailors with rating of 10Find the average age of sailors with rating of 10
SELECT AVG (S.age)FROM Sailors SWHERE S.rating = 10
36
sailors sid sname rating
Boats bid bname
age
color
Reserves sid bid day
Example: Example: Find the name and age of the oldest sailorFind the name and age of the oldest sailor
SELECT S.sname, MAX (S.age)FROM Sailors S
SELECT S.sname, S.ageFROM Sailors SWHERE S.age = (SELECT MAX (S2.age) FROM Sailors S2)
SELECT S.sname, S.ageFROM Sailors SWHERE (SELECT MAX (S2.age) FROM Sailors S2) = S.age
Equivalent to the second query, and is allowed in the SQL/92 standard, but is not supported in some systems.
37
sailors sid sname rating
Boats bid bname
age
color
Reserves sid bid day
Example: Example: Count the number of SailorsCount the number of Sailors
SELECT COUNT (*)FROM Sailors S
Example: Example: Count the number of different sailor namesCount the number of different sailor names
SELECT COUNT (DISTINCT S.name)FROM Sailors S
38
sailors sid sname rating
Boats bid bname
age
color
Reserves sid bid day
Example: Example: Find the names of sailors who are older Find the names of sailors who are older than the oldest sailor with a rating of 10.than the oldest sailor with a rating of 10.
SELECT S.nameFROM Sailors SWHERE S.age > ( SELECT MAX (S2.age)
FROM Sailors S2WHERE S2.rating = 10)
Aggregate operations offer an alternative to the ANY Aggregate operations offer an alternative to the ANY and ALL constructs.and ALL constructs.
39
Group by and HavingGroup by and Having
• So far, we’ve applied aggregate operators to all (qualifying) tuples. Sometimes, we want to apply them to each of several groups of tuples.
• Consider: Find the age of the youngest sailor for each rating level.
– In general, we don’t know how many rating levels exist, and what the rating values for these levels are!
– Suppose we know that rating values go from 1 to 10; we can write 10 queries that look like this (!):
For i = 1, 2, ... , 10:
SELECT MIN (S.age)FROM Sailors SWHERE S.rating = i
40
• To write such queries, we need a major extension to the basic SQL query form, namely the Group BY clause.
• The extension also includes an optional HAVING clause that can be used to specify qualifications over groups.
SELECT S.rating, MIN (S.age)FROM Sailors SGROUP BY S.rating
The query can be expressed as follows
41
SELECT [DISTINCT] target-listFROM relation-listWHERE qualificationGROUP BY grouping-listHAVING group-qualification
• The target-list contains (i) attribute names (ii) terms with aggregate operations (e.g., MIN (S.age)).
– The attribute list (i) must be a subset of grouping-list. Intuitively, each answer tuple corresponds to a group, and these attributes must have a single value per group. (A group is a set of tuples that have the same value for all attributes in grouping-list.)
The general format of GROUP BY and Having
42
Conceptual EvaluationConceptual Evaluation
• The cross-product of relation-list is computed, tuples that fail qualification are discarded, `unnecessary’ fields are deleted, and the remaining tuples are partitioned into groups by the value of attributes in grouping-list.
• The group-qualification is then applied to eliminate some groups. Expressions in group-qualification must have a single value per group!
– In effect, an attribute in group-qualification that is not an argument of an aggregate op also appears in grouping-list. (SQL does not exploit primary key semantics here!)
• One answer tuple is generated per qualifying group.
SELECT [DISTINCT] target-listFROM relation-listWHERE qualificationGROUP BY grouping-listHAVING group-qualification
43
Example:Example: Find the age of the youngest sailor with age 18, for Find the age of the youngest sailor with age 18, for each rating with at least 2 each rating with at least 2 suchsuch sailors sailors
SELECT S.rating, MIN (S.age)FROM Sailors SWHERE S.age >= 18GROUP BY S.ratingHAVING COUNT (*) > 1
sid sname rating age22 dustin 7 45.031 lubber 8 55.571 zorba 10 16.064 horatio 7 35.029 brutus 1 33.058 rusty 10 35.0
• Only S.rating and S.age are mentioned in the SELECT, GROUP BY or HAVING clauses; other attributes are `unnecessary’.
• 2nd column of result is unnamed. (Use AS to name it. See Q32 on
p. 155 of your textbook.)
rating age1 33.07 45.07 35.08 55.510 35.0
rating7 35.0
Answer relation
44
For each red boat, find the number of reservations for For each red boat, find the number of reservations for this boatthis boat
SELECT B.bid, COUNT (*) AS reservationcountFROM Boats B, Reserves RWHERE R.bid=B.bid AND B.color=‘red’GROUP BY B.bid
SELECT B.bid, COUNT (*) AS reservationcountFROM Boats B, Reserves RWHERE R.bid=B.bidGROUP BY B.bidHAVING B.color = ‘red’
sailors sid sname rating
Boats bid bname
age
color
Reserves sid bid day
Only columns that appear in theGROUP BY clause can appear inthe HAVING clause, unlessthey appear as arguments to anaggregate operator in the HAVINGclause.
45
Example:Example: Find the average age of sailors for each rating level Find the average age of sailors for each rating level that has at least two sailors.that has at least two sailors.
sid sname rating age
22 Dustin 7 45.0
29 Brutus 1 33.0
31 Lubber 8 55.5
32 Andy 8 25.5
58 Rusty 10 35.0
64 Horatio 7 35.0
71 Zorba 10 16.0
74 Horatio 9 35.0
85 Art 3 25.5
95 Bob 3 63.5
96 Frodo 3 25.5
SELECT S.rating, AVG(S.age) AS avgageFROM Sailor SGROUP BY S.ratingHAVING COUNT (*) > 1
Instance S3 of Sailor
Rating avgage
3 38.2
7 40.0
8 40.5
10 25.5Answer
SELECT S.rating, AVG(S.age) AS avgageFROM Sailor SGROUP BY S.ratingHAVING 1 < (SELECT COUNT (*)
FROM Sailors S2 WHERE S.rating = S2.rating)
OR
We can use S.ratinginside the nestedsubquery in the HAVINGbecause it has a singlevalue for the current groupof sailors
46
Example:Example: Find the average age of sailors who are at least 18 Find the average age of sailors who are at least 18 years old for each rating level that has at least two sailors.years old for each rating level that has at least two sailors.
sid sname rating age
22 Dustin 7 45.0
29 Brutus 1 33.0
31 Lubber 8 55.5
32 Andy 8 25.5
58 Rusty 10 35.0
64 Horatio 7 35.0
71 Zorba 10 16.0
74 Horatio 9 35.0
85 Art 3 25.5
95 Bob 3 63.5
96 Frodo 3 25.5
SELECT S.rating, AVG(S.age) AS avgageFROM Sailor SWHERE S.age >=18GROUP BY S.ratingHAVING 1 < (SELECT COUNT (*)
FROM Sailors S2 WHERE S.rating = S2.rating)
Rating avgage
3 38.2
7 40.0
8 40.5
10 35.0
Answer Instance S3 of Sailor
Note that the answer is verysimilar to the previous one, with theonly difference being that for the group 10, we now ignore the sailor with age 16 while computingthe average.
47
Example:Example: Find the average age of sailors who are at least 18 Find the average age of sailors who are at least 18 years old for each rating level that has at least two years old for each rating level that has at least two suchsuch sailors.sailors.
sid sname rating age
22 Dustin 7 45.0
29 Brutus 1 33.0
31 Lubber 8 55.5
32 Andy 8 25.5
58 Rusty 10 35.0
64 Horatio 7 35.0
71 Zorba 10 16.0
74 Horatio 9 35.0
85 Art 3 25.5
95 Bob 3 63.5
96 Frodo 3 25.5
SELECT S.rating, AVG(S.age) AS avgageFROM Sailor SWHERE S.age >=18GROUP BY S.ratingHAVING 1 < (SELECT COUNT (*)
FROM Sailors S2 WHERE S.rating = S2.rating AND
S2.age >=18)
Rating avgage
3 38.2
7 40.0
8 40.5
AnswerInstance S3 of Sailor
It differs from the answer of theprevious question in that there is notuple for rating 10, since thereis only one tuple with rating andage >= 18.
48
Some other ways to write the previous query.Some other ways to write the previous query.
SELECT S.rating, AVG(S.age) AS avgageFROM Sailor SWHERE S.age >=18GROUP BY S.ratingHAVING COUNT (*) > 1
SELECT Temp.rating, Temp.avgageFROM (SELECT S.rating, AVG(S.age) AS avgage, count (*) As ratingcount FROM Sailor S WHERE S.age >=18 GROUP BY S.rating) AS TempWHERE Temp.ratingcount > 1
49
Find those ratings for which the average age is the Find those ratings for which the average age is the minimum over all ratingsminimum over all ratings
sailors sid sname rating
Boats bid bname
age
color
Reserves sid bid day
SELECT S.ratingFROM Sailors SWHERE S.age = (SELECT MIN (AVG (S2.age))
FROM Sailors S2 GROUP BY S2.rating)
Aggregate operations cannot be nested!This query will not work even if the expression MIN(AVG(S2.age)), which is illegal,is allowed. In the nested query, Sailors is partitioned into groups by rating, and theaverage age is computed for each rating value. For each group, applying MIN to thisaverage age value for the group will return the same value.
50
Correct solution (in SQL/92):
SELECT Temp.rating, Temp.avgageFROM (SELECT S.rating, AVG (S.age) AS avgage FROM Sailors S GROUP BY S.rating) AS TempWHERE Temp.avgage = (SELECT MIN (Temp.avgage) FROM Temp)
It essentially computes a temporary table containing the average age for each rating value and then finds the rating(s) for which this average age is the minimum.
51
NULL ValueNULL Value
• field value unknown– A new employee has not been assigned a
supervisor yet.
• field attribute inapplicable– An unmarried employee does not have a spouse
Employee eno ename supervisor_eno spouse_name
52
Complications caused by NULL values• Special operators provided to check
if value is / is not NULL
• The condition following ‘where’ clause eliminates FALSE or unknown
For a person who hasn’t been assigned a supervisor yet, is supervisor_eno = 3334445555 true or false? (We need a three value logic: true, false, unknown) NOT unknown -> unknown OR (TRUE, unknown) -> TRUE , OR (FALSE, unknown) -> unknown AND (TRUE, unknown) -> unknown, AND (FALSE, unknown) -> FALSE
53
• Two rows are duplicates if matching columns are either equal or both NULL– implicitly NULL=NULL. – But for comparison in where clause,
(NULL=NULL) = unknown.
• NULL is counted in COUNT(*)
• All other aggregate discard NULL values
54
General ConstraintsGeneral Constraints
CREATE TABLE Sailors( sid INTEGER,sname CHAR(10),rating INTEGER,age REAL,PRIMARY KEY (sid),CHECK ( rating >= 1
AND rating <= 10 )
• Useful when more general ICs than keys are involved.
• Can use queries to express constraint.
55
CREATE TABLE Reserves( sname CHAR(10),bid INTEGER,day DATE,PRIMARY KEY (bid,day),CONSTRAINT noInterlakeResCHECK (`Interlake’ <>
( SELECT B.bnameFROM Boats BWHERE B.bid=bid)))
• Constraints can be named.• When a boat is inserted into Reserves or an existing row
is modified, the conditional expression in the CHECK constraint is evaluated. If it evaluates to false, the command is rejected.
56
Domain ConstraintsDomain Constraints
• We can define a new domain using the CREATE DOMAIN statement, which uses CHECK constraints.
CREATE DOMAIN ratingval INTEGER DEFAULT 1CHECK (VALUE >=1 AND VALUE <=10)
57
Distinct TypeDistinct Type
• This statement defines a new distinct type called ratingtype, with INTEGER as its source type.
• Values of type ratingtype can be compared with each other, but they cannot be compared with values of other types.
• Ratingtype values are treated as being distinct from values for the source type.
CREATE TYPE ratingtype AS INTEGER
58
Constraints Over Multiple RelationsConstraints Over Multiple Relations
CREATE TABLE Sailors( sid INTEGER,sname CHAR(10),rating INTEGER,age REAL,PRIMARY KEY (sid),CHECK ( (SELECT COUNT (S.sid) FROM Sailors S)+ (SELECT COUNT (B.bid) FROM Boats B) < 100 )
Number of boatsplus number of sailors is < 100
• Awkward and wrong!• If Sailors is empty, the number of Boats tuples
can be anything!
59
CREATE ASSERTION smallClubCHECK ( (SELECT COUNT (S.sid) FROM Sailors S)+ (SELECT COUNT (B.bid) FROM Boats B) < 100 )
• ASSERTION is the right solution; not associated with either table.
60
TriggersTriggers
• Trigger: procedure that starts automatically if specified changes occur to the DBMS
• Three parts:– Event (activates the trigger)– Condition (tests whether the triggers should run)– Action (what happens if the trigger runs)
61
Triggers: Example (SQL:1999)Triggers: Example (SQL:1999)
CREATE TRIGGER youngSailorUpdate
AFTER INSERT ON SAILORS
REFERENCING NEW TABLE NewSailors
FOR EACH STATEMENT
INSERT
INTO YoungSailors(sid, name, age, rating)
SELECT sid, name, age, rating
FROM NewSailors N
WHERE N.age <= 18
Appendix: Natural JoinAppendix: Natural Join
• Note that some systems may support natural join. This query
can be rewritten as
• Some systems also support inner join, left join, right join and full join.
62
SELECT S.snameFROM Sailors S, Reserves RWHERE S.sid=R.sid AND R.bid=103
SELECT S.snameFROM Sailors S natural join Reserves RWHERE R.bid=103