OCL2 Oracle 10g: SQL

1
OCL2 Oracle 10gSQL PL/SQLSession 4

• Matthew P. Johnson
• CISDD, CUNY
• Fall, 2004

2
Review

• Examples from sqlzoo.net

SELECT L FROM R1, , Rn WHERE C
PL(sC(R1 x Rn)
3
Questions from last time

• Q What about double quotes?
• A Cant be used in place of single quotes
• But can be used when Oracle would otherwise
  misparse your command, e.g.
• Names with spaces
• create table bad table name (a int, b int)
• Reserved words as names
• create table badfieldname(from int, b int)

4
Questions from last time

• Q Can an escape char be an escape string?
• A No.

SQLgt select from newtable where a like '\'
escape '\' A B ----------
---------- hi there SQLgt select from
newtable where a like '\' escape '\\' select
from newtable where a like '\' escape '\\'

ERROR at line 1 ORA-01425 escape character
must be character string of length 1
5
Case-sensitivity

• By default, all matches and comparisons are
  case-sensitive
• If want case-insensitive, some options
• Convert all to upper or lower case
• SQLgt select from emp where upper(ename) like
  upper('la')
• Create a function index
• Maybe later
• Modify the nls_sort setting
• SQLgt alter session set nls_sortbinary_ci
• The other values binary, binary_ai

6
Disambiguation in Oracle SQL

• Can rename fields by
• Select name as n
• Select name n
• But not by
• Select namen
• Can rename relations only by
• from tab t1, tab t2
• Lesson if you get errors, remove all s, ASs

7
Disambiguation in Oracle SQL

• Every selected field must be unambiguous
• For R(A,B),
• Select A from R, R
• ? Select R1.A from R R1, R R2
• Consider
• Why?
• is shorthand for all fields, each must be
  unambiguous
• ? Select from R R1, R R2

SQLgt Select from R, R Select from R, R
ERROR at line 1 ORA-00918 column ambiguously
defined
8
SQL e.g.

• Acc(name,ssn,balance)
• Q Who has the largest balance?
• Conceptually
• Pname(Acc) - Pa2.name(sa2.bal lt Acc.bal(Acc x
  ra2(Acc)))
• In SQL?

9
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
• Otherwise, null is allowed

10
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 use null fields appear in the
  selection test will pass the test
• Pace Boole, SQL has three boolean values
• FALSE 0
• TRUE 1
• UNKNOWN 0.5

11
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.age20height is NULLweight180
12
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
13
Testing for null values

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

SELECT FROM Person WHERE age lt 25 OR age
gt 25 OR age IS NULL
14
Example with nulls

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

15
Evaluation strategies for SQL queries

• Semantics of a SQL query defined in terms of the
  following conceptual evaluation strategy
• Compute the cross-product of relation-list in
  FROM clause
• Discard resulting tuples if they fail WHERE
  clause
• Delete attributes that are not in SELECT clause
• If DISTINCT is specified, eliminate duplicate
  rows
• Often the least efficient way to compute a query!
• Optimizer finds better ways, but result is the
  same

16
Case-sensitivity redux

• By default, all matches and comparisons are
  case-sensitive
• If want case-insensitive, some options
• Convert all to upper or lower case
• SQLgt select from emp where upper(ename)
  upper(blake')
• Create a function index
• Maybe later
• Modify the nls_sort setting
• SQLgt alter session set nls_sortbinary_ci
• SQLgt alter session set nls_compansi
• The other values binary, binary_ai

17
Subqueries

• Powerful feature of SQL one clause can contain
  other SQL queries!
• So can FROM and HAVING clauses
• Several ways
• Selection ? single constant (scalar) in WHERE
• Selection ? relation in WHERE
• Selection ? relation in FROM
• Etc.

18
Subquery motivation

• Consider standard multi-table example
• Purchase(prodname, buyerssn, etc.)
• Person(name, ssn, etc.)
• What did Conrad buy?
• As usual, need to AND on equality identifying
  ssns row and buyerssns row

SELECT Purchase.prodnameFROM Purchase,
PersonWHERE buyerssn ssn AND name Conrad
19
Subquery motivation

• Purchase(prodname, buyerssn, etc.)
• Person(name, ssn, etc.)
• What did Conrad buy?
• Natural intuition
• 1. Go find Conrads ssn
• 2. Then find purchases

SELECT ssnFROM PersonWHERE name Conrad
SELECT Purchase.prodnameFROM Purchase,
PersonWHERE buyerssn Conrads-ssn
20
Subqueries

• Subquery copy in Conrads selection for his ssn
• The subquery returns one value, so the is valid
• If it returns more (or fewer), we get a run-time
  error

SELECT Purchase.prodname FROM Purchase WHERE
buyerssn (SELECT ssn
FROM Person
WHERE name Conrad)
21
SQL e.g.

• People(ssn, name, street, city, state)
• assume for clarity that cities are unique
• Q Who lives on Georges street?
• Conceptually sstreets2 AND cityc2(rp2(s2,c2)(Pe
  ople) x Pstreet,city(snameGeorge(People)))
• In SQL?
• Another way, conceptually People ?
  Pstreet,city(sname'George(People))
• In SQL? Later on

22
Operators on selections

• Several new operators applied to (unary)
  selections
• EXISTS R
• s gt ALL R
• s gt ANY R
• gt is just an example op
• Each expression can be negated with NOT

23
Subqueries returning relations

• Q Find companies Martha bought from
• Intuition
• Find Marthas ssn
• Find Marthas products
• Find those products companies

SELECT Product.maker FROM Product WHERE
Product.name IN (SELECT
Purchase.product FROM
Purchase WHERE
Purchase.buyerssn (SELECT ssn
FROM Person WHERE name
Martha))
24
Subqueries returning relations

• Equivalent to
• But are they really equivalent?
• Make both distinct to be sure

SELECT Product.maker FROM Product,
Purchase, People WHERE Product.name
Purchase.product AND
Purchase.buyerssn ssn AND name
Martha
25
Subqueries returning relations
You can also use s gt ALL R
s gt ANY R
EXISTS R
Product (pname, price, category, maker) Find
products that are more expensive than all
Gizmo-Works products
SELECT name FROM Product WHERE price gt
ALL (SELECT price
FROM Purchase
WHERE makerGizmo-Works)
26
Correlated Queries

• So far, subquery executed once
• result used for higher query
• More complicated correlated queries
• The subquery is evaluated many times, once
  for each assignment of a value to some term in
  the subquery that comes from a tuple variable
  outside the subquery (Ullman, p286).
• Q What does this mean?
• A That subqueries refer to vars from outer
  queries

27
Correlated Queries

• Movie (title, year, director, length)
• Q Find titles that are titles of multiple movies
• Note (1) scope of variables (2) this can still be
  expressed as single SFW

SELECT DISTINCT title FROM Movie AS x WHERE
year ltgt ANY
(SELECT year FROM
Movie WHERE
title x.title)
correlation
28
Complex Correlated Query

• Product (pname, price, category, maker, year)
• Find products (and their manufacturers) that are
  more expensive than all products made by the same
  manufacturer before 1972
• Powerful, but much harder to optimize!

SELECT DISTINCT pname, maker FROM Product AS
x WHERE price gt ALL (SELECT price
FROM Product AS y
WHERE
x.maker y.maker AND y.year lt 1972)
29
FROM subqueries

• Recall Q Which companies did Martha buy from?
• Before found ssn, found products, found companies

SELECT Product.maker FROM Product WHERE
Product.name IN (SELECT
Purchase.product FROM
Purchase WHERE
Purchase.buyerssn (SELECT ssn
FROM Person WHERE name Martha))
30
FROM subqueries

• Motivation for another way suppose were given
  Marthas purchases
• Then could just cross with Products and select
  identified rows
• ? Substitute (named) subquery for Marthas
  purchases

SELECT Product.maker FROM Product,
(SELECT Purchase.product
FROM Purchase WHERE
Purchase.buyerssn (SELECT ssn
FROM Person WHERE name Martha))
Marthas WHERE Product.name Martha.product
31
Existential/Universal Conditions
Product (pname, price, company) Company(cname,
city)
Find all companies s.t. some of their products
have price lt 100
SELECT DISTINCT Company.cname FROM Company,
Product WHERE Company.cname Product.company
and Produc.price lt 100
Existential easy!
32
Existential/Universal Conditions
Product (pname, price, company) Company(cname,
city)
Find all companies s.t. all of their products
have price lt 100
Universal hard!
33
Existential/universal with IN
1. Find the other companies i.e. s.t. some
product ? 100
SELECT DISTINCT Company.cname FROM
Company WHERE Company.cname IN (SELECT
Product.company
FROM Product
WHERE Produc.price
gt 100
2. Find all companies s.t. all their products
have price lt 100
SELECT DISTINCT Company.cname FROM
Company WHERE Company.cname NOT IN (SELECT
Product.company
FROM Product

WHERE Produc.price gt 100
34
More on Set-Comparison Operators

• Weve already seen IN R, NOT IN R.
• Can also use EXISTS R, NOT EXISTS R
• Also available op ANY R, op ALL R
• Find sailors whose rating is greater than that of
  some sailor called Alberto

SELECT R.SID FROM Reserves R WHERE R.rating gt
ANY (SELECT R2.rating
FROM Reserves R2
WHERE
R2.snameAlberto)
35
Joins operations

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

36
Cross join - example
MovieStar
MovieExec
37
Cross join example

• Select
• From MovieStar Cross Join MovieExec

38
Join On example

• Select
• From MovieStar Join MovieExec
• On MovieStar.Name ltgt MovieExec. Name

39
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)

40
Example - Natural join
MovieStar
MovieExec
Select from MovieStar Natural Join MovieExec
41
Outer Join - Example
MovieStar
MovieExec
Select from MovieStar FULL OUTER JOIN MovieExec
on MovieStart.nameMovieExec.name
42
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
43
New-style join syntax

• Old-style syntax simply lists tables separated by
  commas
• SELECT
• FROM A, B
• WHERE
• New-style makes the join explicit
• SELECT
• FROM A JOIN B ON
• WHERE

44
New-style join syntax

• Functionally equivalent to old-style, but perhaps
  more elegant
• Introduced in Oracle 8i
• Older versions / other DBMSs may only support
  old-style syntax

45
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

46
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

47
Old-style outer joins

• 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

48
SQL e.g.

• People(ssn, name, street, city, state)
• assume for clarity that cities are unique
• Q Who lives on Georges street?
• Now, the second way, conceptually
• People ? Pstreet,city(snameGeorge(People))
• In SQL?

49
Live Examples

• Examples from sqlzoo.net