OCL1 Oracle 8i: SQL

1
OCL1 Oracle 8iSQL PL/SQLSession 3

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

2
High-level agenda

• Review keys, FDs
• Install Oracle
• Start SQL
• Lab on Oracle system info/SQL

3
Key/FD review

• Product(name, price, category, color)
• name, category ? price
• category ? color
• Keys are name, category
• Enrollment(student, address, course, room, time)
• student ? address
• room, time ? course
• student, course ? room, time
• Keys are in class

4
Next topic SQL (6.1)

• Standard language for querying and manipulating
  data
• Structured Query Language
• Many standards ANSI SQL, SQL92/SQL2, SQL3/SQL99
• Vendors support various subsets/extensions
• Well do Oracles version
• Basic form (many more bells and whistles in
  addition)

SELECT attributes FROM relations (possibly
multiple, joined) WHERE conditions (selections)
5
Data Types in SQL

• Characters
• CHAR(20) -- fixed length
• VARCHAR(40) -- variable length
• Numbers
• BIGINT, INT, SMALLINT, TINYINT
• REAL, FLOAT -- differ in precision
• MONEY
• Times and dates
• DATE
• DATETIME -- SQL Server

6
Tables
Table name
Attribute names
Product
Tuples or rows
7
Simple SQL Query
Product
SELECT FROM ProductWHERE
categoryGadgets
selection
8
Simple SQL Query
Product
SELECT PName, Price, ManufacturerFROM
ProductWHERE Price 100
selection and projection
9
A Notation for SQL Queries
Input Schema
Product(PName, Price, Category, Manfacturer)
SELECT Name, Price, ManufacturerFROM
ProductWHERE Price 100
(PName, Price, Manfacturer)
Output Schema
10
SQL

• SQL SELECT
• Sometimes called a projection
• What goes in the WHERE clause
• x y, x
• For number, they have the usual meanings
• For CHAR and VARCHAR lexicographic ordering
• Expected conversion between CHAR and VARCHAR
• For dates and times, what you expect

11
SQL e.g.

• Movies(Title,Year,Length,inColor,Studio,Prdcr)
• Q How long was Star Wars (1977), in SQL?
• Q Which Fox movies are are at least 100 minutes
  long, in SQL?

12
SQL e.g.

• Reps(ssn, name, etc.)
• Clients(ssn, name, rssn)
• Q Who are Georges clients, in SQL?
• Conceptually
• PClients.name(sReps.nameGeorge and
  Reps.ssnrssn(Reps x Clients))

13
The LIKE operator

• s LIKE p pattern matching on strings
• p may contain two special symbols
• _ any single character
• zero or more chars
• Product(Name, Price, Category, Manufacturer)
• Find all products whose name contains gizmo

SELECT FROM ProductsWHERE PName LIKE
gizmo
14
The LIKE operator

• Q What it want to search for values containing a
  ?
• PName LIKE wont work
• Instead, must use escape chars
• In C/C/J, prepend \
• In SQL, prepend an arbitrary escape char
• PName LIKE x ESCAPE x

15
More on escape chars

• SQL no official default escape char
• In SQLPlus default escape char \
• Can set with
• SQL 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

16
More on single-quotes

• Dates with DATE
• DATE 1948-05-14
• Timestamps with TIMESTAMP
• TIMESTAMP 1948-05-14 120000

17
Eliminating Duplicates
SELECT category FROM Product
Compare to
SELECT DISTINCT category FROM Product
18
Ordering the Results

• Ordering is ascending, unless you specify the
  DESC keyword per attribute.
• Ordering is ascending, unless you specify the
  DESC keyword per attribute.

SELECT pname, price, manufacturer FROM
Product WHERE categorygizmo AND price
50 ORDER BY price, pname
SELECT pname, price, manufacturer FROM
Product WHERE categorygizmo AND price
50 ORDER BY price DESC, pname ASC
19
Ordering the Results
SELECT Category FROM Product ORDER BY PName
?
20
Ordering the Results
SELECT DISTINCT category FROM Product ORDER
BY category
Compare to
?
SELECT DISTINCT category FROM Product ORDER
BY PName
21
Joins in SQL

• Connect two or more tables

Product
Company
What is the connection between them?
22
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
CountryJapan AND Price 23
Joins in SQL
Product
Company
SELECT PName, PriceFROM Product,
CompanyWHERE ManufacturerCName AND
CountryJapan AND Price 24
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 Manufa
cturerCName AND CategoryGadgets
25
Joins in SQL
Product
Company
SELECT CountryFROM Product, CompanyWHERE
ManufacturerCName AND CategoryGadgets
What is the problem? Whats thesolution?
26
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
citySeattle AND categoryGadgets
27
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
28
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)
29
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
30
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
31
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
32
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

33
Review

• Examples from sqlzoo.net

SELECT L FROM R1, , Rn WHERE C
PL(sC(R1 x Rn)
34
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)
35
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

36
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

37
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

SQL Select from R, R Select from R, R
ERROR at line 1 ORA-00918 column ambiguously
defined