OCL4 Oracle 10g: SQL

1
OCL4 Oracle 10gSQL PL/SQLSession 3

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

2
Agenda

• Review
• Lab 2
• SQL
• Lab 3
• SQL
• Lab 4

3
High-level design strategy
Conceptual Model
4
Functional dependencies

• Definition
• Notation
• Read Ai functionally determines Bj

If two tuples agree on the attributes
A1, A2, , An
A1, A2, , An ? B1, B2, , Bm
5
Typical Examples of FDs

• Product
• name ? price, manufacturer
• Person
• ssn ? name, age
• fathers/husbands-name ? last-name
• zipcode ? state
• phone ? state (notwithstanding inter-state area
  codes)
• Company
• name ? stockprice, president
• symbol ? name
• name ? symbol

6
Example of anomalies
SSN ? Name, Mailing-address
SSN ? Phone

• Redundancy name, maddress
• Update anomaly Bill moves
• Delete anom. Bill doesnt pay bills, lose phones
  ? lose Bill!
• Insert anom cant insert someone without a
  (non-null) phone
• Underlying cause SSN-phone is many-many
• Effect partial dependency ssn ? name, maddress,
• Whereas key ssn,phone

7
Most important BCNF
A simple condition for removing anomalies from
relations
A relation R is in BCNF if If As ? Bs is a
non-trivial dependency in R , then As is a
superkey for R
I.e. The left side must always contain a
key I.e If a set of attributes determines other
attributes, it must determine all the attributes

• Codd Ted Codd, IBM researcher, inventor of
  relational model, 1970
• Boyce Ray Boyce, IBM researcher, helped develop
  SQL in the 1970s

8
Boyce-Codd Normal Form

• Name/phone example is not BCNF
• ssn,phone is key
• FD ssn ? name,mailing-address holds
• Violates BCNF ssn is not a superkey
• Its decomposition is BCNF
• Only superkeys ? anything else

9

• Lab 2

10

• Spooling
• Review lab 1
• SQL

11
Joins in SQL

• Connect two or more tables

Product
Company
What is the connection between them?
12
Joins in SQL
Product (pname, price, category,
manufacturer) Company (cname, stockPrice,
country) Find all products under 200
manufactured in Japanreturn their names and
prices.

SELECT PName, PriceFROM Product, CompanyWHERE
ManufacturerCName AND Country'Japan'
AND Price 13
Joins in SQL
Product
Company
SELECT PName, PriceFROM Product, CompanyWHERE
ManufacturerCName AND Country'Japan'
AND Price 14
Joins in SQL
Product (pname, price, category,
manufacturer) Company (cname, stockPrice,
country) Find all countries that manufacture
some product in the Gadgets category.
SELECT CountryFROM Product, CompanyWHERE
ManufacturerCName AND Category'Gadgets'
15
Joins in SQL
Product
Company
SELECT CountryFROM Product, CompanyWHERE
ManufacturerCName AND Category'Gadgets'
What is the problem? Whats thesolution?
16
Joins
Product (pname, price, category,
manufacturer) Purchase (buyer, seller, store,
product) Person(name, phone, city) Find names of
Seattleites who bought Gadgets, and the names of
the stores they bought such product from.

SELECT DISTINCT name, storeFROM Person,
Purchase, ProductWHERE persnamebuyer AND
product pname AND city'Seattle' AND
category'Gadgets'
17
Disambiguating Attributes

• Sometimes two relations have the same
  attrPerson(pname, address, worksfor)Company(cna
  me, address)

Whichaddress ?
SELECT DISTINCT pname, addressFROM Person,
CompanyWHERE worksfor cname
SELECT DISTINCT Person.pname, Company.addressFROM
Person, CompanyWHERE Person.worksfor
Company.cname
18
Tuple Variables
Product (pname, price, category,
manufacturer) Purchase (buyer, seller, store,
product) Person(persname, phoneNumber, city)
Find all stores that sold at least one product
that the storeBestBuy also sold
SELECT DISTINCT x.store FROM Purchase AS x,
Purchase AS y WHERE x.product y.product AND
y.store 'BestBuy'
Answer (store)
19
Tuple Variables

• Tuple variables introduced automatically
• Product (name, price, category, manufacturer)
• Becomes
• Doesnt work when Product occurs more than once
• In that case the user needs to define variables
  explicitly

SELECT name FROM Product WHERE price 100
SELECT Product.name FROM Product AS
Product WHERE Product.price 100
20
Details Disambiguation in 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

SQL Select from R, R Select from R, R
ERROR at line 1 ORA-00918 column ambiguously
defined
21
Details 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

22
SQL Query Semantics

• SELECT a1, a2, , ak
• FROM R1 AS x1, R2 AS x2, , Rn AS xn
• WHERE Conditions
• 1. Nested loops

Answer for x1 in R1 do for x2 in R2
do .. for xn in Rn
do if Conditions
then Answer Answer ?
(a1,,ak) return Answer
23
SQL Query Semantics

• SELECT a1, a2, , ak
• FROM R1 AS x1, R2 AS x2, , Rn AS xn
• WHERE Conditions
• 2. Parallel assignment
• Doesnt impose any order!

Answer for all assignments x1 in R1, , xn
in Rn do if Conditions then Answer
Answer ? (a1,,ak) return Answer
24
SQL e.g.

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

25
New topic Subqueries

• Powerful feature of SQL one clause can contain
  other SQL queries
• Anywhere where a value or relation is allowed
• Several ways
• Selection ? single constant (scalar) in SELECT
• Selection ? single constant (scalar) in WHERE
• Selection ? relation in WHERE
• Selection ? relation in FROM

26
Subquery motivation

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

SELECT Purchase.prodnameFROM Purchase,
PersonWHERE buyerssn ssn AND name 'Christo'
27
Subquery motivation

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

SELECT ssnFROM PersonWHERE name 'Christo'
SELECT Purchase.prodnameFROM PurchaseWHERE
buyerssn Christos-ssn
28
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')
29
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

30
Subqueries with IN

• Product(name,maker), Person(name,ssn),
  Purchase(buyerssn,product)
• Q Find companies Martha bought products from
• Strategy
• Find Marthas ssn
• Find products listed with that ssn as buyer
• Find company names of those products

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

• Equivalent to

SELECT DISTINCT Product.maker FROM Product,
Purchase, People WHERE Product.name
Purchase.product AND Purchase.buyerssn
ssn AND name 'Martha'
32
FROM subqueries

• Motivation for another way
• suppose were given Marthas purchases
• Then could just cross with Products to get
  product makers
• ? 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
Marthas.product
33
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')
34
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')
35
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')
36
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!
37
Existential/Universal Conditions
Employees(name, job, divid, salary) Division(name,
id, head)
Find all divisions in which everyone makes
100000
Universal hard!
38
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 39

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

40
Correlated Queries

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

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

42
Correlated Queries

• Last time 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
43
Correlated Queries

• Last time 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
44
EXCEPT and INTERSECT
SELECT R.A, R.B FROM RWHERE EXISTS(SELECT
FROM S WHERE R.AS.A and
R.BS.B)
(SELECT R.A, R.B FROM R) INTERSECT (SELECT
S.A, S.B FROM S)
SELECT R.A, R.B FROM RWHERE NOT EXISTS(SELECT
FROM S WHERE R.AS.A
and R.BS.B)
(SELECT R.A, R.B FROM R) EXCEPT (SELECT
S.A, S.B FROM S)
45
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
ANY (SELECT R2.rating
FROM Reserves R2
WHERE
R2.snameAlberto)
46
Extended e.g.

• Scenario
• Purchase(pid, seller-ssn, buyer-ssn, etc.)
• Person(ssn, name, etc.)
• Product(pid, name, etc.)
• Q Who (give names) bought gizmos from Dick?
• Where to start?
• Purchase uses pid, ssn, so must get them

47
Last time Complex RA Expressions

• Scenario
• Purchase(pid, seller-ssn, buyer-ssn, etc.)
• Person(ssn, name, etc.)
• Product(pid, name, etc.)
• Q Who (give names) bought gizmos from Dick?
• Where to start?
• Purchase uses pid, ssn, so must get them

48
Complex RA Expressions
P name
P pid
P ssn
sname'Dick'
sname'Gizmo'

• Person Purchase Person
  Product

49
Translation to SQL
(the names of the people who bought gadgets from
Dick)

• Were converting the tree on the last slide into
  SQL
• The result of the query should be the names
  indicated above
• One step at a time, well make the query more
  complete, until weve translated the
  English-language description to an actual SQL
  query
• Well also simplify the query when possible

50
Translation to SQL
SELECT DISTINCT name buyer FROM (the info, along
with buyer names, for purchases of gadgets sold
by Dick)

• Blue type actual SQL
• Black italics description of subquery
• Note the subquery above consists of purchase
  records, except with the info describing the
  buyers attached
• In the results, the column header for name will
  be 'buyer'

51
Translation to SQL
SELECT DISTINCT name buyer FROM (SELECT FROM
Person, (the purchases of gadgets from Dick)
P2 WHERE Person.ssn P2.buyer-ssn)

• Note the subquery in this version is being given
  the name P2
• Were pairing our rows from Person with rows from
  P2

52
Translation to SQL
SELECT DISTINCT name buyer FROM Person, (the
purchases of gadgets from Dick) P2 WHERE
Person.ssn P2.buyer-ssn

• We simplified by combining the two SELECTs

53
Translation to SQL
SELECT DISTINCT name buyer FROM Person, (SELECT
FROM Purchases
WHERE seller-ssn (Dicks ssn)
AND pid (the id
of gadget)) P2 WHERE Person.ssn P2.buyer-ssn

• P2 is still the name of the subquery
• Its just been filled in with a query that
  contains two subqueries
• Outer parentheses are bolded for clarity

54
Translation to SQL
SELECT DISTINCT name buyer FROM Person, (SELECT
FROM Purchases
WHERE seller-ssn (SELECT ssn
FROM Person
WHERE name'Dick')
AND pid
(the id of gadget)) P2 WHERE Person.ssn
P2.buyer-ssn

• Now the subquery to find Dicks ssn is filled in

55
Translation to SQL
SELECT DISTINCT name buyer FROM Person, (SELECT
FROM Purchases
WHERE seller-ssn (SELECT ssn
FROM Person
WHERE name'Dick')
AND pid (SELECT pid
FROM Product
WHERE name'Gadget')) P2 WHERE Person.ssn
P2.buyer-ssn

• And now the subquery to find Gadgets product id
  is filled in, too
• Note the SQL simplified by using subqueries
• Not used in relational algebra

56
Review

• Examples from sqlzoo.net

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