Question

a.From the table below, Use sql query to give the total cost of each Ice cream flavor

b. give the count of customers that like exactly two flavors

CUSTOMER (Cust_ID, Cust_name, year_born)

They want to link CUSTOMER to their database. Customer flavor preference is shown in table 2.

Following tables are from assignment 1

ICECREAM (Ice_cream_ID, Ice_cream_flavor, price, years_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.

Years_first_offered is the year that ice cream was first offered

quantity_used is the amount of ingredient used in a given ice cream.

Table 1: CUSTOMER

CUST_ID	CUST_NAME	Year_born
1	Harry, T	2002
2	Sally, P	1992
3	Lio, L	1998
4	Patel, P	2001
5	Roner,K	1978
6	Jackson, O	2002
7	Long, P	2001
8	Smith, G	1992
9	Harry, L	2002
10	Paner, K	1978
11	Dan, U	2010
12	Patel, M	2001

Table 2: CUSTOMER and their Flavor preference

Cust_ID	Flavor preference (to make it easy, instead of flavor name you can use ice cream ID in your table)
1	Vanilla, Coconut
2	Almond, Vanilla, Cookie
3	Banana, Green Tea, Mint
4	Cherry, Coconut
5
6	Cherry, Coconut
7
8	Berry, Vanilla, Mint, Cookie, almond
9	Mint
10
11	Coconut, Vanilla, Cherry
12	Coconut

Answer 1

Below are the SQL queries-

a) Aggregate function SUM() is used to get the sum of prices, group by is used to group the some by flavor.

SELECT Ice_cream_flavor, SUM(price)
FROM ICECREAM
GROUP BY Ice_cream_flavor;

b) Self join is used here on customer preference table to get the customer with same liking of flavors.

SELECT COUNT(c.Cust_ID)
FROM CUSTOMER_PREF AS c
INNER JOIN CUSTOMER_PREF As cp
ON c.Cust_ID = cp.Cust_ID
WHERE c.Flavor = cp.Flavor