Question

SUPPLIER

Sno Sname Status City

s1 Smith 20 London

s2 Jones 10 Paris

s3 Blake 30 Paris

s4 Clark 20 London

s5 Adams 30 NULL

PART

Pno Pname Color Weight City

p1 Nut Red 12 London

p2 Bolt Green 17 Paris

p3 Screw NULL 17 Rome

p4 Screw Red 14 London

p5 Cam Blue 12 Paris

p6 Cog Red 19 London

SHIPMENT

Sno Pno Qty Price

s1 p1 300 .005

s1 p2 200 .009

s1 p3 400 .004

s1 p4 200 .009

s1 p5 100 .01

s1 p6 100 .01

s2 p1 300 .006

s2 p2 400 .004

s3 p2 200 .009

s3 p3 200 NULL

s4 p2 200 .008

s4 p3 NULL NULL

s4 p4 300 .006

s4 p5 400 .003

QUESTIONS (SOLUTIONS MUST BE SQL QUERIES):

1. Print supplier numbers for suppliers who ship ONLY red parts.

2. Print supplier names for suppliers who do not currently ship any parts.

3. Print supplier names for suppliers who ship at least one part that is also shipped by supplier S2. Do not include S2 in the answer.

4. Print supplier numbers for suppliers with status value less than the current average status value of all suppliers.

Answer 1

1. Supplier Names for suppliers who ship only red parts:

SELECT DISTINCT S.Sno FROM Shipment S

WHERE NOT EXISTS

(SELECT * FROM Part P, Shipment S1

WHERE P.Pno = S.Pno AND P.color <> ‘Red’

AND S.Sno = S2.Sno);

2. Supplier names for suppliers who do not currently ship any parts:

SELECT Sno FROM Supplier

WHERE Sno NOT IN

(SELECT Sno FROM Shipment);

3. Supplier names for suppliers who ship at least one part that is also shipped by supplier S2:

SELECT DISTINCT Supplier.Sno FROM Supplier, Shipment

WHERE Supplier.Sno = Shipment.Sno AND Pno IN

(SELECT Pno FROM Shipment WHERE Shipment.Sno = 'S2');

4. Supplier numbers for suppliers with status value less than the current average status value of all suppliers:

SELECT Sno FROM Supplier

WHERE Status < (SELECT avg(Status) FROM Suppliers);