SQL Murali Mani SELECT-FROM-WHERE SELECT * FROM Student WHERE - - PDF document

▶
sql
SMART_READER_LITE
LIVE PREVIEW

SQL Murali Mani SELECT-FROM-WHERE SELECT * FROM Student WHERE - - PDF document

Aug 30, 2023 340 likes •578 views

SQL Murali Mani SELECT-FROM-WHERE SELECT * FROM Student WHERE sName=Greg AND address=320 FL Student sNumber sName address professor sNumber sName address professor 1 Dave 320FL MM 2 Greg 320FL MM 2 Greg 320FL

slide-1
SLIDE 1

1

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)

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

Student

MM 320FL Greg 2 professor address sName sNumber

slide-2
SLIDE 2

2

Murali Mani

Project

SELECT sNumber, sName FROM Student π (sNumber, sName) (Student)

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

Student

Matt 3 Greg 2 Dave 1 sName sNumber

Murali Mani

Extended Projection

SELECT sNumber || sName AS info FROM Student WHERE address=“320 FL” π (sNumber||sName→info) (σ (address=“320 FL”) (Student))

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

Student

3Matt 2Greg 1Dave info

slide-3
SLIDE 3

3

Murali Mani

SQL and Relational Algebra

In short, π L (σ C (R)) becomes SELECT L FROM R WHERE C

Murali Mani

Renaming

SELECT s1.sNumber AS num FROM Student S1 WHERE s1.sNumber >= 1; π (s1.sNumber→num) (σ (s1.sNumber >= 1) (ρ S1 (Student)))

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

Student

3 2 1 num

slide-4
SLIDE 4

4

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.

slide-5
SLIDE 5

5

Murali Mani

Truth table with UNKNOWN

UNKNOWN AND TRUE = UNKNOWN UNKNOWN OR TRUE = TRUE UNKNOWN AND FALSE = FALSE UNKNOWN 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.

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’)

slide-6
SLIDE 6

6

Murali Mani

EXCEPT - Example

ER 320FL Matt 3 ER 300FL Matt 4 MM 320FL Greg 2 MM 320FL Dave 1 professor address sName sNumber

Student (SELECT sName FROM Student) EXCEPT (SELECT sName FROM Student WHERE address=‘320 FL’)

professor address sName sNumber

Murali Mani

Joins

SELECT sName FROM Student, Professor WHERE pName=‘MM’ AND pNumber=professor;

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

slide-7
SLIDE 7

7

Murali Mani

Joins - example

2 320FL Matt 3 1 320FL Greg 2 1 320FL Dave 1 professor address sName sNumber

Student

201FL ER 2 141FL MM 1 address pName pNumber

Professor

Greg Dave sName

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

slide-8
SLIDE 8

8

Murali Mani

Cross Product - Example

2 320FL Matt 3 1 320FL Greg 2 1 320FL Dave 1 professor address sName sNumber

Student

201FL ER 2 141FL MM 1 address pName pNumber

Professor

201FL ER 2 1 320FL Dave 1 141FL MM 1 1 320FL Greg 2 201FL ER 2 1 320FL Greg 2 2 2 1 professor 2 1 1 pNumber ER MM MM pName 201FL 320FL Matt 3 141FL 320FL Matt 3 141FL 320FL Dave 1 address address sName sNumber

Murali Mani

Theta Join

SELECT * FROM Student JOIN Professor ON professor=pNumber;

Student ⋈(professor=pNumber) Professor

SELECT * FROM Student, Professor WHERE professor=pNumber;

slide-9
SLIDE 9

9

Murali Mani

Theta Join Example

141FL MM 1 1 320FL Greg 2 2 1 professor 2 1 pNumber ER MM pName 201FL 320FL Matt 3 141FL 320FL Dave 1 address address sName sNumber

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

slide-10
SLIDE 10

10

Murali Mani

Natural Join - Example

2 320FL Matt 3 1 320FL Greg 2 1 320FL Dave 1 pNumber address sName sNumber

Student

201FL ER 2 141FL MM 1 address pName pNumber

Professor

141FL MM 1 320FL Greg 2 2 1 pNumber ER MM pName 201FL 320FL Matt 3 141FL 320FL Dave 1 address address sName sNumber

Murali Mani

Outer Joins

SELECT * FROM Student NATURAL FULL OUTER JOIN Professor

Student ⋈o Professor

SELECT * FROM Student NATURAL LEFT OUTER JOIN Professor

Student ⋈o

L Professor

slide-11
SLIDE 11

11

Murali Mani

Outer Joins

SELECT * FROM Student NATURAL RIGHT OUTER JOIN Professor

Student ⋈o

R Professor

Murali Mani

Outer Joins - Example

2 320FL Matt 3 4 320FL Ben 4 1 320FL Greg 2 1 320FL Dave 1 pNumber address sName sNumber

Student

201FL ER 2 168FL MW 3 141FL MM 1 address pName pNumber

Professor

201FL ER 2 320FL Matt 3 Null Null 4 320FL Ben 4 141FL MM 1 320FL Greg 2 3 1 pNumber MW MM pName 168FL Null Null Null 141FL 320FL Dave 1 address address sName sNumber

slide-12
SLIDE 12

12

Murali Mani

Sorting: ORDER BY clause

SELECT * FROM Student WHERE sNumber >= 1 ORDER BY sNumber, sName τ (sNumber, sName) (σ (sNumber >= 1) (Student))

Murali Mani

Subqueries

SELECT * 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,

  • therwise it is a run-time error.
slide-13
SLIDE 13

13

Murali Mani

Subqueries - Example

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

Student

201FL ER 2 141FL MM 1 address pName pNumber

Professor

MM 320FL Greg 2 MM 320FL Dave 1 professor address sName sNumber

SELECT * FROM Student WHERE professor = (SELECT pName FROM Professor WHERE 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)

slide-14
SLIDE 14

14

Murali Mani

Subqueries - Example

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

Student

201FL ER 2 141FL MM 1 address pName pNumber

Professor

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

MM 320FL Dave 1 professor address sName sNumber

Murali Mani

Subqueries: EXISTS

SELECT * FROM Student WHERE EXISTS (SELECT pName FROM Professor WHERE Student.professor=pName)

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

Student

201FL ER 2 141FL MM 1 address pName pNumber

Professor

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

slide-15
SLIDE 15

15

Murali Mani

Subqueries with negation

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

201FL ER 2 141FL MM 1 address pName pNumber

Professor

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

Student

ER 320FL Matt 3 MM 320FL Greg 2 professor address sName sNumber

Murali Mani

Subqueries with negation

SELECT * FROM Student WHERE NOT EXISTS (SELECT pName FROM Professor WHERE Student.professor=pName)

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

Student

201FL ER 2 141FL MM 1 address pName pNumber

Professor

professor address sName sNumber

slide-16
SLIDE 16

16

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)

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

Student

201FL ER 2 141FL MM 1 address pName pNumber

Professor

ER 320FL Matt 3 professor address sName sNumber

slide-17
SLIDE 17

17

Murali Mani

Subqueries: ANY - Example

SELECT * FROM Student WHERE sNumber = ANY (SELECT pNumber FROM Professor)

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

Student

201FL ER 2 141FL MM 1 address pName pNumber

Professor

MM 320FL Greg 2 MM 320FL Dave 1 professor address sName sNumber

Murali Mani

Subqueries: NOT ALL - Example

SELECT * FROM Student WHERE NOT sNumber > ALL (SELECT pNumber FROM Professor)

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

Student

201FL ER 2 141FL MM 1 address pName pNumber

Professor

MM 320FL Greg 2 MM 320FL Dave 1 professor address sName sNumber

slide-18
SLIDE 18

18

Murali Mani

Subqueries: NOT ANY - Example

SELECT * FROM Student WHERE NOT sNumber = ANY (SELECT pNumber FROM Professor)

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

Student

201FL ER 2 141FL MM 1 address pName pNumber

Professor

ER 320FL Matt 3 professor address sName sNumber

Murali Mani

Subqueries: Tip

slide-19
SLIDE 19

19

Murali Mani

Subqueries in FROM clause

SELECT sName, pName FROM Student, (SELECT * FROM Professor WHERE pNumber=1) WHERE professor=pName;

201FL ER 2 141FL MM 1 address pName pNumber

Professor

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

Student

MM Greg MM Dave pName sName

Murali Mani

Duplicate Elimination

SELECT DISTINCT * FROM Student; δ (Student)

SELECT DISTINCT address FROM Student WHERE sNumber >= 1;

δ (π (address) (σ (sNumber >= 1) (Student)))

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

Student

320FL address

slide-20
SLIDE 20

20

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

Grouping

SELECT COUNT (sName) FROM Student GROUP BY address;

π(COUNT (sName)) (γ (address, COUNT (sName)) (Student))

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

Student

3 COUNT (sName)

slide-21
SLIDE 21

21

Murali Mani

Grouping

SELECT address, COUNT (sNumber) FROM Student WHERE sNumber > 1 GROUP BY address HAVING COUNT (sNumber) > 1;

ER 320FL Matt 3 ER 300FL Ben 4 MM 320FL Greg 2 MM 320FL Dave 1 professor address sName sNumber

Student

2 320FL COUNT (sNumber) address

Murali Mani

Aggregation and NULLs

NULLs are ignored in any aggregation;

except COUNT (*)

However if the set of attributes to be grouped

  • n has null values, then grouping is done on

the null values as well.

slide-22
SLIDE 22

22

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]