SQL- Introduction to SQL Set Operations

Introduction to SQL SetIntroduction to SQL Set
OperationsOperations

Database QueryDatabase Query
LanguagesLanguages
• Use “Campus” schema
• Given a database, ask questions, get data as answers
o Ex: Get all students with GPA > 3.7 who applied to Berkeley and
Stanford and nowhere else
o Ex: Get all humanities departments at campuses in Florida with < 1000
applicants
o Ex: Get the campus with highest average accept rate over the last
five years
• Some questions are easy to pose, some are not
• Some questions are easy for DBMS to answer, some are not.
• "Query language" also used to update the database
Lecture 15© CIS 4301 - Spring 2006 3

Relational QueryRelational Query
LanguagesLanguages
• Formal: relational algebra, relational calculus,
Datalog
• Actual: SQL, Quel, Query-by-Example (QBE)
• In ALL languages, a query is executed over a set of
relations, get single relation as the result

Relational AlgebraRelational Algebra
• Notation for describing queries in the relational
model
• Relational model has concrete set of “standard”
operations
• Operations are not “Turing Complete”
o Not a defect, helps with query processing and
optimization
o FYI, a language is Turing Complete if it is powerful enough
to implement any Turing machine. It's widely believed that
Turing machines can do any calculation that can be
performed by a modern computer program
• Start by introducing operations of relational algebra,
SQL next
• Algebra applies to sets of tuples, i.e., relations
o Commercial DBMS use different notation of relations
which are multisets

Relational AlgebraRelational Algebra
• Construct new relations from old ones
o Set of operators
o Relations are operands
• Build progressively more complex expressions
by applying operators to relations or to rela.
algebra expressions (which are relations as
well)
• Query is an expression of relational algebra
o First concrete example of a query language
• Four broad classes of operations
o Set operations, selection operations, operations
that combine data from two relations, rename
operation

Sample Relational SchemaSample Relational Schema
Movie (Title,Year,length,filmType,
studioName,producerC#)
StarsIn (MovieTitle,MovieYear,StarName)
MovieStar(Name,address,gender,birthdate)
MovieExec(name,address,Cert#,netWorth)
Studio (Name,address,presC#)

BasicsBasics
Operations of traditional relational algebra fall into
four broad classes:
1.Set operations
2.Operations that remove parts of a relation
3.Operations that combine tuples of two relations
4.Renaming

Set OperationsSet Operations
Union (binary, commutative, associative)
• R ∪ S
Intersection (binary, commutative, associative)
• R ∩ S
Set Difference (binary)
• R - S
o Set of elements in R but not in S
o R-S ≠ S-R !!
• R(A1,A2,…,An), S(B1,B2,…,Bn) must be union compatible
o R and S are of the same degree
o for each i, dom(Ai) = dom(Bi)
o Columns of R and S must be ordered so that order of attributes is
same for both relations

ExampleExample
name address gender birthdate
Carrie Fisher
Mark Hamil
123 Maple St., Hollywood
456 Oak Rd., Brentwood
F
M
9/9/99
8/8/88
R
name address gender birthdate
Carrie Fisher
Harrison Ford
123 Maple St., Hollywood
789 Palm Dr., Beverly Hills
F
M
9/9/99
7/7/77
S

ProjectProject
• PROJECT can produce many tuples with same
value
o Relational algebra semantics says remove duplicates
o SQL does not -- one difference between formal and actual query
languages

Relational Operator:Relational Operator:
SelectSelect
Select or Restrict (unary, commutative)
• σ<predicate> (R)
• <predicate> is a conditional expression of the type that
we are familiar with from conventional programming
languages
o <attribute> <op> <attribute>
o <attribute> <op> <constant>
o attribute in R
o op ∈ {=,≠,<,>,≤, …, AND, OR}
• Ex: σlength≥100 (Movie) vertical restriction”

PictoriallyPictorially
A1 A2 A3 … An
...
i
A1 A2 A3 … An
...
j, i ≥ j
σ
title year length filmType
Star Wars
Mighty
Ducks
Wayne’s
World
1977
1991
1992
124
104
95
color
color
color
Movie
result set

Cartesian ProductCartesian Product
Cartesian Product (binary, commutative, associative)
• R x S
• Sets of all pairs that can be formed by choosing the first
element of the pair to be any element of R, the second any
element of S
• Relation schema is union of schemas for R and S
• Resulting schema may be ambiguous
o Use R.A or S.A to disambiguate an attribute that occurs in
both schemas

ExampleExample
A B
1 2
3 4
B C
2 5
4 7
D
6
8
9 10 11
x
A R.BS.B C D
R S
1 2 2 5 6
1 2 4 7 8
1 2 9 10 11
3 4
3 4
3 4
2 5 6
4 7 8
9 10 11

Join OperationsJoin Operations
Natural Join (binary)
• R join S
• Match only those tuples from R and S that agree in
whatever attributes are common to the schemas of R and
S
o If r and s from r(R) and s(S) are successfully paired,
result is called a joined tuple
• This join operation is the same we used in earlier section to
recombine relations that had been projected onto two
subsets of their attributes (e.g., as a result of a BCNF
decomposition)

ExampleExample
• Resulting schema has attributes from R, either R or S (i.e.,
joining attribute(s)), and
• Tuples that fail to pair with any tuple of the other relation
are called dangling tuples
A B
1 2
3 4
B C
2 5
4 7
D
6
8
9 10 11
join
A B C D
R S
1 2 5 6
3 4 7 8

Join OperationsJoin Operations
Theta Join (binary)
• R joinC S, where C is an arbitrary join condition
• Step 1: take the product of R and S
• Step 2: Select from the product only those tuples
that satisfy condition C
• As with the product operation, the schema for
the result is the union of the schemas of R and S

ExampleExample
B C
2 3
2 3
D
4
5
7 8 10
joinA<D AND U.B≠V.B
A B
1 2
6 7
C
3
8
9 7 8
U V
V.BV.C DA U.BU.C
1 2 3 7 8 10

ThankThank You !!!You !!!
For More Information click below link:
Follow Us on:
http://vibranttechnologies.co.in/sql-classes-in-
mumbai.html

SQL- Introduction to SQL Set Operations

More Related Content

Viewers also liked

Similar to SQL- Introduction to SQL Set Operations

More from Vibrant Technologies & Computers

Recently uploaded

SQL- Introduction to SQL Set Operations