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

Loading in 2 Seconds...

  share
play fullscreen
1 / 31
lanza

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

91 Views
Download Presentation
C20.0046: Database Management Systems Lecture #9
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

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

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

  3. From last time: escaping single quotes • http://reddit.com/info/69jzv/comments/ M.P. Johnson, DBMS, Stern/NYU, Spring 2008

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

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

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

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

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

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

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

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

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

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

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

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

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

  17. New topic: R.A./SQL Set Operators • Relations are sets  have set-theoretic ops • Venn diagrams • Union: R1  R2 • Example: • ActiveEmployees  RetiredEmployees • Difference: R1 – R2 • Example: • AllEmployees – RetiredEmployees = ActiveEmployees • Intersection: R1  R2 • Example: • RetiredEmployees UnionizedEmployees M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  18. Set operations - example R: S: R  S: M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  19. Set operations - example R: S: R  S: M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  20. Set operations - example R: S: R - S: M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  21. Set ops in SQL • Orthodox SQL has set operators: • UNION, INTERSECT, EXCEPT • Oracle SQL uses MINUS rather than EXCEPT • See the Ullman page on more differences • These ops applied to queries: (SELECT name FROM Person WHERE City = 'New York') INTERSECT (SELECT custname FROM Purchase WHERE store='Kim''s') M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  22. Boat examples • Reserve(ssn,bmodel,color) • Q: Find ssns of sailors who reserved red boats or green boats SELECT DISTINCT ssn FROM reserve WHERE color = 'red' OR color = 'green' M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  23. Boat examples • Reserve(ssn,bmodel,color) • Q: Find ssns of sailors who reserved red boats and green boats SELECT DISTINCT ssn FROM reserve WHERE color = 'red' AND color = 'green' M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  24. Boat examples • Reserve(ssn,bmodel,color) • Q: Find ssns of sailors who reserved red boats and green boats SELECT DISTINCT r1.ssn FROM reserve r1, reserve r2 WHERE r1.ssn = r2.ssn AND r1.color = 'red' AND r2.color = 'green' M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  25. Boat examples • Reserve(ssn,bmodel,color) • Q: Find ssns of sailors who reserved red boats and green boats (SELECT DISTINCT ssn FROM reserve WHERE color = 'red') INTERSECT(SELECT DISTINCT ssn FROM reserve WHERE color = 'green') M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  26. Boat examples • Reserve(ssn,bmodel,color) • Q: Find ssns of sailors who reserved red boats or green boats (SELECT DISTINCT ssn FROM reserve WHERE color = 'red') UNION (SELECT DISTINCT ssn FROM reserve WHERE color = 'green') M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  27. Boat examples • Reserve(ssn,bmodel,color) • Q: Find ssns of sailors who reserved red boats but not green boats (SELECT DISTINCT ssn FROM reserve WHERE color = 'red') EXCEPT (SELECT DISTINCT ssn FROM reserve WHERE color = 'green') M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  28. Union-Compatibility • Situation: Cust1(name,address,…), Cust2(name,…) • Want: report of all customer names and addresses (if known) • Can’t do: • Both tables must have same sequence of types • Applies to all set ops (SELECT name, address FROM Cust1) UNION (SELECT name FROM Cust2) M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  29. Result(name, address) Union-Compatibility • Situation: Cust1(name,address,…), Cust2(name,…) • Want: report of all customer names and addresses (if known) • But can do: • Resulting field names taken from first table (SELECT name, address FROM Cust1) UNION (SELECT name, '(N/A)' FROM Cust2) M.P. Johnson, DBMS, Stern/NYU, Spring 2008

  30. First Unintuitive SQLism • Looking for R  (S  T) • But what happens if T is empty? • See transcript of this in Oracle on sales SELECT R.A FROM R, S, T WHERE R.A=S.A OR R.A=T.A M.P. Johnson, DBMS, Stern/NYU, Spring 2008

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