OCL2 Oracle 10g: SQL

1
OCL2 Oracle 10gSQL PL/SQLSession 5

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

2
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

3
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

4
Null/logic review

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

5
INTERSECT and EXCEPT
If R, S have noduplicates, then canwrite
withoutsubqueries(HOW?)
(SELECT R.A, R.B FROM R) INTERSECT (SELECT
S.A, S.B FROM S)
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) EXCEPT (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)
6
Agenda

• More SQL
• Grouping aggregation
• Modifications
• Defining schemata
• Views

7
Grouping Aggregation

• In SQL
• aggregation operators in SELECT,
• Grouping in GROUP BY clause
• Recall aggregation operators
• sum, avg, min, max, count
• strings, numbers, dates
• Each applies to scalars
• Count also applies to row count()
• Can DISTINCT inside aggregation op
  count(DISTINCT x)
• Grouping group rows that agree on single value
• Each group becomes one row in result

8
Straight aggregation

• In R.A. Psum(x)?total(R)
• In SQL
• Just put the aggregation op in SELECT
• NB aggreg. ops applied to each non-null val
• count(x) counts the number of nun-null vals in
  field x
• Use count() to count the number of rows

SELECT SUM(x) AS total FROM R
9
Straight Aggregation example

• COUNT applies to duplicates, unless otherwise
  stated
• Better
• Can we say

SELECT Count(category)FROM Product WHERE
year gt 1995
same as Count(), except excludes nulls
SELECT Count(DISTINCT category) FROM
Product WHERE year gt 1995
SELECT category, Count(category) FROM
Product WHERE year gt 1995
10
Straight Aggregation example

• Purchase(product, date, price, quantity)
• Q Find total sales for the entire database
• Q Find total sales of bagels

SELECT SUM(price quantity) FROM Purchase
SELECT SUM(price quantity) FROM
Purchase WHERE product bagel
11
Straight grouping

• Group rows together by field values
• Produces one row for each group
• I.e., by each (combin. of) grouped val(s)
• Dont select non-grouped fields
• Reduces to DISTINCT selections

SELECT product FROM Purchase GROUP BY product
SELECT DISTINCT product FROM Purchase
12
Grouping aggregation

• Sometimes want to group and compute aggregations
  by group
• Aggreg. op applied to rows in group,
• not all rows in table
• Q How many sales of each product?

SELECT product, count() AS numSales FROM
cia GROUP BY product
13
Evaluation of GA
SELECT S FROM R1,,Rn WHERE C1 GROUP
BY a1,,ak HAVING C2

• Evaluation steps
• Compute the FROM-WHERE part as usual to obtain a
  table with all attributes in R1,,Rn
• Group by the attributes a1,,ak
• Compute the aggregates in C2 and keep only groups
  satisfying C2
• Compute aggregates in S and return the result

14
G A for constructed relations

• Can do the same thing for larger, non-atomic
  relations
• Scenario
• Movie(title,year,producerSsn,length)
• MovieExec(name,ssn,netWorth)
• Q How many mins. of film did each producer make?
• What happens to non-producer movie-execs?

SELECT name, sum(length) AS total FROM
Movie, MovieExec WHERE producerSsn ssn GROUP
BY name
15
Illustrated GA example

• Usually want aggregations on certain parts of the
  relation
• Purchase(product, date, price, quantity)
• Find total sales after 10/18 per product that
  sold gt 20

SELECT product, SUM(pricequantity) AS
TotalSales FROM Purchase WHERE
date gt DATE 10-18-2003 GROUP BY product
16
Illustrated GA example
Purchase
17
Illustrated GA example

• First compute the FROM-WHERE clauses (date gt
  DATE 10-18-2003) then GROUP BY product

18
Illustrated GA example

• Finally, aggregate

SELECT product, SUM(pricequantity) AS
TotalSales FROM Purchase WHERE d
date gt DATE 10-18-2003 GROUP BY product
19
Illustrated GA example

• GROUP BY may be reduced to (maybe more
  complicated) subquery

SELECT product, Sum(pricequantity) AS
TotalSales FROM Purchase WHERE
date gt DATE 10-18-2003 GROUP BY product
SELECT DISTINCT x.product, (SELECT
Sum(y.pricey.quantity)
FROM Purchase y

WHERE x.product y.product
AND y.date gt
DATE 10-18-2003)
AS TotalSales FROM
Purchase x WHERE x.date gt DATE 10-18-2003
20
Another example
For every product, what is the total sales and
max quantity sold?
SELECT product, Sum(price quantity) AS
SumSales
Max(quantity) AS MaxQuantity FROM
Purchase GROUP BY product
21
Live group by e.g.

• Q How many people (immediate subordinates) does
  each manager manage?
• Join emp with self
• If want non-managers, do outer join

22
HAVING clauses

• Sometimes we want to limit which tuples may be
  grouped
• Q How many mins. of film did each rich producer
  (i.e., netWorth gt 10000000) make?
• Q Is HAVING necessary here?
• A No, could just add rich req. to SELECT

SELECT name, sum(length) AS total FROM
Movie, MovieExec WHERE producerSsn ssn GROUP
BY name HAVING netWorth gt 10000000
23
HAVING clauses

• Sometimes we want to limit which tuples may be
  grouped, based on properties of the group
• Q How many mins. of film did each old producer
  (i.e., started before 1930) make?

SELECT name, sum(length) AS total FROM
Movie, MovieExec WHERE producerSsn ssn GROUP
BY name HAVING min(year) lt 1930
24
General form of GA

• NB Any attribute of relations in the FROM
  clause may be aggregated in the HAVING clause,
  but only those attributes that are in the GROUP
  BY list may appear unaggregated in the HAVING
  clause (the same rule as for the SELECT clause)
  (Ullman, p283).

SELECT S FROM R1,,Rn WHERE C1 GROUP
BY As HAVING C2
Why?

• S may contain attributes As and/or any
  aggregates but no other attributes
• C1 condition on the attributes in R1,,Rn
• C2 condition on aggregations or attributes from
  As

Why?
25
Live example

• Q produce list of bosses and underling-counts,
  for bosses with gt1 underling
• Just add HAVING clause

26
More AG Examples

• Web pages, and their authors
• Author(login,name)
• Document(url, title)
• Wrote(login,url)
• Mentions(url,word)

27
Web page examples

• Find all authors who wrote at least 10 documents
  Author(login,name), Wrote(login,url)
• Attempt 1 with nested queries

Bad!
SELECT DISTINCT Author.name FROM
Author WHERE count(SELECT Wrote.url
FROM Wrote
WHERE Author.loginWrote.login
) gt 10
28
Web page examples

• Find all authors who wrote at least 10 documents
• Attempt 2 Simplify with GROUP BY

SELECT Author.name FROM Author,
Wrote WHERE Author.loginWrote.login GROUP
BY Author.name HAVING count(wrote.url) gt 10
Good!
No need for DISTINCT get for free from GROUP BY
29
Web page examples

• Find all authors who have a vocabulary over 10000
  words

Author(login,name), Wrote(login,url),
Mentions(url, world)
SELECT Author.name FROM Author,
Wrote, Mentions WHERE Author.loginWrote.log
in AND Wrote.urlMentions.url GROUP BY
Author.name HAVING count(distinct
Mentions.word) gt 10000
30
Summary SQL queries

• Only SELECT, FROM required
• Cant have HAVING without GROUP BY
• Can have GROUP BY without HAVING
• Any clauses must appear in this order

SELECT LFROM Rs WHERE s GROUP BY
L2 HAVING s2 ORDER BY L3
31
New topic Modifications

• Three kinds of modifications
• Insertions
• Deletions
• Updates
• Sometimes update used as a synonym for
  modification

32
Insertions
General form
INSERT INTO R(A1,., An) VALUES (v1,.,
vn)
Example Insert a new purchase to the database
INSERT INTO Purchase(buyer, seller, product,
store) VALUES (Joe, Fred,
wakeup-clock-espresso-machine,
The Sharper Image)
Missing attribute ? NULL (or other default value)
33
Insertions

• If were sure we have all values in the right
  order, can just say
• Only do this if youre sure of order in which the
  table fields were defined

INSERT INTO R VALUES (v1,., vn)
INSERT INTO Purchase VALUES (Joe,
Fred, wakeup-clock-espresso-machine,
The Sharper Image)
34
Insertions

• Can insert the result of a query Scenario
• Product(name, etc.)
• Purchase(buyerssn, prodName, etc.)
• Maybe some purchases name missing products
• ? add those to the Product table
• Subquery replaces VALUES

INSERT INTO R(As) (query)
35
Insertion example
Product(name, listPrice, category) Purchase(prodNa
me, buyerName, price)

• Premise data corruption ? lose some Product data
• every product referred to in Purchase should
  exist in Product, but some are missing

Product
Purchase
36
Insertion example
INSERT INTO Product(name) SELECT
prodName FROM Purchase WHERE
prodName NOT IN (SELECT name FROM Product)
Purchase
Product
Product
Q Or do we get
A Depends on implementation!
37
Deletions

• General form
• Example
• Q How do you delete just one row with SQL
  simpliciter?
• A You cant!
• Although Oracle has the ROWID pseudo-field
• As usual, WHERE can contain subqueries

DELETE FROM Table WHERE condition
DELETE FROM PURCHASE WHERE seller
Joe AND product Brooklyn
Bridge
38
Updates

• General form
• Example
• As usual, WHERE can contain subqueries

UPDATE Product SET field1 value1,
field2 value2 WHERE condition
UPDATE Product SET price price/2 WHERE
Product.name IN (SELECT
product FROM Purchase
WHERE Date DATEOct, 25,
1999)
39
New topic Defining schemata

• So far, have done queries and data manipulation
• Now doing data definition
• Recall data types
• INT or INTEGER (variant SHORTINT)
• FLOAT or REAL floating-point numbers
• DOUBLE PRECISION
• DECIMAL(n,d)
• E.g. decimal(5,2) five decimal digits, with the
  decimal point two positions from the right e.g.
  123.45
• DATE and TIME
• Character strings
• Fixed length CHAR(n)
• Variable length VARCHAR(n)

40
Creating tables

• Form
• Example

CREATE TABLE Table-name ( field field-type,
field field-type, field field-type )
No comma!
CREATE TABLE People ( name VARCHAR(30),
ssn CHAR(9), age SHORTINT,
city VARCHAR(30), gender BIT(1),
Birthdate DATE )
Not supported in Oracle
41
Default Values

• Specify defaults when creating table
• The default default NULL

CREATE TABLE People ( name VARCHAR(30),
ssn CHAR(9), age SHORTINT DEFAULT 100,
city VARCHAR(30) DEFAULT New York,
gender BIT(1), Birthdate DATE DEFAULT DATE
0000-0000, )
42
Deleting and modifying schemata

• Delete data, indices, schema
• Delete data and indices
• Either way, use EXTREME CAUTION!
• Add or delete attributes

DROP TABLE Person
TRUNCATE TABLE Person
Q Whats put in the new fields?
ALTER TABLE Person ADD
phone CHAR(12) ALTER TABLE Person
DROP age
43
New topic Indices

• Very important speeding up query processing
• Index on field(s) data structure that makes
  searches/comparisons on those fields fast
• Suppose we have a relation
• Person (name, age, city)
• Sequential scan of the whole Person file may take
  a very long time

SELECT FROM Person WHERE name Waksal
44
Creating Indices

• Syntax
• Here
• No searching by name is much faster
• How much faster?
• Log-time, say
• Base-what? Doesnt matter, but say 2
• If all New Yorkers, comparisons
• 8000000 ? log2(8000000) 23
• (i.e., 223 8000000)

CREATE INDEX index-name ON R(field(s))
CREATE INDEX nameIndex ON Person(name)
45
How do indices work?

• What the data structure?
• Different possibilities
• 1st intuition index on field f is an ordered
  list of all values in the tables f field
• each item has address (rowid) of its row
• Where do we get the ordered list?
• 2nd intuition put all f values in a BST
• searching BST take log time (why?)
• DBMSs actually use a variant BTree
• See Ullmans book or data structures texts

46
Creating Indices

• Indexes can be useful in range queries too

CREATE INDEX ageIndex ON Person (age)
SELECT FROM Person WHERE age gt 25
47
Using indices

• Indices can be created on multiple attributes
• Helps in
• And in
• But not in

CREATE INDEX doubleindex ON
Person (age, city)
SELECT FROM Person WHERE age 55 AND city
Seattle
Idea our sorted list is sorted on agecity, not
cityage
SELECT FROM Person WHERE age 55
Q In Movie tbl, should index be on yeartitle or
titleyear?
SELECT FROM Person WHERE city Seattle
48
The Index Selection Problem

• Big Q Why not just indexes all the fields?
• how does the list/BTree stay up to date?
• We are given a workload a set of SQL queries and
  their frequencies
• Q is What indices should we build to speed up
  the workload?
• A
• Attributes in FROM/WHERE clauses ? favor an index
• Attributes in INSERT/UPDATE clauses ? discourage
  an index
• In Oracle your primary key fields get indexed
  automatically (why?)

49
New topic Views

• Stored relations physically exist and persist
• Views are relations that dont
• in some texts, table stored relation base
  table
• Basically names/references given to queries
• maybe a relevant subset of a table
• Employee(ssn, name, department, project, salary)
• Payroll has access to Employee, others only to
  Developers

CREATE VIEW Developers AS SELECT name,
project FROM Employee WHERE department
Development
50
A Different View

• Person(name, city)
• Purchase(buyer, seller, product, store)
• Product(name, maker, category)
• We have a new virtual table
• Seattle-view(buyer, seller, product, store)

CREATE VIEW Seattle-view AS SELECT
buyer, seller, product, store FROM
Person, Purchase WHERE Person.city
Seattle AND
Person.name Purchase.buyer
51
A Different View
CREATE VIEW Seattle-view AS SELECT
buyer, seller, product, store FROM
Person, Purchase WHERE Person.city
Seattle AND
Person.name Purchase.buyer

• Now we can query the view

SELECT name, store FROM Seattle-view,
Product WHERE Seattle-view.product
Product.name AND
Product.category shoes
52
What happens when we query a view?

• SELECT name, Seattle-view.store
• FROM Seattle-view, Product
• WHERE Seattle-view.product Product.name AND
• Product.category shoes

SELECT name, Purchase.store FROM Person,
Purchase, Product WHERE Person.city Seattle
AND Person.name
Purchase.buyer AND
Purchase.poduct Product.name AND
Product.category shoes
53
Can rename view fields
CREATE VIEW Seattle-view(seabuyer, seaseller,
prod, store) AS SELECT buyer,
seller, product, store FROM Person,
Purchase WHERE Person.city Seattle
AND Person.name
Purchase.buyer
54
Types of Views

• Views discussed here
• Used in databases
• Computed only on-demand slow at runtime
• Always up to date
• Sometimes talk about materialized views
• Used in data warehouses
• Pre-computed offline fast at runtime
• May have stale data
• Maybe more later

55
Updating Views
How can I insert a tuple into a table that
doesnt exist? Employee(ssn, name, department,
project, salary)
CREATE VIEW Developers AS SELECT name,
project FROM Employee WHERE department
Development
If we make the following insertion
INSERT INTO Developers VALUES(Joe,
Optimizer)
INSERT INTO Employee(ssn, name, department,
project, salary)VALUES(NULL, Joe, NULL,
Optimizer, NULL)
It becomes
56
Non-Updatable Views

• Person(name, city)
• Purchase(buyer, seller, product, store)
• How can we add the following tuple to the view?
• (Seattle, Nine West)
• We dont know the name of the person who made the
  purchase
• cannot set to NULL (why?)

CREATE VIEW City-Store AS SELECT
Person.city, Purchase.store FROM
Person, Purchase WHERE Person.name
Purchase.buyer
57
Constraints in SQL

• A constraint a property that wed like our
  database to hold
• The system will enforce the constraint by taking
  some actions
• forbid an update
• or perform compensating updates

58
Constraints in SQL
simplest

• Constraints in SQL
• Keys, foreign keys
• Attribute-level constraints
• Tuple-level constraints
• Global constraints assertions
• The more complex the constraint, the harder it is
  to check and to enforce

Mostcomplex
59
Keys
CREATE TABLE Product ( name CHAR(30) PRIMARY
KEY, category VARCHAR(20))

• OR

CREATE TABLE Product ( name CHAR(30), category
VARCHAR(20) PRIMARY KEY (name))
60
Keys with Multiple Attributes

• CREATE TABLE Product (
• name CHAR(30),
• category VARCHAR(20),
• price INT,
• PRIMARY KEY (name, category))

61
Other Keys

• CREATE TABLE Product (
• productID CHAR(10),
• name CHAR(30),
• category VARCHAR(20),
• price INT, PRIMARY KEY (productID),
• UNIQUE (name, category))

There is at most one PRIMARY KEYthere can be
many UNIQUE
62
Foreign Key Constraints
Referentialintegrityconstraints

• CREATE TABLE Purchase (
• prodName CHAR(30)
• REFERENCES Product(name),
• date DATETIME)

prodName is a foreign key to Product(name)name
should be a key in Product
63
Product
Purchase
64
Foreign Key Constraints

• Or
• (name, category) must be a PRIMARY KEY in its
  table

CREATE TABLE Purchase ( prodName
CHAR(30), category VARCHAR(20), date
DATETIME, FOREIGN KEY (prodName, category)
REFERENCES Product(name, category)
65
What happens during updates?

• Types of updates
• In Purchase insert/update
• In Product delete/update

Product
Purchase
66
What happens during updates?

• SQL has three policies for maintaining
  referential integrity
• Reject violating modifications (default)
• Cascade after a delete/update do a delete/update
• Set-null set foreign-key field to NULL

67
Constraints on Attributes and Tuples

• Constraints on attributes NOT NULL -- obvious
  meaning... CHECK condition -- any condition
  (except subqueries)
• Constraints on tuples CHECK condition

68
Whatis the difference fromForeign-Key?
CREATE TABLE Purchase ( prodName
CHAR(30) CHECK (prodName IN
SELECT Product.name
FROM Product), date
DATETIME NOT NULL)
69
General Assertions

• Supported in SQL standard
• In Oracle, supported/simulated with triggers

CREATE ASSERTION myAssert CHECK NOT
EXISTS( SELECT Product.name FROM Product,
Purchase WHERE Product.name Purchase.prodName
GROUP BY Product.name HAVING count() gt 200)
70
Final Comments on Constraints

• Can give them names, and alter later
• We need to understand exactly when they are
  checked
• We need to understand exactly what actions are
  taken if they fail