OCL4 Oracle 10g: SQL

1
OCL4 Oracle 10gSQL PL/SQLSession 4

• Matthew P. Johnson
• CISDD, CUNY
• June, 2005

2
Agenda

• Evals
• Nulls
• Kinds of joins
• Set operations in SQL
• Grouping Aggregation
• Updates
• Creating tables

3
New topic Nulls in SQL

• If we dont 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

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

5
Null values in boolean expressions

• C1 AND C2 min(C1, C2)
• C1 OR C2 max(C1, C2)
• NOT C1 1 C1
• height gt 6 UNKNOWN
• ? UNKNOWN OR weight gt 190 UNKOWN
• ? (age lt 25) AND UNKNOWN UNKNOWN

SELECT FROM Person WHERE (age lt 25) AND
(height gt 6 OR weight gt 190)
E.g.age20heightNULLweight180
6
Comparing null and non-nulls

• Unexpected behavior
• Some Persons are not included!
• The trichotomy law does not hold!

SELECT FROM Person WHERE age lt 25 OR age
gt 25
7
Testing for null values

• Can test for NULL explicitly
• x IS NULL
• x IS NOT NULL
• But
• x NULL is always unknown
• Now it includes all Persons

SELECT FROM Person WHERE age lt 25 OR age
gt 25 OR age IS NULL
8
Null/logic review

• TRUE AND UNKNOWN ?
• TRUE OR UNKNOWN ?
• UNKNOWN OR UNKNOWN ?
• X NULL ?

9
Example with nulls

• look at emp table
• Select names, salaries, commissions, total
  salaries
• What if commission is null?
• nvl

10
Joins operations

• Variations
• Cross join (Cartesian product)
• Join On
• Natural join
• Outer join
• Apply to relations appearing in selections

11
Cross join - example
MovieStar
MovieExec
12
Cross join example
SELECT FROM MovieStar CROSS JOIN MovieExec
13
Join On example
SELECT FROM MovieStar JOIN MovieExec ON
MovieStar.Name ltgt MovieExec.Name
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)

15
Example - Natural join
MovieStar
MovieExec
SELECT FROM MovieStar NATURAL JOIN MovieExec
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

17
Outer Join - Example
SELECT FROM MovieStar LEFT OUTER JOIN MovieExec
ON MovieStart.nameMovieExec.name
SELECT FROM MovieStar RIGHT OUTER JOIN
MovieExec ON MovieStart.nameMovieExec.name
18
Outer Join - Example
MovieStar
MovieExec
SELECT FROM MovieStar FULL OUTER JOIN MovieExec
ON MovieStart.nameMovieExec.name
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
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

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

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

23
Old-style outer joins in Oracle

• Outer joins can also be done with the old-style
  syntax, but with the ()
• WHERE A.attB.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

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

25
More live examples

• Inner joins require an ON clause
• Like a where clause
• Arbitrary boolean expression
• If always true (11), reduces to cross join
• New compar op BETWEEN
• a between 5 and 10 ? a gt 5 and a lt 10
• Q produce a list of employees with their salary
  grades
• emp, salgrade

26
Review

• Examples from sqlzoo.net

SELECT L FROM R1, , Rn WHERE C
PL(sC(R1 x Rn)
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 buyername AND store'The Wiz')
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

29
Set operations - example
R
S
R ? S
30
Set operations - example
R
S
R ? S
31
Set operations - example
R
S
R - S
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')
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'
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'
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'
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')
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')
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')
39
Union-Compatibility

• Situation Cust1(name,address,), Cust2(name,)
• Want list of all customer names and addresses
  (if known)
• Cant do
• Both tables must have same sequence of types
• Applies to all set ops

(SELECT name, address FROM
Cust1) UNION (SELECT name FROM Cust2)
40
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)
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

42
Confession

• Relations arent really sets!
• Theyre bags!

43
Bag theory

• SELECT/WHERE no duplicate elimination
• Cross, join no duplicate elimination
• R1xR2 R1R2
• Can convert to sets when necessary
• DISTINCT
• Allowing duplicates by default is cheaper
• Union
• Projection
• How hard is removing duplicates?

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

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

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.AS.A OR
R.AT.A
47

• Labs
• Tomorrow
• Grouping/Aggregation
• PL/SQL