Question

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

Use Sql to Give the names of customers who are using ice creams that have cocoa as their ingredient.

Answer 1

As here in table 2 we can see that For Making it More Easy we have to modified Customer_flavor table as here we can see there it is in multi value is being used so you have to modified it in single value for make more reliable database Like As here i do the sample for first row

Select Cust_name from Customer where Cust_ID in(Select Cust_ID from Customer_flavor icecreamID=(Select Ice_cream_ID from Icecream inner join Recipe on Recipe.Ice_cream_ID=ICECREAM.Ice_cream_ID inner join INGREDIENT on INGREDIENT.Ingredient_ID=RECIPE.Ingredient_ID where Ingredient_name='cocoa');

if you still have any Problem regarding this question please comment and if you like my code please appreciate me by thumbs up thank you.........