SQL Query Language CS 3431 SELECTFROMWHERE SELECT FROM

SQL : Query Language CS 3431

SELECT-FROM-WHERE SELECT * FROM Student WHERE s. Name=“Greg” AND address=“ 320 FL” Student s. Number s. Name address professor 1 Dave 311 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) CS 3431

Select-From Query 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) CS 3431

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)) CS 3431

SQL and Relational Algebra L ( C (R)) becomes SELECT L FROM R WHERE C CS 3431

Tuple Variables + 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))) CS 3431

String Operators l Comparison Operators based on lexicographic ordering: =, <, >, <>, >=, <= Concatenation operator: || l Pattern match: s LIKE p l l l p denotes a pattern % : any sequence of 0 or more characters - : matches 1 character Use two consecutive quotes (‘) to represent ‘ CS 3431

String Matching Example SELECT s 1. s. Number AS num FROM Student S 1 WHERE s 1. s. Name LIKE ‘Star_ _ _ _’ or S 1. professor LIKE ‘Mad Cow’’s%’ ; CS 3431

Set Operations in SQL Operators : UNION, INTERSECT, and EXCEPT (SELECT s. Name FROM Student) EXCEPT (SELECT s. Name FROM Student WHERE address=‘ 320 FL’) l l For set semantics, use UNION, INTERSECT, EXCEPT. For bag semantics, use UNION ALL, INTERSECT ALL, EXCEPT ALL CS 3431

EXCEPT - Example (SELECT s. Name FROM Student) EXCEPT (SELECT s. Name FROM Student WHERE address=‘ 320 FL’) Reminder : Set semantics ! 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 CS 3431 s. Name address professor

Joins + SQL Multiple relations in FROM clause : SELECT * FROM Student, Professor CS 3431

Cross (Cartesian) Product SELECT * FROM Student, Professor Student X Professor SELECT * FROM Student CROSS JOIN Professor; CS 3431

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 CS 3431

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

Theta Join 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 2 Greg 320 FL 1 1 MM 141 FL 3 Matt 320 FL 2 2 ER 201 FL Student ⋈(professor=p. Number) Professor CS 3431

Natural Join Reminder: Join columns must have same names in both relations SELECT * FROM Student , Professor WHERE Student. pnumber = Professor. pnumber ; Student ⋈ Professor SELECT * FROM Student NATURAL JOIN Professor CS 3431

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 Student ⋈ Professor CS 3431

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 SELECT * FROM Student NATURAL RIGHT OUTER JOIN Professor Student ⋈o. R Professor CS 3431

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 CS 3431 Student ⋈o Professor

Putting it together: Joins+more 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) CS 3431

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; CS 3431 p. Number p. Name address 1 MM 141 FL 2 ER 201 FL s. Name Dave Greg