1 / 41

SQL Queries, Constraints, Triggers

- Chapter 5

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

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)

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")

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

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

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

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

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

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?

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

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

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

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

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

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.

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

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?

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)

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)

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)

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.

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))

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))

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

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)

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)

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

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

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

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

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

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

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)

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

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)

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

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 )

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

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

Summary (Contd.)

- NULL for unknown field values brings many

complications - SQL allows specification of rich integrity

constraints - Triggers respond to changes in the database