OCL4 Oracle 10g: SQL & PL/SQL Session #4

1. OCL4 Oracle 10g:SQL & PL/SQLSession #4 Matthew P. Johnson CISDD, CUNY June, 2005 Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

2. Agenda • Evals • Nulls • Kinds of joins • Set operations in SQL • Grouping & Aggregation • Updates • Creating tables Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

3. New topic: Nulls in SQL • If we don’t have a value, can put a NULL • Null can mean several things: • Value does not exists • Value exists but is unknown • Value not applicable • The schema specifies whether null is allowed for each attribute • NOT NULL if not allowed • By default, null is allowed Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

4. Null Values • x = NULL  4*(3-x)/7 = NULL • x = NULL  x + 3 – x = NULL • x = NULL  3 + (x-x) = NULL • x = NULL x = 'Joe' is UNKNOWN • In general: no row using null fields appear in the selection test will pass the test • With one exception • Pace Boole, SQL has three boolean values: • FALSE = 0 • TRUE = 1 • UNKNOWN = 0.5 Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

5. Null values in boolean expressions • C1 AND C2 = min(C1, C2) • C1 OR C2 = max(C1, C2) • NOT C1 = 1 – C1 • height > 6 = UNKNOWN •  UNKNOWN OR weight > 190 = UNKOWN •  (age < 25) AND UNKNOWN = UNKNOWN SELECT * FROM Person WHERE (age < 25) AND (height > 6 OR weight > 190) E.g.:age=20height=NULLweight=180 Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

6. Comparing null and non-nulls • Unexpected behavior: • Some Persons are not included! • The “trichotomy law” does not hold! SELECT * FROM Person WHERE age < 25 OR age >= 25 Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

7. Testing for null values • Can test for NULL explicitly: • x IS NULL • x IS NOT NULL • But: • x = NULLis always unknown • Now it includes all Persons SELECT * FROM Person WHERE age < 25 OR age >= 25 OR age IS NULL Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

8. Null/logic review • TRUE AND UNKNOWN = ? • TRUE OR UNKNOWN = ? • UNKNOWN OR UNKNOWN = ? • X = NULL = ? Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

9. Example with nulls • look at emp table • Select names, salaries, commissions, total salaries • What if commission is null? • nvl Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

10. Joins operations • Variations: • Cross join (Cartesian product) • Join … On • Natural join • Outer join • Apply to relations appearing in selections Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

11. Cross join - example MovieStar MovieExec Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

12. Cross join – example SELECT * FROM MovieStar CROSS JOIN MovieExec Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

13. Join … On: example SELECT * FROM MovieStar JOIN MovieExec ON MovieStar.Name <> MovieExec.Name Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

14. Natural Joins MovieStar(name, address, gender, birthdate) MovieExec(name, address, networth) Natural Join: …MovieStar Natural Join MovieExec • Results in: list of individuals who are movie-stars as well as executives: (Name, address, gender, birthdate, networth) Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

15. Example - Natural join MovieStar MovieExec SELECT * FROM MovieStar NATURAL JOIN MovieExec Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

16. Outerjoin • Like L R except that dangling tuples are included, padded with nulls • Left outerjoin: dangling tuples from left are included • Nulls appear “on the right” • Right outerjoin: dangling tuples from right are included • Nulls appear “on the left” Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

17. Outer Join - Example SELECT * FROM MovieStar LEFT OUTER JOIN MovieExec ON MovieStart.name=MovieExec.name SELECT * FROM MovieStar RIGHT OUTER JOIN MovieExec ON MovieStart.name=MovieExec.name Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

18. Outer Join - Example MovieStar MovieExec SELECT * FROM MovieStar FULL OUTER JOIN MovieExec ON MovieStart.name=MovieExec.name Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

19. New-style join syntax • Old-style syntax simply lists tables separated by commas: • New-style makes the join explicit: SELECT * FROM A,B WHERE …; SELECT * FROM A JOIN B ON … WHERE …; Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

20. New-style join syntax • Functionally equivalent to old-style, but perhaps more elegant • Introduced in Oracle 8i, MySQL 3.x/4.x • Older versions / other DBMSs may only support old-style syntax Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

21. New-style join types • cross joins (simplest): • …FROM A CROSS JOIN B • Inner joins (regular joins): • …FROM A [INNER] JOIN B ON … • Natural join: • …FROM A NATURAL JOIN B; • Joins on common fields and merges • Outer joins Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

22. New-style outer joins • Outer joins may be left, right, or middle • FROM A LEFT [OUTER] JOIN B; • FROM A RIGHT [OUTER] JOIN B; • FROM A FULL [OUTER] JOIN B; • “OUTER” is optional • If “OUTER” is included, then “FULL” is the default • Q: How to remember left v. right? • A: It indicates the side whose rows are always included Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

23. Old-style outer joins in Oracle • Outer joins can also be done with the old-style syntax, but with the (+) • …WHERE A.att=B.att(+) corresponds to: • …FROM A LEFT JOIN B; • The (+) is applied to all B attributes referred to in the WHERE clause • Q: How to remember which side gets the (+)? • A: The side that gets null rows “added” Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

24. Live examples • Examples from sqlzoo.net • Q: produce a list of employees and their bosses • What if no boss? Or no subordinate? • Joins on emp, emp man: • Comma-based • Inner • Natural • Cross • Outer – left, right, full Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

25. More live examples • Inner joins require an ON clause • Like a where clause • Arbitrary boolean expression • If always true (1=1), reduces to cross join • New compar op: BETWEEN • a between 5 and 10 a >= 5 and a <= 10 • Q: produce a list of employees with their salary grades • emp, salgrade Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

26. Review • Examples from sqlzoo.net SELECTL FROM R1, …, Rn WHERE C PL(sC(R1 x … Rn) Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

27. New topic: Set/bag ops in SQL • Orthodox SQL has set operators: • UNION, INTERSECT, EXCEPT • And bag operators: • UNION ALL, INTERSECT ALL, EXCEPT ALL • These operators are applied to queries: (SELECT name FROM Person WHERE City='New York') UNION (SELECT name FROM Person, Purchase WHERE buyer=name AND store='The Wiz') Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

28. New topic: R.A./SQL Set Operators • Relations are sets  have set-theoretic ops • Venn diagrams • Union: R1  R2 • Example: • ActiveEmployees  RetiredEmployees • Difference: R1 – R2 • Example: • AllEmployees – RetiredEmployees = ActiveEmployees • Intersection: R1  R2 • Example: • RetiredEmployees UnionizedEmployees Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

29. Set operations - example R: S: R  S: Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

30. Set operations - example R: S: R  S: Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

31. Set operations - example R: S: R - S: Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

32. Set ops in SQL • Orthodox SQL has set operators: • UNION, INTERSECT, EXCEPT • Oracle SQL uses MINUS rather than EXCEPT • See the Ullman page on more differences • These ops applied to queries: (SELECT name FROM Person WHERE City = 'New York') INTERSECT (SELECT custname FROM Purchase WHERE store='Kim''s') Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

33. Boat examples • Reserve(ssn,bmodel,color) • Q: Find ssns of sailors who reserved red boats or green boats SELECT DISTINCT ssn FROM reserve WHERE color = 'red' OR color = 'green' Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

34. Boat examples • Reserve(ssn,bmodel,color) • Q: Find ssns of sailors who reserved red boats and green boats SELECT DISTINCT ssn FROM reserve WHERE color = 'red' AND color = 'green' Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

35. Boat examples • Reserve(ssn,bmodel,color) • Q: Find ssns of sailors who reserved red boats and green boats SELECT DISTINCT r1.ssn FROM reserve r1, reserve r2 WHERE r1.ssn = r2.ssn AND r1.color = 'red' AND r2.color = 'green' Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

36. Boat examples • Reserve(ssn,bmodel,color) • Q: Find ssns of sailors who reserved red boats and green boats (SELECT DISTINCT ssn FROM reserve WHERE color = 'red') INTERSECT(SELECT DISTINCT ssn FROM reserve WHERE color = 'green') Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

37. Boat examples • Reserve(ssn,bmodel,color) • Q: Find ssns of sailors who reserved red boats or green boats (SELECT DISTINCT ssn FROM reserve WHERE color = 'red') UNION (SELECT DISTINCT ssn FROM reserve WHERE color = 'green') Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

38. Boat examples • Reserve(ssn,bmodel,color) • Q: Find ssns of sailors who reserved red boats but not green boats (SELECT DISTINCT ssn FROM reserve WHERE color = 'red') EXCEPT (SELECT DISTINCT ssn FROM reserve WHERE color = 'green') Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

39. Union-Compatibility • Situation: Cust1(name,address,…), Cust2(name,…) • Want: list of all customer names and addresses (if known) • Can’t do: • Both tables must have same sequence of types • Applies to all set ops (SELECT name, address FROM Cust1) UNION (SELECT name FROM Cust2) Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

40. Result(name, address) Union-Compatibility • Situation: Cust1(name,address,…), Cust2(name,…) • Want: list of all customer names and addresses (if known) • But can do: • Resulting field names taken from first table (SELECT name, address FROM Cust1) UNION (SELECT name, '(N/A)' FROM Cust2) Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

41. Set/bag ops in Oracle SQL • Oracle SQL support uses MINUS rather than EXCEPT • Oracle SQL supports bag op UNION ALL but not INTERSECT ALL or MINUS ALL • See the Ullman page on more differences Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

42. Confession Relations aren’t really sets! They’re bags! Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

43. Bag theory • SELECT/WHERE: no duplicate elimination • Cross, join: no duplicate elimination • |R1xR2| = |R1|*|R2| • Can convert to sets when necessary • DISTINCT • Allowing duplicates by default is cheaper • Union • Projection • How hard is removing duplicates? Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

44. Bag theory • Bags: like sets but elements may repeat • “multisets” • Set ops change somewhat when applied to bags • intuition: pretend identical elements are distinct • {a,b,b,c}  {a,b,b,b,e,f,f} = {a,a,b,b,b,b,b,c,e,f,f} • {a,b,b,b,c,c} – {b,c,c,c,d} = {a,b,b} • {a,b,b,b,c,c}  {b,c,c,c,d} = {b,c,c} Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

45. Some surprises in bag theory • Be careful about your set theory laws – not all hold in bag theory • (R  S) – T = (R – T)  (S – T) • always true in set theory • But true in bag theory? • suppose x is in R, S and T Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

46. First (?) Unintuitive SQLism • Looking for R  (S  T) • But what happens if T is empty? • See transcript of this in Oracle on sales SELECT R.A FROM R, S, T WHERE R.A=S.A OR R.A=T.A Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

47. Labs… • Tomorrow: • Grouping/Aggregation • PL/SQL Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005