Use Query By Example (QBE) to express queries 1,2,4,5,7,8 SP Suppliers SHSNAME S

Question

Use Query By Example (QBE) to express queries 1,2,4,5,7,8 SP Suppliers SHSNAME STATUS CITY SH P# QTY. S P 300 S P2 200 SP3 400 S P4 200 SIP5 100 S P6 100 S2 P 300 S2 P2 400 S3 P 300 S3P2 200 S4P2 200 S4 P3 300 S4P4 400 S4P 500 S4P6 600 S1 Smith20 London S2 Jones 30 Paris S2 Jones S3Blake S4Clark S5 Adams 30 20 London 30 Athens Paris Parts PA PNAME COLOR WEIGHT CITY S2P2 P Nut P2 BoltGreen P3 Screw P4 Screw Red P5 Cam Red 12.0London 17.0 17.0 Paris Blue Oslo 0London Blue 12.0 Paris Red 19.0 London

Explanation / Answer

4. Get supplier names for suppliers who supply parts with quanitity less than 300
   SELECT s.sname
   FROM suppliers s, sp
   WHERE s.s# = sp.s# and sp.qty < 300;
  
   Output:
   Sname
   ------
   Smith
   Blake
   Clark

5. Get supplier names for suppliers who supply either blue or green
   SELECT s.sname
   FROM s.s# = sp.s# and sp.p# = p.p#
       and (p.color = "Blue" or p.color = "Green");
   Output:
   Sname
   ------
   Smith
   Jones
   Blake
   Clark
6. Get supplier names for suppliers who does not supply any parts
   SELECT s.sname
   FROM Suppliers s
   WHERE s.s# NOT IN
       (SELECT sp.s#
       FROM sp, parts p
       WHERE sp.p# = p.p#);
   Output:
   Sname
   ------
   Adams
      
7. Get supplier names for suppliers who supply all parts
   SELECT distinct s.sname
   FROM s
   WHERE NOT EXISTS
       (SELECT *
       FROM parts p
       WHERE NOT EXISTS
           (SELECT *
           FROM sp
           WHERE sp.s# = s.s#
               and sp.p# = p.p#));
   Output:
   Sname
   ------
   Smith

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.