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 all the supplier information for the supplier(s) making the most money.

2. For each supplier (including the one who doesn’t ship any parts), print the supplier number and how many different parts shipped. For example, S1 6; S2 2, ...

3. For each supplier (including the one who doesn’t ship any parts), print the supplier number, supplier name, total cost of all shipments, and how many different parts shipped.

4. Print supplier numbers for suppliers who ship at least all those parts shipped by supplier S3. Do not include S3 in the answer and do not "count".

Answer 1

1. Print all the supplier information for the supplier(s) making the most money.

SQL query :

select top 1 supplier.sno,sname,status,city
from supplier inner join shipment on supplier.sno=shipment.sno
group by supplier.sno,sname,status,city
order by sum(Qty*Price) desc;

Query result :

***************************************

2. For each supplier (including the one who doesn’t ship any parts), print the supplier number and how many different parts shipped. For example, S1 6; S2 2, ...

SQL Query :

​​​​​​​select supplier.Sno,count(distinct(pno)) as'Number of parts supplied'
from supplier left join shipment
on
supplier.Sno=SHIPMENT.Sno
group by supplier.Sno;

Query result :

****************************************

3. For each supplier (including the one who doesn’t ship any parts), print the supplier number, supplier name, total cost of all shipments, and how many different parts shipped.

SQL query :

select supplier.Sno,sname,sum(Qty*price) as ' total cost of all shipments',
count(distinct(pno)) as'Number of parts supplied'
from supplier left join shipment
on
supplier.Sno=SHIPMENT.Sno
group by supplier.Sno,sname;

Query result :