SQL queries on following Tables SUPPLIERS,PARTS, SUPPLIER_PART Please Help me ou

Question

SQL queries on following Tables SUPPLIERS,PARTS, SUPPLIER_PART

Please Help me out in these queries.

1) Get supplier names for suppliers who supply the parts. Blue Parts.

2)    Get all pairs of supplier numbers who reside in the same city.

3)    Get supplier names for suppliers who do not supply part p2.

4) Get supplier names for suppliers who supply all parts.

s (Supplier) SNAME Smith S2 Jones S3 Blake S4 Clark S5 Adams P Part PNAME PI Nut Bolt P2 P3 Screw P4 Screw P5 Cam P6 Cog SP 300 P2 200 400 P4, 200 PS 100 100 S2 Pu 300 P2 400 P2 200 P2 200 S4 P4 300 400 STATUS 20 30 20 30 COLOR Red Green Blue Red Blue Red CITY London Paris Paris London Athens WEIGHT CITY 12.0 17.00 17.0 14.0 12.0 19.00 London Paris Rome London Paris London

Explanation / Answer

1) Get supplier names for suppliers who supply the parts. Blue Parts.
sol: select sname from s, sp , p   where s.s#=sp.s# and p.p#=sp.p# and p.color='Blue';
           or
select sname from s where s#=(select s# from sp where p#=(select p# from p where color='Blue'));

3) Get supplier names for suppliers who do not supply part p2.
sol: select sname from s where s#=(select s# from sp where p#=(select p# from p where p# <>'P2'));
4) Get supplier names for suppliers who supply all parts.
sol: select sname from s where s#=(select s# from sp where p#=(select p# from p where p# in ('P1','P2','P3','P4','P5','P6')));

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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