Given tables above, write the sql statement AND RESULTING TABLE for following: a

Question

Given tables above, write the sql statement AND RESULTING TABLE for following:

a. Get supplier numbers for suppliers supplying at least one part supplied by at least one
supplier who supplies at least one red part.
b. Get part numbers for parts supplied to all projects in London.
c. Get all pairs of supplier numbers, say Sx and Sy, such that Sx and Sy supply exactly the
same set of parts each.

SUPPLIER SNO SNAME STATUS CITY S1 Smith London 20 S2 Jones 10 Paris Paris S3 Blake 30 S4 Clark London 20 30 Athens Adams S5 PART PNO PNAME COLOR WEIGHT CITY P1 Nut Red 12.0 London P2 Bolt Green 17.0 Paris 17.0 Rome P3 Screw Blue P4 Screw Red London 14.0 Paris P5 Cam Blue 12.0 19.0 London P6 Cog Red PROJECT UNO JNAME CITY Paris J1 Sorter J2 Display Rome J3 OCR Athens J4 Console Athens London J5 RAID Oslo J6 EDS J7 Tape London SPJ SNO PNO JNO QTY S1 P1 J1. 200 J4 S1 P1 700 S2 P3 J1 400 S2 P3 J2 200 S2 P3 J3 200 S2 P3 J4 500 S2 P3 J5 600 S2 P3 J6 400 S2 P3 J7 800 S2 P5 J2 100 S3 P3 J1 200 S3 P4 J2 500 S4 P6 J3 300 S4 P6 J7 300 S5 P2 J2 200 S5 P2 J4 100 S5 P5 J5 500 S5 P5 J7 100 S5 P6 J2 200 S5 P1 J4 100 S5 P3 J4 200 S5 P4 J4 800 S5 P5 J4 400 S5 P6 J4 800

Explanation / Answer

a)Select JNO,JNAME from PROJECT where JNO IN (SELECT JNO from SPJ where SNO=S2);

b)Select * from PART where PNO IN(Select PNO from SPJ where SNO=(Select SNO from SUPPLIER where CITY='London'));

c)Select * from SPJ where QTY>=300 AND QTY<=750

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

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