title: find every item and the most expensive with some condition
body: <p>I have the following relations and </p>
  
  <p>1.I want to find the pids of parts supplied by every supplier and at less than 200.
  (If any supplier either does not simply the part or charges more than 200 for it, the part is not selected)</p>
  
  <p>The fiddle <a href="http://sqlfiddle.com/#!2/4b5d4" rel="nofollow">http://sqlfiddle.com/#!2/4b5d4</a></p>
  
  <pre><code>Supplies            
  sid     1   2   3   4   5
  sname   Jason   David   John    Peter   Jay
  address 221 2b  3c  4d  5e
  
  
  Parts                       
  pid     10  20  30  40  50  60
  pname   Head    Body    Hand    Leg Arm Foot
  color   red blue    green   white   red green
  
  
  Catalog             
  sid     1   1   2   2   3   4   1   1   4   4   1   5   5   3   3   2
  pid     10  20  20  30  30  40  30  40  10  50  50  50  10  60  10  10
  cost    150 220 150 150 130 125 130 280 123 126 120 100 100 210 100 50
  </code></pre>
  
  <p>So I do the following query
  I think that it equal that <code>there is no parts that the suppliers don't supply it.</code></p>
  
  <pre><code>SELECT C.pid
  FROM CATALOG C
  WHERE C.cost &lt; 200
    AND NOT EXISTS (SELECT S.sid
                    FROM Suppliers S
                    WHERE NOT EXISTS
                    (SELECT P.pid
                     FROM Parts P
                     WHERE P.pid = C.pid
                     AND S.sid = C.sid ))
  </code></pre>
  
  <p>but it return null, it should return 10.</p>
  
  <p>2.I want to find for every supplier that supplies a green part and a red part, print the name and price of the most expensive part that she supplies.</p>
  
  <p>I just can find out the green and red part but cannot find the most expensive.</p>
  
  <p>The query is that I have tried.</p>
  
  <pre><code>SELECT S.sname
  FROM Suppliers AS S,
       Parts AS P1,
       CATALOG AS C1,
       Parts AS P2 ,
       CATALOG AS C2
  WHERE S.sid = C1.sid
    AND C1.pid = P1.pid
    AND S.sid = C2.sid
    AND C2.pid = P2.pid
    AND ( P1.color = 'red'
         AND P2.color = 'green' )
  </code></pre>
  
  <p>How do I fix it? Thank in advance.</p>
  
  <p>ps Sorry that I have to go to class and I will reply about 6 hours later.</p>