SQL 2 - The Sequel

1.  SQL 2 – The Sequel  The important thing is not to stop questioning. Albert Einstein CS 186, Spring 2007, Lecture 8 R&G, Chapter 5 Mary Roth Life is just a bowl of queries. -Anon (not Forrest Gump)

2. Administrivia • Homework 1 due Tuesday, Feb 13 10 p.m. • Coming up: • Homework 2 handed out Feb 13 • Midterm 1: in class February 22 • Questions?

3. Review: SQL DML • DML includes 4 main statements: SELECT (query), INSERT, UPDATE and DELETE • e.g: PROJECT SELECT S.name, E.cid FROM Students S, Enrolled E WHERE S.sid = E.sid AND S.age=19 JOIN SELECT

4. Example: Find sailors who have reserved a red and a green boat Now let’s do this with a self-join SELECT R.sid FROM Boats B,Reserves R WHERE B.color = ‘red’ AND R.bid=B.bid INTERSECT SELECT R.sid FROM Boats B,Reserves R WHERE B.color = ‘green’ AND R.bid=B.bid Boats Reserves  =

5. Find sids of sailors who’ve reserved a red and a green boat SELECT R1.sid FROM Boats B1, Reserves R1, Boats B2, Reserves R2 WHERE B1.color = ‘red’ AND B1.bid=R1.bid AND B2.color = ‘green’ AND B2.bid=R2.bid AND R1.sid=R2.sid Find red reserved boats Find green reserved boats Find matching green and red reserved boats =

6. Nested Queries e.g. Find the names of sailors who’ve reserved boat #103: • The WHERE clause can itself contain an SQL query! (Actually, so can FROM and HAVING clauses can too) Reserves SELECT S.sname FROM Sailors S WHERE S.sid IN (SELECT R.sid FROM Reserves R WHERE R.bid=103) Sailors First compute the set of all sailors that have reserved boat 103… …and then check each the sid of each tuple in Sailors to see if it is in S1 S S S

7. Nested Queries • This nestedquery was uncorrelated because the subquery does not refer to anything in the enclosing query SELECT S.sname FROM Sailors S WHERE S.sid IN (SELECT R.sid FROM Reserves R WHERE R.bid=103) • It can evaluated once and then checked for each tuple in enclosing query

8. Nested Queries with correlation • Nestedqueries can also be correlated; the subquery refers to the enclosing query SELECT S.sname FROM Sailors S WHERE EXISTS (SELECT * FROM Reserves R WHERE R.bid=103 AND R.sid=S.sid) • The subquery must be re-evaluated for each tuple in enclosing query • EXISTS is a set operator that is true if result of set expression has at least one tuple

9. Nested Queries e.g. Find the names of sailors who’ve reserved boat #103: Notice that this query computes the same answer as the previous query! SELECT S.sname FROM Sailors S WHERE EXISTS (SELECT * FROM Reserves R WHERE R.bid=103 AND R.sid=S.sid) 3 2 1 Sailors Reserves S 2 103 9/13 S S

10. Set-Comparison Operators • <tuple expression> IN <set expression> • True if <tuple> is a member of <set> • Also, NOT IN • EXISTS <set expression> • True if <set expression> evaluates to a set with at least one member • Also, NOT EXISTS • UNIQUE <set expression> • True if <set expression> evaluates to a set with no duplicates; each row can appear exactly once • Also, NOT UNIQUE • <tuple expression> comparison op ANY <set expression> • True if <set expression> contains at least one member that makes the comparison true • Also, op ALL

11. Use NOT Exists for Division Recall: X/Y means only give me X tuples that have a match in Y. Find sailors who’ve reserved all boats. X = set of sailors and Y = set of all boats with reservations. SELECT S.sname 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)) Find Sailors S such that ... there is no boat B... without a reservation by Sailor S

12. Division SELECT S.sname 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)) 101 103 1 3 2 Reserves Sailors Boats R S R B S R B R S R

13. UNIQUE Find the names of sailors who’ve reserved boat #103 exactly once SELECT S.sname FROM Sailors S WHERE UNIQUE (SELECT sid, bid FROM Reserves R WHERE R.bid=103 AND S.sid=R.sid) 3 2 1 Sailors Reserves S 2 103 9/13 S S

14. ANY Find sailors whose rating is greater than that of some sailor called Bilbo: Correlated or uncorrelated? SELECT * FROM Sailors S WHERE S.rating > ANY(SELECT S2.rating FROM Sailors S2 WHERE S2.sname=‘Bilbo’) Uncorrelated! S1 S2

15. Intermission

16. Aggregate Operators • Very powerful; enables computations over sets of tuples SELECT COUNT (*) FROM Sailors S • COUNT: returns a count of tuples in the set • AVG: returns average of column values in the set SELECT AVG (S.age) FROM Sailors S WHERE S.rating=10 • SUM: returns sum of column values in the set • MIN, MAX: returns min (max) value of column values in a set. • DISTINCT can be added to COUNT, AVG, SUM to perform computation only over distinct values. SELECT AVG(DISTINCT S.age) FROM Sailors S WHERE S.rating=10

17. Aggregate Operators Find name and age of the oldest sailor(s) X Sailors SELECT S.sname, MAX (S.age) FROM Sailors S What will the result be? Not legal syntax; no other columns allowed in SELECT clause without a GROUP BY clause(we’ll learn about those next)

18. Aggregate Operators Find name and age of the oldest sailor(s) Instead: SELECT S.sname, S.age FROM Sailors S WHERE S.age = (SELECT MAX (S2.age) FROM Sailors S2) And then find the sailors(s) of that age… Find the maximum age…

19. GROUP BY and HAVING • So far, we’ve seen aggregate operators applied to all tuples. • What if we want to apply ops to each of several groups of tuples? • Consider: Find the age of the youngest sailor for each rating level. • In general, we don’t know how many rating levels exist, and what the rating values for these levels are! • Suppose we know that rating values go from 1 to 10; we can write 10 queries that look like this (!): SELECT MIN (S.age) FROM Sailors S WHERE S.rating = i For i = 1, 2, ... , 10:

20. Queries With GROUP BY • To generate values for a column based on groups of rows, use aggregate functions in SELECT statements with the GROUP BY clause target-listcontains: • list of column names from grouping–list • terms with aggregate operations (e.g., MIN (S.age)). Returning 1 row per group And finally compute aggregate function over each group… SELECT [DISTINCT] target-list FROM relation-list [WHERE qualification] GROUP BYgrouping-list First select these rows… Then group them by the values in these columns…

21. Group By Example For each rating, find the age of the youngest sailor with age  18 Sailors SELECTS.rating, MIN (S.age) FROM Sailors S WHERE S.age >= 18 GROUP BY S.rating Group 1 Group 2 Group 3

22. Find the number of reservations for each red boat. SELECT B.bid, COUNT(*)AS tot_res FROM Boats B, Reserves R WHERE R.bid=B.bid AND B.color=‘red’ GROUP BY B.bid Boats Reserves

23. Queries With GROUP BY and HAVING SELECT [DISTINCT] target-list FROM relation-list WHERE qualification GROUP BY grouping-list HAVING group-qualification • Use the HAVING clause with the GROUP BY clause to restrict which group-rows are returned in the result set

24. 3 2 Answer Find the age of the youngest sailor with age  18, for each rating with at least 2 such sailors SELECT S.rating, MIN (S.age) FROM Sailors S WHERE S.age >= 18 GROUP BY S.rating HAVING COUNT (*) > 1

25. Sailors Sailors who have reserved all boats SELECT S.name FROM Sailors S, reserves R WHERE S.sid = R.sid GROUP BY S.name, S.sid HAVING COUNT(DISTINCT R.bid) = ( Select COUNT (*) FROM Boats) Boats Reserves

26. More about Joins SELECT (column_list) FROM table_name [INNER | {LEFT |RIGHT | FULL } OUTER] JOIN table_name ON qualification_list WHERE … Explicit join semantics needed unless it is an INNER join (INNER is default)

27. Default semantics: Inner Join Only rows that match search conditions are returned. SELECT s.sid, s.name, r.bid FROM Sailors s INNER JOIN Reserves r ON s.sid = r.sid Returns only those sailors who have reserved boats SQL-92 also allows: SELECT s.sid, s.name, r.bid FROM Sailors s NATURAL JOIN Reserves r “NATURAL” means equi-join for each pair of attributes with the same name

28. SELECT s.sid, s.name, r.bidFROM Sailors s INNER JOIN Reserves rON s.sid = r.sid

29. Left Outer Join Left Outer Join returns all matched rows, plus all unmatched rows from the table on the left of the join clause (use nulls in fields of non-matching tuples) SELECT s.sid, s.name, r.bid FROM Sailors s LEFT OUTER JOIN Reserves r ON s.sid = r.sid Returns all sailors & information on whether they have reserved boats

30. SELECT s.sid, s.name, r.bidFROM Sailors s LEFT OUTER JOIN Reserves rON s.sid = r.sid

31. Right Outer Join Right Outer Join returns all matched rows, plus all unmatched rows from the table on the right of the join clause SELECT r.sid, b.bid, b.name FROM Reserves r RIGHT OUTER JOIN Boats b ON r.bid = b.bid Returns all boats & information on which ones are reserved.

32. SELECT r.sid, b.bid, b.nameFROM Reserves r RIGHT OUTER JOIN Boats bON r.bid = b.bid

33. Full Outer Join Full Outer Join returns all (matched or unmatched) rows from the tables on both sides of the join clause SELECT r.sid, b.bid, b.name FROM Reserves r FULL OUTER JOIN Boats b ON r.bid = b.bid Returns all boats & all information on reservations

34. SELECT r.sid, b.bid, b.nameFROM Reserves r FULL OUTER JOIN Boats bON r.bid = b.bid Note: in this case it is the same as the ROJ because bid is a foreign key in reserves, so all reservations must have a corresponding tuple in boats.

35. INSERT INSERT [INTO] table_name [(column_list)] VALUES ( value_list) INSERT [INTO] table_name [(column_list)] <select statement> INSERT INTO Boats VALUES ( 105, ‘Clipper’, ‘purple’) INSERT INTO Boats (bid, color) VALUES (99, ‘yellow’) “bulk insert” from one table to another (must be type compatible): INSERT INTO TEMP(bid) SELECT r.bid FROM Reserves R WHERE r.sid = 22; “bulk insert” from files (in Postgres) Copy

36. DELETE & UPDATE DELETE [FROM] table_name [WHERE qualification] DELETE FROM Boats WHERE color = ‘red’ DELETE FROM Boats b WHERE b. bid = (SELECT r.bid FROM Reserves R WHERE r.sid = 22) Can also modify tuples using UPDATE statement. UPDATE Boats SET Color = “green” WHERE bid = 103;

37. Null Values • Values are sometimes • unknown(e.g., a rating has not been assigned or • inapplicable(e.g., no spouse’s name). • SQL provides a special value null for such situations. • The presence of null complicates many issues. E.g.: • Special operators needed to check if value is/is not null. • “rating>8” - true or false when rating is null? What about AND, OR and NOT connectives? • Need a 3-valued logic(true, false and unknown). • Meaning of constructs must be defined carefully. (e.g., WHERE clause eliminates rows that don’t evaluate to true.) • New operators (in particular, outer joins) possible/needed.

38. Null Values – 3 Valued Logic (null > 0) (null + 1) (null = 0) null AND true is null is null is null is null T F Null T T T F F F T F Null Null F Null T Null Null