ics321-20090917-sql2

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

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

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

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

View Full Document
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
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

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

View Full Document
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
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’ ))

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

View Full Document
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’ ))
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
Ask a homework question - tutors are online