SQL Structured Query Language 1 Query Language SQL
- Slides: 174
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 sname AS Sailors_Name FROM Sailors; • Applying functions (e. g. , Mathematical manipulations) SELECT (age-5)*2 FROM Sailors; 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
Reminder: 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
Reminder: 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
Reminder: 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
- Introduction to structured query language (sql)
- Introduction to structured query language (sql)
- What does sql stand for?
- A structured query language – sql operators are
- Sql stands for structured query language
- Update sql command
- Structured query language (sql) is an example of a(n)
- Structured query language (sql) is an example of a(n)
- My structured query language
- Disadvantages of unstructured interviews
- Sa sd
- Lir
- Iterative query
- Query tree and query graph
- Query tree and query graph
- Inside the sql server query optimizer
- Sql injection
- Hammerdb
- Inside the sql server query optimizer
- Whoisactive sql query
- Sql insert update delete query
- Shrpe ratio
- Sql query
- Cosmos db query optimization
- Excel sql query
- Sql query for xml
- Sql server intelligent query processing
- Cobol area a and area b
- Structured programming tutorial
- Set serveroutput on
- Sql developer unit testing
- Object query language
- Google visualization api query language
- Similar image search
- Language integrated query developer
- Common query language
- Find the id name dept_name
- Google visualization api query language
- Formal query language in dbms
- Relational query language
- Relational algebra is a procedural language
- Standardized query language
- Common query language
- Microsoft excel conditional formatting
- Dicom structured report
- Structured poem examples
- Sonnet vs ballad
- What is structured analysis
- Spl labeling
- What is an example of a semi-structured interview?
- What is a structured questionnaire
- Structured application development
- Structured data types
- Processing nested loops
- Structured data capture
- Structured systems analysis and design methodology
- High level requirements
- Profilmate
- Structured problem solving
- Structured english immersion model
- Structured vs unstructured observation
- Angket terstruktur
- What is essentially a structured conversation
- Informative abstract example
- Non formal education meaning
- Structured essay type questions
- A questionnaire
-
- Emerging big data ecosystem
- Well-structured relation is
- What is qualitative questions
- Structured guidance and supports
- Describe the process specification structured decisions
- Interview method of data collection
- Structured specification
- Structured specification
- Chapter 4 requirements engineering
- Types of topologies of structured cables
- Structured vs object oriented programming
- Primary research interviews
- What is the purpose of a cause-effect text structure?
- Modern structured analysis
- Bigtable a distributed storage system for structured data
- Unstructured data to structured data conversion
- Structured literacy infographic
- Business analysis foundation and practitioner
- Structured vs object oriented approach
- Structured specification example
- Structured
- What is graphical modelling tool for structured analysis
- The primary tool used in structured design is a:
- Vtk structured grid example
- Warnier orr
- Class dan object
- Structured learning program
- Percy jackson cda
- Structured vlsi design
- Housing continuum
- Bigtable: a distributed storage system for structured data
- Structured algorithm
- Organically structured argument
- Dicom structured report
- Structured design methodology
- Parallel development process
- Structured transactions
- What is structured teaching
- What is structured english
- Structured cobol programming
- Structured academic controversy
- Ssadm data flow diagram
- Structured english is used to describe
- Structured system analysis and design methodology
- Structured exploratory testing
- Tradersonly review
- Tanenbaum structured computer organization
- Describe the process specification structured decisions.
- Do children learn through structured input?
- Bigtable: a distributed storage system for structured data
- Acc structured reporting
- Structured chart for course registration system
- Tanenbaum computer architecture
- Conclusion of case study
- Structured vector quantizers
- What is structured product labeling
- Structured light
- A class is an example of a structured data type.
- Language
- Situation complication question answer
- Hedging adalah
- Structured cabling standards 3 categories
- Structured english
- Structured english
- Structured english in system analysis and design
- Structured brainstorming
- Human vision vs computer vision
- Logic 8 system requirements
- Structured embroidered preserved
- Structured english in system analysis and design
- Example of structured programming
- Structured english
- Structured academic controversy worksheet
- Compile and go loader
- Structured and unstructured observation examples
- Structured interview
- Dicom structured report
- Structured problem solving process
- Bmj structured discussion
- Structured interview
- Language
- Advantages of observation
- Ospe and osce
- Structured support vector machine
- Adebals
- Examples of structured problems
- Objective structured clinical examination
- Semi-structured clinical interview for financial capacity
- Linguistics is descriptive not prescriptive
- Dicom structured reporting
- Ssasd
- Unstructured observation example
- Structured work system examples
- Michael jackson structured programming
- Unstructured and structured data
- Structured cobol programming
- Openlink structured data sniffer
- Structured vs unstructured observation
- Big data splunk
- Structured text array
- Structured data vs unstructured
- Acc structured reporting
- Dicom patient orientation
- Alpha real property investment advisers llp
- Structured thesaurus
- In a well structured relational database
- Structured asic