Overview
Structured query language (SQL) is a user-friendly language
for specifying relational algebra queries. It is supported by all
the major database systems.
SQL provides:
Data Manipulation Language (DML)
– retrieve, insert and modify database contents
Data Definition Language (DDL)
– add and delete database objects
Data Control Language (DCL)
– configure security access
In this lecture, we will learn how to rewrite algebra operators
in SQL (DML).
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 *
from T1,T2
corresponds to T1 × T2
select *
from T1, …,Tm
corresponds to T1 × …× Tm
SQL 1: Basic Statements
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
PROF
pid cid year
p1 c1 2011
p2 c2 2012
p1 c2 2012
TEACH
select *
from PROF, TEACH
PROF× TEACH
SQL 1: Basic Statements
Putting Multiple Operators Together
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
PROF
pid cid year
p1 c1 2011
p2 c2 2012
p1 c2 2012
TEACH
select distinct dept
from PROF, TEACH
where PROF.pid = TEACH.pid
Πdept(σPROF.pid = TEACH.pid(PROF× TEACH))
SQL 1: Basic Statements
Rename ρ
select …
from T as S
where …
corresponds to
…ρS(T )…
SQL 1: Basic Statements
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
PROF
pid cid year
p1 c1 2011
p2 c2 2012
p1 c2 2012
TEACH
select distinct dept
from PROF as A, TEACH as B
where A.pid = B.pid
Πdept(σA.pid = B.pid(ρ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
select distinct PROF.pid, name, dept, rank, sal, cid, year
from PROF, TEACH
where PROF.pid = TEACH.pid
ΠPROF.pid, name, dept, rank, sal, cid, year(σPROF.pid=TEACH.pid(PROF×
TEACH))
=
PROF ./ TEACH
SQL 1 : Basic Statements
Division
T1
pid cid
p1 c1
p1 c2
p1 c3
p2 c2
p2 c3
p3 c1
p4 c1
p4 c2
p4 c3
T2
cid
c1
c2
c3
(select pid from T1)
minus
select pid from (
(select * from (select pid from T1), T2)
minus
(select * from T1))
Note
Notice how an SQL
statement can be nested
in a from clause.
ΠS1−S2(T1)− ΠS1−S2
(
ΠS1−S2(T1)× T2 − T1
)
= T1 ÷ T2
SQL 1: Basic Statements
Funny stuffs
SELECT * FROM politicians WHERE clue > 0;
SQL> select intelligence_level from developer;
select intelligence_level from developer
*
ERROR at line 1:
ORA-00904: “INTELLIGENCE_LEVEL”: invalid identifier
http://www.orafaq.com/wiki/Fun_stuff
SQL 1 : Basic Statements
http://www.orafaq.com/wiki/Fun_stuff