SQL Murali Mani SELECTFROMWHERE SELECT FROM Student WHERE

SQL Murali Mani

SELECT-FROM-WHERE SELECT * FROM Student WHERE s. Name=“Greg” AND address=“ 320 FL” Student s. Number s. Name address professor 1 Dave 320 FL MM 2 Greg 320 FL MM 3 Matt 320 FL ER s. Number s. Name address professor 2 Greg 320 FL MM (s. Name=“Greg” AND address=“ 320 FL”) (Student) Murali Mani

Project SELECT s. Number, s. Name FROM Student s. Number s. Name address professor s. Number s. Name 1 Dave 320 FL MM 1 Dave 2 Greg 320 FL MM 2 Greg 3 Matt 320 FL ER 3 Matt (s. Number, s. Name) (Student) Murali Mani

Extended Projection SELECT s. Number || s. Name AS info FROM Student WHERE address=“ 320 FL” Student s. Number s. Name address professor 1 Dave 320 FL MM 2 Greg 320 FL MM 3 Matt 320 FL ER info 1 Dave 2 Greg 3 Matt (s. Number||s. Name info) ( (address=“ 320 FL”) (Student)) Murali Mani

SQL and Relational Algebra In short, L ( C (R)) becomes SELECT L FROM R WHERE C Murali Mani

Renaming SELECT s 1. s. Number AS num FROM Student S 1 WHERE s 1. s. Number >= 1; Student s. Number s. Name address professor 1 Dave 320 FL MM 2 Greg 320 FL MM 3 Matt 320 FL ER num 1 2 3 (s 1. s. Number num) ( (s 1. s. Number >= 1) ( S 1 (Student))) Murali Mani

String operators l l Comparison Operators based on lexicographic ordering: =, <, >, <>, >=, <= Concatenation operator: || ‘ represented in strings with two consecutive ‘ Pattern match: s LIKE p l l 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 l l l 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 AND TRUE = UNKNOWN OR TRUE = TRUE UNKNOWN AND FALSE = FALSE UNKNOWN OR FALSE = UNKNOWN AND 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 s. Name FROM Student) EXCEPT (SELECT s. Name FROM Student WHERE address=‘ 320 FL’) l l UNION, INTERSECT, EXCEPT have set semantics. For bag semantics, use UNION ALL, INTERSECT ALL, EXCEPT ALL Murali Mani

EXCEPT - Example (SELECT s. Name FROM Student) EXCEPT (SELECT s. Name FROM Student WHERE address=‘ 320 FL’) Student s. Number s. Name address professor 1 Dave 320 FL MM 2 Greg 320 FL MM 3 Matt 320 FL ER 4 Matt 300 FL ER s. Number Murali Mani s. Name address professor

Joins SELECT s. Name FROM Student, Professor WHERE p. Name=‘MM’ AND p. Number=professor; (s. Name)(Student ⋈(p. Name=‘MM’ and p. Number=professor) Professor) Murali Mani

Joins - example Student Professor s. Number s. Name address professor 1 Dave 320 FL 1 2 Greg 320 FL 1 3 Matt 320 FL 2 SELECT s. Name FROM Student, Professor WHERE p. Name=‘MM’ AND p. Number=professor; Murali Mani p. Number p. Name address 1 MM 141 FL 2 ER 201 FL s. Name Dave Greg

Cross Product (Cartesian Product) SELECT * FROM Student CROSS JOIN Professor; Student X Professor can also be written as: SELECT * FROM Student, Professor Murali Mani

Cross Product - Example Student Professor s. Number s. Name address professor 1 Dave 320 FL 1 2 Greg 320 FL 1 3 Matt 320 FL 2 p. Number p. Name address 1 MM 141 FL 2 ER 201 FL s. Number s. Name address professor p. Number p. Name address 1 Dave 320 FL 1 1 MM 141 FL 1 Dave 320 FL 1 2 ER 201 FL 2 Greg 320 FL 1 1 MM 141 FL 2 Greg 320 FL 1 2 ER 201 FL 3 Matt 320 FL 2 1 MM 141 FL 3 Matt 320 FL 2 2 ER 201 FL Murali Mani

Theta Join SELECT * FROM Student JOIN Professor ON professor=p. Number; Student ⋈(professor=p. Number) Professor SELECT * FROM Student, Professor WHERE professor=p. Number; Murali Mani

Theta Join Example s. Number s. Name address professor p. Number p. Name address 1 Dave 320 FL 1 1 MM 141 FL 2 Greg 320 FL 1 1 MM 141 FL 3 Matt 320 FL 2 2 ER 201 FL 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 Student Professor s. Number s. Name address p. Number 1 Dave 320 FL 1 2 Greg 320 FL 1 3 Matt 320 FL 2 p. Number p. Name address 1 MM 141 FL 2 ER 201 FL s. Number s. Name address p. Number p. Name address 1 Dave 320 FL 1 MM 141 FL 2 Greg 320 FL 1 MM 141 FL 3 Matt 320 FL 2 ER 201 FL 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

Outer Joins SELECT * FROM Student NATURAL RIGHT OUTER JOIN Professor Student ⋈o. R Professor Murali Mani

Outer Joins - Example Student Professor s. Number s. Name address p. Number 1 Dave 320 FL 1 2 Greg 320 FL 1 3 Matt 320 FL 2 4 Ben 320 FL 4 p. Number p. Name address 1 MM 141 FL 2 ER 201 FL 3 MW 168 FL s. Number s. Name address p. Number p. Name address 1 Dave 320 FL 1 MM 141 FL 2 Greg 320 FL 1 MM 141 FL 3 Matt 320 FL 2 ER 201 FL 4 Ben 320 FL 4 Null Null 3 MW 168 FL Murali Mani

Sorting: ORDER BY clause SELECT * FROM Student WHERE s. Number >= 1 ORDER BY s. Number, s. Name (s. Number, s. Name) ( (s. Number >= 1) (Student)) Murali Mani

Subqueries SELECT * FROM Student WHERE professor = (SELECT p. Name FROM Professor WHERE p. Number=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 Student s. Number s. Name address professor 1 Dave 320 FL MM 2 Greg 320 FL MM 3 Matt 320 FL ER SELECT * FROM Student WHERE professor = (SELECT p. Name FROM Professor WHERE p. Number=1) Professor p. Number p. Name address 1 MM 141 FL 2 ER 201 FL s. Number s. Name address professor 1 Dave 320 FL MM 2 Greg 320 FL MM Murali Mani

Subqueries l l We can use IN, EXISTS (also NOT IN, NOT EXISTS) ALL, ANY can be used with comparisons SELECT * FROM Student WHERE (s. Number, professor) IN (SELECT p. Number, p. Name FROM Professor) Murali Mani

Subqueries - Example Student Professor s. Number s. Name address professor 1 Dave 320 FL MM 2 Greg 320 FL MM 3 Matt 320 FL ER SELECT * FROM Student WHERE (s. Number, professor) IN (SELECT p. Number, p. Name FROM Professor) p. Number p. Name address 1 MM 141 FL 2 ER 201 FL s. Number s. Name address professor 1 Dave 320 FL MM Murali Mani

Subqueries: EXISTS Professor SELECT * FROM Student WHERE EXISTS (SELECT p. Name FROM Professor WHERE Student. professor=p. Name) p. Number p. Name address 1 MM 141 FL 2 ER 201 FL Student s. Number s. Name address professor 1 Dave 320 FL MM s. Number s. Name address professor 2 Greg 320 FL MM 1 Dave 320 FL MM 3 Matt 320 FL ER 2 Greg 320 FL MM 3 Matt 320 FL ER Murali Mani

Subqueries with negation SELECT * FROM Student WHERE (s. Number, professor) NOT IN (SELECT p. Number, p. Name FROM Professor) Professor p. Number p. Name address 1 MM 141 FL 2 ER 201 FL Student s. Number s. Name address professor 1 Dave 320 FL MM 2 Greg 320 FL MM 3 Matt 320 FL ER s. Number s. Name 2 Greg 320 FL MM 3 Matt 320 FL ER Murali Mani address professor

Subqueries with negation Student Professor s. Number s. Name address professor 1 Dave 320 FL MM 2 Greg 320 FL MM 3 Matt 320 FL ER p. Number p. Name address 1 MM 141 FL 2 ER 201 FL SELECT * FROM Student WHERE NOT EXISTS (SELECT p. Name FROM Professor WHERE Student. professor=p. Name) s. Number s. Name Murali Mani address professor

Subqueries: ALL, ANY SELECT * FROM Student WHERE s. Number > ALL (SELECT p. Number FROM Professor) SELECT * FROM Student WHERE s. Number = ANY (SELECT p. Number FROM Professor) Murali Mani

Subqueries: ALL - Example Student Professor s. Number s. Name address professor 1 Dave 320 FL MM 2 Greg 320 FL MM 3 Matt 320 FL ER SELECT * FROM Student WHERE s. Number > ALL (SELECT p. Number FROM Professor) p. Number p. Name address 1 MM 141 FL 2 ER 201 FL s. Number s. Name 3 Matt Murali Mani address professor 320 FL ER

Subqueries: ANY - Example Student Professor s. Number s. Name address professor 1 Dave 320 FL MM 2 Greg 320 FL MM 3 Matt 320 FL ER SELECT * FROM Student WHERE s. Number = ANY (SELECT p. Number FROM Professor) p. Number p. Name address 1 MM 141 FL 2 ER 201 FL s. Number s. Name address professor 1 Dave 320 FL MM 2 Greg 320 FL MM Murali Mani

Subqueries: NOT ALL Example Student Professor s. Number s. Name address professor 1 Dave 320 FL MM 2 Greg 320 FL MM 3 Matt 320 FL ER SELECT * FROM Student WHERE NOT s. Number > ALL (SELECT p. Number FROM Professor) p. Number p. Name address 1 MM 141 FL 2 ER 201 FL s. Number s. Name address professor 1 Dave 320 FL MM 2 Greg 320 FL MM Murali Mani

Subqueries: NOT ANY Example Student Professor s. Number s. Name address professor 1 Dave 320 FL MM 2 Greg 320 FL MM 3 Matt 320 FL ER SELECT * FROM Student WHERE NOT s. Number = ANY (SELECT p. Number FROM Professor) p. Number p. Name address 1 MM 141 FL 2 ER 201 FL s. Number s. Name 3 Matt Murali Mani address professor 320 FL ER

Subqueries: Tip Murali Mani

Subqueries in FROM clause SELECT s. Name, p. Name FROM Student, (SELECT * FROM Professor WHERE p. Number=1) WHERE professor=p. Name; Professor p. Number p. Name address 1 MM 141 FL 2 ER 201 FL Student s. Number s. Name address professor 1 Dave 320 FL MM s. Name p. Name 2 Greg 320 FL MM Dave MM 3 Matt 320 FL ER Greg MM Murali Mani

Duplicate Elimination SELECT DISTINCT address FROM Student WHERE s. Number >= 1; SELECT DISTINCT * FROM Student; d (Student) d ( (address) ( (s. Number >= 1) (Student))) Student s. Number s. Name address professor address 1 Dave 320 FL MM 320 FL 2 Greg 320 FL MM 3 Matt 320 FL ER Murali Mani

Aggregation SELECT COUNT (*) FROM Student; SELECT COUNT (s. Number) FROM Student; SELECT MIN (s. Number) FROM Student; SELECT MAX (s. Number) FROM Student; SELECT SUM (s. Number) FROM Student; SELECT AVG (s. Number) FROM Student; We can have distinct such as: SELECT COUNT (DISTINCT s. Number) FROM Student Murali Mani

Grouping SELECT COUNT (s. Name) FROM Student GROUP BY address; (COUNT (s. Name)) ( (address, COUNT (s. Name)) (Student)) Student s. Number s. Name address professor COUNT (s. Name) 1 Dave 320 FL MM 3 2 Greg 320 FL MM 3 Matt 320 FL ER Murali Mani

Grouping SELECT address, COUNT (s. Number) FROM Student WHERE s. Number > 1 GROUP BY address HAVING COUNT (s. Number) > 1; Student s. Number s. Name address professor address COUNT (s. Number) 1 Dave 320 FL MM 320 FL 2 2 Greg 320 FL MM 3 Matt 320 FL ER 4 Ben 300 FL ER Murali Mani

Aggregation and NULLs l l 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] a 1, a 2, …, an FROM R 1, R 2, …, Rm [WHERE C 1] [GROUP BY g 1, g 2, …, gl [HAVING C 2]] [ORDER BY o 1, o 2, …, oj] Murali Mani