*AS ALWAYS ALL QUERIES MUST BE DATA INDEPENDENT*\' (PLEASE COMPLETE IN SQL ORACL
ID: 3818055 • Letter: #
Question
*AS ALWAYS ALL QUERIES MUST BE DATA INDEPENDENT*' (PLEASE COMPLETE IN SQL ORACLE)
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
Give the count of employees that do not have any flavor preference.
Get the number of customers that have same preference as Patel, M
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
Explanation / Answer
1.select cus.cust_name,cus.cust_id from Customer_and_Flavor_id cf, icecream ice, CUSTOMER cus
where ice.ice_cream_flavor='cherry'
and cf.flavor_id = ice.Ice_cream_ID
and cus.Cust_ID = cf.Cust_ID
INTERSECT
select cus.cust_name,cus.cust_id from Customer_and_Flavor_id cf, icecream ice, CUSTOMER cus
where ice.ice_cream_flavor='vanilla'
and cf.flavor_id = ice.Ice_cream_ID
and cus.Cust_ID = cf.Cust_ID;
output:
CUST_NAME CUST_ID
------------------- --------------
dan 11
-------------------------------------------------------------------------------------------------------------------------------
Answer 2.
select count(distinct cus.cust_id) from customer cus, Customer_and_Flavor_id cf
where cus.cust_id not in (select cust_id from Customer_and_Flavor_id);
3. select count(distinct cust_id) from customer_and_flavor_id where flavor_id in (select flavor_id from customer_and_flavor_id where cust_id in (select cust_id from customer where cust_name = 'patel, m'));
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.