Please write the following SQL query: 3. Determine the average cost of beverages
ID: 3891091 • Letter: P
Question
Please write the following SQL query:
3. Determine the average cost of beverages by SupplierName and CategoryName. Include only BEER and WINE categories and the groups with an average cost of over $10.
CATEGORY
CategoryID
CategoryName
C01
BEER
C02
WINE
C03
MIXED DRINKS
…
SUPPLIER
SupplierID
SupplierName
SupplierAddress
SupplierCity
SupplierState
S01
BIG BEER CO.
123 MAIN STREET
ST. LOUIS
MO
S02
CRAFT BEER INC.
456 BROADWAY
ROCHESTER
NY
S03
UNITED BEVERAGES
789 BEAUFORT ST
NORMAL
IL
…
BEVERAGE
BevID
BevName
Cost
Qty
CategoryID
SupplierID
B01
KILLIAN’S
0.87
12
C01
S01
B02
COORS
0.59
40
C01
S01
B03
OLD STYLE
0.43
35
C01
S02
B04
ANCHOR STEAM
0.89
27
C01
S02
B05
MILLER LIGHT
0.45
70
C01
S03
B06
DRY WINE
12.35
10
C02
S03
B07
WHITE WINE
8.87
8
C02
S04
B08
RED WINE
7.15
3
C02
S04
B09
WINE COOLER
10.02
16
C03
S05
B10
…
…
…
…
…
CategoryID
CategoryName
C01
BEER
C02
WINE
C03
MIXED DRINKS
…
Explanation / Answer
SQL Query:
SELECT avg(Cost), SupplierName, CategoryName
FROM CATEGORY, BEVERAGE,SUPPLIER
where (CategoryName = 'BEER' or CategoryName = 'WINE')
group by SupplierName, CategoryName
having avg(Cost)>10;
Above SQL query display average cost of beverages by SupplierName and CategoryName.
Cross product of all 3tables is taken in above query
having avg(Cost)>10 selcts rows with an average cost over $10.