SQL: Queries, Programming, Triggers

Chapter 5, Cow Book

or
See http://sqlzoo.net/

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 1

 SQL Language
• DDL: Data definition language
• DML: Data manipulation language
• Embedded and Dynamic SQL
• Triggers
• Security
• Transaction Management
• Remote Database access

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 2

 Basic SQL Query
Attributes from
Optional
input relations

SELECT [DISTINCT] target-list List of relations

FROM relation-list Attr1 op Attr2
WHERE qualification OPS: <, >, =, <=, >=, <>
Combine using AND, OR, NOT
• Semantics/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.
• Not an efficient evaluation plan! (Optimzier picks efficient plans)

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 3

 Example of Conceptual Evaluation
SELECT S.name, A.hours FROM Senators S, Attendance A
WHERE S.ssn = A.ssn and A.date = ‘24-Sept-2016’
ssn name email age income ssn date hours

11-111 Bob bob@ca.gov 51 100.1 11-111 12-Aug-2016 1.1

22-222 Jane jane@mi.gov 54 130.1 33-333 24-Sept-2016 4.1
33-333 Jane jane@wi.gov 51 99.8

(ssn) name email age income (ssn) date hours

11-111 Bob bob@ca.gov 51 100.1 11-111 12-Aug-2016 1.1

22-222 Jane jane@mi.gov 54 130.1 11-111 12-Aug-2016 1.1
33-333 Jane jane@wi.gov 51 99.8 11-111 12-Aug-2016 1.1
11-111 Bob bob@ca.gov 51 100.1 33-333 24-Sept-2016 4.1
22-222 Jane jane@mi.gov 54 130.1 33-333 24-Sept-2016 4.1
33-333 Jane
Jane jane@wi.gov 51 99.8 33-333 24-Sept-2016 4.1
4.1

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 4

 Find senators who attended the
‘24-Sept-2016’ session
SELECT S.name
FROM Senators S, Attendance A
WHERE S.ssn = A.ssn and date = ‘24-Sept-2016’

n Add DISTINCT to this query. Effect?

n Replace S.name by S.ssn.
Effect of adding DISTINCT to this query
RA: pname (sdate=‘24-Sept-2016’(Senators wv Attendance))
n Equivalent SQL?
n What is the schema of Senators ⋈ Attendance?

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 5

 A Note on Range Variables
• Needed only if the same relation appears twice in the
FROM clause.
SELECT S.name, A.hours
FROM Senators S, Attendance A
WHERE S.ssn = A.ssn and A.date = ‘24-Sept-2016’
OR It is good style,
however, to
SELECT Senators.name, Attendance.hours
always use
FROM Senators , Attendance
range variables!
WHERE Senators.ssn = Attendance.ssn and
Attendance.date = ‘24-Sept-2016’

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 6

 Expressions and Strings
SELECT S.name, S.age, age1=S.age+2, S.income/S.age AS iar
FROM Senators S
WHERE S.sname LIKE ‘Ja_%Doe’
ORDER BY S.name

• Illustrates use of arithmetic expressions and string pattern

matching
• 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.
• Collation: sort order for character sets

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 7

 Find senators who attended either the ‘24-Sept-2016’ or
‘25-Sept-2016’ session
SELECT S.ssn
• UNION: Compute the union of
FROM Senators S, Attendance A
two union-compatible sets of WHERE S.ssn = A.ssn
tuples and (A.date = ‘24-Sept-2016’ or
– Same number/types of fields. A.date = ‘25-Sept-2016’)
• Also available: INTERSECT and
SELECT S.ssn
EXCEPT (What do we get if we FROM Senators S, Attendance A
replace UNION by EXCEPT?) WHERE S.ssn = A.ssn
• SQL oddities: duplicates with and A.date = ‘24-Sept-2016’
UNION
union, except, intersect
SELECT S.ssn
– Default: eliminate duplicates! FROM Senators S, Attendance A
– Use ALL to keep duplicates WHERE S.ssn = A.ssn
and A.date = ‘25-Sept-2016’

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 8

 Find senators who attended both the ‘24-Sept-2016’
and ‘25-Sept-2016’ session
• INTERSECT: Compute the SELECT S.ssn
intersection of any two FROM Senators S, Attendance A1,
union-compatible sets of Attendance A2
WHERE S.ssn = A1.ssn and S.ssn = A2.ssn
tuples. and A1.date = ‘24-Sept-2016’
• In the SQL/92 standard, but and A2.date = ‘25-Sept-2016’
some systems don’t support
it. SELECT S.ssn, S.name
FROM Senators S, Attendance A
WHERE S.ssn = A.ssn
and A.date = ‘24-Sept-2016’
INTERSECT
Key field!
What happens if S.name is used SELECT S.ssn, S.name
FROM Senators S, Attendance A
WHERE S.ssn = A.ssn
and A.date = ‘25-Sept-2016’
3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 9
 Sailors (sid, sname, rating, age)
Nested Queries Reserves (sid, bid, day)
Boats (bid, bname, color)

Find names of sailors who’ve reserved boat #103

SELECT S.sname
Can you rewrite FROM Sailors S
this to not use WHERE S.sid IN (SELECT R.sid
a nested query? FROM Reserves R
WHERE R.bid=103)
• Powerful feature of SQL
– 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
• Conceptual Evaluation: nested loops For each Sailors tuple, check
the qualification by computing the subquery.

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 10

 Nested Queries with Correlation
------------------------------------------------------------------
Find names of sailors who’ve reserved boat #103
Find names of sailors with exactly one reservation for boat #103
SELECT S.sname
FROM Sailors S
WHERE EXISTS (SELECT UNIQUE
* R.bid
FROM Reserves R
Why R.bid? WHERE R.bid=103 AND S.sid=R.sid)

n EXISTS tests if the set is not empty

• UNIQUE returns true if the row appears only once
n Illustrates why, in general, subquery must be re-computed
for each Sailors tuple.

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 11

 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 is <, ≤, >, ≥, =, ≠
• Find sailors whose rating is greater than that of some
sailor called Horatio:
(SELECT* *
SELECT
FROM Sailors
FROM SailorsSS
WHERE S.rating
WHERE S.ratin)>> ANY (SELECT S2.rating
FROM Sailors
FROM SailorsS2
S2
WHERES2.sname=‘Horatio’)
WHERE S2.sname=‘Horatio’)

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 12

 Rewriting Except Sailors (sid, sname, rating, age)
Reserves (sid, bid, day)
Queries Using NOT IN Boats (bid, bname, color)

Find sailors (sid) who’ve reserved some boat for

‘24-Sept-2016’ but have no reservations for ‘09-Oct-2016’
SELECT S.sid SELECT S.sid
FROM Sailors S, Reserves R FROM Sailors S, Reserves R
WHERE S.sid=R.sid WHERE S.sid=R.sid
AND R.day=‘24-Sept-2016’ AND R.day=‘24-Sept-2016’
EXCEPT AND S.sid NOT IN
(SELECT S2.sid (SELECT S2.sid
FROM Sailors S2, Reserves R2 FROM Sailors S2, Reserves R2
WHERE S2.sid=R2.sid WHERE S2.sid=R2.sid
AND R2.day=‘‘09-Oct-2016’) AND R2.day=‘‘09-Oct-2016’)

• Similarly, INTERSECT queries re-written using IN.

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 13

 Sailors (sid, sname, rating, age)
Division in SQL Reserves (sid, bid, day)
Boats (bid, bname, color)
(1) SELECT S.sname
Find sailors who’ve FROM Sailors S
reserved all boats. WHERE NOT EXISTS
((SELECT B.bid
Without EXCEPT: FROM Boats B)
EXCEPT
(2) SELECT S.sname (SELECT R.bid
FROM Reserves R
FROM Sailors S
WHERE R.sid=S.sid))
WHERE NOT EXISTS (SELECT B.bid
FROM Boats B
Sailors S such that ... WHERE NOT EXISTS (SELECT R.bid
FROM Reserves R
there is no boat B without ...
WHERE R.bid=B.bid
AND R.sid=S.sid))
a Reserves tuple showing S reserved B
3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 14
 COUNT (*)
Aggregate Operators COUNT ( [DISTINCT] A)
SUM ( [DISTINCT] A)
SELECT COUNT (*) FROM Sailors S
AVG ( [DISTINCT] A)
SELECT COUNT (DISTINCT S.name) MAX (A) Can use Distinct
FROM Sailors S MIN (A) Can use Distinct
single column
SELECT AVG (S.age)
SELECT AVG ( DISTINCT S.age)
FROM Sailors S
FROM Sailors S
WHERE S.rating=10
WHERE S.rating=10

SELECT S.sname FROM Sailors S

WHERE S.rating= (SELECT MAX(S2.rating) FROM Sailors S2)

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 15

 Find name & age of the oldest sailor(s)
SELECT S.sname, MAX (S.age)
• The first query is illegal! (wait
FROM Sailors S
for GROUP BY.)
• Q3 is allowed in the SQL/92 SELECT S.sname, S.age
standard, but not supported in FROM Sailors S
some systems WHERE S.age =
(SELECT MAX (S2.age)
How many tuples FROM Sailors S2)
in the result?
SELECT S.sname, S.age
FROM Sailors S
WHERE (SELECT MAX (S2.age)
FROM Sailors S2)
= S.age

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 16

 GROUP BY and HAVING
• Apply aggregate to each of several groups of tuples
• Find the age of the youngest sailor for each rating level
– Don’t know: # rating levels, and rating values
– Suppose we did 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), S.rating
SELECT MIN (S.age) FROM Sailors S
FROM Sailors S GROUP BY S.rating
WHERE S.rating = i

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 17

 Queries With GROUP BY and HAVING
SELECT [DISTINCT] target-list
FROM relation-list
WHERE qualification How many tuples
GROUP BY grouping-list in the result?
HAVING group-qualification

• The target-list contains

– Attribute names: must be a subset of grouping-list.
– Terms with aggregate operations (e.g., MIN (S.age)).
• The group-qualification
– Must have a single value per group

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 18

 Conceptual Evaluation
• Cross-product -> discard tuples -> apply projection
-> partition into groups using the grouping-list attribute values
-> eliminate groups that don’t satisfy the group-qualification

• Expressions in group-qualification 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.

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 19

 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) sid sname rating age

FROM Sailors S 22 dustin 7 45.0
WHERE S.age >= 18 31 lubber 8 55.5
GROUP BY S.rating 71 zorba 10 16.0
HAVING COUNT (*) > 1 64 horatio 7 35.0
• 2nd column of result is unnamed. 29 brutus 1 33.0
(Use AS to name it) 58 rusty 10 35.0
rating age rating
1 33.0 7 35.0
7 45.0 Answer relation
7 35.0
8 55.5
10 35.0
3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 20
 For each red boat, find the number of
reservations for this boat

SELECT B.bid, COUNT (*) AS scount

FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’
GROUP BY B.bid

SELECT B.bid, COUNT (*) AS scount

FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
GROUP BY B.bid Would this work?
HAVING B.color = ‘red’ note: one color per bid

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 21

 Find the age of the youngest sailor with age>18, for each
rating with at least 2 sailors (of any age)

SELECT S.rating, MIN (S.age) AS MINAGE

FROM Sailors S
WHERE S.age > 18
GROUP BY S.rating
HAVING 1 < (SELECT COUNT (*) FROM Sailors S2
WHERE S.rating=S2.rating)

• Subquery in the HAVING clause

• Compare this with the query where we considered only
ratings with 2 sailors over 18!

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 22

 Find ratings for which the average age is the minimum of
the average age over all ratings
• Aggregate operations cannot be nested! WRONG:
SELECT S.rating
FROM Sailors S
WHERE AVG(S.age) =
(SELECT MIN (AVG (S2.age)) FROM Sailors S2)
§ Correct solution (in SQL/92):
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)

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 23

 Null Values
p OR
p q p AND q
• Represent q

– unknown (e.g., rating not assigned) or T T T T

T F F T
– inapplicable (e.g., no spouse’s name)
T U U T
• Complications with nulls: F T F T
– Operators to check if value is/is not null. F F F F

– Is rating > 8 true or false when rating is null? F U F U

• Answer: Evaluate to unknown U T U T

U F F U
– What about AND, OR and NOT connectives?
U U U U
• Need 3-valued logic (true, false and unknown)
– Not unknown = unknown
– WHERE clause eliminates rows that don’t evaluate to true
– New operators (in particular, outer joins) possible/needed.

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 24

 Outer Join
Sailors Reserves
sid sname rating age sid bid day
22 dustin 7 45.0 22 101 10/10/99
58 rusty 10 35.0

Select S.sid, R.bid

From Sailors S NATURAL LEFT OUTER JOIN Reserves R

Result
Similarly:
sid bid • RIGHT OUTER JOIN
22 101 • FULL OUTER JOIN
58 null

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 25

 Embedded SQL
• Call SQL commands from a host language (e.g., C)
program.
– SQL statements can refer to host variables (including special
variables used to return status).
– Must include a statement to connect to the right database.
• SQL relations are (multi-) sets of records, with no a
priori bound on the number of records. No such data
structure in C.
– SQL supports a mechanism called a cursor to handle this.

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 26

 Cursors
• Can declare a cursor on a relation or query statement
(which generates a relation).
• Can open a cursor, and repeatedly fetch a tuple then
move the cursor, until all tuples have been retrieved.
– Special clause, called ORDER BY, in cursor queries to control the
order in which tuples are returned.
– Fields in ORDER BY must also appear in SELECT clause.
• Can also modify/delete tuple pointed to by a cursor

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 27

 Cursor that gets names of sailors who’ve reserved a
red boat, in alphabetical order
EXEC SQL DECLARE sinfo CURSOR FOR
SELECT S.sname
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’
ORDER BY S.sname

• Can we replace S.sname by S.sid in the ORDER BY clause!

– Every column in the ORDER BY clause must appear in the SELECT
clause

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 28

 Integrity Constraints
• An IC describes conditions that every legal instance of a relation must
satisfy.
– Inserts/deletes/updates that violate IC’s are disallowed.
• Types of IC’s: Domain constraints, primary key constraints, foreign key
constraints, general constraints.
• Can create new domains, Domain Constraints:
– CREATE DOMAIN LegalRatings INTEGER DEFAULT 0
CHECK (VALUE >= 1 and VALUE <=10)
– Create Table Sailor (…, rating LegalRatings, …)
– Underlying domain is still Integers for comparison
• Can create new types: CREATE TYPE AllRatings as INTEGER
– Underlying domain is now a new type. Can’t compare with INTEGER without a
cast. None of the aggregates on INTEGER work on AllRatings

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 29

 CREATE TABLE Sailors
Table Constraints ( sid INTEGER,
sname CHAR(10),
• More general ICs than rating INTEGER,
key constraints age REAL,
• Can use queries PRIMARY KEY (sid),
to express CHECK ( rating >= 1
constraint CREATE TABLE Reserves AND rating <= 10 )
• Constraints ( sname CHAR(10),
can be named. bid INTEGER,
day DATE,
PRIMARY KEY (bid,day),
CONSTRAINT noInterlakeRes
CHECK (`Interlake’ <>
( SELECT B.bname
FROM Boats B
WHERE B.bid=bid)))
3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 30
 Constraints Over Multiple Relations
CREATE TABLE Sailors
• Awkward Number of boats
( sid INTEGER,
& Wrong! plus number of
sname CHAR(10),
• If Sailors is empty, rating INTEGER, sailors is < 100
the number of age REAL,
Boats tuples can be PRIMARY KEY (sid),
anything! CHECK
• ASSERTION is the ((SELECT COUNT (S.sid) FROM Sailors S) +
right solution; not (SELECT COUNT (B.bid) FROM Boats B) < 100)
associated with
either table CREATE ASSERTION smallClub
CHECK
((SELECT COUNT (S.sid) FROM Sailors S) +
(SELECT COUNT (B.bid) FROM Boats B) < 100)
3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 31
 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)
• Before and After Triggers
• Trigger Execution
– Row-level Triggers: Once per row
– Statement-level Triggers: Once per SQL statement

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 32

 Triggers: Example
CREATE TRIGGER init_count BEFORE INSERT ON Students /* Event */
DECLARE
count INTEGER /* Action */
BEGIN
count := 0
END

CREATE TRIGGER incr_count AFTER INSERT ON Student /* Event */

WHEN (new.age < 18) /* Condition */
FOR EACH ROW
BEGIN /* Action */
count := count + 1;
END

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 33

 Triggers
• First trigger executed before the activating statement, second
executes after the activating statement.
• Options:
– “BEFORE”
– “AFTER”
– “INSTEAD OF” (only valid on views)
• In combination with:
– “FOR EACH ROW” - execute once per modified record
– (default) - execute once per activating statement.
Can also specify using “FOR EACH STATEMENT”
• In combination with:
– “INSERT”
– “DELETE”
– “UPDATE”

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 34

 Triggers
• Referring to values
– Old
– New
– Set of changed record

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 35

 Triggers: Example
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

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 36

 Triggers v/s Constraints
• Often used to maintain consistency
– Can you use a foreign key?
– Foreign keys are not defined operationally
• Constraints are easier to understand than triggers
• Triggers are more powerful.
– Often used to fill out fields in a form
– Check complex actions (such as credit limit in a shopping
application)
– Check preferred customer status
– Generate logs for auditing and security checks.
– Internally can be used by the DBMS for replication management.

3/25/17 CS 564: Database Management Systems, Jignesh M. Patel 37