SQL 1: Basic Statements
Yufei Tao
Department of Computer Science and Engineering
Chinese University of Hong Kong
SQL 1: Basic Statements
Overview
Structured query language (SQL) is a user-friendly language for
specifying relational algebra queries. It is supported by all the major
database systems. In this lecture, we will learn how to rewrite algebra
operators in SQL.
SQL 1: Basic Statements
Syntax of an SQL Statement
select distinct A1 , A2 , ..., An
from T1 , ..., Tm
where P
where T1 , ..., Tm are tables, A1 , ..., An are attributes, and P is a
predicate. The statement returns a table, and corresponds to the
following relational algebra query:
ΠA1 ,...,An (σP (T1 × ... × Tm ))
SQL 1: Basic Statements
Selection σ
select *
from T
where P
corresponds to
σP (T )
SQL 1: Basic Statements
PROF
pid name dept rank sal
p1 Adam CS asst 6000
p2 Bob EE asso 8000
p3 Calvin CS full 10000
p4 Dorothy EE asst 5000
p5 Emily EE asso 8500
p6 Frank CS full 9000
select *
from PROF
where rank = ‘asst’
σrank = “asst” (PROF)
SQL 1: Basic Statements
PROF
pid name dept rank sal
p1 Adam CS asst 6000
p2 Bob EE asso 8000
p3 Calvin CS full 10000
p4 Dorothy EE asst 5000
p5 Emily EE asso 8500
p6 Frank CS full 9000
select *
from PROF
where not(rank = ‘asst’ and dept = ‘EE’)
σ¬(rank = “asst” ∧ dept=“EE”) (PROF)
SQL 1: Basic Statements
Selection Predicate
select *
from T
where P
In P, you can specify the standard comparisons and logic operators:
=, <>, <, <=, >, >=
Connect multiple comparisons with: AND, OR, NOT.
SQL 1: Basic Statements
Projection Π
select distinct A1 , ..., An
from T
corresponds to
ΠA1 ,...,An (T )
SQL 1: Basic Statements
PROF
pid name dept rank sal
p1 Adam CS asst 6000
p2 Bob EE asso 8000
p3 Calvin CS full 10000
p4 Dorothy EE asst 5000
p5 Emily EE asso 8500
p6 Frank CS full 9000
select distinct dept, rank
from PROF
Πdept, rank (PROF)
Note
The keyword distinct removes all duplicate rows in the output. Omitting
the keyword keeps all duplicates. See the next slide.
SQL 1: Basic Statements
PROF
pid name dept rank sal
p1 Adam CS asst 6000
p2 Bob EE asso 8000
p3 Calvin CS full 10000
p4 Dorothy EE asst 5000
p5 Emily EE asso 8500
p6 Frank CS full 9000
“select dept, rank from PROF” returns:
dept rank
CS asst
EE asso
CS full
EE asst
EE asso
CS full
This duplicate-retaining feature is useful for aggregate queries as we will
discuss later in the course.
SQL 1: Basic Statements
Cartesian Product ×
select *
corresponds to T1 × T2
from T1 , T2
select *
corresponds to T1 × ... × Tm
from T1 , ..., Tm
SQL 1: Basic Statements
PROF
TEACH
pid name dept rank sal
pid cid year
p1 Adam CS asst 6000
p1 c1 2011
p2 Bob EE asso 8000
p2 c2 2012
p3 Calvin CS full 10000
p1 c2 2012
p4 Dorothy EE asst 5000
p5 Emily EE asso 8500
select *
from PROF, TEACH
PROF × TEACH
SQL 1: Basic Statements
Putting Multiple Operators Together
PROF
TEACH
pid name dept rank sal
pid cid year
p1 Adam CS asst 6000
p1 c1 2011
p2 Bob EE asso 8000
p2 c2 2012
p3 Calvin CS full 10000
p1 c2 2012
p4 Dorothy EE asst 5000
p5 Emily EE asso 8500
select distinct dept
from PROF, TEACH
where [Link] = [Link]
Πdept (σ[Link] = [Link] (PROF × TEACH))
SQL 1: Basic Statements
Rename ρ
select ...
from T as S
where ...
corresponds to
...ρS (T )...
SQL 1: Basic Statements
PROF
TEACH
pid name dept rank sal
pid cid year
p1 Adam CS asst 6000
p1 c1 2011
p2 Bob EE asso 8000
p2 c2 2012
p3 Calvin CS full 10000
p1 c2 2012
p4 Dorothy EE asst 5000
p5 Emily EE asso 8500
select distinct dept
from PROF as A, TEACH as B
where [Link] = [Link]
Πdept (σ[Link] = [Link] (ρA (PROF) × ρB (TEACH)))
SQL 1: Basic Statements
Set Difference −
([SQL statement 1])
minus
([SQL statement 2])
corresponds to
T1 − T2
where T1 (T2 ) is the table returned by SQL statement 1 (2).
Note
T1 and T2 need to have the same schema.
Duplicates in T1 and T2 will first be removed before performing the
set difference.
In some systems (e.g., SQL server from Microsoft), the set
difference operator is named “except”, instead of “minus”.
SQL 1: Basic Statements
PROF
pid name dept rank sal
p1 Adam CS asst 6000
p2 Bob EE asso 8000
p3 Calvin CS full 10000
p4 Dorothy EE asst 5000
p5 Emily EE asso 8500
p6 Frank CS full 9000
(select rank from PROF)
minus
(select rank from PROF where dept = ‘CS’)
Πrank (PROF) − Πrank (σdept = “CS” (PROF))
SQL 1: Basic Statements
Set Union ∪
([SQL statement 1])
union
([SQL statement 2])
corresponds to
T1 ∪ T2
where T1 (T2 ) is the table returned by SQL statement 1 (2).
Note
T1 and T2 need to have the same schema.
Duplicates in T1 and T2 will first be removed before performing the
set union.
SQL 1: Basic Statements
PROF
pid name dept rank sal
p1 Adam CS asst 6000
p2 Bob EE asso 8000
p3 Calvin CS full 10000
p4 Dorothy EE asst 5000
p5 Emily EE asso 8500
p6 Frank CS full 9000
(select * from PROF where sal <= 6000)
union
(select * from PROF where sal >= 9000)
σsal ≤ 6000 (PROF) ∪ σsal ≥ 9000 (PROF)
SQL 1: Basic Statements
We have shown how to rewrite the 6 fundamental algebra operators in
SQL. How about the extended operators ←, ∩, ./ and ÷? As we will see
next, there is an explicit statement only for ∩. Nevertheless, as ∩ and ./
can be implemented using the 6 fundamental operators, they can also be
written in SQL using the statements introduced earlier. We will, however,
ignore ← from our discussion (this operator is the least useful one,
anyway).
SQL 1: Basic Statements
Set Intersection ∩
([SQL statement 1])
intersect
([SQL statement 2])
corresponds to
T1 ∩ T2
where T1 (T2 ) is the table returned by SQL statement 1 (2).
Note
T1 and T2 need to have the same schema.
Duplicates in T1 and T2 will first be removed before performing the
set union.
SQL 1: Basic Statements
PROF
pid name dept rank sal
p1 Adam CS asst 6000
p2 Bob EE asso 8000
p3 Calvin CS full 10000
p4 Dorothy EE asst 5000
p5 Emily EE asso 8500
p6 Frank CS full 9000
(select * from PROF where sal >= 6000)
intersect
(select * from PROF where dept = ‘CS’)
σsal ≥ 6000 (PROF) ∩ σdept = “CS” (PROF)
SQL 1: Basic Statements
Natural Join
PROF
TEACH
pid name dept rank sal
pid cid year
p1 Adam CS asst 6000
p1 c1 2011
p2 Bob EE asso 8000
p2 c2 2012
p3 Calvin CS full 10000
p1 c2 2012
p4 Dorothy EE asst 5000
p5 Emily EE asso 8500
select distinct [Link], name, dept, rank, sal, cid, year
from PROF, TEACH
where [Link] = [Link]
Π[Link], name, dept, rank, sal, cid, year (σ[Link] = [Link] (PROF×
TEACH))
=
PROF ./ TEACH
SQL 1: Basic Statements
Division
T1 T2
pid cid cid
p1 c1 c1
p1 c2 c2
p1 c3 c3
p2 c2
p2 c3
p3 c1
p4 c1
p4 c2
p4 c3
(select pid from T1 )
minus Note
select pid from (
Notice how an SQL
(select * from (select pid from T1 ), T2 )
statement can be nested
minus
in a from clause.
(select * from T1 ))
ΠS1 −S2 (T1 ) − ΠS1 −S2 ΠS1 −S2 (T1 ) × T2 − T1 = T1 ÷ T2
SQL 1: Basic Statements