What is the least expensive mineral water product? To answer this question, you must obtain the product costs from multiple suppliers for all mineral water products in each vendor's product line, then average the costs, group the data by vendor, and sort the data by cost.
With SQL, you can use summary records to obtain the answer by running a single query. The PRODUCT file includes a ProdCat (product category) field in which all mineral water products are identified by the value 100570100. The ProdName field contains the vendor name.
To transfer a summary record for each product name:
T1.PRODID = T2.PRODID AND T2.SUPID = T3.SUPID
The Query box at the top of the Overview page displays the complete query statement:
SELECT AVG(T2.PRODCOST), T1.PRODNAME FROM grocery/product, grocery/supprod, grocery/supplier WHERE (T1.PRODID=T2.PRODID AND T2.SUPID=T3.SUPID) AND (T1.PRODCAT=’100570100’) GROUP BY T1.PRODNAME ORDER BY AVG(T2.PRODCOST)
The resulting records are:
|Whisper Rain Mineral Water||3.00|
|Maple Falls Mineral Water||3.17|
|Mt. Baker Sparkling Water||3.35|
|Panda Mineral Water||3.62|
|Panda Sparkling Lemon Essence||3.81|
|Panda Sparkling Lime Essence||3.81|