1 / 9

Data Manipulation 2

After this lecture, you should be able to: Use SQL SELECT statement effectively to retrieve the data from multiple related tables . Join Operation Aliasing of Tables Brainstorm on Assignment 3 (Part 1). Data Manipulation 2.

chione
Télécharger la présentation

Data Manipulation 2

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. After this lecture, you should be able to: Use SQL SELECT statement effectively to retrieve the data from multiple related tables. Join Operation Aliasing of Tables Brainstorm on Assignment 3 (Part 1). Data Manipulation 2

  2. Review: Get the S#'s, cities, and statuses of the suppliers in Athens or Paris. Sort the resultant table in the descending order of statuses. Table S sno | sname | status | city ----------------------------- s1 | Smith | 20 | London s2 | Jones | 10 | Paris s3 | Blake | 30 | Paris s4 | Clark | 20 | London s5 | Adams | 30 | Athens sno | city | status ----------------------- s3 | Paris | 30 s5 | Athens | 30 s2 | Paris | 10 select sno, city, status from s where city in (‘Athens’, ‘Paris’) order by status desc;

  3. Select Statements Select [Distinct] column(s) From table(s) Where condition [ Group By field(s) ] [ Having condition ] [ Order By field(s) ] ; • Select columns – Projection • From – Database tables • Where -- Selection condition • Group By – Partitioning data into groups • Having -- Group selection condition • Order By -- Sorting

  4. Get the names and cities of the suppliers who supplied P4. Join Operation Table S Table SP sno | sname | status | city sno | pno | qty ----------------------------- --------------- s1 | Smith | 20 | London s1 | p1 | 300 s2 | Jones | 10 | Paris s1 | p4 | 200 s3 | Blake | 30 | Paris s2 | p3 | 400 s3 | p4 | 100 sname city ----- ------ Smith London Blake Paris select sname, city from s, sp where s.sno = sp.sno and sp.pno = ‘p4’;

  5. Who supplied blue parts? Table S Table P sno | sname | sts | city pno | pname | color | wgt | city -------------------------- ---------------------------------- s1 | Smith | 20 | London p1 | nut | red | 12 | London s2 | Jones | 10 | Paris p2 | bolt | green | 17 | Paris p3 | screw | blue | 17 | Rome Table SP sno | pno | qty --------------- s1 | p1 | 300 s2 | p3 | 200 select sname from s, sp, p where s.sno = sp.sno and sp.pno = p.pno and color = 'blue' ; sname -------- Jones

  6. List the names and the colors of the parts supplied by the suppliers located in London. Table S Table P sno | sname | sts | city pno | pname | color | wgt | city -------------------------- ---------------------------------- s1 | Smith | 20 | London p1 | nut | red | 12 | London s2 | Jones | 10 | Paris p2 | bolt | green | 17 | Rome p3 | screw | blue | 17 | Rome Table SP sno | pno | qty --------------- s1 | p1 | 300 s1 | p2 | 250 s2 | p3 | 200 pname | color -------------- nut | red bolt | green select pname, color from s, p, sp where s.sno = sp.sno and sp.pno = p.pno and s.city = ‘London’ ;

  7. Aliasing of TablesWhich parts are of the same color? Table P pno | pname | color -------------------- p1 | nut | red p2 | screw | blue p3 | cam | blue p4 | cog | red Table P PX Table P PY pno | pname | color pno | pname | color -------------------- -------------------- p1 | nut | red p1 | nut | red p2 | screw | blue p2 | screw | blue p3 | cam | blue p3 | cam | blue p4 | cog | red p4 | cog | red px.pno | py.pno | color ----------------------- p2 | p3 | blue p1 | p4 | red select px.pno, py.pno, px.color from p px, p py where px.color = py.color and px.pno < py.pno ;

  8. Get the (distinct) names of the cities where both blue and green parts are located? Table P pno | pname | color | wgt | city ---------------------------------- p1 | nut | blue | 12 | London p2 | bolt | green | 17 | Rome p3 | screw | blue | 15 | Rome p4 | cog | green | 18 | London p5 | cam | red | 19 | Athens City -------- London Rome select distinct px.city from p px, p py where px.color = ‘blue’ and py.color = ‘green’ and px.city = py.city;

  9. Get the names of the suppliers who supplied p1 and p2. Table S Table SP sno | sname | status | city sno | pno | qty ----------------------------- --------------- s1 | Smith | 20 | London s1 | p1 | 300 s2 | Jones | 10 | Paris s1 | p3 | 200 s3 | Blake | 30 | Paris s2 | p1 | 400 s2 | p2 | 100 s2 | p3 | 50 s3 | p2 | 80 sname ----- Jones select sname from s, sp spx, sp spy where s.sno = spx.sno and spx.pno = ‘p1’ and s.sno = spy.sno and spy.pno = ‘p2’;

More Related