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

QUESTION (SOLUTION MUST BE SQL QUERY):

1. Print all the supplier information for the supplier(s) making the most money. The supplier money is determined by the sum of all shipment cost. Each shipment cost is found by the number of units being shipped times the price per unit.

Answer 1

SQL query in MySQL :

select supplier.sno,sname,status,city
from supplier,shipment
where
supplier.sno=shipment.sno
group by supplier.sno,sname,status,city
order by sum(Qty*Price) desc limit 1;
Query result :

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

SQL query in SQL Server :

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

Query result :