Transcript SQL: Queries, Programming, Triggers

SQL: Queries, Programming,
Triggers
Chapter 5
Modified by Donghui Zhang
Database Management Systems 3ed, 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 (possibly with a
range-variable after each name).
 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 is that
duplicates are not eliminated!

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
2
Conceptual 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.
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 3ed, R. Ramakrishnan and J. Gehrke
3
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:
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 3ed, R. Ramakrishnan and J. Gehrke
It is good style,
however, to use
range variables
always!
4
Expressions and Strings
SELECT S.age, age1=S.age-5, 2*S.age AS age2
FROM Sailors S
WHERE S.sname LIKE ‘B_%B’



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.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
5
Set Operations in SQL
UNION: 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)
 Also, IN, ANY, ALL, EXISTS, to be covered in
‘Nested Queries’.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
6
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)
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.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
7
Nested Queries with Correlation
Find names of sailors who’ve reserved boat #103:
SELECT S.sname
FROM Sailors S
WHERE EXISTS (SELECT *
FROM Reserves R
WHERE R.bid=103 AND S.sid=R.sid)

EXISTS is another set comparison operator, like IN.
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 recomputed for each Sailors tuple.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
8
More on Set-Comparison Operators
We’ve already seen IN, EXISTS and UNIQUE. Can also
use NOT IN, NOT EXISTS and NOT UNIQUE.
 Also available: op ANY, op ALL, op IN , , , ,, 
 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
WHERE S2.sname=‘Horatio’)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
9
(1)
Division in SQL
Find sailors who’ve reserved all boats.

Let’s do it the hard
way, without EXCEPT:
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))
(2) SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS (SELECT B.bid
FROM Boats B
WHERE NOT EXISTS (SELECT R.bid
Sailors S such that ...
FROM Reserves R
WHERE R.bid=B.bid
there is no boat B without ...
AND R.sid=S.sid))
a Reserves tuple showing S reserved B
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
10
Aggregate Operators

Significant extension of
relational algebra.
SELECT COUNT (*)
FROM Sailors S
SELECT AVG (S.age)
FROM Sailors S
WHERE S.rating=10
COUNT (*)
COUNT ( [DISTINCT] A)
SUM ( [DISTINCT] A)
AVG ( [DISTINCT] A)
MAX (A)
MIN (A)
single column
SELECT S.sname
FROM Sailors S
WHERE S.rating= (SELECT MAX(S2.rating)
FROM Sailors S2)
SELECT COUNT (DISTINCT S.rating)
FROM Sailors S
WHERE S.sname=‘Bob’
SELECT AVG ( DISTINCT S.age)
FROM Sailors S
WHERE S.rating=10
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
11
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)).

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.)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
12
Conceptual 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.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
13
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 (*) >= 2


Only S.rating and S.age are
mentioned in the SELECT,
GROUP BY or HAVING clauses;
other attributes `unnecessary’.
2nd column of result is
unnamed. (Use AS to name it.)
sid
22
31
71
64
29
58
rating
1
7
7
8
10
Database Management Systems 3ed, 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
age
33.0
45.0
35.0
55.5
35.0
rating
7
35.0
Answer relation
14
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 (*) >= 2
SELECT T.rating, T.minimum
FROM (SELECT S.rating, MIN(S.age) AS minimum,
COUNT (*) AS ratingcount
FROM Sailors S
WHERE S.age > 18
GROUP BY S.rating) AS T
WHERE T.ratingcount >= 2
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
15
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.
The presence of null complicates many issues. E.g.:





Special operators needed to check if value is/is not null.
Is rating>8 true or false when rating is equal to null? What
about AND, OR and NOT connectives?
We need a 3-valued logic (true, false and unknown).
Meaning of constructs must be defined carefully. (e.g.,
WHERE clause eliminates rows that don’t evaluate to true.)
New operators (in particular, outer joins) possible/needed.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
16
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 3ed, R. Ramakrishnan and J. Gehrke
17
CREATE TABLE Sailors
( sid INTEGER,
sname CHAR(10),
rating INTEGER,
age REAL,
PRIMARY KEY (sid),
 Useful when
CHECK ( rating >= 1
more general
AND rating <= 10 )
ICs than keys
CREATE TABLE Reserves
are involved.
( sname CHAR(10),
 Can use queries
bid INTEGER,
to express
day DATE,
constraint.
PRIMARY KEY (bid,day),
 Constraints can
CONSTRAINT noInterlakeRes
be named.
CHECK (`Interlake’ <>
( SELECT B.bname
FROM Boats B
WHERE B.bid=bid)))
General Constraints
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
18
Constraints Over Multiple Relations
CREATE TABLE Sailors
( sid INTEGER,
Number of boats
sname CHAR(10),
plus number of
 Awkward and
rating INTEGER,
sailors is < 100
wrong!
age REAL,
 If Sailors is
PRIMARY KEY (sid),
empty, the
CHECK
number of Boats
( (SELECT COUNT (S.sid) FROM Sailors S)
tuples can be
+ (SELECT COUNT (B.bid) FROM Boats B) < 100 )
anything!

ASSERTION is the
CREATE ASSERTION smallClub
right solution;
CHECK
not associated
with either table. ( (SELECT COUNT (S.sid) FROM Sailors S)
+ (SELECT COUNT (B.bid) FROM Boats B) < 100
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
19
Triggers
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)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
20
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
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
21
Exercises
Sailors ( sid, sname, rating, age)
Boats ( bid, bname, color )
Reserve( sid, bid, date )
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
22
Find snames whose rating>10 and who
reserved some red boat.
Solution 1: 3-way join.
Solution 2: use IN:
… WHERE S.rating>10 AND S.sid IN
Solution 3: use EXISTS
… WHERE S.rating>10 AND EXISTS …
Solution 4: use INTERSECT
… WHERE S.sid IN ( … INTERSECT … )
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
23
Find snames whose rating>10 or who
reserved some red boat.
Simple modifications from the above four cases.
In particular: INTERSECT  UNION
Solution 5: union on snames.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
24
Find sids with the highest rating.
Solution 1: use MAX
… S.rating = (SELECT MAX(S2.rating) …)
Solution 2: use ALL
… S.rating >= ALL ( SELECT S2.rating … )
Solution 3: use EXCEPT
… EXCEPT ( … S.sid < ANY (SELECT S2.rating) … )
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
25
Find bids not reserved by sid=5.
Solution 1: use EXCEPT
Solution 2: use NOT EXISTS
Find sids who reserved all boats.
Find sids who reserved all red boats.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
26
Find sids who made exactly one reservation.
Solution 1: use NOT EXISTS
not exists another reserve record with same sid.
Solution 2: use UNIQUE
… WHERE UNIQUE (… WHERE R.sid=R2.sid)
Solution 3: use GROUPBY & HAVING
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
27
More examples on groupby.
- For each sid who reserved, find # reservations.
- For each sid who reserved at least 5 times, find
# different boats she reserved.
- For each sid who reserved, find # different
colors she reserved.
- For each bid which was reserved by at least two
different sailors, find avg rating of reservers.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
28
More examples on nested query as source.
- Find the ratings for which the average age of
sailors is minimum over all ratings.
- Find the sailor sids who made more than
average number of reservations per sailor.
* choice 1: counting only those in Reserves.
* choice 2: including those who did not reserve.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
29
Exercise 5.2
Suppliers (sid: integer, sname: string, address: string)
Parts (pid: integer, pname: string, color: string)
Catalog (sid: integer, pid: integer, cost: real)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
30
4. Find the pnames of parts supplied by
Acme Widget Suppliers and no one else.
SELECT P.pname
FROM Parts P, Catalog C, Suppliers S
WHERE P.pid = C.pid AND C.sid = S.sid
AND S.sname = `Acme Widget Suppliers'
AND NOT EXISTS ( SELECT *
FROM Catalog C1, Suppliers S1
WHERE P.pid = C1.pid
AND C1.sid = S1.sid
AND S1.sname <> `Acme
Widget Suppliers' )
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
31
3. Find the snames of suppliers who
supply every red part.
SELECT S.sname
FROM Suppiers S
WHERE NOT EXISTS ( (SELECT P.pid
FROM Parts P
WHERE P.color = `red' )
EXCEPT
(SELECT C.pid
FROM Catalog C
WHERE C.sid = S.sid) )
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
32
6. For each part, find the sname of the
supplier who charges the most for that
part.
SELECT P.pid, S.sname
FROM Parts P, Suppliers S, Catalog C
WHERE C.pid = P.pid
AND C.sid = S.sid
AND C.cost = (SELECT MAX (C1.cost)
FROM Catalog C1
WHERE C1.pid = P.pid)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
33
7. Find the sids of suppliers who supply
only red parts.
SELECT DISTINCT C.sid
FROM Catalog C
WHERE NOT EXISTS ( SELECT *
FROM Catalog C1, Parts P
WHERE C1.pid = C.pid
AND C1.pid = P.pid
AND P.color <> `red' )
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
34
10. For every supplier that only supplies green
parts, print the name of the supplier and the total
number of parts that she supplies.
SELECT
FROM
WHERE
GROUP BY
HAVING
S.sname, COUNT(*) as PartCount
Suppliers S, Parts P, Catalog C
P.pid = C.pid AND C.sid = S.sid
S.sname, S.sid
EVERY (P.color='green')
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
35
Exercise 5.7
Emp (eid: integer, ename: string, age: integer, salary: real)
Works (eid: integer, did: integer, pct_time: integer)
Dept (did: integer, budget: real, managerid: integer)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
36
1. Define a table constraint on Emp that
will ensure that every employee makes at
least $10,000.
CREATE TABLE Emp ( eid INTEGER,
ename CHAR(10),
age INTEGER ,
salary REAL,
PRIMARY KEY (eid),
CHECK ( salary >= 10000 ))
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
37
3. Define an assertion on Dept that will ensure that
all managers have age > 30. Compare this assertion
with the equivalent table constraint. Explain which
is better.
CREATE TABLE Dept ( did INTEGER,
budget REAL,
managerid INTEGER ,
PRIMARY KEY (did) )
CREATE ASSERTION managerAge
CHECK ( (SELECT MIN(E.age)
FROM Emp E, Dept D
WHERE E.eid = D.managerid ) > 30 )
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
38
Exercise 5.10
Emp (eid: integer, ename: string, age: integer, salary: real)
Works (eid: integer, did: integer, pct_time: integer)
Dept (did: integer, budget: real, managerid: integer)


An employee can work in more than one department;
the pct_time field of the Works relation shows the
percentage of time that a given employee works in a
given department.
Write integrity constraints or triggers.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
39
2. Every manager must also be an
employee.
CREATE ASSERTION ManagerIsEmployee
CHECK ( ( SELECT COUNT (*)
FROM Dept D
WHERE D.managerid NOT IN
(SELECT eid FROM Emp)) = 0)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
40
3. The total percentage of all
appointments for an employee must be
under 100%.
CREATE TABLE Works ( eid INTEGER,
did INTEGER,
pct time INTEGER,
PRIMARY KEY (eid, did),
CHECK ( (SELECT COUNT (W.eid)
FROM Works W
GROUP BY W.eid
HAVING Sum(pct time) >
100) = 0))
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
41
5. Whenever an employee is given a
raise, the manager’s salary must be
increased to be at least as much.
CREATE TRIGGER GiveRaise AFTER UPDATE ON Emp
WHEN old.salary < new.salary
FOR EACH ROW
BEGIN
UPDATE Emp M
SET
M.Salary = new.salary
WHERE M.salary < new.salary
AND
M.eid IN (SELECT D.mangerid
FROM Emp E, Works W, Dept D
WHERE E.eid = new.eid
AND E.eid = W.eid
AND W.did = D.did);
END
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
42