sql

Murali Mani

SQL

Murali Mani

SELECT-FROM-WHERE

SELECT *

FROM Student

WHERE sName=“Greg” AND address=“320 FL”

(sName=“Greg” AND address=“320 FL”) (Student)

sNumber sName address professor

1 Dave 320FL MM

2 Greg 320FL MM

3 Matt 320FL ER

Student

sNumber sName address professor

2 Greg 320FL MM

Murali Mani

Project

SELECT sNumber, sName

FROM Student

(sNumber, sName) (Student)

sNumber sName address professor

1 Dave 320FL MM

2 Greg 320FL MM

3 Matt 320FL ER

Student

sNumber sName

1 Dave

2 Greg

3 Matt

Murali Mani

Extended Projection

SELECT sNumber || sName AS info

FROM Student

WHERE address=“320 FL”

(sNumber||sNameinfo) ( (address=“320 FL”) (Student))

sNumber sName address professor

1 Dave 320FL MM

2 Greg 320FL MM

3 Matt 320FL ER

Student

info

1Dave

2Greg

3Matt

Murali Mani

SQL and Relational Algebra

In short, L ( C (R)) becomes

SELECT LFROM RWHERE C

Murali Mani

RenamingSELECT s1.sNumber AS num

FROM Student S1

WHERE s1.sNumber >= 1;

(s1.sNumbernum) ( (s1.sNumber >= 1) ( S1 (Student)))

sNumber sName address professor

1 Dave 320FL MM

2 Greg 320FL MM

3 Matt 320FL ER

Student

num

1

2

3

Murali Mani

String operators Comparison Operators based on

lexicographic ordering: =, <, >, <>, >=, <= 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 ‘x%%am%’ ESCAPE ‘x’

Murali Mani

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.

Murali Mani

Truth table with UNKNOWN

UNKNOWN AND TRUE = UNKNOWNUNKNOWN OR TRUE = TRUEUNKNOWN AND FALSE = FALSEUNKNOWN OR FALSE = UNKNOWNUNKNOWN AND UNKNOWN = UNKNOWNUNKNOWN OR UNKNOWN = UNKNOWNNOT UNKNOWN = UNKNOWN

A WHERE clause is satisfied only when it evaluates to TRUE.

Murali Mani

UNION, INTERSECT, EXCEPT

UNION, INTERSECT, EXCEPT have set semantics.

For bag semantics, use UNION ALL, INTERSECT ALL, EXCEPT ALL

(SELECT sName FROM Student) EXCEPT(SELECT sName FROM Student

WHERE address=‘320 FL’)

Murali Mani

EXCEPT - Example

sNumber sName address professor

1 Dave 320FL MM

2 Greg 320FL MM

3 Matt 320FL ER

4 Matt 300FL ER

Student

(SELECT sName FROM Student) EXCEPT(SELECT sName FROM Student

WHERE address=‘320 FL’)

sNumber sName address professor

Murali Mani

Joins

SELECT sName

FROM Student, Professor

WHERE pName=‘MM’ AND pNumber=professor;

(sName)(Student ⋈(pName=‘MM’ and pNumber=professor) Professor)

Murali Mani

Joins - example

sNumber sName address professor

1 Dave 320FL 1

2 Greg 320FL 1

3 Matt 320FL 2

Student

pNumber pName address

1 MM 141FL

2 ER 201FL

Professor

sName

Dave

Greg

SELECT sName

FROM Student, Professor

WHERE pName=‘MM’

AND pNumber=professor;

Murali Mani

Cross Product (Cartesian Product)

SELECT *

FROM Student CROSS JOIN Professor;

can also be written as:

SELECT *

FROM Student, Professor

Student X Professor

Murali Mani

Cross Product - Example

sNumber sName address professor

1 Dave 320FL 1

2 Greg 320FL 1

3 Matt 320FL 2

Student

pNumber pName address

1 MM 141FL

2 ER 201FL

Professor

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

Murali Mani

Theta Join

SELECT *

FROM Student JOIN Professor

ON professor=pNumber;

Student ⋈(professor=pNumber) Professor

SELECT *FROM Student, ProfessorWHERE professor=pNumber;

Murali Mani

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

Murali Mani

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

Murali Mani

Natural Join - Example

sNumber sName address pNumber

1 Dave 320FL 1

2 Greg 320FL 1

3 Matt 320FL 2

Student

pNumber pName address

1 MM 141FL

2 ER 201FL

Professor

sNumber sName address pNumber pName address

1 Dave 320FL 1 MM 141FL

2 Greg 320FL 1 MM 141FL

3 Matt 320FL 2 ER 201FL

Murali Mani

Outer Joins

SELECT * FROM

Student NATURAL FULL OUTER JOIN Professor

Student ⋈o Professor

SELECT * FROM

Student NATURAL LEFT OUTER JOIN Professor

Student ⋈oL Professor

Murali Mani

Outer Joins

SELECT * FROM

Student NATURAL RIGHT OUTER JOIN Professor

Student ⋈oR Professor

Murali Mani

Outer Joins - Example

sNumber sName address pNumber

1 Dave 320FL 1

2 Greg 320FL 1

3 Matt 320FL 2

4 Ben 320FL 4

Student

pNumber pName address

1 MM 141FL

2 ER 201FL

3 MW 168FL

Professor

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

Murali Mani

Sorting: ORDER BY clause

SELECT *

FROM Student

WHERE sNumber >= 1

ORDER BY sNumber, sName

(sNumber, sName) ( (sNumber >= 1) (Student))

Murali Mani

SubqueriesSELECT * FROM Student

WHERE professor =

(SELECT pName

FROM Professor

WHERE pNumber=1)

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.

Murali Mani

Subqueries - Example

sNumber sName address professor

1 Dave 320FL MM

2 Greg 320FL MM

3 Matt 320FL ER

Student

pNumber pName address

1 MM 141FL

2 ER 201FL

Professor

sNumber sName address professor

1 Dave 320FL MM

2 Greg 320FL MM

SELECT * FROM StudentWHERE professor =

(SELECT pNameFROM ProfessorWHERE pNumber=1)

Murali Mani

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)

Murali Mani

Subqueries - Example

sNumber sName address professor

1 Dave 320FL MM

2 Greg 320FL MM

3 Matt 320FL ER

Student

pNumber pName address

1 MM 141FL

2 ER 201FL

Professor

SELECT * FROM Student

WHERE (sNumber, professor) IN

(SELECT pNumber, pName

FROM Professor)

sNumber sName address professor

1 Dave 320FL MM

Murali Mani

Subqueries: EXISTS

SELECT * FROM Student

WHERE EXISTS

(SELECT pName FROM Professor

WHERE Student.professor=pName)

sNumber sName address professor

1 Dave 320FL MM

2 Greg 320FL MM

3 Matt 320FL ER

Student

pNumber pName address

1 MM 141FL

2 ER 201FL

Professor

sNumber sName address professor

1 Dave 320FL MM

2 Greg 320FL MM

3 Matt 320FL ER

Murali Mani

Subqueries with negation

SELECT * FROM Student

WHERE (sNumber, professor) NOT IN

(SELECT pNumber, pName

FROM Professor)

pNumber pName address

1 MM 141FL

2 ER 201FL

Professor

sNumber sName address professor

1 Dave 320FL MM

2 Greg 320FL MM

3 Matt 320FL ER

Student

sNumber sName address professor

2 Greg 320FL MM

3 Matt 320FL ER

Murali Mani

Subqueries with negation

SELECT * FROM Student

WHERE NOT EXISTS

(SELECT pName FROM Professor

WHERE Student.professor=pName)

sNumber sName address professor

1 Dave 320FL MM

2 Greg 320FL MM

3 Matt 320FL ER

Student

pNumber pName address

1 MM 141FL

2 ER 201FL

Professor

sNumber sName address professor

Murali Mani

Subqueries: ALL, ANY

SELECT * FROM Student

WHERE sNumber > ALL

(SELECT pNumber FROM Professor)

SELECT * FROM Student

WHERE sNumber = ANY

(SELECT pNumber FROM Professor)

Murali Mani

Subqueries: ALL - Example

SELECT * FROM Student

WHERE sNumber > ALL

(SELECT pNumber

FROM Professor)

sNumber sName address professor

1 Dave 320FL MM

2 Greg 320FL MM

3 Matt 320FL ER

Student

pNumber pName address

1 MM 141FL

2 ER 201FL

Professor

sNumber sName address professor

3 Matt 320FL ER

Murali Mani

Subqueries: ANY - Example

SELECT * FROM Student

WHERE sNumber = ANY

(SELECT pNumber

FROM Professor)

sNumber sName address professor

1 Dave 320FL MM

2 Greg 320FL MM

3 Matt 320FL ER

Student

pNumber pName address

1 MM 141FL

2 ER 201FL

Professor

sNumber sName address professor

1 Dave 320FL MM

2 Greg 320FL MM

Murali Mani

Subqueries: NOT ALL - Example

SELECT * FROM Student

WHERE NOT sNumber > ALL

(SELECT pNumber

FROM Professor)

sNumber sName address professor

1 Dave 320FL MM

2 Greg 320FL MM

3 Matt 320FL ER

Student

pNumber pName address

1 MM 141FL

2 ER 201FL

Professor

sNumber sName address professor

1 Dave 320FL MM

2 Greg 320FL MM

Murali Mani

Subqueries: NOT ANY - Example

SELECT * FROM Student

WHERE NOT sNumber = ANY

(SELECT pNumber

FROM Professor)

sNumber sName address professor

1 Dave 320FL MM

2 Greg 320FL MM

3 Matt 320FL ER

Student

pNumber pName address

1 MM 141FL

2 ER 201FL

Professor

sNumber sName address professor

3 Matt 320FL ER

Murali Mani

Subqueries: Tip

Murali Mani

Subqueries in FROM clause

SELECT sName, pNameFROM Student,

(SELECT * FROM Professor WHERE pNumber=1)

WHERE professor=pName;

pNumber pName address

1 MM 141FL

2 ER 201FL

Professor

sNumber sName address professor

1 Dave 320FL MM

2 Greg 320FL MM

3 Matt 320FL ER

Student

sName pName

Dave MM

Greg MM

Murali Mani

Duplicate Elimination

SELECT DISTINCT *

FROM Student;

(Student)

SELECT DISTINCT address

FROM Student

WHERE sNumber >= 1;

( (address) ( (sNumber >= 1) (Student)))

sNumber sName address professor

1 Dave 320FL MM

2 Greg 320FL MM

3 Matt 320FL ER

Student

address

320FL

Murali Mani

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

Murali Mani

GroupingSELECT COUNT (sName) FROM Student GROUP BY address;

(COUNT (sName)) ( (address, COUNT (sName)) (Student))

sNumber sName address professor

1 Dave 320FL MM

2 Greg 320FL MM

3 Matt 320FL ER

Student

COUNT (sName)

3

Murali Mani

GroupingSELECT address, COUNT (sNumber)

FROM Student

WHERE sNumber > 1

GROUP BY address

HAVING COUNT (sNumber) > 1;

sNumber sName address professor

1 Dave 320FL MM

2 Greg 320FL MM

3 Matt 320FL ER

4 Ben 300FL ER

Student

address COUNT (sNumber)

320FL 2

Murali Mani

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.

Murali Mani

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]