Relational Algebra (end) SQL Queries

1. Relational Algebra (end)SQL Queries Lecture #9

2. Expression Trees • Leaves are operands --- either variables standing for relations or particular, constant relations. • Interior nodes are operators, applied to their child or children.

3. Example • Using the relations Bars(name, addr) and Sells(bar, beer, price), find the names of all the bars that are either on Maple St. or sell Bud for less than $3.

4. UNION RENAMER(name) PROJECTname PROJECTbar SELECTaddr = “Maple St.” SELECT price<3 AND beer=“Bud” As a Tree: • Using the relations Bars(name, addr) and Sells(bar, beer, price), find the names of all the bars that are either on Maple St. or sell Bud for less than $3. Bars Sells

5. Example • Using Sells(bar, beer, price), find the bars that sell two different beers at the same price. • Strategy: by renaming, define a copy of Sells, called S(bar, beer1, price). The natural join of Sells and S consists of quadruples (bar, beer, beer1, price) such that the bar sells both beers at this price.

6. PROJECTbar SELECTbeer != beer1 JOIN RENAMES(bar, beer1, price) The Tree Sells Sells

7. Complex Queries Product ( pid, name, price, category, maker-cid) Purchase (buyer-ssn, seller-ssn, store, pid) Company (cid, name, stock price, country) Person(ssn, name, phone number, city) Note: • in Purchase: buyer-ssn, seller-ssn are foreign keys in Person, pid is foreign key in Product; • in Product maker-cid is a foreign key in Company Find phone numbers of people who bought gizmos from Fred. Find telephony products that somebody bought

8. seller-ssn=ssn pid=pid buyer-ssn=ssn Expression Tree Person Purchase Person Product Pname Pssn Ppid sname=fred sname=gizmo

9. Exercises Product ( pid, name, price, category, maker-cid) Purchase (buyer-ssn, seller-ssn, store, pid) Company (cid, name, stock price, country) Person(ssn, name, phone number, city) Ex #1: Find people who bought telephony products. Ex #2: Find names of people who bought American products Ex #3: Find names of people who bought American products and did not buy French products Ex #4: Find names of people who bought American products and they live in Champaign. Ex #5: Find people who bought stuff from Joe or bought products from a company whose stock prices is more than $50.

10. Operations on Bags (and why we care) • Union: {a,b,b,c} U {a,b,b,b,e,f,f} = {a,a,b,b,b,b,b,c,e,f,f} • add the number of occurrences • Difference: {a,b,b,b,c,c} – {b,c,c,c,d} = {a,b,b,d} • subtract the number of occurrences • Intersection: {a,b,b,b,c,c} {b,b,c,c,c,c,d} = {b,b,c,c} • minimum of the two numbers of occurrences • Selection: preserve the number of occurrences • Projection: preserve the number of occurrences (no duplicate elimination) • Cartesian product, join: no duplicate elimination Read the book for more detail

11. Summary of Relational Algebra • Why bother ? Can write any RA expression directly in C++/Java, seems easy. • Two reasons: • Each operator admits sophisticated implementations (think of , sC) • Expressions in relational algebra can be rewritten: optimized

12. Glimpse Ahead: Efficient Implementations of Operators • s(age >= 30 AND age <= 35)(Employees) • Method 1: scan the file, test each employee • Method 2: use an index on age • Which one is better ? Depends a lot… • Employees Relatives • Iterate over Employees, then over Relatives • Iterate over Relatives, then over Employees • Sort Employees, Relatives, do “merge-join” • “hash-join” • etc

13. Glimpse Ahead: Optimizations Product ( pid, name, price, category, maker-cid) Purchase (buyer-ssn, seller-ssn, store, pid) Person(ssn, name, phone number, city) • Which is better: sprice>100(Product) (Purchase scity=seaPerson) (sprice>100(Product) Purchase) scity=seaPerson • Depends ! This is the optimizer’s job…

14. Finally: RA has Limitations ! • Cannot compute “transitive closure” • Find all direct and indirect relatives of Fred • Cannot express in RA !!! Need to write C program

15. SQLSelect-From-Where StatementsMeaning of QueriesSubqueries

16. SQL Introduction Standard language for querying and manipulating data Structured Query Language Many standards out there: SQL92, SQL2, SQL3, SQL99 Vendors support various subsets of these, but all of what we’ll be talking about.

17. Why SQL? • SQL is a very-high-level language, in which the programmer is able to avoid specifying a lot of data-manipulation details that would be necessary in languages like C++. • What makes SQL viable is that its queries are “optimized” quite well, yielding efficient query executions.

18. Select-From-Where Statements • The principal form of a query is: SELECT desired attributes FROM one or more tables WHERE condition about tuples of the tables

19. Single-Relation Queries

20. Our Running Example • Most of our SQL queries will be based on the following database schema. • Underline indicates key attributes. Beers(name, manf) Bars(name, addr, license) Drinkers(name, addr, phone) Likes(drinker, beer) Sells(bar, beer, price) Frequents(drinker, bar)

21. Example • Using Beers(name, manf), what beers are made by Anheuser-Busch? SELECT name FROM Beers WHERE manf = ‘Anheuser-Busch’;

22. Result of Query name ‘Bud’ ‘Bud Lite’ ‘Michelob’ The answer is a relation with a single attribute, name, and tuples with the name of each beer by Anheuser-Busch, such as Bud.

23. Meaning of Single-Relation Query • Begin with the relation in the FROM clause. • Apply the selection indicated by the WHERE clause. • Apply the extended projection indicated by the SELECT clause.

24. Operational Semantics • To implement this algorithm think of a tuple variable ranging over each tuple of the relation mentioned in FROM. • Check if the “current” tuple satisfies the WHERE clause. • If so, compute the attributes or expressions of the SELECT clause using the components of this tuple.

25. * In SELECT clauses • When there is one relation in the FROM clause, * in the SELECT clause stands for “all attributes of this relation.” • Example using Beers(name, manf): SELECT * FROM Beers WHERE manf = ‘Anheuser-Busch’;

26. Result of Query: name manf ‘Bud’ ‘Anheuser-Busch’ ‘Bud Lite’ ‘Anheuser-Busch’ ‘Michelob’ ‘Anheuser-Busch’ Now, the result has each of the attributes of Beers.

27. Another Example Company(sticker, name, country, stockPrice) Find all US companies whose stock is > 50: Output schema: R(sticker, name, country, stockPrice) SELECT *FROM CompanyWHERE country=“USA” AND stockPrice > 50

28. Renaming Attributes • If you want the result to have different attribute names, use “AS <new name>” to rename an attribute. • Example based on Beers(name, manf): SELECT name AS beer, manf FROM Beers WHERE manf = ‘Anheuser-Busch’

29. Result of Query: beer manf ‘Bud’ ‘Anheuser-Busch’ ‘Bud Lite’ ‘Anheuser-Busch’ ‘Michelob’ ‘Anheuser-Busch’

30. Expressions in SELECT Clauses • Any expression that makes sense can appear as an element of a SELECT clause. • Example: from Sells(bar, beer, price): SELECT bar, beer, price * 120 AS priceInYen FROM Sells;

31. Result of Query bar beer priceInYen Joe’s Bud 300 Sue’s Miller 360 … … …

32. Another Example: Constant Expressions • From Likes(drinker, beer): SELECT drinker, ‘likes Bud’ AS whoLikesBud FROM Likes WHERE beer = ‘Bud’;

33. Result of Query drinker whoLikesBud ‘Sally’ ‘likes Bud’ ‘Fred’ ‘likes Bud’ … …

34. Complex Conditions in WHERE Clause • From Sells(bar, beer, price), find the price Joe’s Bar charges for Bud: SELECT price FROM Sells WHERE bar = ‘Joe’’s Bar’ AND beer = ‘Bud’;

35. Selections What you can use in WHERE: attribute names of the relation(s) used in the FROM. comparison operators: =, <>, <, >, <=, >= apply arithmetic operations: stockprice*2 operations on strings (e.g., “||” for concatenation). Lexicographic order on strings. Pattern matching: s LIKE p Special stuff for comparing dates and times.

36. Important Points • Two single quotes inside a string represent the single-quote (apostrophe). • Conditions in the WHERE clause can use AND, OR, NOT, and parentheses in the usual way boolean conditions are built. • SQL is case-insensitive. In general, upper and lower case characters are the same, except inside quoted strings.

37. Patterns • WHERE clauses can have conditions in which a string is compared with a pattern, to see if it matches. • General form: <Attribute> LIKE <pattern> or <Attribute> NOT LIKE <pattern> • Pattern is a quoted string with % = “any string”; _ = “any character.”

38. Example • From Drinkers(name, addr, phone) find the drinkers with exchange 555: SELECT name FROM Drinkers WHERE phone LIKE ‘%555-_ _ _ _’;

39. The LIKE operator • s LIKE p: pattern matching on strings • p may contain two special symbols: • % = any sequence of characters • _ = any single character Company(sticker, name, address, country, stockPrice) Find all US companies whose address contains “Mountain”: SELECT *FROM CompanyWHERE country=“USA” ANDaddress LIKE “%Mountain%”

40. Motivating Example for Next Few Slides • From the following Sells relation: bar beer price .... .... ... SELECT bar FROM Sells WHERE price < 2.00 OR price >= 2.00;

41. Null Values

42. NULL Values • Tuples in SQL relations can have NULL as a value for one or more components. • Meaning depends on context. Two common cases: • Missing value : e.g., we know Joe’s Bar has some address, but we don’t know what it is. • Inapplicable : e.g., the value of attribute spouse for an unmarried person.

43. Comparing NULL’s to Values • The logic of conditions in SQL is really 3-valued logic: TRUE, FALSE, UNKNOWN. • When any value is compared with NULL, the truth value is UNKNOWN. • But a query only produces a tuple in the answer if its truth value for the WHERE clause is TRUE (not FALSE or UNKNOWN).

44. Three-Valued Logic • To understand how AND, OR, and NOT work in 3-valued logic, think of TRUE = 1, FALSE = 0, and UNKNOWN = ½. • AND = MIN; OR = MAX, NOT(x) = 1-x. • Example: TRUE AND (FALSE OR NOT(UNKNOWN)) = MIN(1, MAX(0, (1 - ½ ))) = MIN(1, MAX(0, ½ ) = MIN(1, ½ ) = ½.

45. UNKNOWN UNKNOWN UNKNOWN Surprising Example • From the following Sells relation: bar beer price Joe’s Bar Bud NULL SELECT bar FROM Sells WHERE price < 2.00 OR price >= 2.00;

46. Reason: 2-Valued Laws != 3-Valued Laws • Some common laws, like the commutativity of AND, hold in 3-valued logic. • But others do not; example: the “law of excluded middle,” p OR NOT p = TRUE. • When p = UNKNOWN, the left side is MAX( ½, (1 – ½ )) = ½ != 1.

47. Null Values • If x=Null then 4*(3-x)/7 is still NULL • If x=Null then x=“Joe” is UNKNOWN • Three boolean values: • FALSE = 0 • UNKNOWN = 0.5 • TRUE = 1

48. Null Value Logic • C1 AND C2 = min(C1, C2) • C1 OR C2 = max(C1, C2) • NOT C1 = 1 – C1 SELECT * FROM Person WHERE (age < 25) AND (height > 6 OR weight > 190) Semantics of SQL: include only tuples that yield TRUE

49. Null Values Unexpected behavior: SELECT * FROM Person WHERE age < 25 OR age >= 25 Some Persons are not included !

50. Testing for Null Can test for NULL explicitly: • x IS NULL • x IS NOT NULL SELECT * FROM Person WHERE age < 25 OR age >= 25 OR age IS NULL Now it includes all Persons