1 / 31

C20.0046: Database Management Systems Lecture #9

C20.0046: Database Management Systems Lecture #9. M.P. Johnson Stern School of Business, NYU Spring, 2008. Agenda. Subqueries, etc. Sets, etc. From last time: escaping single quotes. http://reddit.com/info/69jzv/comments/. Subqueries. Subquery: copy in Conrad’s selection for his ssn:

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

More Related