{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

ics321-20090917-sql2 - ICS 321 Fall 2009 SQL Queries...

Info iconThis preview shows pages 1–7. Sign up to view the full content.

View Full Document Right Arrow Icon
ICS 321 Fall 2009 SQL: Queries, Constraints, Triggers (ii) Asst. Prof. Lipyeow Lim Information & Computer Science Department University of Hawaii at Manoa 9/8/2009 1 Lipyeow Lim -- University of Hawaii at Manoa
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Find the sid of sailors who have reserved exactly one boat 9/8/2009 Lipyeow Lim -- University of Hawaii at Manoa 2 SELECT S1.sid FROM Sailors S1 EXCEPT SELECT R1.sid FROM Reserves R1, Boats B1, Reserves R2, Boats B2 WHERE R1.sid=R2.sid AND R1.bid=B1.bid AND R2.bid=B2.bid AND R1.bid≠R2.bid SELECT R3.sid FROM Reserves R3 EXCEPT SELECT R1.sid FROM Reserves R1, Boats B1, Reserves R2, Boats B2 WHERE R1.sid=R2.sid AND R1.bid=B1.bid AND R2.bid=B2.bid AND R1.bid≠R2.bid
Background image of page 2
Nested Queries A nested query is a query that has another query, called a subquery, embedded within it. Subqueries can appear in WHERE, FROM, HAVING clauses 9/8/2009 Lipyeow Lim -- University of Hawaii at Manoa 3 SELECT S.sname FROM Sailors S WHERE S.sid IN ( SELECT R.sid FROM Reserves R WHERE R.bid=103 ) SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND bid=103 Q1 : Find the names of sailors who have reserved boat 103
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Conceptual Evaluation Strategy for Nested Queries 1. Compute the cross-product of relation-list . If there is a subquery, recursively (re-)compute the subquery using this conceptual evaluation strategy Compute the cross-product over the results of the subquery. 2. Discard resulting tuples if they fail qualifications . If there is a subquery, recursively (re-)compute the subquery using this conceptual evaluation strategy Evaluate the qualification condition that depends on the subquery 3. Delete attributes that are not in target-list . 4. If DISTINCT is specified, eliminate duplicate rows. 9/8/2009 Lipyeow Lim -- University of Hawaii at Manoa 4
Background image of page 4
Q2: Find the names of sailors who have reserved a red boat Unravel the nesting from the innermost subquery 9/8/2009 Lipyeow Lim -- University of Hawaii at Manoa 5 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.color =`red’ ))
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Q21: Find the names of sailors who have not reserved a red boat 9/8/2009 Lipyeow Lim -- University of Hawaii at Manoa 6 SELECT S.sname FROM Sailors S WHERE S.sid NOT IN ( SELECT R.sid FROM Reserves R WHERE R.bid IN ( SELECT B.bid FROM Boats B WHERE B.color =`red’ ))
Background image of page 6
Image of page 7
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}