sql
DESCRIPTION
SQL. SELECT-FROM-WHERE. SELECT * FROM Student WHERE sName=“Greg” AND address=“320 FL”. Student. (sName=“Greg” AND address=“320 FL”) (Student). Project. SELECT sNumber, sName FROM Student. Student. (sNumber, sName) (Student). Extended Projection. SELECT sNumber || sName AS info - PowerPoint PPT PresentationTRANSCRIPT
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]