Megan Taylor

front-end dev, volunteacher, news & data junkie, bibliophile, Flyers fan, sci-fi geek and kitteh servant

Intro to Databases: SQL Table Variable and Set Operators

Table variables – From clause of SELECT statement

set operators: union, intersect, except

Demo: simple college admissions database
College (cName, state, enrollment)
Student (sID, sName, GPA, sizeHS)
Apply (sID, cName, major, decision)

Table variable example
select S.sID, sNAme, GPA, A.cName, enrollment
from Student S, College C, Apply A
where A.sID = S.sID and A.cName = C.cName;

Find all pairs of students with the same GPA
select S1.sID, S1.sName, S1.GPA, S2.sID, S2.sName, S2.GPA
from Student S1, Student S2
where S1.GPA = S2.GPA and S1.sID < S2.sID; Watch out for duplicates! Union Set Operator select cName as name from College union all select sName as name from Student order by name; union operator by default eliminates duplicates to include duplicates add all Intersect Set Operator select sID from Apply where major = 'CS' intersect select sID from Apply where major = 'EE'; some database systems don't support the intersect operator, just write queries differently, watch out for duplicates! select distinct A1.sID from Apply A1, Apply A2 where A1.sID = A2.sID and A1.major = 'CS' and A2.major = 'EE'; Except Set Operator select sID from Apply where major = 'CS' except select sID from Apply where major = 'EE'; some database systems don't support the except operator, need to write queries different, not addressed in this lecture.

November 5, 2011 | Comments Off on Intro to Databases: SQL Table Variable and Set Operators | Categories: Posts | Permalink

Comments are closed.

%d bloggers like this: