程序代写代做代考 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 who’ve reserved all boats”

  ( , (
,

Re ) / ( ))Tempsids
sid bid

serves
bid

Boats

 sname Tempsids Sailors( )

Query 2

“Find sailors who’ve 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:

SELECT S.sname

FROM Sailors S

WHERE NOT EXISTS

(

(SELECT B.bid FROM Boats B)

EXCEPT

(SELECT R.bid FROM Reserves R

WHERE R.sid=S.sid)

)

“Find sailors who’ve reserved all boats.”

Division: Solution 2

SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS (SELECT B.bid
FROM Boats B
WHERE NOT EXISTS (SELECT *
FROM Reserves R
WHERE 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

“Find sailors who’ve reserved all boats.”

Without EXCEPT:

“Find sailors who’ve reserved all red boats.”

With EXCEPT:

SELECT S.sname

FROM Sailors S

WHERE NOT EXISTS

(

(SELECT B.bid FROM Boats B

WHERE B.color = ‘red’)

EXCEPT

(SELECT R.bid FROM Reserves R

WHERE R.sid=S.sid) )

“Find sailors who’ve reserved all red boats.”

Without EXCEPT:

“Find sailors who’ve reserved all red boats.”

SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS (SELECT B.bid
FROM Boats B
WHERE B.color=‘red’ AND
NOT EXISTS (SELECT *
FROM Reserves R
WHERE R.bid=B.bid
AND R.sid=S.sid))

“Find sailors who’ve 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.”