421-sql1

Sname from skaters s where exists select from

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: r           EXISTS (relation) is true iff the relation is non-empty Find names of skaters who have participated in competition 103 SELECT S.sname FROM Skaters S WHERE EXISTS (SELECT * FROM Participated P WHERE P.cid = 103 AND P.sid = S.sid) Scoping rule: to refer to outer Skater in the inner subquery, we need to give a range variable to the outer relation. A subquery that refers to values from a surrounding query is called a correlated subquery. Since the inner query depends on the row of the outer query it must be reevaluated for each row in the outer query   WHERE (a1,a2) IN (SELECT a3, a4… COMP-421: Database Systems - SQL Queries I 15 COMP-421: Database Systems - SQL Queries I Complex queries Quantifiers     ANY and ALL behave as existential and universal quantifiers, respectively. Syntax     WHERE attr op ALL (SELECT   op is one of <, =, >, <>, <=, >= Find the skater with the highest rating SELECT * FROM Skaters WHERE rating >= ALL (SELECT rating FROM Skaters) COMP-421: Database Systems - SQL Queries I Find skaters who have competed in all competitions SELECT sname FROM Skaters S WHERE NOT EXISTS ((SELECT C.cid FROM Competition C) EXCEPT (SELECT P.cid FROM Participates P WHERE P.sid=S.sid)) SELECT sname FROM Skaters S WHERE NOT EXISTS (SELECT C.cid FROM Competition C WHERE NOT EXISTS (SELECT P.cid FROM Participates P WHERE P.cid = C.cid AND P.sid = S.sid))   WHERE attr op ANY (SELECT …   16 17 COMP-421: Database Systems - SQL Queries I 18 3...
View Full Document

Ask a homework question - tutors are online