1. Give the names of customers that like both Cherry and Vanilla flavors. (note:
ID: 3816893 • Letter: 1
Question
1. Give the names of customers that like both Cherry and Vanilla flavors. (note: it is NOT either/Or but AND) (hint: think of UNION, INTERSECT, MINUS)
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)
CUSTOMER (Cust_ID, Cust_name, year_born)
Customer_and_Flavor_id (cust_id, Flavor_id)
ICE CREAM ID ICE CREAM FLAVOR 101 berry 201 vanilla 211 cherry 311 chocolate 321 strawberry 405 pistachio 406 almond 307 blackberry 510 mint 520 Cookie 525 green tea ICE CREAM ID ICE CREAM FLAVOR 540 raspberry 545 banana 550 pineapple 560 coconut 15 rows selected. PRICE YEAR FIRST OFFERED SELLIN 2010 null 1981 high 2.5 1976 low 1981 high 1961 high 2.5 2012 low 2010 null 2015 null 1.5 1998 medium 2001 medium 4.5 2012 low PRICE YEAR FIRST OFFERED SELLIN 2012 null 1992 medium 1.5 2.5 2001 high 3.25 2012 mediumExplanation / Answer
Query To fetch all the customer names who likes ice cream flavor cheery,venilla
------------------------------------
SELECT C.Cust_name,I.Ice_cream_flavor
FROM
CUSTOMER C,
Customer_and_Flavor_id M,
ICECREAM I
WHERE
C.Cust_ID = M.Cust_ID and
I.Ice_cream_ID = M.Flavor_id and
I.Ice_cream_flavor IN ( 'cheery','venilla')
Description :
In above query equi join is applied that compare and returns only those records which will get matched.Here join got applicable on following table
1. CUSTOMER C,
2. Customer_and_Flavor_id M,
3. ICECREAM I
Please let me know if you face any difficulties to get the desire results
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.