C20'0046: Database Management Systems Lecture

1
C20.0046 Database Management SystemsLecture 10

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

2
Next topic SQL

• 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 SQL99/Oracle/MySQL
• No one ever got fired for buying Oracle.
• Basic form (many more bells and whistles in
  addition)

SELECT attributes FROM relations (possibly
multiple, joined) WHERE conditions (selections)
3
SQL Query Semantics

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

Answer for all assignments x1 in R1, , xn
in Rn do if Conditions then Answer
Answer ? (a1,,ak) return Answer
4
SQL Query Semantics

• SELECT a1, a2, , ak
• FROM R1 AS x1, R2 AS x2, , Rn AS xn
• WHERE Conditions
• 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
5
Details 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
6
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

SQLgt Select from R, R Select from R, R
ERROR at line 1 ORA-00918 column ambiguously
defined
7
Details 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 store'BestBuy' also sold
SELECT DISTINCT x.store AS competitor FROM
Purchase AS x, Purchase AS y WHERE x.product
y.product AND y.store 'BestBuy'
Answer (store)
8
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

9
R.A. SQL

• Reps(ssn, name, etc.)
• Clients(ssn, name, rssn)
• Q Who are Georges clients, in R.A.?
• PClients.name(sReps.name'George' and
  Reps.ssnrssn(Reps x Clients))
• In SQL?

10
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 gt
50 ORDER BY price, pname
SELECT pname, price, manufacturer FROM
Product WHERE categorygizmo AND price gt
50 ORDER BY price DESC, pname ASC
11
Ordering the Results
SELECT Category FROM Product ORDER BY PName
?
12
Details Case-sensitivity

• By default, all matches and comparisons are
  case-sensitive
• If want case-insensitive, some options
• Convert all to upper or lower case - slow
• SQLgt select from emp where upper(ename)
  upper(Blake')
• Create a function index
• Maybe later
• Modify the nls_sort setting
• SQLgt alter session set nls_sortbinary_ci
• SQLgt alter session set nls_compansi
• The other values binary, binary_ai

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
Details 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 vary
• SQL string literals put in
• 'mystring'
• Single-quote literals escaped with single-quotes
• 'George''s string'

16
Details More on escape chars

• Q Can an escape char be an escape string?
• A No.

SQLgt select from newtable where a like '\'
escape '\' A B ----------
---------- hi there SQLgt select from
newtable where a like '\' escape '\\' select
from newtable where a like '\' escape '\\'

ERROR at line 1 ORA-01425 escape character
must be character string of length 1
17
Details More on single-quotes

• Dates with DATE
• DATE '1948-05-14'
• Timestamps with TIMESTAMP
• TIMESTAMP '1948-05-14 120000'
• Details may vary by DBMS

18
Details More on quotes

• Q What about double quotes?
• A Cant be used in place of single quotes
• But can be used when Oracle would otherwise
  misparse your command, e.g.
• Names with spaces
• create table bad table name (a int, b int)
• Reserved words as names
• create table badfieldname(from int, b int)

19
Complex RA/SQL Expressions

• Reps(ssn, name, etc.)
• Clients(ssn, name, rssn)
• Q Who are Georges clients?
• PClients.name(sReps.name'George' and
  Reps.ssnrssn(
• Reps x Clients))
• Or PClients.name(Reps.ssnrssn(
• sReps.name'George'(Reps) x Clients))

20
Complex RA Expressions

• People(ssn, name, street, city, state, state)
• Q Who lives on Georges street?
• A First, find George
• sname'George'(People)
• Get Georges street/city/state
• Pstreet,city,state(sname'George'(People))
• Join with People
• People x Pstreet,city,state(sname'George'(People)
  )

21
Complex RA Expressions

• How to specify street street? Rename
• rp2(s2,c2)(People) x Pstreet,city(sname'George'(P
  eople))
• Now can select
• sstreets2 AND cityc2(rp2(s2,c2)(People) x
  Pstreet,city(sname'George'(People)))
• Then project names
• Only way? No. Join!
• People Pstreet,city(sname'George'(People))
• Q Would the following work?
• Pstreet,city(sname'George'(People ? People))

22
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(sname'George'(People)))
• In SQL?
• The other way in R.A. People Pstreet,city(sna
  me'George'(People))
• In SQL? Later on

23
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

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

• Person Purchase Person
  Product