SQL

1
SQL
2
SELECT-FROM-WHERE

• SELECT
• FROM Student
• WHERE sNameGreg AND address320 FL

Student
sNumber sName address professor
1 Dave 320FL MM
2 Greg 320FL MM
3 Matt 320FL ER
sNumber sName address professor
2 Greg 320FL MM
? (sNameGreg AND address320 FL) (Student)
3
Project

• SELECT sNumber, sName
• FROM Student

Student
sNumber sName address professor
1 Dave 320FL MM
2 Greg 320FL MM
3 Matt 320FL ER
sNumber sName
1 Dave
2 Greg
3 Matt
? (sNumber, sName) (Student)
4
Extended Projection

• SELECT sNumber sName AS info
• FROM Student
• WHERE address320 FL

Student
info
1Dave
2Greg
3Matt
sNumber sName address professor
1 Dave 320FL MM
2 Greg 320FL MM
3 Matt 320FL ER
? (sNumbersName?info) (? (address320 FL)
(Student))
5
SQL and Relational Algebra

• In short, ? L (? C (R)) becomes
• SELECT L
• FROM R
• WHERE C

6
Renaming

• SELECT s1.sNumber AS num
• FROM Student S1
• WHERE s1.sNumber gt 1

Student
num
1
2
3
sNumber sName address professor
1 Dave 320FL MM
2 Greg 320FL MM
3 Matt 320FL ER
? (s1.sNumber?num) (? (s1.sNumber gt 1) (? S1
(Student)))
7
String operators

• Comparison Operators based on lexicographic
  ordering , lt, gt, ltgt, gt, lt
• Concatenation operator
• represented in strings with two consecutive
• Pattern match s LIKE p
• p pattern
• any sequence of 0 or more characters
• - matches 1 character
• Patterns can explicitly declare escape characters
  as s LIKE xam ESCAPE x

8
Comparison with NULL values

• Arithmetic operations on NULL return NULL.
• Comparison operators on NULL return UNKNOWN.
• We can explicitly check whether a value is null
  or not, by IS NULL, IS NOT NULL.

9
Truth table with UNKNOWN
UNKNOWN AND TRUE UNKNOWN UNKNOWN OR TRUE
TRUE UNKNOWN AND FALSE FALSEUNKNOWN OR FALSE
UNKNOWN UNKNOWN AND UNKNOWN UNKNOWN UNKNOWN OR
UNKNOWN UNKNOWN NOT UNKNOWN UNKNOWN A WHERE
clause is satisfied only when it evaluates to
TRUE.
10
UNION, INTERSECT, EXCEPT
(SELECT sName FROM Student) EXCEPT (SELECT sName
FROM Student WHERE address320 FL)

• UNION, INTERSECT, EXCEPT have set semantics.
• For bag semantics, use UNION ALL, INTERSECT ALL,
  EXCEPT ALL

11
EXCEPT - Example
(SELECT sName FROM Student) EXCEPT (SELECT sName
FROM Student WHERE address320 FL)
Student
sNumber sName address professor
1 Dave 320FL MM
2 Greg 320FL MM
3 Matt 320FL ER
4 Matt 300FL ER
sNumber sName address professor
12
Joins

• SELECT sName
• FROM Student, Professor
• WHERE pNameMM AND pNumberprofessor

?(sName)(Student ?(pNameMM and
pNumberprofessor) Professor)
13
Joins - example
Student
Professor
sNumber sName address professor
1 Dave 320FL 1
2 Greg 320FL 1
3 Matt 320FL 2
pNumber pName address
1 MM 141FL
2 ER 201FL

• SELECT sName
• FROM Student, Professor
• WHERE pNameMM
• AND pNumberprofessor

sName
Dave
Greg
14
Cross Product (Cartesian Product)

• SELECT
• FROM Student CROSS JOIN Professor

Student X Professor
can also be written as SELECT FROM Student,
Professor
15
Cross Product - Example
Student
Professor
sNumber sName address professor
1 Dave 320FL 1
2 Greg 320FL 1
3 Matt 320FL 2
pNumber pName address
1 MM 141FL
2 ER 201FL
sNumber sName address professor pNumber pName address
1 Dave 320FL 1 1 MM 141FL
1 Dave 320FL 1 2 ER 201FL
2 Greg 320FL 1 1 MM 141FL
2 Greg 320FL 1 2 ER 201FL
3 Matt 320FL 2 1 MM 141FL
3 Matt 320FL 2 2 ER 201FL
16
Theta Join

• SELECT
• FROM Student JOIN Professor
• ON professorpNumber

Student ?(professorpNumber) Professor
SELECT FROM Student, Professor WHERE
professorpNumber
17
Theta Join Example
sNumber sName address professor pNumber pName address
1 Dave 320FL 1 1 MM 141FL
2 Greg 320FL 1 1 MM 141FL
3 Matt 320FL 2 2 ER 201FL
18
Natural Join

• SELECT
• FROM Student NATURAL JOIN Professor
• (Note This requires the columns on which the
  join should be done should have the same names
  for Student and Professor).

Student ? Professor
19
Natural Join - Example
Student
Professor
sNumber sName address pNumber
1 Dave 320FL 1
2 Greg 320FL 1
3 Matt 320FL 2
pNumber pName address
1 MM 141FL
2 ER 201FL
sNumber sName address pNumber pName address
1 Dave 320FL 1 MM 141FL
2 Greg 320FL 1 MM 141FL
3 Matt 320FL 2 ER 201FL
20
Outer Joins

• SELECT FROM
• Student NATURAL FULL OUTER JOIN Professor

Student ?o Professor
SELECT FROM Student NATURAL LEFT OUTER JOIN
Professor
Student ?oL Professor
21
Outer Joins

• SELECT FROM
• Student NATURAL RIGHT OUTER JOIN Professor

Student ?oR Professor
22
Outer Joins - Example
Student
Professor
sNumber sName address pNumber
1 Dave 320FL 1
2 Greg 320FL 1
3 Matt 320FL 2
4 Ben 320FL 4
pNumber pName address
1 MM 141FL
2 ER 201FL
3 MW 168FL
sNumber sName address pNumber pName address
1 Dave 320FL 1 MM 141FL
2 Greg 320FL 1 MM 141FL
3 Matt 320FL 2 ER 201FL
4 Ben 320FL 4 Null Null
Null Null Null 3 MW 168FL
23
Sorting ORDER BY clause

• SELECT
• FROM Student
• WHERE sNumber gt 1
• ORDER BY sNumber, sName

? (sNumber, sName) (? (sNumber gt 1) (Student))
24
Subqueries

• SELECT FROM Student
• WHERE professor
• (SELECT pName
• FROM Professor
• WHERE pNumber1)

Note the inner subquery returns a relation, but
SQL runtime ensures that the subquery returns a
relation with one column and with one row,
otherwise it is a run-time error.
25
Subqueries - Example
Student
SELECT FROM Student WHERE professor (SELECT
pName FROM Professor WHERE pNumber1)
sNumber sName address professor
1 Dave 320FL MM
2 Greg 320FL MM
3 Matt 320FL ER
Professor
pNumber pName address
1 MM 141FL
2 ER 201FL
sNumber sName address professor
1 Dave 320FL MM
2 Greg 320FL MM
26
Subqueries

• We can use IN, EXISTS (also NOT IN, NOT EXISTS)
• ALL, ANY can be used with comparisons

SELECT FROM Student WHERE (sNumber, professor)
IN (SELECT pNumber, pName FROM Professor)
27
Subqueries - Example
Student
Professor
sNumber sName address professor
1 Dave 320FL MM
2 Greg 320FL MM
3 Matt 320FL ER
pNumber pName address
1 MM 141FL
2 ER 201FL
SELECT FROM Student WHERE (sNumber, professor)
IN (SELECT pNumber, pName FROM Professor)
sNumber sName address professor
1 Dave 320FL MM
28
Subqueries EXISTS
Professor

• SELECT FROM Student
• WHERE EXISTS
• (SELECT pName FROM Professor
• WHERE Student.professorpName)

pNumber pName address
1 MM 141FL
2 ER 201FL
Student
sNumber sName address professor
1 Dave 320FL MM
2 Greg 320FL MM
3 Matt 320FL ER
sNumber sName address professor
1 Dave 320FL MM
2 Greg 320FL MM
3 Matt 320FL ER
29
Subqueries with negation

• SELECT FROM Student
• WHERE (sNumber, professor) NOT IN
• (SELECT pNumber, pName
• FROM Professor)

Professor
pNumber pName address
1 MM 141FL
2 ER 201FL
Student
sNumber sName address professor
1 Dave 320FL MM
2 Greg 320FL MM
3 Matt 320FL ER
sNumber sName address professor
2 Greg 320FL MM
3 Matt 320FL ER
30
Subqueries with negation
Student
Professor
sNumber sName address professor
1 Dave 320FL MM
2 Greg 320FL MM
3 Matt 320FL ER
pNumber pName address
1 MM 141FL
2 ER 201FL
SELECT FROM Student WHERE NOT EXISTS (SELECT
pName FROM Professor WHERE Student.professorpN
ame)
sNumber sName address professor
31
Subqueries ALL, ANY

• SELECT FROM Student
• WHERE sNumber gt ALL
• (SELECT pNumber FROM Professor)

SELECT FROM Student WHERE sNumber
ANY (SELECT pNumber FROM Professor)
32
Subqueries ALL - Example
Student
Professor
sNumber sName address professor
1 Dave 320FL MM
2 Greg 320FL MM
3 Matt 320FL ER
pNumber pName address
1 MM 141FL
2 ER 201FL

• SELECT FROM Student
• WHERE sNumber gt ALL
• (SELECT pNumber
• FROM Professor)

sNumber sName address professor
3 Matt 320FL ER
33
Subqueries ANY - Example
Student
Professor
sNumber sName address professor
1 Dave 320FL MM
2 Greg 320FL MM
3 Matt 320FL ER
pNumber pName address
1 MM 141FL
2 ER 201FL

• SELECT FROM Student
• WHERE sNumber ANY
• (SELECT pNumber
• FROM Professor)

sNumber sName address professor
1 Dave 320FL MM
2 Greg 320FL MM
34
Subqueries NOT ALL - Example
Student
Professor
sNumber sName address professor
1 Dave 320FL MM
2 Greg 320FL MM
3 Matt 320FL ER
pNumber pName address
1 MM 141FL
2 ER 201FL

• SELECT FROM Student
• WHERE NOT sNumber gt ALL
• (SELECT pNumber
• FROM Professor)

sNumber sName address professor
1 Dave 320FL MM
2 Greg 320FL MM
35
Subqueries NOT ANY - Example
Student
Professor
sNumber sName address professor
1 Dave 320FL MM
2 Greg 320FL MM
3 Matt 320FL ER
pNumber pName address
1 MM 141FL
2 ER 201FL

• SELECT FROM Student
• WHERE NOT sNumber ANY
• (SELECT pNumber
• FROM Professor)

sNumber sName address professor
3 Matt 320FL ER
36
Subqueries Tip
37
Subqueries in FROM clause
Professor

• SELECT sName, pName
• FROM Student,
• (SELECT FROM Professor
• WHERE pNumber1)
• WHERE professorpName

pNumber pName address
1 MM 141FL
2 ER 201FL
Student
sNumber sName address professor
1 Dave 320FL MM
2 Greg 320FL MM
3 Matt 320FL ER
sName pName
Dave MM
Greg MM
38
Duplicate Elimination
SELECT DISTINCT address FROM Student WHERE
sNumber gt 1

• SELECT DISTINCT
• FROM Student

• (Student)

• (? (address) (? (sNumber gt 1) (Student)))

Student
sNumber sName address professor
1 Dave 320FL MM
2 Greg 320FL MM
3 Matt 320FL ER
address
320FL
39
Aggregation

• SELECT COUNT () FROM Student
• SELECT COUNT (sNumber) FROM Student
• SELECT MIN (sNumber) FROM Student
• SELECT MAX (sNumber) FROM Student
• SELECT SUM (sNumber) FROM Student
• SELECT AVG (sNumber) FROM Student

We can have distinct such as SELECT COUNT
(DISTINCT sNumber) FROM Student
40
Grouping

• SELECT COUNT (sName)
• FROM Student
• GROUP BY address

• (COUNT (sName)) (? (address, COUNT (sName))
  (Student))

Student
sNumber sName address professor
1 Dave 320FL MM
2 Greg 320FL MM
3 Matt 320FL ER
COUNT (sName)
3
41
Grouping
SELECT address, COUNT (sNumber) FROM
Student WHERE sNumber gt 1 GROUP BY address HAVING
COUNT (sNumber) gt 1
Student
sNumber sName address professor
1 Dave 320FL MM
2 Greg 320FL MM
3 Matt 320FL ER
4 Ben 300FL ER
address COUNT (sNumber)
320FL 2
42
Aggregation and NULLs

• NULLs are ignored in any aggregation except
  COUNT ()
• However if the set of attributes to be grouped on
  has null values, then grouping is done on the
  null values as well.

43
SQL Queries - Summary

• SELECT DISTINCT a1, a2, , an
• FROM R1, R2, , Rm
• WHERE C1
• GROUP BY g1, g2, , gl HAVING C2
• ORDER BY o1, o2, , oj