Query_m.sql: set of SQL statements for given queries running on MS SQL Server. 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 

The Queries:

1.Print supplier numbers for suppliers who are located in the same city as supplier S1. Do not include S1 in the answer.

2.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".

3.Print supplier numbers for suppliers who ship at least one type of red part.

4.Print supplier numbers for suppliers who do not ship any red parts.

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

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

7.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.

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

9.Print all the shipment information for the shipment(s) with the highest unit cost (i.e. price).

10.Print all the shipment information for the shipment(s) with the highest total cost, (i.e. price*Qty).

11.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.

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

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