421-sql1

Cid p2cid and p1sid p2sid note that r1sid r2sid is

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: all the relations mentioned in the FROM clause FROM Participates p1, Participates p2 WHERE p1.cid = p2.cid AND p1.sid < p2.sid (note that r1.sid < r2.sid is needed to avoid producing (58,58) and to avoid producing a pair in both directions. 1 7 COMP-421: Database Systems - SQL Queries I Union, Intersection, Difference     Union UNION, INTERSECT, EXCEPT Input relations for set operators must be set-compatible, I.e. they must have       Same number of attributes   The attributes, taken in order, must have same   type As default, result relation is a set!!! (no multiset)   Many systems do not provide primitives for intersection and difference   Skaters(sid,sname,rating,age) Participates(sid,cid,rank) Competition(cid,date,type) Find skaters that have participated in a regional or a local competition SELECT P.sid FROM Participates P, Competition C WHERE P.cid = C.cid AND (C.type = regional OR C.type = 9 COMP-421: Database Systems - SQL Queries I Intersection Find skaters that have participated in a regional and a local competition   FROM Participates P, Competition C WHERE P.cid = C.cid AND C.type = local INTERSECT SELECT P.sid FROM Participates P, Competition WHERE P.cid = C.cid AND C.type = regional (2) SELECT P1.sid FROM Participates P1, Participates P2, Competition C1, Competition C2 WHERE (P1.cid = C1.cid AND C1.type = local ) AND (P2.cid = C2.cid AND C2.type = regional ) AND P1.sid = P2.sid) sid cid 101 58  103 rank 2 sid cid 31 101 5 58  103 rank 2 5 58 101 7 58 101 7 58 104 1 58 104 1 COMP-421: Database Systems - SQL Queries I 10 Difference (1) SELECT P.sid 31 local ) SELECT P.sid FROM Participates P, Competition C WHERE P.cid = C.cid AND C.type = local UNION SELECT P.sid FROM Participates P, Competition WHERE P.cid = C.cid AND C.type = regional COMP-421: Database Systems - SQL Queries I   8 COMP-421: Database Systems - SQL Queries I Find skaters that have participated in a local but not in a regional competition SELECT P.sid FROM Participates P, Competition C WHERE P.ci...
View Full Document

This document was uploaded on 02/11/2014.

Ask a homework question - tutors are online