Transcript Chapter 5 - UCF Computer Science

SQL: Queries, Programming,
Triggers
Chapter 5
Database Management Systems, R. Ramakrishnan and J. Gehrke
1
Basic SQL Query
SELECT
FROM
WHERE
[DISTINCT] target-list
relation-list
qualification

relation-list A list of relation names

target-list A list of attributes of relations in relation-list

qualification Comparisons (“Attr op const” or “Attr1 op Attr2,”
where op is one of ˂, ˃, ≤, ≥, =, ≠ ) combined using AND, OR,
and NOT.

DISTINCT is an optional keyword indicating that the answer
should not contain duplicates. (Default: not eliminated)
Database Management Systems, R. Ramakrishnan and J. Gehrke
2
SQL vs Domain Relational Calculus
SELECT
FROM
WHERE




[DISTINCT] target-list
relation-list
qualification
I, N,T, A | I, N,T, A  Sailors  T  7
Database Management Systems, R. Ramakrishnan and J. Gehrke




3
Querying Relations (1)
What does the following query compute?
Enrolled
sid
53831
53831
53650
53666
cid
grade
Carnatic101
C
Reggae203
B
Topology112
A
History105
B
SELECT S.name, E.cid
FROM Students S, Enrolled E
WHERE S.sid=E.sid AND E.grade=“A”
A student with
“sid” has an
entry in Enrolled
Students
sid
name
login
age
gpa
53831
Zhang
zhang@ee
19
3.2
53650
Smith
smith@cs
21
3.9
53666
Jones
jones@cs
20
3.5
Database Management Systems, R. Ramakrishnan and J. Gehrke
The Enrolled
entry has a
grade of “A”
Retrieve names of students
and the courses they
received an “A” grade
4
Querying Relations (2)
Enrolled
sid
53831
53831
53650
53666
cid
grade
Carnatic101
C
Reggae203
B
Topology112
A
History105
B
Students
sid
name
login
age
gpa
53831
Zhang
zhang@ee
19
3.2
53650
Smith
smith@cs
21
3.9
53666
Jones
jones@cs
20
3.5
SELECT S.name, E.cid
FROM Students S, Enrolled E
WHERE S.sid=E.sid AND E.grade=“A”
S.name
E.cid
Smith
Topology112
Database Management Systems, R. Ramakrishnan and J. Gehrke
5
Example Schema
We will use these table definitions in our subsequent
examples.
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day: date)
Database Management Systems, R. Ramakrishnan and J. Gehrke
6
Example Instances
v
v
We will use these
instances of the
Sailors and Reserves
relations in our
examples.
If the key for the
Reserves relation
contained only the
attributes sid and
bid, how would the
semantics differ?
R1 sid
22
58
S1 sid
22
31
58
S2 sid
28
31
44
58
Database Management Systems, R. Ramakrishnan and J. Gehrke
bid
101
103
day
10/10/96
11/12/96
sname rating age
dustin
7
45.0
lubber
8
55.5
rusty
10
35.0
sname rating age
yuppy
9
35.0
lubber
8
55.5
guppy
5
35.0
rusty
10
35.0
7
Semantics of SQL
QUERY PROCESSOR
SELECT
FROM
WHERE
[DISTINCT] target-list
relation-list
qualification
R1 × R2 × R3 × · · ·
Define search space
˂, ˃, ≤, ≥, =, ≠
Select rows
Query Result
Database Management Systems, R. Ramakrishnan and J. Gehrke
Projection
Select columns
8
Conceptual Evaluation Strategy
v
Semantics of an SQL query defined in terms of the
following conceptual evaluation strategy:
–
–
–
–
v
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.
Database Management Systems, R. Ramakrishnan and J. Gehrke
9
Example of Conceptual Evaluation
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid=R.sid AND R.bid=103
S×R
(sid)
22
sname
dustin
rating
7
age
45.0
(sid)
22
bid
day
101 10/10/96
22
31
31
dustin
lubber
lubber
7
8
8
45.0
55.5
55.5
58
22
58
103 11/12/96
101 10/10/96
103 11/12/96
rusty
rusty
10
10
35.0
35.0
22
58
101 10/10/96
103 11/12/96
58
Answer
58
Database Management Systems, R. Ramakrishnan and J. Gehrke
10
Example of Conceptual Evaluation
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid=R.sid AND R.bid=103
S×R
(sid)
22
sname
dustin
rating
7
age
45.0
(sid)
22
bid
day
101 10/10/96
22
31
31
dustin
lubber
lubber
7
8
8
45.0
55.5
55.5
58
22
58
103 11/12/96
101 10/10/96
103 11/12/96
58
58
rusty
rusty
10
10
35.0
35.0
22
58
101 10/10/96
103 11/12/96
Database Management Systems, R. Ramakrishnan and J. Gehrke
11
A Note on Range Variables
Really needed only if the same relation appears twice
in the FROM clause. The previous query can be
written in two ways:
Range variable
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid=R.sid AND bid=103
OR
SELECT sname
FROM Sailors, Reserves
WHERE Sailors.sid=Reserves.sid
AND bid=103
Database Management Systems, R. Ramakrishnan and J. Gehrke
It is good style,
however, to use
range variables
always!
12
Find sailors who’ve reserved at least one boat
SELECT S.sid
FROM Sailors S, Reserves R
WHERE S.sid = R.sid

Would adding DISTINCT to this query make a
difference? Remove duplicate sid

What is the effect of replacing S.sid by S.sname in
the SELECT clause? Since two sailors may have
the same name, some sailor may have no
reservation even his/her name is in the output
Database Management Systems, R. Ramakrishnan and J. Gehrke
13
Find sailors who’ve reserved at least one boat
Sailors(sid, sname, rating, age)
sid has a
reservation
Reserves(sid, bid, day)
SELECT S.sid
FROM Sailors S, Reserves R
WHERE S.sid = R.sid
Given a
sailor sid
sid has a
reservation
Database Management Systems, R. Ramakrishnan and J. Gehrke
14
Arithmetic Expressions and Strings
AS and = are two ways to
name fields in result
SELECT S.age, age1 = S.age-5, 2*S.age AS age2
FROM Sailors S
WHERE S.sname LIKE ‘B_%B’
Name begins and ends with ‘B’ and contains
at least three characters
LIKE is used for string matching. ‘_ ’ stands for any one
character and ‘%’ stands for 0 or more arbitrary characters.
Database Management Systems, R. Ramakrishnan and J. Gehrke
15
Find names of sailors who’ve reserved a red or a green boat
Sailors(sid, sname, rating, age)
sid has a
reservation for bid
Reserves(sid, bid, day)
bid is a boat
Boats(bid, bname, color)
color = ‘red’ OR color = ‘green’
SELECT S.name
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND (B.color=‘red’ OR B.color=‘green’)
Database Management Systems, R. Ramakrishnan and J. Gehrke
16
Find names of sailors who’ve reserved a red or a green boat
SELECT S.name
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘red’
UNION
SELECT S.name
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘green’

Name of sailors
who’ve reserved
red boats
Name of sailors
who’ve reserved
green boats
UNION: Compute the union of any two unioncompatible sets of tuples (which are themselves the
result of SQL queries).
Database Management Systems, R. Ramakrishnan and J. Gehrke
17
EXCEPT
SELECT S.name
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘red’
EXCEPT
SELECT S.name
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘green’

Name of sailors
who’ve reserved
red boats
Name of sailors
who’ve reserved
green boats
What do we get if we replace UNION by EXCEPT?
Find the sids of all sailors who have reserved red
boats but not green boats
Database Management Systems, R. Ramakrishnan and J. Gehrke
18
Find names of sailors who’ve reserved a red and a green boat
Database Management Systems, R. Ramakrishnan and J. Gehrke
19
Find names of sailors who’ve reserved a red and a green boat
Sailors(sid, sname, rating, age)
sid also has a reservation
for another bid
sid has a reservation
for bid
R1 Reserves(sid, bid, day)
Reserves(sid, bid, day)
bid is a boat
R2
That bid is a boat
B1 Boats(bid, bname, color)
‘red’
Boats(bid, bname, color) B2
‘green’
SELECT S.name
FROM Sailors S, Boats B1, Reserves R1,
Boats B2, Reserves R2
WHERE S.sid=R1.sid AND R1.bid=B1.bid
The sailor reserves 1st boat
AND S.sid=R2.sid AND R2.bid=B2.bid
The same sailor reserves 2nd boat
AND (B1.color=‘red’ AND B2.color=‘green’)
Database Management Systems, R. Ramakrishnan and J. Gehrke
20
Find names of sailors who’ve reserved a red and a green boat
Can’t we say:
SELECT S.name
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND (B.color=‘red’ AND B.color=‘green’)
No boat has two colors
→ Result is empty !
Database Management Systems, R. Ramakrishnan and J. Gehrke
21
Find names of sailors who’ve reserved a red and a green boat
SELECT S.name
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘red’
Name of sailors
who’ve reserved
red boats
INTERSECT
SELECT S.name
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND B.color=‘green’
Name of sailors
who’ve reserved
green boats
v
INTERSECT: Can be used to compute the intersection of any two
v
union-compatible sets of tuples.
Included in the SQL/92 standard, but some systems don’t
support it.
Database Management Systems, R. Ramakrishnan and J. Gehrke
22
Nested Queries
Find names of sailors who’ve reserved boat #103
SELECT S.sname
FROM Sailors S
WHERE S.sid IN (SELECT R.sid
FROM Reserves R
WHERE R.bid=103)
Sailor S has at least one
of these reservations
A very powerful feature of SQL: a WHERE clause can itself contain
an SQL query! (Actually, so can FROM and HAVING clauses.)
To understand semantics of nested queries, think of a nested loops
evaluation: For each Sailors tuple, check the qualification by
computing the subquery.
To find sailors who’ve not reserved #103, use NOT IN.
Database Management Systems, R. Ramakrishnan and J. Gehrke
23
Nested Queries with Correlation (1)
Query: Find
names of sailors who’ve
reserved boat #103
EXISTS tests whether
SELECT S.sname
a set is nonempty.
FROM Sailors S
WHERE EXISTS (SELECT *
FROM Reserves R
WHERE S.sid=R.sid AND R.bid=103)
Sailor S reserves
boat 103
Database Management Systems, R. Ramakrishnan and J. Gehrke
24
NOT EXIST
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS (SELECT *
FROM Reserves R
WHERE R.bid=103 AND S.sid=R.sid)
Use NOT EXIST to
find the names of
sailors who have not
reserved a red boat
Database Management Systems, R. Ramakrishnan and J. Gehrke
25
Nested Queries with Correlation (2)
Query: Find
names of sailors who
reserve boat 103 at most once.
SELECT S.sname
FROM Sailors S
WHERE UNIQUE (SELECT R.bid
FROM Reserves R
“at most
WHERE R.bid=103 AND S.sid=R.sid)
once”


UNIQUE returns true if no row appears more than
once. (Note: returns true if answer is empty)
Can we replace “SELECT R.bid” by “ SELECT * ” ?
No, A sailor may reserve boat 103 on different days; and
UNIQUE would return true
Database Management Systems, R. Ramakrishnan and J. Gehrke
26
More on Set-Comparison Operators
Also available: op ANY, op ALL , where op: ˃, ˂, =, ≠, ≥, ≤
Example: “Find sailors whose rating is greater than that
of some sailor called Horatio”
SELECT *
FROM Sailors S
WHERE S.rating > ANY (SELECT S2.rating
FROM Sailors S2
The subquery must return
WHERE S2.sname=‘Horatio’)
a row that makes the
comparison true, in order
for S.rating > ANY … to
return true
Database Management Systems, R. Ramakrishnan and J. Gehrke
27
Rewriting INTERSECT Queries
Using IN
Find sid’s of sailors who’ve reserved both a
red and a green boat:
sid of sailors
who’ve reserved
red boats
SELECT S.sid
sid of sailors
FROM Sailors S, Boats B, Reserves R
who’ve reserved
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’
green boats
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, we can rewrite EXCEPT queries using NOT IN.
Database Management Systems, R. Ramakrishnan and J. Gehrke
28
Division Operations in SQL (1)
Find names of sailors who’ve
reserved all boat:
SELECT S.sname
FROM Sailors S
WHERE NOT EXIST
The sailor
reserved all boats
Boats not reserved
by the sailor
((SELECT B.bid
FROM Boats B) EXCEPT
(SELECT R.bid
FROM Reserves R
WHERE R.sid = S.sid ))
All boats
Database Management Systems, R. Ramakrishnan and J. Gehrke
All boats reserved
by the sailor
29
Division Operations in SQL (2)
Find names of sailors who’ve
reserved all boat:
Sailor S
SELECT S.sname
FROM Sailors S
WHERE NOT EXIST ((SELECT B.bid
FROM
Boats B
such that
WHERE NOT EXISTS (SELECT R.bid
there is
FROM Reserves R
no boat B
without a
WHERE R.bid = B.bid
reservation
AND R.sid = S.sid))
showing
Sailor S such that …
there is no boat B without …
a Reserves tuple showing S reserved B.
Database Management Systems, R. Ramakrishnan and J. Gehrke
Sailor S
reserved
boat B
30
Aggregate Operators
Significant extension of relational algebra
COUNT (*)
The number of rows in the relation
The number of (unique) values in the A
COUNT ([DISTINCT] A)
column
The sum of all (unique) values in the A
SUM ([DISTINCT] A)
column
The average of all (unique) values in the A
AVG ([DISTINCT] A)
column
MAX (A)
The maximum value in the A column
MIN (A)
The minimum value in the A column
Database Management Systems, R. Ramakrishnan and J. Gehrke
31
Aggregate Operators
SELECT COUNT (*)
FROM Sailors S
SELECT AVG (S.age)
FROM Sailors S
WHERE S.rating=10
Count the
number of
sailors
Find the name of
sailors with the
highest rating
SELECT S.sname
Find theSailors
average S
age
FROM
the average of the
of sailors
with a Find
WHERE
S.rating=
(SELECT
MAX(S2.rating)
Count the
number ofdistinct
ages of sailors
rating of 10
distinct ratings of FROM
Sailors
S2)
with a rating
of 10
sailors called “Bob”
SELECT AVG (DISTINCT S.age)
SELECT COUNT (DISTINCT S.rating) FROM Sailors S
WHERE S.rating=10
FROM Sailors S
WHERE S.sname=‘Bob’
Database Management Systems, R. Ramakrishnan and J. Gehrke
32
Aggregate Operators
SELECT COUNT (*)
FROM Sailors S
SELECT AVG (S.age)
FROM Sailors S
WHERE S.rating=10
SELECT S.sname
FROM Sailors S
WHERE S.rating= (SELECT MAX(S2.rating)
FROM Sailors S2)
SELECT AVG (DISTINCT S.age)
SELECT COUNT (DISTINCT S.rating) FROM Sailors S
WHERE S.rating=10
FROM Sailors S
WHERE S.sname=‘Bob’
Database Management Systems, R. Ramakrishnan and J. Gehrke
33
Find name and age of the oldest sailor(s)
Comparing a number
with a relation is
allowed here
SELECT S.sname
FROM Sailors S
WHERE S.rating = (SELECT MAX(S2.rating)
FROM Sailors S2)
Allowed in
SQL/92
standard, but is
not supported in
some systems
SELECT S.sname
FROM Sailors S
WHERE ( SELECT MAX (S2.rating)
FROM Sailors S2 ) = S.rating
Database Management Systems, R. Ramakrishnan and J. Gehrke
34
Find name and age of the oldest
sailor(s)
Only aggregate
operations allowed
SELECT S.sname, MAX (S.age)
FROM Sailors S
If the SELECT clause uses an aggregate operation,
then it must use only aggregate operations unless
the query contains a GROUP BY clause (aggregate
value for each group – discussed later.)
Database Management Systems, R. Ramakrishnan and J. Gehrke
35
SIMPLE GROUP BY
So far, we’ve applied aggregate operators to all
(qualifying) tuples.
32
Aggregator
Qualifier
Relation
Aggregator
SELECT AVG (S.age)
FROM
Sailors S
WHERE S.rating=10
Find the average age
of sailors with a
rating of 10
Qualifier
Database Management Systems, R. Ramakrishnan and J. Gehrke
36
GROUP BY and HAVING
So far, we’ve applied aggregateOnly
operators
to all
one
(qualifying) tuples.
group
32
Aggregator
Qualifier
Relation
Sometimes, we want to apply them to each of several
groups of tuples.
12
9
11
Aggregator
Group 1
Aggregator
Group 2
Aggregator
Group 3
Database Management Systems, R. Ramakrishnan and J. Gehrke
Relation
37
GROUP BY and HAVING (2)
Consider: Find the age of the youngest sailor for
each rating level. /* Min(age) for multiple groups
–
If 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 S
WHERE S.rating = i
In general, we don’t know how many rating levels
exist, and what the rating values for these levels are !
Database Management Systems, R. Ramakrishnan and J. Gehrke
38
Queries With GROUP BY and HAVING
SELECT
FROM
WHERE
GROUP BY
HAVING
[DISTINCT] target-list
relation-list
qualification
MIN(Attribute)
grouping-list
group-qualification
HAVING
12
Aggregator
9
Aggregator
Qualifier
selecting
groups
Database Management Systems, R. Ramakrishnan and J. Gehrke
GROUP BY
Group 1
Group 2
Group 3
Output
a table
SELECT
FROM
WHERE
39
Queries With GROUP BY and HAVING
SELECT
FROM
WHERE
GROUP BY
HAVING
[DISTINCT] target-list
relation-list
qualification
grouping-list
group-qualification
The target-list contains: (i) attribute names, (ii) terms with
aggregate operations (e.g., MIN (S.age)).

Each answer tuple belongs to a group.

The attribute list must be a subset of grouping-list.

A group is a set of tuples that have the same value for all
attributes in grouping-list.
Database Management Systems, R. Ramakrishnan and J. Gehrke
40
Conceptual
Evaluation
SELECT
FROM
WHERE
GROUP BY
HAVING
[DISTINCT] target-list
relation-list
qualification
grouping-list
group-qualification
1.
The cross-product of relation-list is computed
2.
Tuples that fail qualification are discarded
3.
`Unnecessary’ fields are deleted
4.
The remaining tuples are partitioned into groups by
the value of attributes in grouping-list.
5.
The group-qualification is then applied to eliminate
some groups
6.
One answer tuple is generated per qualifying group
Database Management Systems, R. Ramakrishnan and J. Gehrke
41
Find the age of the youngest sailor with age ≥
18, for each rating with at least 2 such sailors
SELECT
FROM
WHERE
GROUP BY
HAVING
S.rating, MIN (S.age)
Sailors S
S.age >= 18
S.rating
COUNT (*) > 1
rating age
7
35.0
Only one
4 rating
group
groups
satisfies
HAVING
rating age
Answer
Disqualify
1
33.0
7
45.0
7
35.0
8
55.5
10 35.0
Input relation
Sailors
sid
22
31
71
64
29
58
Database Management Systems, R. Ramakrishnan and J. Gehrke
sname rating age
dustin
7
45.0
lubber
8
55.5
zorba
10 16.0
horatio
7
35.0
brutus
1
33.0
rusty
10 35.0
Only S.rating and S.age are
mentioned in SELECT
42
“GROUP BY and HAVING” Examples
Find the age of the youngest
sailor with age ≥ 18
SELECT MIN (S.age)
FROM Sailors S
WHERE S.age >= 18
SELECT S.rating, MIN (S.age) Find the age of the youngest
FROM Sailors S
sailor with age ≥ 18, for
WHERE S.age >= 18
each rating
GROUP BY S.rating
Find the age of the youngest
sailor with age ≥ 18, for each
rating with at least 2 such
sailors
SELECT S.rating, MIN (S.age)
FROM Sailors S
WHERE S.age >= 18
GROUP BY S.rating
HAVING COUNT (*) > 1
Database Management Systems, R. Ramakrishnan and J. Gehrke
43
For each red boat, find the number
of reservations for this boat
3) Count the number of
reservations for
each red-boat group
SELECT
FROM
WHERE
GROUP BY
B.bid, COUNT (*) AS scount
Boats B, Reserves R
R.bid=B.bid AND B.color=‘red’
B.bid
1) Find all reservations
for red boats
2) Group the reservations
for red boats according to
bid
Database Management Systems, R. Ramakrishnan and J. Gehrke
44
Illegal Having Clause
Having clause is
to select groups;
but B.Color is not
in grouping-list
?
Aggregator
?
Aggregator
SELECT
FROM
WHERE
GROUP BY
HAVING
B.bid, COUNT (*) AS scount
Boats B, Reserves R
R.bid=B.bid
B.bid
B.color=‘red’
HAVING
“red” ?
Qualifier
selecting
groups
Database Management Systems, R. Ramakrishnan and J. Gehrke
GROUP BY
“bid”
Group 1
Group 2
Group 3
Output
a table
SELECT
FROM
WHERE
45
Needs aggregate
function
Find the age of the youngest sailor older than 18,
for each rating with at least 2 sailors

Step 1: Select the desired tuples (using WHERE)

Step 2: Form the groups (using GROUP BY)

Step 3: Select the desired groups (using HAVING)

Step 4: Compute the aggregation for each group
(using COUNT, MAX, AVG, etc.)
Database Management Systems, R. Ramakrishnan and J. Gehrke
46
4
1
Find the age of the youngest sailor older than 18,
for each rating with at least 2 sailors
2
3
HAVING 1 ˂ (SELECT COUNT (*)
FROM Sailors S2
WHERE S.rating = S2.rating)
Sailors
Rating
Age
>18
Group
WHERE
S.age > 18
S.rating
26
GROUP BY
S.rating
Size>1
22
Database Management Systems, R. Ramakrishnan and J. Gehrke
Min.
age
MIN(S.age)
47
4
1
Find the age of the youngest sailor older than 18,
for each rating with at least 2 sailors
2
3
HAVING 1 ˂ (SELECT COUNT (*)
FROM Sailors S2
WHERE S.rating = S2.rating)
3
1
WHERE
S.age > 18
SELECT
S.rating, MIN (S.age)
FROM
Sailors S
WHERE
S.age > 18
GROUP BY S.rating
HAVING
1 ˂ (SELECT COUNT (*)
FROM Sailors S2
WHERE S.rating = S2.rating)
GROUP BY
S.rating
2
Database Management Systems, R. Ramakrishnan and J. Gehrke
MIN(S.age)
4
48
Find the age of the youngest sailor older than 18,
for each rating with at least 2 sailors
4) Find youngest age for
each qualified group
1) Find all sailors
SELECT
S.rating, MIN (S.age)
older than 18
FROM
Sailors S
WHERE
S.age > 18
2) Group qualified sailors
GROUP BY S.rating
according to rating
HAVING
1 ˂ (SELECT COUNT (*)
FROM Sailors S2
WHERE S.rating = S2.rating)
3.2) Discard groups
with less than
two sailors
Number of sailors
with this rating
Database Management Systems, R. Ramakrishnan and J. Gehrke
3.1) Count the
number of
sailors in a
group
49
Find the age of the youngest sailor older than 18
for each rating that has at least 2 sailors
SELECT
S.rating, MIN (S.age)
FROM
Sailors S
WHERE
S.age > 18
GROUP BY S.rating
HAVING
1 ˂ (SELECT COUNT (*)
FROM Sailors S2
WHERE S.rating = S2.rating)
What if HAVING clause
is replaced by
“HAVING COUNT(*) > 1” ?
Find the age of the youngest
sailor older than 18, for each
rating level that has at least
two such sailors
(MORE IN NEXT PAGE)
Database Management Systems, R. Ramakrishnan and J. Gehrke
50
Find the age of the youngest sailor older than 18
for each rating that has at least 2 sailors
Counting includes
SELECT
S.rating, MIN (S.age)
sailors younger
FROM
Sailors S
than 18
WHERE
S.age > 18
GROUP BY S.rating
“age” is not
mentioned in
HAVING
1 ˂ (SELECT COUNT (*)
this subquery
FROM
Sailors
S2
At least
WHERE S.rating = S2.rating)
2 sailors
SELECT
S.rating, MIN (S.age)
FROM
Sailors S
Counting
WHERE
S.age > 18
only adult
GROUP BY S.rating
sailors
HAVING
COUNT (*) › 1
Database Management Systems, R. Ramakrishnan and J. Gehrke
Find the age of the youngest
sailor older than 18 for each
rating level that has at least
two such sailors
At least 2 such sailors,
i.e., older than 18
51
Find the age of the youngest sailor older than 18,
for each rating that has at least 2 sailors
SELECT
S.rating, MIN (S.age)
FROM
Sailors S
WHERE
S.age > 18
GROUP BY S.rating
HAVING
1 ˂ (SELECT COUNT (*)
FROM Sailors S2
WHERE S.rating = S2.rating)


Shows HAVING clause can also contain a subquery.
We can use S.rating inside the nested subquery
because it has a single value for the current group
of sailors.
Database Management Systems, R. Ramakrishnan and J. Gehrke
52
Find those ratings for which the average
age is the minimum over all ratings
Aggregate
operations cannot
be nested
SELECT S.rating
FROM Sailors S
WHERE S.age = (SELECT MIN (AVG (S2.age))
FROM Sailors S2)
Database Management Systems, R. Ramakrishnan and J. Gehrke
53
Find those ratings for which the average
age is the minimum over all ratings
Correct solution (in SQL/92):
Find average age for
each rating group
Temp
rating
avgage
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)
Average age for some
rating group
Minimum over
all ratings
Database Management Systems, R. Ramakrishnan and J. Gehrke
54
Null Values
Field values in a tuple are sometimes
– unknown (e.g., a rating has not been assigned),
or
– inapplicable (e.g., no spouse’s name).
SQL provides a special value null for such
situations.
Database Management Systems, R. Ramakrishnan and J. Gehrke
55
Null Values
The presence of null complicates many issues:

Special operators needed, e.g., IS NULL to test if a value is null.

Is rating>8 true or false when rating is equal to null? null

What about AND, OR and NOT ? Need a 3-valued logic (true,
false, and unknown), e.g., (unknown OR false) = unknown.

Meaning of constructs must be defined carefully, e.g., WHERE
clause eliminates rows that don’t evaluate to true.
Null + 5 = null; but SUM (null, 5) = 5. (nulls can cause some unexpected
behavior)

New operators (in particular, outer joins) possible/needed.
Database Management Systems, R. Ramakrishnan and J. Gehrke
56
Outer Joins
S1 sid
22
31
58
S1
R1 sid
22
58
sname rating age
dustin
7
45.0
lubber
8
55.5
rusty
10
35.0
R1
No match
in R1
bid
101
103
day
10/10/96
11/12/96
sid
sname
rating
age
bid
day
22
31
dustin
lubber
7
8
45.0
55.55
101
null
10/10/96
null
58
rusty
10
35.0
103
11/12/96
Database Management Systems, R. Ramakrishnan and J. Gehrke
57
Integrity Constraints (Review)

An IC 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 (e.g., sid is a key),
or prevent inconsistencies (e.g., sname has to be a string, age
must be < 200)
Types of IC’s: Domain constraints, primary key
constraints, foreign key constraints, general constraints.
–
Domain constraints: Field values must be of right type. Always
enforced.
Database Management Systems, R. Ramakrishnan and J. Gehrke
58
General Constraints
CREATE TABLE Sailors
( sid
INTEGER,
sname CHAR(10),
A general constraint
rating INTEGER,
1 ≤ rating ≤ 10
age
REAL,
PRIMARY KEY (sid),
CHECK ( rating >= 1 AND rating <= 10 )
Useful when more general ICs than keys are involved.
Database Management Systems, R. Ramakrishnan and J. Gehrke
59
General Constraints
Constraints can be named
Can use queries to express constraint
Convenient to have a named
constraint, e.g., delete it later:
CREATE TABLE Reserves
ALTER TABLE RESERVES
( sname CHAR(10),
DROP CHECK noInterlakeRes
A named
bid INTEGER,
general
day DATE,
constraint
PRIMARY KEY (bid,day),
For each boat
CONSTRAINT noInterlakeRes
CHECK (`Interlake’ <>
( SELECT B.bname
Find the
name of
FROM Boats B
the boat
WHERE B.bid=bid)))
Database Management Systems, R. Ramakrishnan and J. Gehrke
60
Constraints Over Multiple Relations
CREATE TABLE Sailors
Number of boats
( sid INTEGER,
plus number of
sname CHAR(10),
This is not
sailors is < 100
rating INTEGER,
checked in
age REAL,
Boats. If it is not
modified, the
PRIMARY KEY (sid),
number of Boats
CHECK
tuples can be
( (SELECT COUNT (S.sid) FROM Sailors S)
anything
+ (SELECT COUNT (B.bid) FROM Boats B) < 100 )
Awkward and wrong!
ASSERTION is the
right solution; not
associated with
either table
CREATE ASSERTION smallClub
CHECK
( (SELECT COUNT (S.sid) FROM Sailors S)
+ (SELECT COUNT (B.bid) FROM Boats B) < 100 )
Database Management Systems, R. Ramakrishnan and J. Gehrke
61
Triggers
Trigger is a procedure that starts automatically if
specified changes occur to the DBMS
Three parts
Event
A change to the database that activates the
trigger (e.g., BEFORE insert, AFTER update)
Condition
A query or test that is run when the trigger is
activated (e.g., WHEN total salaries > $1M)
Action
A procedure that is executed when the
trigger is activated and its condition is true
Database Management Systems, R. Ramakrishnan and J. Gehrke
Event
Condition
Action
62
Specify Action
The action can be executed before, after, or instead of
the triggering event
AFTER
The action is executed after the
triggering event
INSTEAD The action is executed and the
OF
triggering event is never executed
Database Management Systems, R. Ramakrishnan and J. Gehrke
(1) Event
trigger
The action is executed before the
BEFORE event that triggered the action
(2) Action
63
Two kinds of triggers
An SQL INSERT/DELETE/UPDATE statement
may affect multiple rows of a table
Database Management Systems, R. Ramakrishnan and J. Gehrke
64
Two kinds of triggers
An SQL INSERT/DELETE/UPDATE statement may affect
multiple rows of a table
Statement-level trigger: executed once for all the tuples
that are changed in one SQL statement.
REFERENCING NEW TABLE AS newtuples, /* Set of new tuples
OLD TABLE AS oldtuples /* Set of old tuples
Row-level trigger: executed once for each modified tuple.
REFERENCING OLD AS oldtuple,
NEW AS newtuple
newtuples, oldtuple, newtuple can be used in the CONDITION and
ACTION clauses
Database Management Systems, R. Ramakrishnan and J. Gehrke
65
Trigger Examples (SQL:1999)
CREATE TRIGGER InitCounter
BEFORE INSERT ON SAILORS
FOR EACH STATEMENT
INSERT INTO CountTable
SET count = 0
WHERE age = 18
CREATE TRIGGER IncrCount
AFTER INSERT ON SAILORS
FOR EACH ROW
UPDATE CountTable
SET count = count + 1
WHERE age = 18
CountTable
Statement-level
trigger:
execute trigger
only once to
initialize counter
Row-level trigger:
evaluate each new
sailor to decide
whether to
increment the
counter
Database Management Systems, R. Ramakrishnan and J. Gehrke
age
17
18
.
.
.
99
count
113
0
.
.
.
2
CountTable
age
17
18
.
.
.
99
count
113
229
.
.
.
2
66
Statement-Level Trigger Example (SQL:1999)
Maintain information on young sailors in a
separate YoungSailors table
• Give a table name to the
set of newly inserted tuples
• OLD TABLE declaration is
not needed for INSERT
operation
CREATE TRIGGER youngSailorUpdate
AFTER INSERT ON SAILORS /* Event
REFERENCING NEW TABLE AS NewSailors
FOR EACH STATEMENT /* Statement-level trigger (default)
New tuples
INSERT /* Action
Insert
INTO YoungSailors(sid, name, age, rating)
SELECT sid, name, age, rating
Sailors
NewSailors
FROM NewSailors N
TRIGGER
WHERE N.age <= 18
≤ 18
YoungSailors
Database Management Systems, R. Ramakrishnan and J. Gehrke
67
Row-Level Trigger Example (SQL:1999)
Sailors(SID, sname, rating, age)
CREAT TRIGGER RatingTrigger
/* Event
AFTER UPDATE OF rating ON Sailors
REFERENCING
OLD AS OldTuple, /* value before update
NEW AS NewTuple /* value after update
WHEN (OldTupple.rating ˃ NewTupple.rating) /* Condition
FOR EACH ROW
/* Row-level trigger
UPDATE Sailors
/* Action: Restore
SET rating = OldTuple.rating
/* any attempt to
/* lower rating
WHERE SID = NewTuple.SID
Database Management Systems, R. Ramakrishnan and J. Gehrke
68
Summary
SQL was an important factor in the early acceptance of
the relational model; more natural than earlier,
procedural query languages.
Relationally complete; in fact, significantly more
expressive power than relational algebra.
Even queries that can be expressed in relational
algebra can often be expressed more naturally in SQL.
Many alternative ways to write a query; optimizer
should look for most efficient evaluation plan.
–
In practice, users need to be aware of how queries are
optimized and evaluated for best results.
Database Management Systems, R. Ramakrishnan and J. Gehrke
69
Summary (Contd.)
NULL for unknown-field values brings many
complications
SQL allows specification of rich integrity constraints
Triggers respond to changes in the database
Database Management Systems, R. Ramakrishnan and J. Gehrke
70
Midterm
Closed-book exam
Chapters 1, 2, 3, 4, 5
Date: February 25, 2016
How to prepare ?
– For each chapter, spend three hours to
review the slides
– Practice the Algebra, Calculus, and SQL
examples in the textbook
– Practice the homeworks
Database Management Systems, R. Ramakrishnan and J. Gehrke
71