c20 0046 database management systems lecture 7 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
C20.0046: Database Management Systems Lecture #7 PowerPoint Presentation
Download Presentation
C20.0046: Database Management Systems Lecture #7

Loading in 2 Seconds...

play fullscreen
1 / 46

C20.0046: Database Management Systems Lecture #7 - PowerPoint PPT Presentation


  • 146 Views
  • Uploaded on

C20.0046: Database Management Systems Lecture #7. M.P. Johnson Stern School of Business, NYU Spring, 2008. Agenda. Basic SQL Joins Hw1 probably soon (email from Blackboard)…. Current topic: SQL. Standard language for querying and manipulating data Structured Query Language

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'C20.0046: Database Management Systems Lecture #7' - elysia


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
c20 0046 database management systems lecture 7

C20.0046: Database Management SystemsLecture #7

M.P. Johnson

Stern School of Business, NYU

Spring, 2008

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

agenda
Agenda
  • Basic SQL
  • Joins
  • Hw1 probably soon (email from Blackboard)…

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

current topic sql
Current topic: SQL
  • Standard language for querying and manipulating data

Structured Query Language

  • Many standards: ANSI SQL, SQL92/SQL2, SQL3/SQL99
  • Originally: Structured English Query Language (SEQUEL)
  • Vendors support various subsets/extensions
  • We’ll do Oracle/MySQL/generic
    • “No one ever got fired for buying Oracle.”
  • Basic form (many more bells and whistles in addition):

SELECTattributes

FROM relations (possibly multiple, joined)

WHERE conditions (selections)

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

next parallel topic relational algebra
Next (parallel) topic: relational algebra
  • Projection
  • Selection
  • Cartesian Product
  • Joins: natural joins, theta joins
  • Set operations: union, intersection, difference
  • Combining operations to form queries
  • Dependent and independent operations

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

what is relational algebra
What is relational algebra?
  • An algebra for relations
  • “High-school” algebra: an algebra for numbers
  • Algebra = formalism for constructing expressions
    • Operations
    • Operands: Variables, Constants, expressions
  • Expressions:
    • Vars & constants
    • Operators applied to expressions
    • They evaluate to values

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

why do we care about relational algebra
Why do we care about relational algebra?
  • The exprs are the form that questions about the data take
    • The relations these exprs cash out to are the answers to our questions
  • RA ~ more succinct rep. of many SQL queries
  • DBMS parse SQL into something like RA
  • First proofs of concept for RDBMS/RA:
    • System R at IBM
    • Ingress at Berkeley
  • “Modern” implementation of RA: SQL
    • Both state of the art, mid-70s

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

relation operators
Relation operators
  • Basic operators:
    • Selection: s
    • Projection: P
    • Cartesian Product: 
  • Other set-theoretic ops:
    • Union: 
    • Intersection:
    • Difference: -
  • Additional operators:
    • Joins (natural, equijoin, theta join, semijoin)
    • Renaming: r
    • Grouping…

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

selection op
Selection op
  • Selects all tuples satisfying a condition
  • Notation: sc(R)
  • Examples
    • ssalary > 100000(Employee)
    • sname = “Smith”(Employee)
  • The condition c can have
    • comparison ops:=, <, , >,, <>
    • boolean ops: and, or

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

selection example

Theater

N’hood

Title

Sunshine

Village

Annie Hall

Sunshine

Village

Bad Edu.

Theater

N’hood

Title

Film Forum

Village

Masc. Fem.

Sunshine

Village

Annie Hall

Sunshine

Village

Bad Edu.

Selection example
  • Select the movies at Angelica:
    • sTheater=“Sunshine”(Showings)

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

projection op
Projection op
  • Keep only certain columns
  • Projection: op we used for decomposition
    • Eliminates other columns, then removes duplicates
  • Notation: PA1,…,An(R)

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

join op
Join op
  • Corresponds to SQL query doing cross & equality test
  • Specifically:

R1 R2 = Pevery att once(sshared atts =(R1 R2))

    • I.e., first compute the cross product R1 x R2
    • Next, select the rows in which shared fields agree
    • Finally, project onto the union of R1 and R2’s fields (remove duplicates)

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

rename op
Rename op
  • Changes the schema, not the instance
  • Notation: rB1,…,Bn(R)
  • r is spelled “rho”, pronounced “row”
  • Example:
    • Employee(ssn,name)
    • rE2(social, name)(Employee)
    • Or just: rE(Employee)

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

ra sql
RA  SQL
  • SQL SELECT RA Projection P
  • SQL WHERE  RA Selection s
  • SQL FROM  RA Join/cross
    • Comma-separated list…
  • SQL renaming  RA rho r
  • More ops later
  • Keep RA in the back of your mind…

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

next joins in sql
Next: Joins in SQL
  • Connect two or more tables:

Product

Company

What is

the connection

between

them?

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

joins in sql

Joinbetween Productand Company

Joins in SQL

Product (pname, price, category, manufacturer)

Company (cname, stockPrice, country)

Find all products under $200 manufactured in Japan;return their names and prices.

SELECT PName, PriceFROM Product, CompanyWHEREManufacturer=CName AND Country='Japan' AND Price <= 200

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

joins in sql1
Joins in SQL

Product

Company

SELECT PName, PriceFROM Product, CompanyWHEREManufacturer=CName AND Country='Japan' AND Price <= 200

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

joins in sql2
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, CompanyWHEREManufacturer=CName AND Category='Gadgets'

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

joins in sql3
Joins in SQL

Product

Company

SELECT CountryFROM Product, CompanyWHEREManufacturer=CName AND Category='Gadgets'

What is

the problem?

What’s thesolution?

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

joins
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, ProductWHEREpersname=buyer AND product = pname AND city='Seattle' AND category='Gadgets'

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

sql query semantics
SQL Query Semantics

Parallel assignment – all tuples

Doesn’t impose any order

SELECT a1, a2, …, ak

FROM R1 AS x1, R2 AS x2, …, Rn AS xn

WHERE Conditions

Answer = {}

for all assignments x1 in R1, …, xn in Rn do

if Conditions then

Answer = Answer  {(a1,…,ak)}

return Answer

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

sql query semantics1
SQL Query Semantics

Nested loops:

SELECT a1, a2, …, ak

FROM R1 AS x1, R2 AS x2, …, Rn AS xn

WHERE Conditions

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

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

multiple join syntaxes
Multiple join syntaxes
  • Old-style syntax simply lists tables separated by commas
  • New-style makes the join explicit:
  • Functionally equivalent to old-style, but perhaps more elegant
  • Introduced in Oracle 8i, MySQL 3.x/4.x
  • Older versions / other DBMSs may not support this

SELECT *

FROM A,B

WHERE …;

SELECT *

FROM A JOIN B ON …

WHERE …;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

new style join types
New-style join types
  • Cross joins (simplest):
    • FROM A CROSS JOIN B
  • Inner joins (regular joins):
    • FROM A [INNER] JOIN B ON …
  • Natural join:
    • FROM A NATURAL JOIN B;
    • Joins on common fields and merges
  • Outer joins (later)
    • No dangling rows

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

cross join e g
CROSS JOIN e.g.

MovieStar

MovieExec

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

cross join e g1
CROSS JOIN e.g.

SELECT *

FROM MovieStar CROSS JOIN MovieExec

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

join on e g
JOIN … ON e.g

SELECT *

FROM MovieStar JOIN MovieExec

ON MovieStar.Name <> MovieExec.Name

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

natural join
NATURAL JOIN
  • MovieStar(name, address, gender, birthdate)
  • MovieExec(name, address, networth)
  • Natural Join syntax:
    • FROM MovieStar NATURAL JOIN MovieExec
  • Results: list of movie stars who are also execs:
    • (Name, address, gender, birthdate, networth)

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

natural join e g
NATURAL JOIN e.g.

MovieStar

MovieExec

SELECT * FROM MovieStar NATURAL JOIN MovieExec

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

disambiguating attributes
Disambiguating Attributes
  • Sometimes two relations have the same attr:Person(pname, address, worksfor)Company(cname, address)

Whichaddress?

SELECT DISTINCT pname, addressFROM Person, CompanyWHEREworksfor = cname

SELECT DISTINCT Person.pname, Company.addressFROM Person, CompanyWHEREPerson.worksfor = Company.cname

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

tuple var e g
Tuple Var e.g.

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 DISTINCTx.store

FROM Purchase AS x, Purchase AS y

WHEREx.product = y.product AND y.store = 'BestBuy'

Result: (store)

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

details disambiguation in sql
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:
  • Prob:* is shorthand for all fields
    • each must be unambiguous
  • Soln: Select * from R R1, R R2

SQL> Select * from R, R;

Select * from R, R

*

ERROR at line 1:

ORA-00918: column ambiguously defined

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

details disambiguation in oracle sql
Details: Disambiguation in Oracle SQL
  • Depending on DBMS, can rename fields by:
    • Select name as n …
    • Select name n …
    • Select name=n… (not in Oracle)
  • Can rename relations only by
    • … from tab t1, tab t2

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

sql e g with tuple vars
SQL e.g. with tuple vars
  • Reps(ssn, name, etc.)
  • Clients(ssn, name, rssn)
  • Q: Who are George’s clients, in SQL?
  • Conceptually:
    • PClients.name(sReps.name=“George” and Reps.ssn=rssn(Reps x Clients))

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

ordering the results
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 category='gizmo' AND price > 50

ORDER BY price, pname

SELECT pname, price, manufacturer

FROM Product

WHERE category='gizmo' AND price > 50

ORDER BY price DESC, pname ASC

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

ordering the results1
Ordering the Results

SELECT Category

FROM Product

ORDER BY PName

?

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

details case sensitivity
Details: Case-sensitivity
  • In Oracle, compares are case-sensitive by default
  • If want case-insensitive, some options:
  • Create a function index
    • Maybe later…
  • Manually convert vals to upper/lower case
    • SQL> select * from emp where upper(ename) = upper(‘Blake');
  • Modify the nls_sort setting:
    • SQL> alter session set nls_sort=binary_ci;
    • SQL> alter session set nls_comp=ansi;
    • The other values: binary, binary_ai

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

the like operator
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 Name LIKE '%gizmo%'

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

the like operator1
The LIKE operator
  • Q: How to search the actual '%' char?
    • The usual meta-char issue
  • PName LIKE '%%%' won’t work
  • Instead, must use escape chars
    • In C/C++/J, prepend \
    • In SQL, prepend an arbitrary escape char:
  • PName LIKE '%x%%' ESCAPE 'x'

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

details more on escape chars
Details: more on escape chars
  • SQL: no official default escape char
  • In Oracle’s SQL*Plus: default escape char = '\'
    • Can set with SQL> 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'
  • No distinction between strings and single chars

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

details more on escape chars1
Details: more on escape chars
  • Q: Can an escape char be an escape string?
  • A: No.

SQL> select * from newtable where a like '%\%%' escape '\';

A B

---------- ----------

h%i there

SQL> 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

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

details more on single quotes
Details: more on single-quotes
  • Dates with DATE:
    • DATE '1948-05-14'
  • Timestamps with TIMESTAMP:
    • TIMESTAMP '1948-05-14 12:00:00'
  • Details may vary by DBMS…

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

details more on quotes
Details: more on quotes
  • Q: What about double quotes?
  • A: Can’t be used in place of single quotes
    • But are used…
  • 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);

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

another complex example
Another complex example
  • People(ssn, name, street, city, state, state)
  • Q: Who lives on George’s street?
  • A: First, generate pairs of (renamed) people:
    • rp1(People) x rp2(People)
  • Then pick out pairs with George:
    • sp1.name='George'(rp1(People) x rp2(People))
  • And refine to rows with George and someone else:
    • sp1.name='George‘ AND p1.name<>p2.name(rp1(People) x rp2(People))
  • Finally, project out the names:
    • Pp2.name(sp1.name='George‘ AND p1.name<>p2.name(rp1(People) x rp2(People))

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

live examples
Live examples
  • 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

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

more live examples
More live examples
  • Inner joins require an ON clause
    • Like a where clause
    • Arbitrary boolean expression
    • If always true (1=1), reduces to cross join
  • New compar op: BETWEEN
    • a between 5 and 10 a >= 5 and a <= 10
  • Q: produce a list of employees with their salary grades
    • emp, salgrade

M.P. Johnson, DBMS, Stern/NYU, Spring 2008

review
Review
  • Examples from sqlzoo.net

SELECTL

FROM R1, …, Rn

WHERE C

PL(sC(R1 x … Rn)

M.P. Johnson, DBMS, Stern/NYU, Spring 2008