SHIPMENTSno Pno Qty Prices1 p1 300 .005s1 p2 200 .009s1 p3 400 .004s1 p4 200 .009s1 p5 100 .01s1 p6 100 .01s2 p1 300 .006s2 p2 400 .004s3 p2 200 .009s3 p3 200 NULLs4 p2 200 .008s4 p3 NULL NULLs4 p4 300 .006s4 p5 400 .003
SUPPLIERSno Sname Status Citys1 Smith 20 Londons2 Jones 10 Pariss3 Blake 30 Pariss4 Clark 20 Londons5 Adams 30 NULL
PARTPno Pname Color Weight Cityp1 Nut Red 12 Londonp2 Bolt Green 17 Parisp3 Screw NULL 17 Romep4 Screw Red 14 Londonp5 Cam Blue 12 Parisp6 Cog Red 19 LondonQUERY:
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 doesn’t 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 doesn’t ship any parts), print the supplier number, supplier name, total cost of all shipments, and how many different parts shipped.
Need SQL Statements