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 DocumentRight 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 DocumentRight 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 DocumentRight 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.

Page1 / 23

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

This preview shows document pages 1 - 7. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online