SQL: Queries, Constraints, Triggers

1
SQL Queries, Constraints, Triggers

• Chapter 5

2
Overview aspects of SQL

• DML Data Management Language. Pose queries (Ch.
  5) and insert, delete, modify rows (Ch. 3)
• DDL Data Definition Language. Creation, deletion
  and modification of tables or views (Ch. 3)
• Triggers and Advanced Integrity Constraints (Ch
  5)
• Triggers are actions executed by DBMS whenever
  changes to the database meet conditions specified
  in the trigger
• Embedded and dynamic SQL (Ch. 6). Allow SQL to be
  called from a host language such as C
• Client-server execution and remote database
  access (Ch. 7)
• Transaction management (Ch. 21)
• Security
• Advanced features
• OO, recursive, decision support, data mining,
  spatial data, text, XML

3
Example Instances

• Sailors(sid integer, sname string, rating
  integer, age real)
• Boats(bid integer, bname string, color string)
• Reserves(sid integer, bid integer, day date)

4
enter data into MySql

• Schema on p133, tables on p134
• create table sailors(sid integer, sname char(20),
  rating integer, age real, primary key(sid))
• create table boats(bid integer, bname char(20),
  color char(20), primary key(bid))
• create table reserves(sid integer, bid integer,
  day date, primary key(sid,bid,day), foreign
  key(sid) references sailors, foreign key(bid)
  references boats)
• insert into sailors(sid,sname,rating,age)
  values(22,"Dustin",7,45)
• insert into boats(bid,bname,color)
  values(101,"Interlake","blue")
• insert into reserves(sid,bid,day)
  values(22,101,"1998-10-10")

5
Basic SQL Query
SELECT DISTINCT select-list FROM
from-list WHERE qualification

• from-list A list of relation names, specifying a
  cross-product of tables
• select-list A list of attributes of relations in
  from-list, specifying columns to be retained in
  the result
• qualification optional. Specifies select
  conditions on the tables mentioned in the
  from-list
• DISTINCT optional keyword indicating that the
  answer should not contain duplicates
• By default duplicates are not eliminated

6
Conceptual Evaluation Strategy

• Semantics of an SQL query defined in terms of
  the following conceptual evaluation strategy
• Compute the cross-product of from-list
• Discard resulting tuples if they fail
  qualifications
• Delete attributes that are not in select-list
• If DISTINCT is specified, eliminate duplicate
  rows
• This strategy is probably the least efficient way
  to compute a query
• optimizer will find more efficient strategies to
  compute the same answers

7
Example of Conceptual Evaluation
SELECT S.sname FROM Sailors S, Reserves
R WHERE S.sidR.sid and R.bid103
select from Sailors, Reserves select from
Sailors S, Reserves R where S.sidR.sid and
R.bid103 select S.sname from Sailors S,
Reserves R where S.sidR.sid and R.bid103
8
A Note on Range Variables

• from-list table name can be followed by a range
  variable
• select-list column name can be prefixed by a
  range variable
• When is it necessary?
• Really needed only if the same relation appears
  twice in from-list. Previous query can also be
  written as
• However, as a good style, use range variables
  always!

SELECT sname FROM Sailors, Reserves WHERE
Sailors.sidReserves.sid AND
bid103
9
Some examples

• Find the sids of sailors whove reserved a red
  boat
• Find the names of sailors whove reserved a red
  boat
• Find the colors of boats reserved by Lubber

SELECT R.sid FROM Boats B, Reserves R WHERE
B.bidR.bid and B.colorred
SELECT S.sname FROM Sailors S, Boats B,
Reserves R WHERE S.sidR.sid and B.bidR.bid and
B.colorred
SELECT B.color FROM Sailors S, Boats B,
Reserves R WHERE S.sidR.sid and B.bidR.bid and
S.nameLubber
10
Find sailors whove reserved at least one boat
SELECT S.sid FROM Sailors S, Reserves R WHERE
S.sidR.sid

• Would adding DISTINCT to this query make a
  difference?
• What is the effect of replacing S.sid by S.sname
  in the SELECT clause? Would adding DISTINCT to
  this variant of the query make a difference?

11
Expressions and Strings
SELECT S.age, age1S.age-5, 2S.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

12
Find names of sailors whove reserved a red or a
green boat

• UNION Can be used to compute the union of any
  two union-compatible sets of tuples (which are
  themselves the result of SQL queries)

SELECT S.sname FROM Sailors S, Boats B,
Reserves R WHERE S.sidR.sid AND R.bidB.bid
AND (B.colorred OR B.colorgreen)
SELECT S.sname FROM Sailors S, Boats B,
Reserves R WHERE S.sidR.sid AND R.bidB.bid
AND B.colorred UNION SELECT
S2.sname FROM Sailors S2, Boats B2, Reserves
R2 WHERE S2.sidR2.sid AND R2.bidB2.bid
AND B2.colorgreen
13
Find names of sailors whove reserved a red and a
green boat
SELECT S.sname FROM Sailors S, Boats B1,
Reserves R1, Boats B2, Reserves
R2 WHERE S.sidR1.sid AND R1.bidB1.bid AND
S.sidR2.sid AND R2.bidB2.bid AND
(B1.colorred AND B2.colorgreen)

• 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
  dont support it
• Upper query hard to understand, more intuitive to
  use intersect
• Problem?

SELECT S.sname FROM Sailors S, Boats B,
Reserves R WHERE S.sidR.sid AND R.bidB.bid
AND B.colorred INTERSECT SELECT
S2.sname FROM Sailors S2, Boats B2, Reserves
R2 WHERE S2.sidR2.sid AND R2.bidB2.bid
AND B2.colorgreen
14
Find sids of sailors whove reserved red but not
green boats
SELECT R.sid FROM Boats B, Reserves R WHERE
R.bidB.bid AND B.colorred EXCEPT SELECT
R2.sid FROM Boats B2, Reserves R2 WHERE
R2.bidB2.bid AND B2.colorgreen
15
Find all sids of sailors who have a rating of 10
or reserved boat 104

• any two union-compatible sets of tuples
• For UNION, INTERSECTION and EXCEPT, by default
  duplicates are eliminated

SELECT S.sid FROM Sailors S WHERE S.rating
10 UNION SELECT R.sid FROM Reserves R WHERE
R.bid104
16
Nested Queries
Find names of sailors whove reserved boat 103
SELECT S.sname FROM Sailors S WHERE S.sid IN
(SELECT R.sid
FROM Reserves R
WHERE R.bid103)

• Powerful feature of SQL a WHERE clause can
  itself contain an SQL query! (Actually, so can
  FROM and HAVING clauses.)
• Why can be nested?
• IN tests whether a value is in a set
• To find sailors whove not reserved 103, use NOT
  IN.

17
Nested Queries
Find names of sailors whove reserved a red boat
SELECT S.sname FROM Sailors S WHERE S.sid IN
(SELECT R.sid
FROM Reserves R
WHERE R.bid IN (SELECT B.bid
FROM Boats B WHERE B.colorred )

• Find names of sailors who have not reserved a red
  boat
• How about replace both to NOT IN

18
Nested Queries with Correlation
Find names of sailors whove reserved boat 103
SELECT S.sname FROM Sailors S WHERE EXISTS
(SELECT FROM
Reserves R WHERE
R.bid103 AND S.sidR.sid)

• EXISTS is another set comparison operator, like
  IN
• Tests whether a set is non-empty
• NOT EXISTS tests whether a set is empty
• 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
• Why do we have to replace by R.bid?

19
Correlated vs. Non-correlated

• Two types of sub query
• correlated
• non-correlated.
• Difference
• A correlated subquery is evaluated once per row
  processed by the parent query
• A non-correlated subquery is only executed once
  and the result set is kept in memory (if the
  result set is small)

20
More on Set-Comparison Operators

• Weve already seen IN, EXISTS and UNIQUE. Can
  also use NOT IN, NOT EXISTS and NOT UNIQUE
• Also available op ANY, op ALL
• op
• Find sailors whose rating is greater than some
  sailor called Horatio
• Find sailors whose rating is greater than every
  sailor called Horatio

SELECT FROM Sailors S WHERE S.rating gt ANY
(SELECT S2.rating
FROM Sailors S2
WHERE S2.snameHoratio)
21
Find the sailors with the highest rating

• IN equivalent to ANY
• NOT IN equivalent to ltgt ALL

SELECT FROM Sailors S WHERE S.rating gt ALL
(SELECT S2.rating
FROM Sailors S2)

22
Rewriting INTERSECT Queries Using IN
Find the names of sailors whove reserved both a
red and a green boat
SELECT S.sname FROM Sailors S, Boats B,
Reserves R WHERE S.sidR.sid AND R.bidB.bid AND
B.colorred AND S.sid IN (SELECT
S2.sid
FROM Sailors S2, Boats B2, Reserves R2
WHERE S2.sidR2.sid
AND R2.bidB2.bid
AND B2.colorgreen)

• Similarly, EXCEPT queries re-written using NOT
  IN.

23
Using INTERSECT
Find names of sailors whove reserved both a red
and a green boat
SELECT S.sname FROM Sailors S WHERE S.sid IN
(( SELECT R.sid FROM Boats B, Reserves
R WHERE R.bidB.bid AND B.colorred) INTERSEC
T (SELECT R2.sid FROM Boats B2, Reserves
R2 WHERE R2.bidB2.bid AND B2.colorgreen))
24
Division in SQL
Find sailors whove reserved all boats
SELECT S.sname FROM Sailors S WHERE NOT EXISTS
((SELECT B.bid
FROM Boats B) EXCEPT
(SELECT R.bid FROM
Reserves R WHERE R.sidS.sid))
25
Aggregate Operators

• Significant extension of relational algebra
• In addition to simply retrieving data, we often
  want to perform some computation and summarization

COUNT () num of tuples COUNT ( DISTINCT A)
num of (unique) values in column A SUM (
DISTINCT A) sum of all (unique) values in
column A AVG ( DISTINCT A) average of
(unique) values in column A MAX (A) max value
in column A MIN (A) min value in column A
26
Aggregate Examples
SELECT COUNT () FROM Sailors S
SELECT COUNT (DISTINCT S.sname) FROM Sailors S
SELECT AVG (S.age) FROM Sailors S WHERE
S.rating10
SELECT S.sname, MAX (S.age) FROM Sailors
S Illegal! If the SELECT clause uses an
aggregate, then it must use only aggregate unless
the query contains GROUP BY
SELECT S.sname, S.age FROM Sailors S WHERE
S.age (SELECT MAX(S2.age)
FROM Sailors S2)
27
Find the sailors with the highest rating
SELECT FROM Sailors S WHERE S.rating gt ALL
(SELECT S2.rating
FROM Sailors S2)

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

28
GROUP BY and HAVING

• So far, weve applied aggregate operators to all
  (qualifying) tuples. Sometimes, we want to apply
  them to each of several groups of tuples
• Find the age of the youngest sailor for each
  rating level
• In general, we dont 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 S WHERE
S.rating i
29
Queries With GROUP BY and HAVING
SELECT DISTINCT select-list FROM
from-list WHERE qualification GROUP BY
grouping-list HAVING group-qualification
SELECT S.rating, MIN (S.age) FROM Sailors
S GROUP BY S.rating

• The select-list contains (i) column names (ii)
  terms with aggregate operations (e.g., MIN
  (S.age))
• The column names in (i) must appear in the
  grouping-list
• Intuitively, each answer tuple corresponds to a
  group, and these attributes must have a single
  value per group
• Aggregates always return a single value
• A group is a set of tuples having the same value
  for all attributes in grouping-list
• If not subset, i.e., a column appears in list (i)
  but not grouping list, there can be multiple rows
  within a group that have different values in this
  column, and it is not clear what value should be
  assigned to this column in an answer row
• If GROUP BY is omitted, entire table is
  considered as a single group

30
Conceptual Evaluation

• The cross-product of from-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!
• One answer tuple is generated per qualifying
  group
• If DISTINCT, duplicates are eliminated

31
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 gt 18 GROUP BY S.rating HAVING
COUNT () gt 1

• Only S.rating and S.age are mentioned in the
  SELECT, GROUP BY or HAVING clauses other
  attributes unnecessary
• Use AS to rename 2nd column
• SELECT S.rating, MIN (S.age) AS minage

32
More examples of aggregate queries
Find the age of the youngest sailor with age gt
18, for each rating with at least 2 sailors (of
any age)
SELECT S.rating, MIN (S.age) FROM Sailors
S WHERE S.age gt 18 GROUP BY S.rating HAVING 1
lt (SELECT COUNT ()
FROM Sailors S2 WHERE
S.ratingS2.rating)

• Shows HAVING clause can also contain a subquery
• Compare this with the query where we considered
  only ratings with at least 2 sailors over 18

33
For each red boat, find the number of
reservations for this boat
SELECT B.bid, COUNT () AS reservationcount FROM
Boats B, Reserves R WHERE R.bidB.bid AND
B.colorred GROUP BY R.bid
34
Find those ratings for which the average age is
the minimum over all ratings
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)
Not supported by MySql! How to solve? How about
Oracle?
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
(Temp2.avgage2)
FROM (SELECT S2.rating, AVG (S2.age)
AS avgage2
FROM Sailors S2 GROUP BY
S2.rating) AS Temp2)
35
Null Values

• Field values in a tuple are sometimes unknown
  (e.g., a rating has not been assigned) or
  inapplicable (e.g., no spouses name)
• SQL provides a special value null for such
  situations
• The presence of null complicates many issues
• Special operators needed to check if value is/is
  not null
• Is ratinggt8 true or false when rating is equal to
  null?
• We need a 3-valued logic (true, false and
  unknown)
• Meaning of constructs must be defined carefully
  (e.g., WHERE clause eliminates rows that dont
  evaluate to true)
• NOT NULL
• Implicit for each field in PRIMARY KEY constraint

36
Integrity Constraints (Review)

• An IC describes conditions that every legal
  instance of a relation must satisfy
• Inserts/deletes/updates that violate ICs 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 lt
  200)
• Types of ICs Domain constraints, primary key
  constraints, foreign key constraints, general
  constraints (table constraints, assertions)

37
Table Constraints
CREATE TABLE Sailors ( sid INTEGER, sname
CHAR(10), rating INTEGER, age REAL, PRIMARY
KEY (sid), CHECK ( rating gt 1 AND rating
lt 10 )
Rating must be in the range of 1 to 10
CREATE TABLE Reserves ( sid
INTEGER bid INTEGER, day
DATE, PRIMARY KEY (sid,bid,day),
FOREIGN KEY (sid) REFERENCES Sailors
FOREIGN KEY (bid) REFERENCES Boats CONSTRAINT
noInterlakeRes CHECK (Interlake ltgt (
SELECT B.bname FROM Boats B WHERE
B.bidReserves.bid)))
Interlake boats cannot be reserved

• Can use queries to express constraint
• Can be named

38
Assertions

• Constraints over multiple tables
• Not associated with a single table as table
  constraints

Number of boats plus number of sailors is lt 100
CREATE ASSERTION smallClub CHECK ( (SELECT
COUNT (S.sid) FROM Sailors S) (SELECT COUNT
(B.bid) FROM Boats B) lt 100 )
39
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)
• Useful in practice
• MySql 5.0 new feature

40
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
• 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

41
Summary (Contd.)

• NULL for unknown field values brings many
  complications
• SQL allows specification of rich integrity
  constraints
• Triggers respond to changes in the database