6SQL_PART2 - Nested Structures SELECT Name FROM Items WHERE...

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

View Full Document Right Arrow Icon
Fall 2001 Database Systems 1 Nested Structures SELECT Name FROM Items WHERE Iid IN (SELECT DISTINCT Iid FROM Bids) I TEMS I id Oid Name Location I 1 1 Scales Bost on I 2 2 Dipping Bird Houst on I 3 Telescope New York I 4 3 Compass Dallas I 5 4 “Pour Roby” Picasso Seat t le I 6 Pipe Bost on BI DS Bid Dat e I id BuyI d Amount Bid1 8/ 30 15 Bid2 I 4 5 65 Bid3 8/ 31 18 Bid4 9/ 1 I 6 75 Bid5 9/ 1 20 Bid6 9/ 2 I 4 70 Bid7 I 2 Bid8 9/ 3 I 2
Background image of page 1

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

View Full DocumentRight Arrow Icon
Fall 2001 Database Systems 2 Let Temp = SELECT Iid FROM Bids TEMP I id I 1 I 4 I 6 I 2 SELECT Name FROM Items WHERE Iid IN (SELECT Iid FROM Bids) Is Iid IN Temp? Is I1 IN Temp? I TEMS I id Oid Name Location 1 Scales Bost on Pick a tuple from Items YES! Put this tuple in the output I TEMS I id Oid Name I 2 2 Dipping B. Houst on Is Iid IN Temp? Is I2 IN Temp? Pick another tuple from Items YES! Put this tuple in the output I TEMS I id Oid Name I 3 2 Telescope NY Is Iid IN Temp? Is I3 IN Temp? Pick another tuple from Items NO! Remove this tuple from the output
Background image of page 2
Fall 2001 Database Systems 3 Nested Structures – correlation SELECT I.Name FROM Items I WHERE I.Iid IN (SELECT DISTINCT B.Iid FROM Bids B WHERE B.Buyid = I.Oid) I TEMS I id Oid Name Location I 1 1 Scales Bost on Pick a tuple from Items Compute the nested relation TEMP Iid I2 Is I1 in Temp? No, remove this tuple! I TEMS I id Oid Name I 2 2 Dipping B. Houst on Pick a tuple from Items Compute the nested relation I1 Is I2 in Temp? No, remove this tuple!
Background image of page 3

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

View Full DocumentRight Arrow Icon
Fall 2001 Database Systems 4 Nested Structures – correlation SELECT I.Name FROM Items I WHERE I.Iid IN (SELECT DISTINCT B.Iid FROM Bids B WHERE B.Buyid = I.Oid) I TEMS I id Oid Name Location I 3 2 Telescope New Y. Pick a tuple from Items Compute the nested relation TEMP Iid I1 Is I3 in Temp? No, remove this tuple! I TEMS I id Oid Name I 4 3 Compass Dallas Pick a tuple from Items Compute the nested relation TEMP I4 I2 Is I4 in Temp? YES, keep this tuple!
Background image of page 4
Fall 2001 Database Systems 5 SQL - Nested Statements A nested sub-expression may refer to the tuples from the outer relation SELECT Buy.Name FROM Buyers Buy WHERE Buy.BuyId IN ( SELECT B.BuyID FROM Bids B WHERE B.Amount > 20 AND B.BuyID NOT IN ( SELECT B2.BuyID FROM Bids B2 WHERE B2.Amount <= 20 AND B2.Iid = B.Iid)) Names of buyers with a bid greater than 20 on an item for which the buyer has no other bids less than 20.
Background image of page 5

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

View Full DocumentRight Arrow Icon
Fall 2001 Database Systems 6 Nested Structures - Exists SELECT FROM WHERE ( NOT ) EXISTS ( SELECT * FROM R 1 , . .., R m WHERE P ) Semantics: For each tuple of the outer query, execute the inner query; if there is at least one (no) tuple in the result of the inner query, then retrieve that tuple of the outer query. This accounts for the “there exists” type of queries
Background image of page 6
Fall 2001 Database Systems 7 SQL Nested Statements Two more nested conditions: EXISTS ( SELECT … FROM … WHERE … ) is true if the SFW clause returns a non-empty relation NOT EXISTS ( SELECT … FROM … WHERE … ) is true if the SFW clause returns an empty relation Find the name of all buyers who have no bids with an amount less than 50 SELECT Buy.Name FROM Buyers Buy WHERE NOT EXISTS (SELECT B.BuyId FROM Bids B WHERE B.Amount <50 AND B.BuyID=Buy.BuyId)
Background image of page 7

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

View Full DocumentRight Arrow Icon
Image of page 8
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 38

6SQL_PART2 - Nested Structures SELECT Name FROM Items WHERE...

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

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