Give the names of flavors that cost more than $500 (total). Hint: may want to cr

Question

Give the names of flavors that cost more than $500 (total). Hint: may want to create view for this

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)

NEEDED INFORMATION!!!

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)

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

ICECREAM TABLE

INGREDIENT

RECIPE

CUSTOMER

CUSTOMER_AND_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 medium

Explanation / Answer

Please try to type the queries by double checking the table and column names, spellings and for capital and small letters in your records from your database. Copy and paste may create problems.

Query 1: Give the names of flavors that cost more than $500 (total).

SELECT ICECREAM.Ice_cream_flavor, INGREDIENT.cost * RECIPE.quantity_used FROM ICECREAM, INGREDIENT, RECIPE
WHERE ICECREAM.Ice_cream_ID = RECIPE.Ice_cream_ID
AND INGREDIENT.Ingredient_ID = RECIPE.ingredient_ID
AND (INGREDIENT.cost * RECIPE.quantity_used) > 500;

Query 2: Give the names of customers that like both Cherry and Vanilla flavors.

SELECT Cust_name FROM CUSTOMER WHERE Cust_ID IN
(SELECT cust_id FROM Customer_and_Flavor_id WHERE Flavor_id =
(SELECT Ice_cream_ID FROM ICECREAM WHERE Ice_cream_flavor LIKE 'cherry'))
INTERSECT
SELECT Cust_name FROM CUSTOMER WHERE Cust_ID IN
(SELECT cust_id FROM Customer_and_Flavor_id WHERE Flavor_id =
(SELECT Ice_cream_ID FROM ICECREAM WHERE Ice_cream_flavor LIKE 'vanilla'));

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.