421-sql1

Cid ccid and ctype regional c cid date type cid date

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: d = C.cid AND C.type = local EXCEPT SELECT P.sid FROM Participates P, Competition WHERE P.cid = C.cid AND C.type = regional C cid date type cid date type 101 12/13/08 local 101 12/13/13 local 103 1/12/09 103 1/12/14 regio 104 1/20/09 local regio 104 1/20/14 local COMP-421: Database Systems - SQL Queries I 12 2 Multiset Semantic   Multiset Semantic in SQL A multiset (bag) may contain the same tuple more than once, although there is no specified order (unlike a list).     Example: {1, 2, 1, 3} is a multiset, but not a set   Multiset Union: {1, 2, 2} ∪ {1, 2, 3, 3}     Sum the times an element appears in the two multisets   UNION ALL, INTERSECT ALL, EXCEPT ALL   Example: {1, 2, 2} ∪ {1, 2, 3, 3} = {1, 1, 2, 2, 2, 3, 3}   Multiset Intersection: {1, 2, 2} ∩ {1, 1, 2, 2, 3, 3} SELECT P.sid FROM Participates P, Competition C WHERE P.cid = C.cid AND C.type = local UNION ALL SELECT P.sid FROM Participates P, Competition WHERE P.cid = C.cid AND C.type = regional   Take the minimum of the number of occurrences in each multiset.   Example: {1, 2, 2} ∩ {1, 1, 2, 2, 3, 3} = {1, 2,2}   Multiset Difference: {1, 2, 2} - {1, 2, 3, 3}   Subtract the number of occurrences in the two multisets   Examples: {1, 2, 2} - {1, 2, 3, 3} = {2}   Some familiar laws for sets also hold for multisets (e.g., union is commutative); but other laws do not hold (e.g., R ∩ (S ∪ T) ≠ (R ∩ S) ∪ (R ∩ T) COMP-421: Database Systems - SQL Queries I 13 COMP-421: Database Systems - SQL Queries I Nested queries: The IN operator           Although SQL generally works with multisets, it uses set semantic for union/intersection/ difference To enforce multiset semantic for these operators use A where clause can itself contain an SQL query. The inner query is called a subquery Find names of skaters who have particpated in competition #103 SELECT S.sname FROM Skaters S WHERE S.sid IN (SELECT P.sid FROM Participates P WHERE P.cid = 103) To find skaters who have NOT participated in competition 103 use NOT IN Semantics best understood by nested loop assignment Multiple attributes: 14 Exists Operato...
View Full Document

This document was uploaded on 02/11/2014.

Ask a homework question - tutors are online