SQL Structured Query Language 1 Query Language SQL

SQL Structured Query Language 1

Query Language • SQL is a query language • Used to examine data in the database • SQL queries do not change the contents of the database (no side-effects!) • The result of an SQL query is printed to the screen, not stored in the database 2

Basic SQL query structure SELECT Attributes FROM relations WHERE condition For example: SELECT sid, sname FROM students WHERE sid=1122 3

Query Components • A query can contain the following clauses – select – from – where – group by – having – order by • Only select and from are obligatory • Order of clauses is always as above 4

Very Basic SQL Query SELECT [Distinct] Attributes FROM relation • Attributes: The attributes or values which will appear in the query result (For example: id, name). • DISTINCT: Optional keyword to delete duplicates • Relation: Relation to perform the query on. Example: Select student. ID, student. Name From students 5

Student. ID Student. Dept. Student. Name Student. Age 1123 Math Moshe 25 2245 Computers Mickey 26 55611 Math Menahem 29 Select student. ID, student. Name From students Result: Student. ID Student. Name 1123 Moshe 2245 Mickey 55611 Menahem 6

Basic SQL Query SELECT [Distinct] Attributes FROM relation WHERE condition • condition: A Boolean condition (For example: Eid>21, or Ename=‘Yuval’ ). Only tuples which return ‘true’ for this condition will appear in the result 7

Student. ID Student. Dept. Student. Name Student. Age 1123 Math Moshe 25 2245 Computers Mickey 26 55611 Math Menahem 29 Select student. ID, student. Name From students Where Student. Dept=‘Math’ Result: Student. ID Student. Name 1123 Moshe 55611 Menahem 8

SQL and relational algebra SELECT Distinct A 1, …, An FROM R 1, …, Rm WHERE C; A 1, …, An ( C(R 1 x…x Rm)) 9

Basic SQL Query SELECT [Distinct] attributes FROM relations WHERE condition; Important! The evaluation order, conceptually, is: 1. Compute the cross product of the tables in relations. 2. Delete all rows that do not satisfy condition. 3. Delete all columns that do not appear in attributes. 4. If Distinct is specified eliminate duplicate rows. 10

Example Tables Used Boats Sailors sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 35. 0 bid bname color 101 Nancy red 103 Gloria green Reserves sid bid day 22 101 10/10/96 58 103 11/12/96 11

What does this compute? Select sname from sailors, reserves Where sailors. sid=reserves. sid All sailors who have reserved a boat Sailors Reserves sid sname rating age sid bid day 22 Dustin 7 45. 0 22 101 10/10/96 31 Lubber 8 55. 5 58 103 11/12/96 58 Rusty 10 35. 0 12

Stage 1: Sailors x Reserves Sailors Reserves sid sname rating age sid bid day 22 Dustin 7 45. 0 22 101 10/10/96 22 Dustin 7 45. 0 58 103 11/12/96 31 Lubber 8 55. 5 22 101 10/10/96 31 Lubber 8 55. 5 58 103 11/12/96 58 Rusty 10 35. 0 22 101 10/10/96 58 Rusty 10 35. 0 58 103 11/12/96 13

Stage 2: “where sailors. sid=reserves. sid” Sailors Reserves sid sname rating age sid bid day 22 Dustin 7 45. 0 22 101 10/10/96 22 Dustin 7 45. 0 58 103 11/12/96 31 Lubber 8 55. 5 22 101 10/10/96 31 Lubber 8 55. 5 58 103 11/12/96 58 Rusty 10 35. 0 22 101 10/10/96 58 Rusty 10 35. 0 58 103 11/12/96 14

Stage 2: “where sailors. sid=reserves. sid” Sailors Reserves sid sname rating age sid bid day 22 Dustin 7 45. 0 22 101 10/10/96 58 Rusty 10 35. 0 58 103 11/12/96 15

Stage 3: “select sname” Sailors Reserves sid sname rating age sid bid day 22 Dustin 7 45. 0 22 101 10/10/96 58 Rusty 10 35. 0 58 103 11/12/96 16

Stage 3: “select sname” sname Dustin Final answer Rusty 17

Example Query SELECT sname, age FROM Sailors WHERE rating>7; Q: What does this compute? 18

Example Query SELECT DISTINCT sname FROM Sailors, Reserves WHERE Sailors. sid = Reserves. sid and bid = 103; Q: What does this compute? 19

WHERE Sailors. sid = Reserves. sid and bid = 103; Sailors Reserves sid sname rating age sid bid day 22 Dustin 7 45. 0 22 101 10/10/96 22 Dustin 7 45. 0 58 103 11/12/96 31 Lubber 8 55. 5 22 101 10/10/96 31 Lubber 8 55. 5 58 103 11/12/96 58 Rusty 10 35. 0 22 101 10/10/96 58 Rusty 10 35. 0 58 103 11/12/96 20

Select sname Sailors Reserves sid sname rating age sid bid day 22 Dustin 7 45. 0 22 101 10/10/96 22 Dustin 7 45. 0 58 103 11/12/96 31 Lubber 8 55. 5 22 101 10/10/96 31 Lubber 8 55. 5 58 103 11/12/96 58 Rusty 10 35. 0 22 101 10/10/96 58 Rusty 10 35. 0 58 103 11/12/96 21

A Few SELECT Options • Select all columns: SELECT * FROM Sailors; • Rename selected columns: SELECT S. sname AS Sailors_Name FROM Sailors S; • Applying functions (e. g. , Mathematical manipulations) SELECT (age-5)*2 FROM Sailors S; 22

The WHERE Clause • Numerical and string comparison: !=, <>, =, <, >, >=, <=, between(val 1 AND val 2) • Logical components: AND, OR • Null verification: IS NULL, IS NOT NULL • Checking against a list with IN, NOT IN. 23

Examples SELECT sname FROM Sailors WHERE age>=40 AND rating IS NOT NULL ; SELECT sid, sname FROM sailors WHERE sid IN (1223, 2334, 3344) or sname between(‘George’ and ‘Paul’); 24

The LIKE Operator • A pattern matching operator (regular expression) • Basic format: colname LIKE pattern – Example: SELECT sid FROM Sailors WHERE sname LIKE ‘R_%y’; _ is a single character % is 0 or more characters 25

Relation naming SELECT S. sname FROM Sailors S, Reserves R WHERE S. sid = R. sid and R. bid = 103; • Naming relations is good style • It is necessary if the same relation appears twice in the FROM clause 26

Example Query SELECT S. sname FROM Sailors S, Reserves R WHERE S. sid = R. sid and R. bid != 103; Q: Does this return the names of sailors who did not reserve boat 103? A: No! it returns the names of sailors who reserved a boat other than boat 103 Explanation in the next slides 27

SELECT S. sname FROM Sailors S, Reserves R WHERE S. sid = R. sid and R. bid != 103; Sailors Reserves sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 sid bid day 22 101 10/10/07 22 103 11/12/07 31 104 12/2/07 28

Sailors Reserves sid sname rating age sid bid day 22 Dustin 7 45. 0 22 101 10/10/07 22 Dustin 7 45. 0 22 103 11/12/07 22 Dustin 7 45. 0 31 104 12/2/07 31 Lubber 8 55. 5 22 101 10/10/07 31 Lubber 8 55. 5 22 103 11/12/07 31 Lubber 8 55. 5 31 104 12/2/07 29

Sailors Reserves sid sname rating age sid bid day 22 Dustin 7 45. 0 22 101 10/10/07 22 Dustin 7 45. 0 22 103 11/12/07 22 Dustin 7 45. 0 31 104 12/2/07 31 Lubber 8 55. 5 22 101 10/10/07 31 Lubber 8 55. 5 22 103 11/12/07 31 Lubber 8 55. 5 31 104 12/2/07 30

Sailors Reserves sid sname rating age sid bid day 22 Dustin 7 45. 0 22 101 10/10/07 31 Lubber 8 55. 5 31 104 12/2/07 sname Dustin But Dustin did order boat 103! Lubber 31

SQL query SELECT S. sid FROM Sailors S, Reserves R WHERE S. sid = R. sid; When would adding DISTINCT give a different result? When there is a sailor who reserved more than a single boat 32

Are any of these the same? SELECT S. sid FROM Sailors S, Reserves R WHERE S. sid = R. sid; SELECT DISTINCT R. sid FROM Sailors S, Reserves R WHERE S. sid = R. sid; SELECT R. sid FROM Reserves R Reserves Sailors sid sname rating age sid bid day 33

Example Query How would you find sailors who have reserved more than one boat? SELECT S. sname FROM Sailors S, Reserves R 1, Reserves R 2 WHERE S. sid = R 1. sid and R 1. sid=R 2. sid and R 1. bid!=R 2. bid; 34

SQL query SELECT S. sname FROM Sailors S, Reserves R, Boats B WHERE S. sid = R. sid and R. bid = B. bid and B. color = 'red' Q: What does this return? 35

SQL query Q: How would you find the colors of boats reserved by Bob? A: SELECT distinct B. color FROM Sailors S, Reserves R, Boats B WHERE S. sname = ‘Bob’ and S. sid = R. sid and R. bid = B. bid 36

Order Of the Result • The ORDER BY clause can be used to sort results by one or more columns • The default sorting, when ORDER BY is used, is in ascending order • Can specify ASC or DESC SELECT FROM WHERE ORDER BY sname, rating, age Sailors S age > 50 rating ASC, age DESC 37

What does this return? SELECT DISTINCT S. sname FROM Sailors S, Reserves R, Boats B WHERE S. sid = R. sid and R. bid = B. bid and (B. color = 'red' or B. color='green') What would happen if we replaced or by and ? We would get no results! Then how can we find sailors who have reserved both a green and a red boat? 38

Sailors who’ve reserved red and green boats SELECT S. sname FROM Sailors S, Reserves R 1, Reserves R 2 Boats B 1, Boats B 2 WHERE S. sid = R 1. sid and R 1. bid = B 1. bid and B 1. color = ‘red’ and S. sid = R 2. sid and R 2. bid = B 2. bid and B 2. color = ‘green’; 39

Other Relational Algebra Operators • So far, we have seen selection, projection and Cartesian product • How do we do operators UNION and MINUS? 40

Three SET Operators • [Query] UNION [Query] • [Query] EXCEPT [Query] • [Query] INTERSECT [QUERY] • Note: The operators remove duplicates by default! 41

Sailors who’ve reserved red or green boat SELECT S. sname FROM Sailors S, Boats B, Reserves R WHERE S. sid = R. sid and R. bid = B. bid and B. color = ‘red’ Would INTERSECT give us sailors who UNION reserved both red and green boats? SELECT S. sname FROM Sailors S, Boats B, Reserves R WHERE S. sid = R. sid and R. bid = B. bid and B. color = ‘green’; Almost, but not quite because sname is not unique… 42

Multiset (Bag) Operators • Union without removing duplicates: UNION ALL SELECT FROM DISTINCT sname Sailors S 43

Nested Queries

Nested Queries • A query is nested if one of its clauses contains a query • Queries can be nested in the following clauses: – Select – From – Where – Having 45

Nested Queries (cont) • A sub-query of a nested query is correlated if it refers to relations appearing in the outer portion of the query • We start by discussing subqueries in the WHERE clause – Common operators used to correlate are: IN, ANY/ALL, EXISTS 46

Remember! • The WHERE clause is evaluated for each tuple in the Cartesian Product formed by the FROM clause, and a Boolean answer is returned – The subquery is used to define the Boolean answer! 47

Nested queries in WHERE Subqueries with multiple results: SELECT S. sname FROM Sailors S WHERE S. sid IN (SELECT R. sid FROM Reserves R WHERE R. bid = 103); What would happen if we wrote NOT IN? 48

In/Not In Format • The format of these subqueries is always: – Attribute or value – In / Not in – Subquery that returns a single column • Returns true if the attribute value is in / not in the result of the subquery 49

What does this produce? SELECT S. sname FROM Sailors S WHERE S. sid NOT IN (SELECT R. sid FROM Reserves R WHERE R. bid IN (SELECT B. bid FROM Boats B WHERE B. color='red')) Names of sailors who did not reserve a red boat 50

Any/All Format • The format of these subqueries is always: – Attribute or value – Arithmetic comparison operator – Any / All – Subquery that returns a single column • Returns true if the attribute value satisfies the arithmetic operator with respect to any/all of the query results 51

Set-Comparison Queries SELECT * FROM Sailors S 1 WHERE S 1. age > ANY (SELECT S 2. age FROM Sailors S 2); We can also use op ALL (op is >, <, =, >=, <=, or <>). 52

Exists/Not Exists Format • The format of these subqueries is always: – Exists / Not Exists – Subquery that returns any number of columns • Returns true if the subquery returns a nonempty (resp. empty) result 53

Correlated Nested Queries SELECT S. sid FROM Sailors S WHERE EXISTS (SELECT * FROM Reserves R WHERE R. bid = 103 and S. sid = R. sid); S not in subquery, refers to outer loop Sid of sailors who reserved boat 103 Q: What if we wrote NOT EXISTS? A: We would get sid of sailors who did not reserve boat 103 54

Exists and Not Exists • Differs from In and Not In • Exists: For every tuple in the outer loop, the inner loop is tested. If the inner loop produces a result, the outer tuple is added to the result. 55

How would you find the names of sailors who have reserved a red boat but not a green boat? SELECT SS. sname from sailors SS where SS. sid in ( SELECT R 1. sid FROM Reserves R 1, Boats B 1 WHERE R 1. bid=B 1. bid and B 1. color=‘red’ EXCEPT SELECT R 2. sid FROM Reserves R 2, Boats B 2 WHERE R 2. bid=B 2. bid and B 2. color=‘green’ ); 56

Rewrite using not in SELECT SS. sname from sailors SS where SS. sid in ( SELECT R 1. sid FROM Reserves R 1, Boats B 1 WHERE R 1. bid=B 1. bid and B 1. color=‘red’ and R 1. sid not in ( SELECT R 2. sid FROM Reserves R 2, Boats B 2 WHERE R 2. bid=B 2. bid and B 2. color=‘green’ )); 57

How would you find the sailors who have reserved all boats? 58

Remember: Algebraic Operator of Division • Consider: A(X, Y) and B(Y). Then A B = • In general, we require that the set of fields in B be contained in those of A. 59

Suppliers from A who supply All Parts from B (1) sno pno S 1 P 1 S 1 P 2 S 1 P 3 S 1 P 4 S 2 P 1 S 2 P 2 S 3 P 2 S 4 P 4 A sno pno P 2 = B 1 60

Suppliers from A who supply All Parts from B (2) sno pno S 1 P 1 S 1 P 2 S 1 P 3 S 1 P 4 S 2 P 1 S 2 P 2 S 3 P 2 S 4 P 4 A sno pno P 2 P 4 = B 2 61

Suppliers from A who supply All Parts from B (3) sno pno S 1 P 1 S 1 P 2 S 1 P 3 S 1 P 4 S 2 P 1 S 2 P 2 S 3 P 2 S 4 P 4 A sno pno P 1 P 2 P 4 = B 3 62

So what is the result of this? Reserves(sid, bid) Boats(bid) Sailors who Reserved all Boats Sailor S whose "set of boats reserved" contains the "set of all boats" 63

What is the strategy for finding sailors who have reserved all boats? The sailors for which there does not exist a boat which they have not reserved 64

Sailors who reserved all boats (Division 1) Sailors for which there does not exist a boat they did not reserve SELECT sid FROM Sailors S WHERE NOT EXISTS (SELECT B. bid FROM Boats B WHERE B. bid NOT IN (SELECT R. bid FROM Reserves R WHERE R. sid = S. sid)); 65

Sailors who reserved all boats (Division 2) Sailors for which there does not exist a boat they did not reserve SELECT S. sid FROM Sailors S WHERE NOT EXISTS( SELECT B. bid FROM Boats B WHERE NOT EXISTS( SELECT R. bid FROM Reserves R WHERE R. bid=B. bid and R. sid=S. sid)) 66

Sailors who reserved all boats (Division 3) Sailors for which there does not exist a boat for which there is no reservation in Reserves SELECT S. sid FROM Sailors S WHERE NOT EXISTS((SELECT FROM EXCEPT (SELECT FROM WHERE B. bid Boats B) R. bid Reserves R R. sid = S. sid)); 67

Aggregation 6

Aggregate Operators • The aggregate operators available in SQL are: – COUNT(*) – COUNT([DISTINCT] A) – SUM([DISTINCT] A) – AVG([DISTINCT] A) – MAX(A) – MIN(A) 69

Some Examples SELECT COUNT(*) FROM Sailors S SELECT COUNT(sid) FROM Sailors S SELECT AVG(S. age) FROM Sailors S WHERE S. rating=10 SELECT COUNT(distinct color) FROM Boats 70

Find Average Age for each Rating • So far, aggregation has been applied to all tuples that passed the WHERE clause test. • How can we apply aggregation to groups of tuples? 71

Find Average Age for each Rating SELECT AVG(age) FROM Sailors GROUP BY rating; 72

Basic SQL Query SELECT FROM WHERE GROUP BY HAVING [Distinct] attributes relation-list condition grouping-attributes group-condition; • attributes: must appear in grouping-attributes or aggregation operators • group-condition: Constrains groups. Can only constrain attributes appearing in grouping-attributes or in aggregation operators 73

Evaluation- important! SELECT FROM WHERE GROUP BY HAVING [Distinct] attributes relation-list condition grouping-attributes group-condition; 1. Compute cross product of relation-list 2. Tuples failing condition are thrown away 3. Tuples are partitioned into groups by values of groupingattributes 4. The group-condition is applied to eliminate groups 5. One answer in generated for each group! 74

SELECT AVG(age) FROM Sailors GROUP BY rating; Sailors sid Sname rating age sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 63 Fluffy 7 44. 0 58 Rusty 10 35. 0 78 Morley 7 31. 0 63 Fluffy 7 44. 0 31 Lubber 8 55. 5 78 Morley 7 31. 0 58 Rusty 10 35. 0 84 Popeye 10 33. 0 40 55. 5 34 75

SELECT AVG(age) FROM Sailors Where age<50 GROUP BY rating Having count(*)>2; Sid Sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 35. 0 63 Fluffy 7 44. 0 78 Morley 7 31. 0 84 Popeye 10 33. 0 Step 1 Sid Sname rating age 22 Dustin 7 45. 0 58 Rusty 10 35. 0 63 Fluffy 7 44. 0 78 Morley 7 31. 0 84 Popeye 10 33. 0 76

SELECT AVG(age) FROM Sailors Where age<50 GROUP BY rating Having count(*)>2; Step 2 Sid sname rating age 22 Dustin 7 45. 0 63 Fluffy 7 44. 0 78 Morley 7 31. 0 58 Rusty 10 35. 0 84 Popeye 10 33. 0 77

SELECT AVG(age) FROM Sailors Where age<50 GROUP BY rating Having count(*)>2; Sid sname rating age 22 Dustin 7 45. 0 63 Fluffy 7 78 Step 3 Sid sname rating age 44. 0 22 Dustin 7 45. 0 Morley 7 31. 0 63 Fluffy 7 44. 0 58 Rusty 10 35. 0 78 Morley 7 31. 0 84 Popeye 10 33. 0 78

SELECT AVG(age) FROM Sailors Where age<50 GROUP BY rating Having count(*)>2; Sid sname Step 4 rating age Final Answer: 22 Dustin 7 45. 0 63 Fluffy 7 44. 0 78 Morley 7 31. 0 40 79

Find name and age of oldest Sailor. . ? SELECT S. sname, MAX(S. age) FROM Sailors S GROUP BY S. sname Wrong! Why? 80

Find name and age of oldest Sailor SELECT S. sname, S. age FROM Sailors S WHERE S. age = (SELECT MAX(S 2. age) FROM Sailors S 2) Right!! How else can this be done? HINT: Use ALL 81

What does this return? SELECT FROM WHERE GROUP BY B. bid, COUNT(*) Boats B, Reserves R R. bid=B. bid and B. color=‘red’ B. bid What would happen if we put the condition about the color in the HAVING clause? 82

What would happen if we put the condition about the color in the HAVING clause? SELECT B. bid, COUNT(*) FROM Boats B, Reserves R WHERE R. bid=B. bid GROUP BY B. bid, B. color HAVING B. color=‘red’ 83

What does this return? SELECT bname FROM Boats B, Reserves R WHERE R. bid=B. bid GROUP BY bid, bname HAVING count(DISTINCT day) <= 5 Names of Boats that were not Reserved on more than 5 days Can we move the condition in the HAVING to the WHERE? No! Aggregate functions are not allowed in WHERE 84

The Color for which there are the most boats. . ? SELECT FROM GROUP BY HAVING color Boats B color max(count(bid)) What is wrong with this? How would you fix it? 85

The Color for which there are the most boats SELECT FROM GROUP BY HAVING color Boats B color count(bid) >= ALL (SELECT count(bid) FROM Boats GROUP BY Color) 86

Aggregation Instead of Exists • Aggregation can take the place of exists. • What does this return? SELECT color FROM Boats B 1 WHERE NOT EXISTS( SELECT * FROM Boats B 2 WHERE B 1. bid <> B 2. bid AND B 1. color=B 2. color) The color of boats which have a unique color (no other boats with the same color) 87

Aggregation Instead of Exists SELECT FROM GROUP BY HAVING color Boats B 1 color count(bid) = 1 Somewhat simpler… 88

Subqueries in the FROM and in the SELECT clauses

A Complex Query • We would like to create a table containing 3 columns: – Sailor id – Sailor age – Age of the oldest Sailor (same value in all rows) How can this be done? 90

What We Want: Sailors sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 35. 0 Result Max- sid age 22 45. 0 55. 5 31 55. 5 58 35. 0 55. 5 age 91

Attempt 1 SELECT S. sid, S. age, MAX(S. age) FROM Sailors S; Why is this wrong? 92

Attempt 2 SELECT S. sid, S. age, MAX(S. age) FROM Sailors S GROUP BY S. id, S. age; Why is this wrong? 93

Solution 1: Subquery in FROM SELECT S. sid, S. age, M. mx FROM Sailors S, (SELECT MAX(S 2. age) as mx FROM Sailors S 2) M; • We can put a query in the FROM clause instead of a table • The query in the FROM clause must be renamed with a range variable (M in this case). 94

Solution 2: Subquery in SELECT S. sid, S. age, (SELECT MAX(S 2. age) FROM Sailors S 2) FROM Sailors S; • A query in the SELECT clause must return at most one value for each row returned by the outer query. 95

Another Example of a Sub-query in SELECT S. sid, S. age, (SELECT MAX(S 2. age) FROM Sailors S 2 WHERE S 2. age<S. age) FROM Sailors S; • What does this query return? • Note the use of S (defined in the outer query) within the inner query. 96

Result: Sailors sid sname rating age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 35. 0 Result (Select sid age 22 45. 0 31 55. 5 45. 0 58 35. 0 null …) 97

Another Example of a Sub-query in FROM? ? SELECT S. sid, S. age, M. mx FROM Sailors S, (SELECT MAX(S 2. age) as mx FROM Sailors S 2 WHERE S 2. age<S. age); Why is this wrong? 98

Translating RA to SQL 99

RA is strictly less expressive than SQL • Every query in relational algebra can be equivalently written in SQL • There are SQL queries that cannot be expressed using relational algebra – Examples? • We now present a procedure for translating RA to SQL – Note: This is not the most efficient translation 100

Assumptions • To make the presentation simpler, assume we are translating a relational algebra expression E into SQL where: – E does not use the same relation twice – No two relations have any attributes with the same names • Easy to overcome these assumptions using RA renaming and SQL aliasing. 101

Translation By Induction on Structure of E • Induction on the number of relational algebra operators appearing in E. • Base case: 0 operators. – E is simply a relation R SELECT DISTINCT * FROM R 102

Translation By Induction on Structure of E • Induction Step: Assume that for all E with less than k operators, there is an SQL expression equivalent to E. We show for k • Must consider several cases, depending on the “last operator” using in E: , , - 103

Last Operator is • E = C(E 1), where C is a Boolean condition • Let S be an SQL expression equivalent to E 1 (there is one by the induction hypothesis) • E is equivalent to SELECT DISTINCT * FROM S Sub-query in the WHERE C FROM Clause! 104

Last Operator is • E = A 1, . . , Ak(E 1), where A 1, …, Ak are attributes • Let S be an SQL expression equivalent to E 1 (there is one by the induction hypothesis) • E is equivalent to SELECT DISTINCT A 1, …, Ak FROM S Sub-query in the FROM Clause! 105

Last Operator is • E = E 1 E 2 • Let S 1, S 2 be SQL expressions equivalent to E 1 and E 2 • E is equivalent to S 1 UNION S 2 106

Last Operator is • E = E 1 E 2 • Let S 1, S 2 be SQL expressions equivalent to E 1 and E 2 • E is equivalent to SELECT * FROM S 1, S 2 Sub-queries in the FROM Clause! 107

Last Operator is • E = E 1 - E 2 • Let S 1, S 2 be SQL expressions equivalent to E 1 and E 2 • E is equivalent to S 1 EXCEPT S 2 108

Example • Translate – sname, color( rating<10(Sailors Boats)) 109

Null Values 110

What does NULL Mean? • There are different interpretations to a value of NULL 1. Value Unknown: I know that there is a value that belongs here, but I don’t know what it is. – Example: Birthday attribute 2. Value Inapplicable: There is no value that makes sense here – Example: Spouse attribute for unmarried person 111

What does NULL Mean? (cont) 3. Value Withheld: We are not entitled to know this value – Example: phone number attribute 112

Null Values in Expressions • Two important rules: – When we operate on NULL and any other value, (including another NULL), using an arithmetic operator like * or +, the result is always NULL – When we compare NULL and any other value (including another NULL) , using a comparison operator like = or >, the result is UNKNOWN. • The correct way to determine if an attribute x has value NULL is using x IS NULL or x IS NOT NULL, which will return true or false 113

3 Valued Logic: True, False, Unknown A B A and B A or B True True False True Unknown True False False Unknown True Unknown A Not A Unknown True False Unknown Unknown Only tuples for which the WHERE clause has value TRUE are used to create tuples in the result 114

What does this return? SELECT * FROM Sailors WHERE sname = sname 115

What does this return? SELECT * FROM Sailors WHERE rating > 5 or rating <= 5 116

What do these return? SELECT sname, rating * 0 FROM Sailors SELECT sname, rating - rating FROM Sailors 117

Nulls in Aggregation Functions • count(*): counts all rows (even rows that are all null values) • count(A): counts non-null A-s. returns 0 if all As are null • sum(A), avg(A), min(A), max(A) – ignore null values of A – if A only contains null value, the result is null 118

Distinct and Group By • Rows are considered identical, for group by and distinct, if they have all the same nonnull values and both have null values in the same columns • Distinct removes duplicates of such rows • Such rows form a single group when using GROUP BY 119

Example R B C 1 null 2 null 3 4 3 5 SELECT count(*), count(c), min(c), sum(c) FROM (SELECT c FROM R WHERE c IS NULL or c <> NULL GROUP BY c) 120

Join Operators in the FROM Clause “Syntactic Sugar” 121

Shorthand for Conditional Join SELECT S 1. sname, S 2. sname FROM Sailors S 1, Sailors S 2 WHERE S 1. sid != S 2. sid and S 1. sname = ‘Rusty’ SELECT S 1. sname, S 2. sname FROM Sailors S 1 JOIN Sailors S 2 on S 1. sid != S 2. sid WHERE S 1. sname = ‘Rusty’ 122

Shorthand for Equi-Join SELECT S. sname, FROM Sailors S, Reserves R WHERE S. sid = R. sid and S. age > 20 SELECT S. sname, FROM Sailors S JOIN Reserves R USING (sid) WHERE S. age > 20 123

Shorthand for Natural Join SELECT S. sname, FROM Sailors S, Reserves R WHERE S. sid = R. sid and S. age > 20 SELECT S. sname, FROM Sailors S NATURAL JOIN Reserves R WHERE S. age > 20 Requires equality on all common fields 124

Outer Join 125

Left Outer Join • The left outer join of R and S contains: – all the tuples in the join of R and S – all the tuples in R that did not join with tuples from S, padded with null values SELECT Sailors. sid, Reserves. bid FROM Sailors NATURAL LEFT OUTER JOIN Reserves 126

Result Reserves Sailors sid sname rating age sid bid day 22 Dustin 7 45. 0 22 101 10/10/96 31 Lubber 8 55. 5 58 103 11/12/96 58 Rusty 10 35. 0 Result sid bid 31 null 22 101 58 103 127

Right Outer Join, Full Outer Join • The right outer join of R and S contains: – all the tuples in the join of R and S – all the tuples in S that did not join with tuples from R, padded with null values • The full outer join of R and S contains: – all the tuples in the left outer join of R and S – all the tuples in the right outer join of R and S 128

Express the Left Outer Join in SQL, without the Left Outer Join Operator • Suppose we have R(A, B) and S(B, C). • Can you write a query that returns the left outer join of R and S, that does not use the left outer join operator? 129

ALL and ANY: Special Cases 130

Query 1: What does this return? SELECT * FROM Sailors WHERE age > ANY (SELECT age FROM Sailors WHERE sname=‘Joe’) 131

Query 2: What does this return? SELECT * FROM Sailors WHERE age > ALL (SELECT age FROM Sailors WHERE sname=‘Joe’) 132

Query Containment • We say that a query Q 1 contains query Q 2, if for all databases D, the result of applying Q 1 to D contains the result of applying Q 2 to D. • Does Query 2 contain Query 1? • Does Query 1 contain Query 2? • (the answer to both questions is no – but why? ) 133

Query Q 3: What does this return? SELECT * FROM Sailors WHERE age = ANY (SELECT age FROM Sailors WHERE sname=‘Joe’) 134

Query Q 4: What does this return? SELECT * FROM Sailors WHERE age IN (SELECT age FROM Sailors WHERE sname=‘Joe’) Equivalent to Q 3 135

Query Q 5: What does this return? SELECT * FROM Sailors WHERE age <> ANY (SELECT age FROM Sailors WHERE sname=‘Joe’) 136

Query Q 6: What does this return? SELECT * FROM Sailors WHERE age NOT IN (SELECT age FROM Sailors WHERE sname=‘Joe’) Not equivalent to Q 5 – why? 137

Views 138

What is a View? • A view is a virtual table • A view is defined by a query • The result of the query is the contents of the virtual table – always update with respect to the database – does not exist, is computed every time referenced • Changing a table (insert/update/delete) automatically changes the view 139

Defining a View • CREATE VIEW <view-name> AS <view-def>; – Where view-def is an SQL query • Example: CREATE VIEW Great. Sailors AS SELECT sid, sname FROM Sailors WHERE rating>=9 140

Defining a View • Another example: CREATE VIEW Sailors. Dates AS SELECT sid, date FROM Sailors S, Reservations R WHERE S. sid = R. sid 141

Querying a View • Once you have defined a view, you can use it in a query (in the same way that you use a relation) SELECT sid FROM Great. Sailors WHERE sname = ‘Joe’ 142

Querying a View • You can use a view and a regular relation together in a query SELECT bname FROM Great. Sailors G, Reservations R, Boats B WHERE G. sid = R. sid and R. bid = B. bid 143

Understanding Queries using Views • When writing a query with a view it is as if the expression defining the view is a subquery is the FROM clause SELECT bname FROM Great. Sailors G, FROM (SELECT sid FROM Sailors WHERE rating >=9) G, Reservations R, Boats B WHERE G. sid = R. sid and R. bid = B. bid 144

What are views good for? (1) • Simplifying complex queries: Here is an example allows the user to "pretend" that there is a single table in the database – CREATE VIEW SRB as SELECT S. sid, sname, rating, age, R. bid, day, bname, color FROM Sailors S, Boats B, Reserves R WHERE S. sid = R. sid and R. bid = B. bid 145

What are views good for? (1) • Now: Find snames of Sailors who reserved red boats on 1/11/09 using SRB SELECT sname FROM SRB WHERE color = ‘red’ and day = ‘ 1/11/09’ 146

What are views good for? (2) • Security issues – preventing unauthorized access. Example: hiding the rating value CREATE VIEW Sailor. Info SELECT sname, sid, age FROM Sailors grant SELECT on Sailor. Info to shimon; 147

Modifying Views • Sometimes it is possible to insert into, delete from, or update, a view !!! • Actually, the user request is translated into a modification of the base tables (the tables used in the view definition) • Modifications are possible only when the view is updatable 148

Updatable Views • There are complex rules determining when a view is updatable • Basically, updates are possible when the view is defined by selecting (SELECT, not SELECT DISTINCT) from a single relation R such that: 1. The WHERE clause does not involve R in a subquery 2. The FROM clause contains only the one relation R 3. The list in the SELECT clause must include enough attributes such that for every tuple inserted through the view, we can fill the other attributes with NULL or a default value 149

Inserting Example CREATE VIEW Great. Sailors AS SELECT sid, sname FROM Sailors WHERE rating>=9 INSERT INTO Great. Sailors VALUES(113, ‘Sam’) Interestingly, we won’t see this tuple when we query Great. Sailors INSERT INTO Sailors(sid, sname) VALUES(113, ‘Sam’) 150

IMPORTANT NOTE • There is no relation Great. Sailors • Insertion actually affects the table over which Great. Sailors is defined, i. e. , Sailors • Similarly, deletion and updates will affect the underlying tables… 151

Deleting Example CREATE VIEW Great. Sailors AS SELECT sid, sname FROM Sailors WHERE rating>=9 DELETE FROM Great. Sailors WHERE sname = ‘John’ We add the where condition from the view definition to make sure that only tuples appearing in the view are deleted DELETE FROM Sailors WHERE sname = ‘John’ and rating>=9 152

Updating Example CREATE VIEW Great. Sailors AS SELECT sid, sname FROM Sailors WHERE rating>=9 Update Great. Sailors SET sname = ‘Abraham’ WHERE sname = ‘John’ Update Sailors SET sname = ‘Abraham’ WHERE sname = ‘John’and rating>=9 153

Postgres Support • Postgres does not support updatable views • Can achieve the same effect using triggers… 154

Recursion 155

Flights • Flight(airline, from, to) Airline El Al Continental Air Canada Frm Tel Aviv New York Los Angeles Toronto Montreal 156

Can you find? • How can you find all places that you can get to by a direct flight from Tel Aviv? SELECT to FROM Flights WHERE frm = ‘Tel Aviv’ 157

Can you find? • How can you find all places that you can get to by a flight with one stop-over from Tel Aviv? SELECT F 2. to FROM Flights F 1, Flights F 2 WHERE F 1. frm = ‘Tel Aviv’ and F 1. to = F 2. frm 158

Can you find? • How can you find all places that you can get to by a flight with zero or one stop-over from Tel Aviv? SELECT to FROM Flights WHERE frm = ‘Tel Aviv’ UNION SELECT F 2. to FROM Flights F 1, Flights F 2 WHERE F 1. frm = ‘Tel Aviv’ and F 1. to = F 2. frm 159

Why can’t you find? • How can you find all places that you can get to by a flight any number of stop-overs from Tel Aviv? • Problem: How many times should Flights appear in the FROM clause? 160

Recursion in SQL • The SQL-99 standard allows us to define temporary relations which can be recursive WITH R AS <definition of R> <query involving R> Or more generally: WITH [RECURSIVE] R 1 AS <definition of R 1> , …, [RECURSIVE] Rn AS <definition of Rn> <query involving R 1, . . , Rn> 161

Example WITH RECURSIVE Reaches(frm, to) AS (SELECT frm, to FROM Flights) UNION (SELECT R 1. frm, R 2. to FROM Reaches R 1, Reaches R 2 WHERE R 1. to = R 2. frm) SELECT to FROM Reaches WHERE frm=‘Tel Aviv’ 162

Fix-Point Semantics • The value of Reaches is derived by repeatedly evaluating its definition until no changes are made – Before starting evaluation, Reaches is empty – Then, its definition is repeatedly evaluated, and the result defines Reaches – This continues until no more changes appear 163

WITH RECURSIVE Reaches(frm, to) AS (SELECT frm, to FROM Flights) UNION (SELECT R 1. frm, R 2. to FROM Reaches R 1, Reaches R 2 WHERE R 1. to = R 2. frm) SELECT to FROM Reaches WHERE frm=‘Tel Aviv’ Flights Reaches: Step 1 Airline Frm To El Al Tel Aviv New York Los Angeles Continental New York Air Canada Los Angeles Toronto Air Canada Los Angeles Montreal 164

Flights Reaches: Step 1 Airline Frm To El Al Tel Aviv New York Los Angeles Continental New York Air Canada Los Angeles Toronto Air Canada Los Angeles Montreal Reaches: Step 2 Frm To Tel Aviv New York Los Angeles Toronto Los Angeles Montreal Tel Aviv Los Angeles New York Toronto New York Montreal 165

Flights Reaches: Step 1 Airline Frm To El Al Tel Aviv New York Los Angeles Continental New York Air Canada Los Angeles Toronto Air Canada Los Angeles Montreal Reaches: Step 3 Frm To Tel Aviv New York Los Angeles Toronto Los Angeles Montreal Tel Aviv Los Angeles New York Toronto New York Montreal Tel Aviv Toronto Tel Aviv Montreal 166

Flights Reaches: Step 1 Airline Frm To El Al Tel Aviv New York Los Angeles Continental New York Air Canada Los Angeles Toronto Air Canada Los Angeles Montreal Reaches: Final Value Frm To Tel Aviv New York Los Angeles Toronto Los Angeles Montreal Tel Aviv Los Angeles New York Toronto New York Montreal Tel Aviv Toronto Tel Aviv Montreal Query will return values in Red 167

Mutually Recursive Relations • We can define several recursive queries, which can use one another in their definitions. • A dependency graph has a node for each relation defined, and an edge from one node to another if the first uses the second in its definition – In particular, in the previous example, there would be an edge from Reaches to itself • R and S are mutually recursive, if there is a cycle in the graph involving nodes R and S 168

Example WITH RECURSIVE P(x) AS R P (SELECT * FROM R) EXCEPT (SELECT * FROM Q) RECURSIVE Q(x) AS (SELECT * FROM R) EXCEPT Q P and Q are mutually recursive (SELECT * FROM P) SELECT * FROM P 169

Problematic Recursion • Complicated recursions are allowed • However, sometimes the result may not be well defined – These cases are not allowed by SQL • Before defining exactly what is not allowed, we consider an example 170

Is there a Fix Point? • Recall that the result of a defined relation is derived by simply evaluating it again and again until it no longer changes. • However, what happens if this process never terminates? 171

Example WITH RECURSIVE P(x) AS (SELECT * FROM R) EXCEPT What is in P and Q if R has the single tuple (0)? (SELECT * FROM Q) RECURSIVE Q(x) AS (SELECT * FROM R) EXCEPT (SELECT * FROM P) SELECT * FROM P 172

Monotonicity Requirement • R can be defined using a mutually recursive relation S only if R is monotone in S, i. e. , – Adding an arbitrary tuple to S might add tuples to R, or might leave R unchanged, but can never cause a tuple to be deleted from R 173

Monotonicity Requirement • In the previous example, R uses the mutually recursive relation Q, but R is not monotonic in Q (adding tuples to Q can cause tuples to be removed from R) • Therefore, this type of recursion is not allowed in SQL 174