C 20. 0046: Database Management Systems Lecture #11 Matthew P. Johnson Stern School of Business, NYU Spring, 2004 M. P. Johnson, DBMS, Stern/NYU, Sp 2004 1
Agenda n Last time: Started SQL n This time: More SQL n Homework 2 is up M. P. Johnson, DBMS, Stern/NYU, Sp 2004 2
Review n Examples from sqlzoo. net SELECT L FROM R 1, …, Rn WHERE C PL(s. C(R 1 x … Rn) M. P. Johnson, DBMS, Stern/NYU, Sp 2004 3
First Unintuitive SQLism n SELECT R. A FROM R, S, T WHERE R. A=S. A OR R. A=T. A n Looking for R (S T) n But what happens if T is empty? n See transcript of this in Oracle on sales M. P. Johnson, DBMS, Stern/NYU, Sp 2004 4
More on escape chars n n SQL: no official default escape char In SQL*Plus: default escape char == ‘’ q q n n Other tools, DBMSs: your mileage may very SQL string literals put in ‘ ‘: q n Can set with SQL> set escape X ‘mystring’ Single-quote literals escaped with singlequotes: q ‘George’’s string’ M. P. Johnson, DBMS, Stern/NYU, Sp 2004 5
More on single-quotes n Bitstrings specified like regular strings but with B: q n Hex strings with X: q n X’ 7 ff’ Dates with DATE: q n B’ 001’ DATE ‘ 1948 -05 -14’ Timestamps with TIMESTAMP: q TIMESTAMP ‘ 1948 -05 -14 12: 00’ M. P. Johnson, DBMS, Stern/NYU, Sp 2004 6
Set/bag ops in SQL n Orthodox SQL has set operators: q n And bag operators: q n UNION, INTERSECT, EXCEPT UNION ALL, INTERSECT ALL, EXCEPT ALL These operators are applied to queries: (SELECT name FROM Person WHERE City=“Seattle”) UNION (SELECT name FROM Person, Purchase WHERE buyer=name AND store=“The Bon”) M. P. Johnson, DBMS, Stern/NYU, Sp 2004 7
Set/bag ops in Oracle SQL n n n 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 M. P. Johnson, DBMS, Stern/NYU, Sp 2004 8
Disambiguation in Oracle SQL n Can rename fields by q q n But not by q n Select name=n… Can rename relations only by q n Select name as n … Select name n … … from tab t 1, tab t 2 Lesson: if you get errors, remove all =s, ASs M. P. Johnson, DBMS, Stern/NYU, Sp 2004 9
Disambiguation in Oracle SQL n n Every selected field must be unambiguous For R(A, B), q q Select A from R, R Select R 1. A from R R 1, R R 2 n Consider: n Why? n * is shorthand for all fields, each must be unambiguous n Select * from R R 1, R R 2 SQL> Select * from R, R; Select * from R, R * ERROR at line 1: ORA-00918: column ambiguously defined M. P. Johnson, DBMS, Stern/NYU, Sp 2004 10
R. A. SQL n People(ssn, name, street, city, state) q n n n assume for clarity that cities are unique Q: Who lives on George’s street? In R. A. : sstreet=s 2 AND city=c 2(rp 2(s 2, c 2)(People) x Pstreet, city(sname=“George”(People))) In SQL? The other way in R. A. : People ⋈ Pstreet, city(sname=“George”(People)) In SQL? Later on… M. P. Johnson, DBMS, Stern/NYU, Sp 2004 11
R. A. SQL Acc(name, ssn, balance) n Q: Who has the largest balance? n In R. A. : Pname(Acc) - Pa 2. name(sa 2. bal < Acc. bal(Acc x ra 2(Acc))) n In SQL? n M. P. Johnson, DBMS, Stern/NYU, Sp 2004 12
Nulls in SQL n n If we don’t have a value, can put a NULL Null can mean several things: q q q n Value does not exists Value exists but is unknown Value not applicable The schema specifies whether null is allowed for each attribute q q not null if not allowed Otherwise, null is allowed M. P. Johnson, DBMS, Stern/NYU, Sp 2004 13
Null Values n n n x = NULL 4*(3 -x)/7 = NULL x = NULL x + 3 – x = NULL 3 + (x-x) = NULL x = NULL x = ‘Joe’ is UNKNOWN In general: no row use null fields appear in the selection test will pass the test Pace Boole, SQL has three boolean values: q q q FALSE TRUE UNKNOWN = = = 0 1 0. 5 M. P. Johnson, DBMS, Stern/NYU, Sp 2004 14
Null values in boolean expressions n n n C 1 AND C 2 C 1 OR C 2 NOT C 1 = min(C 1, C 2) = max(C 1, C 2) = 1 – C 1 SELECT * FROM Person WHERE (age < 25) AND (height > 6 OR weight > 190) n n n E. g. age=20 heigth=NULL weight=200 height > 6 = UNKNOWN OR weight > 190 = UNKOWN (age < 25) AND UNKNOWN = UNKNOWN M. P. Johnson, DBMS, Stern/NYU, Sp 2004 15
Comparing null and non-nulls n Unexpected behavior: SELECT * FROM Person WHERE age < 25 OR age >= 25 n n Some Persons are not included! The “trichotomy law” does not hold! M. P. Johnson, DBMS, Stern/NYU, Sp 2004 16
Testing for null values n Can test for NULL explicitly: q q x IS NULL x IS NOT NULL SELECT * FROM Person WHERE age < 25 OR age >= 25 OR age IS NULL n Now it includes all Persons M. P. Johnson, DBMS, Stern/NYU, Sp 2004 17
Evaluation strategies for SQL queries n Semantics of a SQL query defined in terms of the following conceptual evaluation strategy: q q n n Compute the cross-product of relation-list in FROM clause Discard resulting tuples if they fail WHERE clause Delete attributes that are not in SELECT clause If DISTINCT is specified, eliminate duplicate rows Often the least efficient way to compute a query! Optimizer finds better ways, but result is the same M. P. Johnson, DBMS, Stern/NYU, Sp 2004 18
Subqueries (5. 3) n Powerful feature of SQL: one clause can contain other SQL queries! q n So can FROM and HAVING clauses Several ways: q q Selection single constant (scalar) in WHERE Selection relation in FROM Etc. M. P. Johnson, DBMS, Stern/NYU, Sp 2004 19
Subquery motivation n Consider standard multi-table example: q q q Purchase(prodname, buyerssn, etc. ) Person(name, ssn, etc. ) What did Conrad buy? SELECT Purchase. prodname FROM Purchase, Person WHERE buyerssn = ssn AND name = ‘Conrad’ n As usual, need to AND on equality identifying ssn’s row and buyerssn’s row M. P. Johnson, DBMS, Stern/NYU, Sp 2004 20
Subquery motivation n Purchase(prodname, buyerssn, etc. ) Person(name, ssn, etc. ) What did Conrad buy? n Natural intuition: n n 1. Go find Conrad’s ssn 2. Then find purchases SELECT ssn FROM Person WHERE name = ‘Conrad’ SELECT Purchase. prodname FROM Purchase, Person WHERE buyerssn = Conrad’s-ssn M. P. Johnson, DBMS, Stern/NYU, Sp 2004 21
Subqueries n Subquery: copy in Conrad’s selection for his ssn: SELECT Purchase. prodname FROM Purchase WHERE buyerssn = (SELECT ssn FROM Person WHERE name = ‘Conrad’) n The subquery returns one value, so the = is valid n If it returns more, we get a run-time error. M. P. Johnson, DBMS, Stern/NYU, Sp 2004 22
Operators on selections Several new operators applied to (unary) selections: n 1. 2. 3. n n EXISTS R s > ALL R s > ANY R > is just an example op Each expression can be negated with NOT M. P. Johnson, DBMS, Stern/NYU, Sp 2004 23
Subqueries returning relations n n Q: Find companies Martha bought from Intuition: q q q Find Martha’s ssn Find Martha’s products Find those products’ companies SELECT Product. maker FROM Product WHERE Product. name IN (SELECT Purchase. product FROM Purchase WHERE Purchase. buyerssn = (SELECT ssn FROM Person WHERE name = ‘Martha’)) M. P. Johnson, DBMS, Stern/NYU, Sp 2004 24
Subqueries returning relations n Equivalent to: SELECT Product. maker FROM Product, Purchase, People WHERE Product. name = Purchase. product AND Purchase. buyerssn = ssn AND name = ‘Martha’ n But are they really equivalent? n Make both distinct to be sure M. P. Johnson, DBMS, Stern/NYU, Sp 2004 25
Subqueries returning relations You can also use: s > ALL R s > ANY R EXISTS R Product (pname, price, category, maker) Find products that are more expensive than all Gizmo-Works products SELECT name FROM Product WHERE price > ALL (SELECT price FROM Purchase WHERE maker=‘Gizmo-Works’) M. P. Johnson, DBMS, Stern/NYU, Sp 2004 26
Correlated Queries n n n So far, subquery executed once; result used for higher query More complicated: correlated queries “[T]he 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” (p 286). Q: What does this mean? A: That subqueries refer to vars from outer qureries M. P. Johnson, DBMS, Stern/NYU, Sp 2004 27
Correlated Queries n n Movie (title, year, director, length) Q: Find titles that are titles of multiple movies SELECT DISTINCT title FROM Movie AS x WHERE year <> ANY (SELECT year FROM Movie WHERE title = x. title); n correlation Note (1) scope of variables (2) this can still be expressed as single SFW M. P. Johnson, DBMS, Stern/NYU, Sp 2004 28
Complex Correlated Query n n Product (pname, price, category, maker, year) Find products (and their manufacturers) that are more expensive than all products made by the same manufacturer before 1972 SELECT DISTINCT pname, maker FROM Product AS x WHERE price > ALL (SELECT price FROM Product AS y WHERE x. maker = y. maker AND y. year < 1972); n Powerful, but much harder to optimize! M. P. Johnson, DBMS, Stern/NYU, Sp 2004 29
FROM subqueries n n Recall Q: Which companies did Martha buy from? Before: found ssn, found products, found companies SELECT Product. maker FROM Product WHERE Product. name IN (SELECT Purchase. product FROM Purchase WHERE Purchase. buyerssn = (SELECT ssn FROM Person WHERE name = ‘Martha’)) M. P. Johnson, DBMS, Stern/NYU, Sp 2004 30
FROM subqueries n n n Motivation for another way: suppose we’re given Martha’s purchases Then could just cross with Products and select identified rows Substitute (named) subquery for Martha’s 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 = Martha. product M. P. Johnson, DBMS, Stern/NYU, Sp 2004 31
Existential/Universal Conditions Product (pname, price, company) Company(cname, city) Find all companies s. t. some of their products have price < 100 SELECT DISTINCT Company. cname FROM Company, Product WHERE Company. cname = Product. company and Produc. price < 100 Existential: easy! M. P. Johnson, DBMS, Stern/NYU, Sp 2004 32
Existential/Universal Conditions Product (pname, price, company) Company(cname, city) Find all companies s. t. all of their products have price < 100 Universal: hard! M. P. Johnson, DBMS, Stern/NYU, Sp 2004 33
Existential/universal with IN 1. Find the other companies: i. e. s. t. some product 100 SELECT DISTINCT Company. cname FROM Company WHERE Company. cname IN (SELECT Product. company FROM Product WHERE Produc. price >= 100 2. Find all companies s. t. all their products have price < 100 SELECT DISTINCT Company. cname FROM Company WHERE Company. cname NOT IN (SELECT Product. company FROM Product WHERE Produc. price >= 100 M. P. Johnson, DBMS, Stern/NYU, Sp 2004 34
More on Set-Comparison Operators n n We’ve 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 Horatio: SELECT R. SID FROM Reserves R WHERE R. rating > ANY (SELECT R 2. rating FROM Reserves R 2 WHERE R 2. sname=‘Horatio’) M. P. Johnson, DBMS, Stern/NYU, Sp 2004 35
Joins operations n Variations: q q n Cross join (Cartesian product) Join … On Natural join Outer join Apply to relations appearing in selections M. P. Johnson, DBMS, Stern/NYU, Sp 2004 36
Cross join - example Address Gender Birthdate Hanks 123 Palm Rd M 01/01/60 456 Maple Av F 02/02/40 Lucas Movie. Exec Name Taylor Movie. Star 789 Oak St M 03/03/55 Address Networth Name Spielberg 246 Palm Rd 10 M Taylor 456 Maple Av 20 M Lucas 789 Oak St M. P. Johnson, DBMS, Stern/NYU, Sp 2004 30 M 37
Cross join – example n Select * From Movie. Star Cross Join Movie. Exec Row Movie. S tar. nam e Movie. Star. add ress Movie. Sta r. Gender Movie. Star. Movie. Exec. Ad Birthdate ec. Name dress Movie. Ex ec. Networth 1 Hanks 123 Palm Rd M 01/01/60 Spielberg 246 Palm Rd 10 M 2 Hanks 123 Palm Rd M 01/01/60 Taylor 456 Maple Av 20 M 3 Hanks 123 Palm Rd M 01/01/60 Lucas 789 Oak St 30 M 4 Taylor 456 Maple Av F 02/02/40 Spielberg 246 Palm Rd 10 M 5 Taylor 456 Maple Av F 02/02/40 Taylor 456 Maple Av 20 M Taylor 456 Maple Av F 02/02/40 Lucas 789 Oak St 30 M Lucas 789 Oak St M 03/03/55 Spielberg 246 Palm Rd 10 M Lucas 789 Oak St M 03/03/55 Taylor 456 Maple Av 20 M Lucas 789 Oak St M 03/03/55 Lucas 789 Oak St 30 M 6 7 8 9 M. P. Johnson, DBMS, Stern/NYU, Sp 2004 38
Join … On: example n Select * From Movie. Star Join Movie. Exec On Movie. Star. Name <> Movie. Exec. Name Row Movie. St ar. name Movie. Star. addr ess Movie. Sta r. Gender Movie. Star. Birthdate Movie. Exe c. Name Movie. Exec. Add ress Movie. Exe c. Networth 1 Hanks 123 Palm Rd M 01/01/60 Spielber g 246 Palm Rd 10 M Hanks 123 Palm Rd M 01/01/60 Taylor 456 Maple Av 20 M Hanks 123 Palm Rd M 01/01/60 Lucas 789 Oak St 30 M Taylor 456 Maple Av F 02/02/40 Spielber g 246 Palm Rd 10 M 6 Taylor 456 Maple Av F 02/02/40 Taylor 456 Maple Av 20 M 7 Taylor 456 Maple Av F 02/02/40 Lucas 789 Oak St 30 M 8 Lucas 789 Oak St M 03/03/55 Spielber g 246 Palm Rd 10 M Lucas 789 Oak St M 03/03/55 Taylor 456 Maple Av 20 M Lucas Stern/NYU, 789 Oak St M. P. Johnson, DBMS, 03/03/55 Sp 2004 M Lucas 789 Oak St 30 M 39 2 3 4 5 9
Natural Joins Movie. Star(name, address, gender, birthdate) Movie. Exec(name, address, networth) Natural Join: Movie. Star Natural Join Movie. Exec; n Results in: list of individuals who are moviestars as well as executives: (Name, address, gender, birthdate, networth) M. P. Johnson, DBMS, Stern/NYU, Sp 2004 40
Example - Natural join Name Address Gender Birthdate Hanks 123 Palm Rd M 01/01/60 Taylor 456 Maple Av F 02/02/40 Lucas Movie. Star M 03/03/55 Address Networth Movie. Exec 789 Oak St Name Spielberg 246 Palm Rd 10 M Taylor 456 Maple Av 20 M Lucas 789 Oak St 30 M Select *from Movie. Star Natural Join Movie. Exec Name Address Taylor Lucas Gender Birthdate Networth 456 Maple Av F 02/02/40 20 M 789 Oak St 03/03/55 30 M M M. P. Johnson, DBMS, Stern/NYU, Sp 2004 41
Outer Join - Example Movie. Star Movie. Exec Name Address Gender Birthdate Name Address Networth Hanks 123 Palm Rd M 01/01/60 Spielberg 246 Palm Rd 10 M Taylor 456 Maple Av F 02/02/40 Taylor 456 Maple Av 20 M Lucas 789 Oak St M 03/03/55 Lucas 789 Oak St 30 M Select *from Movie. Star NATURAL FULL OUTER JOIN Movie. Exec Name Address Gender Birthdate Networth Hanks 123 Palm Rd M 01/01/60 NULL Spielberg 246 Palm Rd NULL 10 M Taylor 456 Maple Av F 02/02/40 20 M Lucas 789 Oak St M 03/03/55 30 M M. P. Johnson, DBMS, Stern/NYU, Sp 2004 42
Outer Join - Example n Select *from Movie. Star LEFT OUTER JOIN Movie. Exec Name Address Gender Birthdate Networth Hanks 123 Palm Rd M 01/01/60 NULL Spielberg 246 Palm Rd NULL 10 M Taylor 456 Maple Av F 02/02/40 20 M Lucas 789 Oak St M 03/03/55 30 M Select *from Movie. Star RIGHT OUTER JOIN Movie. Exec Name Address Gender Birthdate Networth Hanks 123 Palm Rd M 01/01/60 NULL Spielberg 246 Palm Rd NULL 10 M Taylor 456 Maple Av F 02/02/40 20 M Lucas 789 Oak St M 03/03/55 30 M M. P. Johnson, DBMS, Stern/NYU, Sp 2004 43
R. A. SQL n People(ssn, name, street, city, state) q n n n assume for clarity that cities are unique Q: Who lives on George’s street? Now, the second way in R. A. : People ⋈ Pstreet, city(sname=“George”(People)) In SQL? M. P. Johnson, DBMS, Stern/NYU, Sp 2004 44
Live Examples n Examples from sqlzoo. net M. P. Johnson, DBMS, Stern/NYU, Sp 2004 45
Скачать презентацию C 20 0046 Database Management Systems Lecture 11
d5e092e35ab5579a242ca1e1cde8e8b3.ppt