C20.0046: Database Management Systems Lecture

1
C20.0046 Database Management SystemsLecture 9

• M.P. Johnson
• Stern School of Business, NYU
• Spring, 2008

2
Agenda

• Subqueries, etc.
• Sets, etc.

3
From last time escaping single quotes

• http//reddit.com/info/69jzv/comments/

4
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 'Christo')
5
Operators on subqueries

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

6
Next ALL op
Employees(name, job, divid, salary) Find which
employees are paid more than all the programmers
SELECT name FROM Employees WHERE salary ALL
(SELECT salary FROM
Employees WHERE
job'programmer')
7
ANY/SOME op
Employees(name, job, divid, salary) Find which
employees are paid more than at least one vice
president
SELECT name FROM Employees WHERE salary ANY
(SELECT salary FROM
Employees WHERE job'VP')
8
ANY/SOME op
Employees(name, job, divid, salary) Find which
employees are paid more than at least one vice
president
SELECT name FROM Employees WHERE salary SOME
(SELECT salary FROM
Employees WHERE job'VP')
9
Existential/Universal Conditions
Employees(name, job, divid, salary) Division(name,
id, head)
Find all divisions with an employee whose salary
is 100000
SELECT DISTINCT Division.name FROM Employees,
Division WHERE salary 100000 AND
dividid
Existential easy!
10
Existential/Universal Conditions
Employees(name, job, divid, salary) Division(name,
id, head)
Find all divisions in which everyone makes
100000
Existential easy!
11
Existential/universal with IN
1. Find the other divisions in which someone
makes SELECT name FROM Division WHERE id IN (SELECT
divid FROM Employees
WHERE salary 2. Select the divisions we didnt find
SELECT name FROM Division WHERE id NOT IN
(SELECT divid FROM Employees
WHERE salary 12
Next correlated subqueries

• Acc(name,bal,type)
• Q Who has the largest balance?
• Can we do this with subqueries?

13
Correlated Queries

• Acc(name,bal,type,)
• Q Find holder of largest account

SELECT name FROM Acc WHERE bal ALL
(SELECT bal FROM Acc)
14
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

15
Correlated Queries

• Acc(name,bal,type,)
• Q2 Find holder of largest account of each type

SELECT name, type FROM Acc WHERE bal ALL
(SELECT bal FROM Acc
WHERE typetype)
correlation
16
Correlated Queries

• Acc(name,bal,type,)
• Q2 Find holder of largest account of each type
• Note
• scope of variables
• this can still be expressed as single SFW

SELECT name, type FROM Acc a1 WHERE bal
ALL (SELECT bal FROM Acc
WHERE typea1.type)
correlation
17
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

18
Set operations - example
R
S
R ? S
19
Set operations - example
R
S
R ? S
20
Set operations - example
R
S
R - S
21
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')
22
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'
23
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'
24
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'
25
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')
26
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')
27
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')
28
Union-Compatibility

• Situation Cust1(name,address,), Cust2(name,)
• Want report 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)
29
Union-Compatibility

• Situation Cust1(name,address,), Cust2(name,)
• Want report 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)
30
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
31
Review

• Examples from sqlzoo.net

SELECT L FROM R1, , Rn WHERE C
PL(sC(R1 x Rn)