Data Manipulation 2

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’;