could someone please write queries for these?

Hi,

I wasn't going to do this, but I'm starting to get desperate.

I have the following 3 tables with constraints.

Supplier(S# char(5) not null,

                      Sname char(20),
                      Status int default 0,
                      City char(15),
                      primary key(S#),
                      check(Status >= 0));

Parts(P# char(6) not null,
                   Pname char(20),
                   Color char(20),
                   Weight float(8),
                   City char(15),
                   primary key(P#),
                   check(Weight >= 0));

Shipments(S# char(5) not null,
                       P# char(6) not null,
                       Qty int not null,
                       foreign key(S#) references Supplier(S#),
                       foreign key(P#) references Parts(P#),
                       primary key(S#, P#));

I need an SQL query for each of the following English descriptions.

1. Get supplier numbers for suppliers who supply at least all those parts supplied by supplier S2.
2. Get all pairs of supplier numbers, Sx and Sy, such that Sx and Sy each supply exactly the same set set of parts.

Here's an example of what the tables might look like if that helps.

Supplier

s#   Sname          Status         City
s1   Smith          20        London
s2   Jones          10        Paris
s3   Blake          30        Paris
s4   Clark          20        London
s5   Adams          30        Athens

Shipments

s# p# qty
s1 p1 300
s1 p2 200
s1 p3 400
s1 p4 200
s1 p5 100
s1 p6 100
s2 p1 300
s2 p2 400
s3 p2 200
s4 p2 200
s4 p4 300
s4 p5 400

Parts

p#   Pname     Color   Weight      City
p1   Nut       Red       12        London
p2   Bolt      Green     17        Paris
p3   Screw     Blue      17        Rome
p4   Screw     Red       14        London
p5   Cam       Blue      12        Paris
p6   Cog       Red       19        London

If anyone could help me out I'd be very grateful.

Thanks,

Alicia Received on Sat Nov 18 2000 - 15:32:50 CST