Title: SQL: Queries, Constraints, Triggers
1SQL Queries, Constraints, Triggers
2Overview 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
3Example Instances
- Sailors(sid integer, sname string, rating
integer, age real) - Boats(bid integer, bname string, color string)
- Reserves(sid integer, bid integer, day date)
4enter 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")
5Basic 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
6Conceptual 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
7Example 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
8A 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
9Some 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
10Find 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?
11Expressions 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
12Find 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
13Find 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
14Find 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
15Find 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
16Nested 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.
17Nested 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
18Nested 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?
19Correlated 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)
20More 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)
21Find 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)
22Rewriting 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.
23Using 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))
24Division 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))
25Aggregate 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
26Aggregate 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)
27Find 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)
28GROUP 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
29Queries 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
30Conceptual 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
31Find 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
32More 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
33For 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
34Find 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)
35Null 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
36Integrity 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)
37Table 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
38Assertions
- 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 )
39Triggers
- 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
40Summary
- 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
41Summary (Contd.)
- NULL for unknown field values brings many
complications - SQL allows specification of rich integrity
constraints - Triggers respond to changes in the database