ICECREAM (Ice_cream_ID, Ice_cream_flavor, price per scoop, year_first_offered, sellling _status)

INGREDIENT ( Ingredient_ID, Ingredient_name, cost)

RECIPE (Ice_cream_ID, ingredient_ID, quantity_used)

WHERE:

Ice_cream_ID is the internal Id given to an ice cream.

Ingredient_ID is the internal Id given to an ingredient

selling_staus is an internal control which keeps track of ice cream sales as high, low, medium or none. If no figures are available this field has no value.

Year_first_offered is the year that ice cream flavor was first offered

quantity_used is the amount of ingredient used in one pint of ice cream.

Following sample data is available:

ICECREAM table

IcecreamID	Ice cream_Flavor	Price per scoop	year_first offered	Selling_status
101	Berry	$4	2010
201	Vanilla	$2	1981	high
211	Cherry	$3	1976	low
311	Chocolate	$3	1981	high
321	Strawberry	$2	1961	high
405	Pistachio	$2.50	2012	low
406	Almond	$3	2010
407	Blackberry	$4	2015
510	Mint	$1.50	1998	medium
520	Cookie	$2	2001	medium
525	Green Tea	$4.50	2012	low
540	Raspberry	$3	2012
545	Banana	$1.50	1992	medium
550	Pineapple	$2	2001	high
560	coconut	$3.25	2012	medium

RECIPE Table

Ice_cream_ID	Ingredient_ID	Quantity used/pint
101	1000	30
540	1000	75
545	1000	55
101	1100	40
201	1100	45
407	1200	25
101	1200	20
201	1200	45
311	1200	40
321	1300	35
101	1300	80
540	1300	50
545	1400	15
405	1400	10
406	1400	27.5
201	1500	10
540	1500	5.5
211	1500	60
101	1700	5
520	1700	10

INGREDIENT

Ingredient_ID	Ingredient_name	Cost/pint
1000	cocoa	$125
1100	Vanilla beans	$30
1200	Tea leaves	$35
1300	Raw almonds	$215
1400	Mint Leaves	$42
1500	Sugar	$5
1600	Cream	$25
1700	Pistachio	$45
1710	raspberry	$12

Using sql to Give the average price of vanilla, almond and Pistachio ice creams from the icecream table above.