sql: queries and constraintsavid.cs.umass.edu/courses/445/s2008/lectures/445-lec5-sql-part1.… ·...

1

SQL: Queries and Constraints

Yanlei DiaoUMass Amherst

Feb 12, 2007

Slides Courtesy of R. Ramakrishnan, J. Gehrke, and G. Miklau

2

DML versus DDL

Data Manipulation Language (DML)posing queries and operating on tuples

Data Definition Language (DDL)operating on tables/views

3

SQL Overview

Query capabilitiesSELECT-FROM-WHERE blocks Set operations (union, intersect, except)Nested queries (correlation)Aggregation & GroupingNull values

Database updatesViews

4

Example Instancessid sname rating age22 dustin 7 45.031 lubber 8 55.558 rusty 10 35.0

sid sname rating age28 yuppy 9 35.031 lubber 8 55.544 guppy 5 35.058 rusty 10 35.0

sid bid day22 101 10/10/9658 103 11/12/96

R1

S1

S2

5

Basic SQL Query

relation-list: A list of relation names (possibly with a range-variable after each relation name).qualification: Predicates combined using AND, OR and NOT.

Predicate: Attr op const or Attr1 op Attr2, where op is one of <, >, >=, <=, =, <>

target-list: A list of attributes of relations in relation-listDISTINCT indicates no duplicates in the answer. Default is that duplicates are not eliminated!

SELECT [DISTINCT] target-listFROM relation-listWHERE qualification;

6

Conceptual Evaluation Strategy

Semantics of an SQL query defined in terms of the following conceptual evaluation strategy:

Compute the cross-product of relation-list.Iterate over resulting tuples and discard those that 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.

7

Example of Conceptual EvaluationSELECT S.snameFROM Sailors S, Reserves RWHERE S.sid=R.sid AND R.bid=103;

(sid) sname rating age (sid) bid day22 dustin 7 45.0 22 101 10/10/9622 dustin 7 45.0 58 103 11/12/9631 lubber 8 55.5 22 101 10/10/9631 lubber 8 55.5 58 103 11/12/9658 rusty 10 35.0 22 101 10/10/9658 rusty 10 35.0 58 103 11/12/96

XXXXX

What is the relational algebra for this query?

X

X

8

Relational Algebra for the Query

SELECT S.snameFROM Sailors S, Reserves RWHERE S.sid=R.sid AND R.bid=103;

π σsname bid serves Sailors(( Re ) )=103 ><

9

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.snameFROM Sailors S, Reserves RWHERE S.sid=R.sid AND bid=103;

SELECT snameFROM Sailors, Reserves WHERE Sailors.sid=Reserves.sid

AND bid=103;

It is good style,however, to userange variablesalways!OR

10

Find sailors who’ve reserved at least one boat

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

SELECT S.sidFROM Sailors S, Reserves RWHERE S.sid=R.sid;

11

String Pattern Matching

Find the ages of sailors whose names begin and end with ‘B’ and contain at least three characters.LIKE is used for string matching.

`_’ stands for any one character. `%’ stands for 0 or more arbitrary characters.

SELECT S.ageFROM Sailors SWHERE S.sname LIKE ‘B_%B’;

12

Arithmetic Expressions

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.Arithmetic expressions can also appear in the predicates in WHERE.

SELECT S.age, age1=S.age-5, 2*S.age AS age2FROM Sailors SWHERE S.sname LIKE ‘B_%B’;

13

SQL Overview

Query capabilitiesSELECT-FROM-WHERE blocks Set operations (union, intersect, except)Nested queries (correlation)Aggregation & GroupingNull values

Database updatesViews

14

Find sid’s of sailors who’ve reserved a red or a green boat

If we replace OR by AND in this query, what do we get?

UNION: computes the union of any two union-compatible sets of tuples (which are themselves the result of SQL queries).

SELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid

AND (B.color=‘red’ OR B.color=‘green’);

SELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid

AND B.color=‘red’UNIONSELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid

AND B.color=‘green’;

15

Find sid’s of sailors who’ve reserved a red and a green boat

INTERSECT: computes the intersection of any two union-compatible sets of tuples.

Included in SQL-92, but some systems don’t support it.

SELECT S.sidFROM Sailors S, Boats B1, Reserves R1,

Boats B2, Reserves R2WHERE S.sid=R1.sid AND R1.bid=B1.bid

AND S.sid=R2.sid AND R2.bid=B2.bidAND (B1.color=‘red’ AND B2.color=‘green’);

SELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid

AND B.color=‘red’INTERSECTSELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid

AND B.color=‘green’;

Need DISTINCT to be equivalent!

16

Find sid’s of sailors who’ve reserved …

Also available: EXCEPT(What does this query return?)

SELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid

AND B.color=‘red’EXCEPTSELECT S.sidFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid

AND B.color=‘green’;

17

SQL Overview

Query capabilitiesSELECT-FROM-WHERE blocks Set operations (union, intersect, except)Nested queries (correlation)Aggregation & GroupingNull values

Database updatesViews

18

Nested queriesA nested query is a query with another query embedded within it. The embedded query is called the subquery.The subquery usually appears in the WHERE clause:

SELECT S.snameFROM Sailors SWHERE S.sid IN ( SELECT R.sid

FROM Reserves RWHERE R.bid = 103 )

(Subqueries also possible in FROM or HAVING clause.)

19

Conceptual evaluation, extended

SELECT S.snameFROM Sailors SWHERE S.sid IN ( SELECT R.sid

FROM Reserves RWHERE R.bid = 103 )

For each row in cross product of outer query, evaluate the WHERE clause conditions, (re)computing the subquery.

SELECT S.snameFROM Sailors S, Reserves RWHERE S.sid=R.sid AND R.bid=103

equivalent to:

20

Correlated subquery

SELECT S.snameFROM Sailors SWHERE EXISTS ( SELECT *

FROM Reserves RWHERE R.bid = 103

AND R.sid = S.sid )

If the inner subquery depends on tables mentioned in the outer query then it is a correlated subquery.In terms of conceptual evaluation, we must recompute subquery for each row of outer query.

Correlation

21

Set-comparison operators

Optional NOT may precede these:EXISTS R -- true if R is non-emptyattr IN R -- true if R contains attrUNIQUE R -- true if no duplicates in R

For arithmetic operator op {<,<=,=,< >, >=,>}attr op ALL R-- all elements of R satisfy conditionattr op ANY R -- some element of R satisfies condition

IN equivalent to = ANYNOT IN equivalent to < > ALL

22

Example

Find the sailors with the highest rating

SELECT S.sidFROM Sailors SWHERE S.rating >= ALL (SELECT S2.rating

FROM Sailors S2 )

23

Please write SQL

Find sailors whose rating is higher than some sailor named Harry.

Find sailors whose rating is higher than allsailors named Harry.

SELECT S.sidFROM Sailors SWHERE S.rating > ANY (SELECT S2.rating

FROM Sailors S2 S2.name = ‘Harry’)

SELECT S.sidFROM Sailors SWHERE S.rating > ALL (SELECT S2.rating

FROM Sailors S2 S2.name = ‘Harry’)

24

Simulating INTERSECTSuppose we have tables R(a,b) and S(a,b)The following computes R ∩ S:

SELECT DISTINCT *FROM RWHERE (R.a, R.b) IN (SELECT *

FROM S );

SELECT DISTINCT R.a, R.bFROM R, SWHERE R.a = S.a AND R.b = S.b;Intersection!

• Given R(a,b), S(a,b), what is R S ?

This can be expressed without nesting:

25

Find the names of sailors who reserved a red and a green boat.

SELECT snameFROM Sailors S, Reserves R, Boats BWHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘red’

INTERSECT

SELECT snameFROM Sailors S, Reserves R, Boats BWHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘green’

using INTERSECT

SELECT snameFROM Sailors S, Reserves R, Boats BWHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘red’

AND S.sid IN(SELECT S2.sidFROM Sailors S2, Reserves R2, Boats B2WHERE S2.sid = R2.sid AND R2.bid = B2.bid AND B2.color = ‘green’ )

without INTERSECT

“Find all sailors who have reserved a red boat and, further, have sids that are included in the set of sids of sailors who have reserved a green boat.”

26

Simulating EXCEPT (set difference)What does this query compute?SELECT B.bidFROM Boats BWHERE B.bid NOT IN (SELECT R.bid

FROM Reserves RWHERE R.sid = 100 );

Inner on R: boats reserved by sailor with sid=100All boats − inner is what we want.

Find boats not reserved by sailor with sid = 100.

27

Find sailors who’ve reserved all boats.SELECT S.snameFROM Sailors SWHERE NOT EXISTS

((SELECT B.bidFROM Boats B)EXCEPT(SELECT R.bidFROM Reserves RWHERE R.sid=S.sid));

SELECT S.snameFROM Sailors SWHERE NOT EXISTS (

SELECT B.bidFROM Boats B WHERE NOT EXISTS (

SELECT R.bidFROM Reserves RWHERE R.bid=B.bid

AND R.sid=S.sid));

Sailors S such that ...

there is no boat B without ...

a Reserves tuple showing S reserved B

(2)

(1)