*AS ALWAYS ALL QUERIES MUST BE DATA INDEPENDENT*\' (PLEASE COMPLETE IN SQL ORACL
ID: 3818132 • Letter: #
Question
*AS ALWAYS ALL QUERIES MUST BE DATA INDEPENDENT*' (PLEASE COMPLETE IN SQL ORACLE)
Give the price of berry per pint (hint this will be total of all ingredient (price *quantity) used in berry)
Ben and Jerry want to discontinue flavors that none of their current customer like. Give the names of those flavors.
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 TABLE
RECIPE TABLE
CUSTOMER TABLE
CUSTOMER_AND_FLAVOR_ID TABLE
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
Here you go! Upvote please :)
SELECT sum(sub_cost) AS total_cost FROM
(SELECT (quantity_used * cost) AS sub_cost FROM ice_cream AS icr JOIN recipe AS rc JOIN ingredient AS ingrd
ON icr.ice_cream_id = rc.ice_cream_id AND rc.ingredient_id = ingrd.ingredient_id
WHERE icr.ice_cream_flavor = 'berry') AS temp_table;
SELECT ice_cream_flavor FROM ice_cream WHERE ice_cream_id NOT IN
(SELECT DISTINCT flavor_id FROM customer_and_flavor_id);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.