C20'0046: Database Management Systems Lecture

1
C20.0046 Database Management SystemsLecture 11

• Matthew P. Johnson
• Stern School of Business, NYU
• Spring, 2004

2
Agenda

• Last time Started SQL
• This time More SQL
• Homework 2 is up

3
Review

• Examples from sqlzoo.net

SELECT L FROM R1, , Rn WHERE C
PL(sC(R1 x Rn)
4
First Unintuitive SQLism

• SELECT R.A
• FROM R, S, T
• WHERE R.AS.A OR R.AT.A
• Looking for R ? (S ? T)
• But what happens if T is empty?
• See transcript of this in Oracle on sales

5
More on escape chars

• SQL no official default escape char
• In SQLPlus default escape char \
• Can set with
• SQLgt set escape X
• Other tools, DBMSs your mileage may very
• SQL string literals put in
• mystring
• Single-quote literals escaped with single-quotes
• Georges string

6
More on single-quotes

• Bitstrings specified like regular strings but
  with B
• B001
• Hex strings with X
• X7ff
• Dates with DATE
• DATE 1948-05-14
• Timestamps with TIMESTAMP
• TIMESTAMP 1948-05-14 120000

7
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
CitySeattle) UNION (SELECT name FROM
Person, Purchase WHERE buyername AND
storeThe Bon)
8
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

9
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

10
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
11
R.A. ? SQL

• People(ssn, name, street, city, state)
• assume for clarity that cities are unique
• Q Who lives on Georges street?
• In R.A. sstreets2 AND cityc2(rp2(s2,c2)(People)
  x Pstreet,city(snameGeorge(People)))
• In SQL?
• The other way in R.A. People ?
  Pstreet,city(snameGeorge(People))
• In SQL? Later on

12
R.A. ? SQL

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

13
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

14
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

15
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.age20heigthNULLweight200
16
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
17
Testing for null values

• Can test for NULL explicitly
• x IS NULL
• x IS NOT NULL
• Now it includes all Persons

SELECT FROM Person WHERE age lt 25 OR age
gt 25 OR age IS NULL
18
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

19
Subqueries (5.3)

• 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.

20
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
21
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
22
Subqueries

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

SELECT Purchase.prodname FROM Purchase WHERE
buyerssn (SELECT ssn
FROM Person
WHERE name Conrad)
23
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

24
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))
25
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
26
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)
27
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 (p286).
• Q What does this mean?
• A That subqueries refer to vars from outer
  qureries

28
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
29
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)
30
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))
31
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
32
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!
33
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!
34
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
35
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 Horatio

SELECT R.SID FROM Reserves R WHERE R.rating gt
ANY (SELECT R2.rating
FROM Reserves R2
WHERE
R2.snameHoratio)
36
Joins operations

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

37
Cross join - example
MovieStar
MovieExec
38
Cross join example

• Select
• From MovieStar Cross Join MovieExec

39
Join On example

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

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

41
Example - Natural join
MovieStar
MovieExec
Select from MovieStar Natural Join MovieExec
42
Outer Join - Example
MovieStar
MovieExec
Select from MovieStar NATURAL FULL OUTER JOIN
MovieExec
43
Outer Join - Example

• Select from MovieStar LEFT OUTER JOIN MovieExec

Select from MovieStar RIGHT OUTER JOIN MovieExec
44
R.A. ? SQL

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

45
Live Examples

• Examples from sqlzoo.net