Upgrade to Pro — share decks privately, control downloads, hide ads and more …

SQL 2

Lipyeow
August 17, 2015

SQL 2

SQL

Lipyeow

August 17, 2015
Tweet

More Decks by Lipyeow

Other Decks in Technology

Transcript

  1. ICS  321  Data  Storage  &  Retrieval   The  Database  Language

     SQL  (ii)   Asst.  Prof.    Lipyeow  Lim   InformaFon  &  Computer  Science  Department   University  of  Hawaii  at  Manoa   1   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa  
  2. UNION,  INTERSECT  &  EXCEPT   •  Set-­‐manipulaFon  constructs  for  result

     sets  of  SQL   queries  that  are  union-­‐compa*ble   •  Can  simplify  some  complicated  SQL  queries   •  Consider  Q5:  Find  the  names  of  sailors  who  have   reserved  a  red  or  a  green  boat   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   2   SELECT S1.sname FROM Sailors S1, Reserves R1, Boats B1 WHERE S1.sid=R1.sid AND R1.bid=B1.bid AND ( B1.color=`red’ OR B1.color=`green’)
  3. Q6:  Find  the  names  of  sailors  who  have   reserved

     both  a  red  and  a  green  boat   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   3   SELECT S1.sname FROM Sailors S1, Reserves R1, Boats B1 WHERE S1.sid=R1.sid AND R1.bid=B1.bid AND ( B1.color=`red’ OR AND B1.color=`green’) SELECT S1.sname FROM Sailors S1, Reserves R1, Boats B1, Reserves R2, Boats B2 WHERE S1.sid=R1.sid AND R1.bid=B1.bid AND S1.sid=R2.sid AND R2.bid=B2.bid AND B1.color=`red’ AND B2.color=`green’
  4. Q6  with  INTERSECT  :  Find  the  names  of  sailors  

    who  have  reserved  both  a  red  and  a  green  boat   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   4   SELECT S1.sname FROM Sailors S1, Reserves R1, Boats B1 WHERE S1.sid=R1.sid AND R1.bid=B1.bid AND B1.color=`red’ INTERSECT SELECT S2.sname FROM Sailors S2, Reserves R2, Boats B2 WHERE S2.sid=R2.sid AND R2.bid=B2.bid AND B2.color=`green’
  5. Q6  Nested:  Find  the  names  of  sailors  who  have  

    reserved  both  a  red  and  a  green  boat   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   5   SELECT S3.sname FROM Sailors S3 WHERE S3.sid IN ( SELECT S1.sid FROM Sailors S1, Reserves R1, Boats B1 WHERE S1.sid=R1.sid AND R1.bid=B1.bid AND B1.color=`red’ INTERSECT SELECT S2.sid FROM Sailors S2, Reserves R2, Boats B2 WHERE S2.sid=R2.sid AND R2.bid=B2.bid AND B2.color=`green’ )
  6. Q5  with  UNION  :  Find  the  names  of  sailors  who

      have  reserved  a  red  or  a  green  boat   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   6   SELECT S1.sname FROM Sailors S1, Reserves R1, Boats B1 WHERE S1.sid=R1.sid AND R1.bid=B1.bid AND B1.color=`red’ UNION SELECT S2.sname FROM Sailors S2, Reserves R2, Boats B2 WHERE S2.sid=R2.sid AND R2.bid=B2.bid AND B2.color=`green’
  7. Q19:  Find  the  sids  of  sailors  who  have  reserved  

    red  boats  but  not  green  boats   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   7   SELECT S1.sid FROM Sailors S1, Reserves R1, Boats B1 WHERE S1.sid=R1.sid AND R1.bid=B1.bid AND B1.color=`red’ EXCEPT SELECT S2.sid FROM Sailors S2, Reserves R2, Boats B2 WHERE S2.sid=R2.sid AND R2.bid=B2.bid AND B2.color=`green’
  8. Find  the  sid  of  sailors  who  have   reserved  exactly

     one  boat   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   8   SELECT S1.sid FROM Sailors S1 EXCEPT SELECT R1.sid FROM Reserves R1, Boats B1, Reserves R2, Boats B2 WHERE R1.sid=R2.sid AND R1.bid=B1.bid AND R2.bid=B2.bid AND R1.bid≠R2.bid SELECT R3.sid FROM Reserves R3 EXCEPT SELECT R1.sid FROM Reserves R1, Boats B1, Reserves R2, Boats B2 WHERE R1.sid=R2.sid AND R1.bid=B1.bid AND R2.bid=B2.bid AND R1.bid≠R2.bid
  9. Nested  Queries   •  A  nested  query  is  a  query

     that  has  another  query,  called  a   subquery,  embedded  within  it.   •  Subqueries  can  appear  in  WHERE,  FROM,  HAVING  clauses   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   9   SELECT S.sname FROM Sailors S WHERE S.sid IN ( SELECT R.sid FROM Reserves R WHERE R.bid=103 ) SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND bid=103 Q1 : Find the names of sailors who have reserved boat 103
  10. Conceptual  EvaluaFon  Strategy  for   Nested  Queries   1.  Compute

     the  cross-­‐product  of  rela*on-­‐list.   !  If  there  is  a  subquery,  recursively  (re-­‐)compute  the   subquery  using  this  conceptual  evaluaFon  strategy   !  Compute  the  cross-­‐product  over  the  results  of  the   subquery.   2.  Discard  resulFng  tuples  if  they  fail  qualifica*ons.   !  If  there  is  a  subquery,  recursively  (re-­‐)compute  the   subquery  using  this  conceptual  evaluaFon  strategy   !  Evaluate  the  qualificaFon  condiFon  that  depends  on  the   subquery   3.  Delete  a`ributes  that  are  not  in  target-­‐list.   4.  If  DISTINCT  is  specified,  eliminate  duplicate  rows.   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   10  
  11. Q2:  Find  the  names  of  sailors  who   have  reserved

     a  red  boat   •  Unravel  the  nesFng  from  the  innermost   subquery   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   11   SELECT S.sname FROM Sailors S WHERE S.sid IN ( SELECT R.sid FROM Reserves R WHERE R.bid IN ( SELECT B.bid FROM Boats B WHERE B.color=`red’ ))
  12. Q21:  Find  the  names  of  sailors  who   have  not

     reserved  a  red  boat     Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   12   SELECT S.sname FROM Sailors S WHERE S.sid NOT IN ( SELECT R.sid FROM Reserves R WHERE R.bid IN ( SELECT B.bid FROM Boats B WHERE B.color=`red’ ))
  13. Correlated  Nested  Queries   •  EXISTS  is  another  set  comparison

     operator,  like  IN.       •  If  UNIQUE  is  used,  and  *  is  replaced  by  R.bid,  finds   sailors  with  at  most  one  reservaFon  for  boat  #103.     (UNIQUE  checks  for  duplicate  tuples;  *  denotes  all   a`ributes.    Why  do  we  have  to  replace  *  by  R.bid?)   •  Illustrates  why,  in  general,  subquery  must  be  re-­‐ computed  for  each  Sailors  tuple.   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   13   SELECT S.sname FROM Sailors S WHERE EXISTS ( SELECT * FROM Reserves R WHERE R.bid = 103 AND R.sid=S.sid Q1: Find the names of sailors who’ve reserved boat #103
  14. Set  Comparison  Operators:  ANY   •  Q22:  Find  sailors  whose

     raFng  is  be`er  than   some  sailor  called  HoraFo.   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   14   SELECT S1.sid FROM Sailors S1 WHERE S1.rating > ANY ( SELECT S2.rating FROM Sailors S2 WHERE S2.name=`Horatio’ ) •  Subquery  must  return  a  row  that  makes  the   comparison  true,  in  order  for  S1.raFng>ANY  to   return  true  
  15. Set  Comparison  Operators:  ALL   •  Q23:  Find  sailors  whose

     raFng  is  be`er  than   every  sailor.   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   15   SELECT S1.sid FROM Sailors S1 WHERE S1.rating >= ALL ( SELECT S2.rating FROM Sailors S2) •  Every  row  returned  by  the  subquery  must   makes  the  comparison  true,  in  order  for   S1.raFng>=ALL  to  return  true  
  16. RewriFng  INTERSECT  Queries  using  IN   •  Q6:  Find  sid’s

     of  sailors  who’ve  reserved  both   a  red  and  a  green  boat.   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   16   SELECT S1.sid FROM Sailors S1, Boats B1, Reserves R1 WHERE S1.sid=R1.sid AND R1.bid=B1.bid AND B1.color=‘red’ AND S1.sid IN ( SELECT S2.sid FROM Sailors S2, Boats B2, Reserves R2 WHERE S2.sid=R2.sid AND R2.bid=B2.bid AND B2.color=`green’ )
  17. Q9:  Find  the  names  of  sailors  who   have  reserved

     all  boats   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   17   SELECT S.sname FROM Sailors S WHERE NOT EXISTS (( SELECT B.bid FROM Boats B ) EXCEPT ( SELECT R.bid FROM Reserves R WHERE R.sid=S.sid ))
  18. Q9:  Find  the  names  of  sailors  who  have   reserved

     all  boats  (without  EXCEPT)   Lipyeow  Lim  -­‐-­‐  University  of  Hawaii  at  Manoa   18   SELECT S.sname FROM Sailors S WHERE NOT EXISTS ( SELECT B.bid FROM Boats B WHERE NOT EXISTS ( SELECT R.bid FROM Reserves R WHERE R.bid=B.bid AND R.sid=S.sid ))