[SOLVED] database SQL SQL: Queries, Constraints, Triggers

SQL: Queries, Constraints, Triggers

SQL Division

CS430/630
Lecture 7

Slides based on Database Management Systems 3rd ed, Ramakrishnan and Gehrke

Division

Used to answer queries such as:

Find sailors who have reserved all boats.

Let A have 2 fields, x and y; B have only field y:

A/B =

A/B contains all x tuples (sailors) such that for every y tuple

(boat) in B, there is an xy tuple in A

Or, if the set of y values (boats) associated with an x value (sailor)

in A contains all y values in B, the x value is in A/B.

In general, x and y can be any sets of fields (not singletons)

x x y A y B| ,

bid

b1

b2

b4

bid

b2

b4

bid

b2

sid

s1

s2

s3

s4

sid

s1

s4

sid

s1

Examples of Division A/B

A

B1

B2
B3

A/B1 A/B2 A/B3

sid bid

s1 b1

s1 b2

s1 b3

s1 b4

s2 b1

s2 b2

s3 b2

s4 b2

s4 b4

Query 1

Find the names of sailors whove reserved all boats

( , (
,

Re ) / ( ))Tempsids
sid bid

serves
bid

Boats

sname Tempsids Sailors( )

Query 2

Find sailors whove reserved all red boats

)))

((/)Re
,

(,( Boats
redcolorbid

serves
bidsid

Temp

)( SailorsTempsname

Expressing A/B Using Basic Operators

For A/B, compute all x values that are not disqualified by

some y value in B

x value is disqualified if by attaching y value from B, we obtain an

xy tuple that is not in A

Disqualified x values:

A/B:

)))((( ABAxx

x A( ) all disqualified tuples

)))((()( ABAxxAx

Division in SQL

Not supported as primitive operator

Need to use nested queries to express division

One of the most subtle queries in SQL

Need to pay close attention to writing SQL division queries!

There are two ways of writing division queries

Using the set EXCEPT operator (2-level nesting)

Without the EXCEPT operator (3-level nesting)

Division: Solution 1

With EXCEPT:

SELECTS.sname

FROMSailors S

WHERENOT EXISTS

(

(SELECTB.bid FROMBoats B)

EXCEPT

(SELECTR.bid FROMReserves R

WHERER.sid=S.sid)

)

Find sailors whove reserved all boats.

Division: Solution 2

SELECTS.sname
FROMSailors S
WHERENOT EXISTS(SELECTB.bid
FROMBoats B
WHERENOT EXISTS(SELECT*
FROMReserves R
WHERER.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

Find sailors whove reserved all boats.

Without EXCEPT:

Find sailors whove reserved all red boats.

With EXCEPT:

SELECTS.sname

FROMSailors S

WHERENOT EXISTS

(

(SELECTB.bid FROMBoats B

WHERE B.color = red)

EXCEPT

(SELECTR.bid FROMReserves R

WHERER.sid=S.sid) )

Find sailors whove reserved all red boats.

Without EXCEPT:

Find sailors whove reserved all red boats.

SELECTS.sname
FROMSailors S
WHERENOT EXISTS(SELECTB.bid
FROMBoats B
WHEREB.color=red AND
NOT EXISTS(SELECT*
FROMReserves R
WHERER.bid=B.bid
AND R.sid=S.sid))

Find sailors whove reserved all red boats.

Another Example

SELECT A.name FROM Actors A
WHERE NOT EXISTS(

SELECT M.movie_id FROM Movies M
WHERE M.year=1990 AND M.studio=Universal

EXCEPT

SELECT S.movie_id FROM Stars_In S
WHERE S.actor_id=A.actor_id
)

Find names of actors who star in ALL movies produced by Universal in year 1990.