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

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 MM 3 Matt 320FL ER σ (sName=“Greg” AND address=“320 FL”) (Student) Murali Mani 1

Project SELECT sNumber, sName FROM Student Student sNumber sName address professor sNumber sName 1 Dave 320FL MM 1 Dave 2 Greg 320FL MM 2 Greg 3 Matt 320FL ER 3 Matt π (sNumber, sName) (Student) Murali Mani Extended Projection SELECT sNumber || sName AS info FROM Student WHERE address=“320 FL” Student info sNumber sName address professor 1Dave 1 Dave 320FL MM 2Greg 2 Greg 320FL MM 3Matt 3 Matt 320FL ER π (sNumber||sName → info) ( σ (address=“320 FL”) (Student)) Murali Mani 2

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; Student num sNumber sName address professor 1 1 Dave 320FL MM 2 2 Greg 320FL MM 3 3 Matt 320FL ER π (s1.sNumber → num) ( σ (s1.sNumber >= 1) ( ρ S1 (Student))) Murali Mani 3

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 4

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 (SELECT sName FROM Student) EXCEPT (SELECT sName FROM Student WHERE address=‘320 FL’) � UNION, INTERSECT, EXCEPT have set semantics. � For bag semantics, use UNION ALL, INTERSECT ALL, EXCEPT ALL Murali Mani 5

EXCEPT - Example (SELECT sName FROM Student) EXCEPT (SELECT sName FROM Student WHERE address=‘320 FL’) Student sNumber sName address professor 1 Dave 320FL MM sNumber sName address professor 2 Greg 320FL MM 3 Matt 320FL ER 4 Matt 300FL ER Murali Mani Joins SELECT sName FROM Student, Professor WHERE pName=‘MM’ AND pNumber=professor; π (sName) (Student ⋈ (pName=‘MM’ and pNumber=professor) Professor) Murali Mani 6

Joins - example Student Professor sNumber sName address professor pNumber pName address 1 Dave 320FL 1 1 MM 141FL 2 Greg 320FL 1 2 ER 201FL 3 Matt 320FL 2 SELECT sName sName FROM Student, Professor Dave Greg WHERE pName=‘MM’ AND pNumber=professor; Murali Mani Cross Product (Cartesian Product) SELECT * FROM Student CROSS JOIN Professor; Student X Professor can also be written as: SELECT * FROM Student, Professor Murali Mani 7

Cross Product - Example Student Professor sNumber sName address professor pNumber pName address 1 Dave 320FL 1 1 MM 141FL 2 Greg 320FL 1 2 ER 201FL 3 Matt 320FL 2 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, Professor WHERE professor=pNumber; Murali Mani 8

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 9

Natural Join - Example Student Professor sNumber sName address pNumber pNumber pName address 1 Dave 320FL 1 1 MM 141FL 2 Greg 320FL 1 2 ER 201FL 3 Matt 320FL 2 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 ⋈ o L Professor Murali Mani 10

Outer Joins SELECT * FROM Student NATURAL RIGHT OUTER JOIN Professor Student ⋈ o R Professor Murali Mani Outer Joins - Example Student Professor sNumber sName address pNumber pNumber pName address 1 Dave 320FL 1 1 MM 141FL 2 Greg 320FL 1 2 ER 201FL 3 Matt 320FL 2 3 MW 168FL 4 Ben 320FL 4 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 11

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 , otherwise it is a run-time error . Murali Mani 12

Subqueries - Example Student SELECT * FROM Student sNumber sName address professor WHERE professor = 1 Dave 320FL MM (SELECT pName 2 Greg 320FL MM FROM Professor 3 Matt 320FL ER WHERE pNumber=1) Professor pNumber pName address 1 MM 141FL sNumber sName address professor 2 ER 201FL 1 Dave 320FL MM 2 Greg 320FL MM 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 13

Subqueries - Example Student Professor sNumber sName address professor pNumber pName address 1 Dave 320FL MM 1 MM 141FL 2 Greg 320FL MM 2 ER 201FL 3 Matt 320FL ER SELECT * FROM Student sNumber sName address professor WHERE (sNumber, professor) IN 1 Dave 320FL MM (SELECT pNumber, pName FROM Professor) Murali Mani Subqueries: EXISTS Professor SELECT * FROM Student WHERE EXISTS pNumber pName address (SELECT pName FROM Professor 1 MM 141FL WHERE Student.professor=pName) 2 ER 201FL Student sNumber sName address professor 1 Dave 320FL MM sNumber sName address professor 2 Greg 320FL MM 1 Dave 320FL MM 3 Matt 320FL ER 2 Greg 320FL MM 3 Matt 320FL ER Murali Mani 14

Subqueries with negation SELECT * FROM Student Professor WHERE (sNumber, professor) NOT IN pNumber pName address (SELECT pNumber, pName 1 MM 141FL FROM Professor) 2 ER 201FL Student sNumber sName address professor 1 Dave 320FL MM sNumber sName address professor 2 Greg 320FL MM 2 Greg 320FL MM 3 Matt 320FL ER 3 Matt 320FL ER Murali Mani Subqueries with negation Student Professor sNumber sName address professor pNumber pName address 1 Dave 320FL MM 1 MM 141FL 2 Greg 320FL MM 2 ER 201FL 3 Matt 320FL ER SELECT * FROM Student WHERE NOT EXISTS (SELECT pName FROM Professor WHERE Student.professor=pName) sNumber sName address professor Murali Mani 15

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 Student Professor sNumber sName address professor pNumber pName address 1 Dave 320FL MM 1 MM 141FL 2 Greg 320FL MM 2 ER 201FL 3 Matt 320FL ER SELECT * FROM Student WHERE sNumber > ALL sNumber sName address professor (SELECT pNumber 3 Matt 320FL ER FROM Professor) Murali Mani 16

Subqueries: ANY - Example Student Professor sNumber sName address professor pNumber pName address 1 Dave 320FL MM 1 MM 141FL 2 Greg 320FL MM 2 ER 201FL 3 Matt 320FL ER SELECT * FROM Student sNumber sName address professor WHERE sNumber = ANY 1 Dave 320FL MM (SELECT pNumber 2 Greg 320FL MM FROM Professor) Murali Mani Subqueries: NOT ALL - Example Student Professor sNumber sName address professor pNumber pName address 1 Dave 320FL MM 1 MM 141FL 2 Greg 320FL MM 2 ER 201FL 3 Matt 320FL ER SELECT * FROM Student WHERE NOT sNumber > ALL sNumber sName address professor 1 Dave 320FL MM (SELECT pNumber 2 Greg 320FL MM FROM Professor) Murali Mani 17