1 / 45

C20.0046: Database Management Systems Lecture #8

C20.0046: Database Management Systems Lecture #8. M.P. Johnson Stern School of Business, NYU Spring, 2008. Agenda. Subqueries, etc. Hw1 on Blackboard. Relation operators. Basic operators: Selection: s Projection: P Cartesian Product:  Other set-theoretic ops: Union: 

jereni
Download Presentation

C20.0046: Database Management Systems Lecture #8

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. C20.0046: Database Management SystemsLecture #8 M.P. Johnson Stern School of Business, NYU Spring, 2008 M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  2. Agenda • Subqueries, etc. • Hw1 on Blackboard M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  3. 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

  4. 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

  5. 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

  6. 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

  7. Ordering the Results SELECT Category FROM Product ORDER BY PName ? M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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

  14. New topic: Subqueries • Powerful feature of SQL: one clause can contain other SQL queries • Anywhere where a value or relation is allowed • Several ways: • Selection  single constant (scalar) in SELECT • Selection  single constant (scalar) in WHERE • Selection  relation in WHERE • Selection  relation in FROM M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  15. Standard multi-table example • Purchase(prodname, buyerssn, etc.) • Person(name, ssn, etc.) • Q: What did Christo buy? • As usual, need to AND on equality identifying ssn’s row and buyerssn’s row SELECT Purchase.prodnameFROM Purchase, PersonWHERE buyerssn = ssn AND name = 'Christo' M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  16. Subquery motivation • Purchase(prodname, buyerssn, etc.) • Person(name, ssn, etc.) • Q: What did Christo buy? • Natural intuition: • Go find Conrad’s ssn • Then find purchases SELECT ssnFROM PersonWHERE name = 'Christo' SELECT Purchase.prodnameFROM PurchaseWHERE buyerssn = Christo’s-ssn M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  17. Subqueries • Subquery: copy in Conrad’s selection for his ssn: • The subquery returns one value, so the = is valid • If it returns more (or fewer), we get a run-time error SELECT Purchase.prodname FROM Purchase WHERE buyerssn = (SELECT ssn FROM Person WHERE name = 'Christo') M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  18. Operators on subqueries • Several new operators applied to (unary) selections: • IN R • EXISTS R • UNIQUE R • s > ALL R • s > ANY R • x IN R • > is just an example op • Each expression can be negated with NOT M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  19. Subqueries with IN • Product(prodname,maker), Person(name,ssn), Purchase(buyerssn,product) • Q: Find companies Martha bought products from • Strategy: • Find Martha’s ssn • Find products listed with that ssn as buyer • Find company names of those products SELECT DISTINCT Product.maker FROM Product WHERE prodname IN (SELECT product FROM Purchase WHERE buyerssn = (SELECT ssn FROM Person WHERE name = 'Martha')) M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  20. Subqueries returning relations • Equivalent to: • Or: SELECT DISTINCT Product.maker FROM Product, Purchase, People WHERE prodname = product AND buyerssn = ssn AND name = 'Martha' SELECT DISTINCT Product.maker FROM Product JOIN Purchase ON prodname=product JOIN People ON buyerssn=ssn WHERE name = 'Martha' M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  21. FROM subqueries • Motivation for another way: • suppose we’re given Martha’s purchases • Then could just cross with Products to get product makers •  Substitute (named) subquery for Martha’s purchases SELECT maker FROM Product, (SELECT product FROM Purchase WHERE buyerssn = (SELECT ssn FROM Person WHERE name = 'Martha')) Marthas WHERE Product.name = Marthas.product M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  22. 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… M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  23. seller-ssn=ssn pid=pid buyer-ssn=Person.ssn Complex RA Expressions Pname Person Purchase Person Product Pssn Ppid sname='Dick' sname='Gizmo' M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  24. Translation to SQL • We’re converting the tree on the last slide into SQL • The result of the query should be the names indicated above • One step at a time, we’ll make the query more complete, until we’ve translated the English-language description to an actual SQL query • We’ll also simplify the query when possible (the names of the people who bought gadgets from Dick) M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  25. Translation to SQL • Blue type = actual SQL • Black italics = description of subquery • Note: the subquery above consists of purchase records, except with the info describing the buyers attached • In the results, the column header for name will be 'buyer' SELECT DISTINCT name buyer FROM (the info, along with buyer names, for purchases of gadgets sold by Dick) M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  26. Translation to SQL • Note: the subquery in this version is being given the name P2 • We’re pairing our rows from Person with rows from P2 SELECT DISTINCT name buyer FROM (SELECT * FROM Person,(the purchases of gadgets from Dick)P2 WHERE Person.ssn = P2.buyer-ssn) M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  27. Translation to SQL • We simplified by combining the two SELECTs SELECT DISTINCT name buyer FROM Person,(the purchases of gadgets from Dick)P2 WHERE Person.ssn = P2.buyer-ssn M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  28. Translation to SQL • P2 is still the name of the subquery • It’s just been filled in with a query that contains two subqueries • Outer parentheses are bolded for clarity SELECT DISTINCT name buyer FROM Person, (SELECT * FROM Purchases WHERE seller-ssn = (Dick’s ssn) AND pid = (the id of gadget)) P2 WHERE Person.ssn = P2.buyer-ssn M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  29. Translation to SQL • Now the subquery to find Dick’s ssn is filled in SELECT DISTINCT name buyer FROM Person, (SELECT * FROM Purchases WHERE seller-ssn = (SELECT ssn FROM Person WHERE name='Dick') AND pid = (the id of gadget)) P2 WHERE Person.ssn = P2.buyer-ssn M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  30. Translation to SQL • And now the subquery to find Gadget’s product id is filled in, too • Note: the SQL simplified by using subqueries • Not used in relational algebra SELECT DISTINCT name buyer FROM Person, (SELECT * FROM Purchases WHERE seller-ssn = (SELECT ssn FROM Person WHERE name='Dick') AND pid = (SELECT pid FROM Product WHERE name='Gadget')) P2 WHERE Person.ssn = P2.buyer-ssn M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  31. George’s-neighbors with subqueries • People(ssn, name, street, city, state, state) • Q: Who lives on George’s street? • A: First, find George: • sname='George'(People) • And get George’s street/city/state: • Pstreet(sname='George'(People)) • Look up people on that street… M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  32. Next:ALL op Employees(name, job, divid, salary) Find which employees are paid more than all the programmers SELECT name FROM Employees WHERE salary > ALL (SELECT salary FROM Employees WHERE job='programmer') M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  33. ANY/SOME op Employees(name, job, divid, salary) Find which employees are paid more than at least one vice president SELECT name FROM Employees WHERE salary > ANY (SELECT salary FROM Employees WHERE job='VP') M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  34. ANY/SOME op Employees(name, job, divid, salary) Find which employees are paid more than at least one vice president SELECT name FROM Employees WHERE salary > SOME (SELECT salary FROM Employees WHERE job='VP') M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  35. Existential/Universal Conditions Employees(name, job, divid, salary) Division(name, id, head) Find all divisions with an employee whose salary is > 100000 SELECT DISTINCT Division.name FROM Employees, Division WHERE salary > 100000 AND divid=id Existential: easy! M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  36. Existential/Universal Conditions Employees(name, job, divid, salary) Division(name, id, head) Find all divisions in which everyone makes > 100000 Existential: easy! M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  37. Existential/universal with IN 1. Find the other divisions: in which someone makes <= 100000: SELECT name FROM Division WHERE id IN (SELECT divid FROM Employees WHERE salary <= 100000 2. Select the divisions we didn’t find: SELECT name FROM Division WHERE id NOT IN (SELECT divid FROM Employees WHERE salary <= 100000 M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  38. Next: correlated subqueries • Acc(name,bal,type…) • Q: Who has the largest balance? • Can we do this with subqueries? M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  39. Correlated Queries • Acc(name,bal,type,…) • Q: Find holder of largest account SELECT name FROM Acc WHERE bal >= ALL (SELECT bal FROM Acc) M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  40. Correlated Queries • 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” (Ullman, p286). • Q: What does this mean? • A: That subqueries refer to vars from outer queries M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  41. Correlated Queries • Acc(name,bal,type,…) • Q2: Find holder of largest account of each type SELECT name, type FROM Acc WHERE bal >= ALL (SELECT bal FROM Acc WHERE type=type) correlation M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  42. Correlated Queries • Acc(name,bal,type,…) • Q2: Find holder of largest account of each type • Note: • scope of variables • this can still be expressed as single SFW SELECT name, type FROM Acc a1 WHERE bal >= ALL (SELECT bal FROM Acc WHERE type=a1.type) correlation M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  43. Review • Examples from sqlzoo.net SELECTL FROM R1, …, Rn WHERE C PL(sC(R1 x … Rn) M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  44. 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

  45. 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

More Related