Database SQL question Answer fully for thumbs up Question 2 (a) A restaurant sto

Question

Database SQL question

Answer fully for thumbs up

Question 2 (a) A restaurant stores information in a relational database about the ingredients used in each of the dishes which it has on its menu. There are two tables with attributes as follows. The primary keys are underlined Recipe Stores Dish Ingredient Quantity Ingredient Supplier Stock The Recipe table records, for each dish listed on the menu, the quantity of each ingre- dient needed for one serving (so for each dish there is one recipe made from a number of ingredients). The Stores table records for each ingredient used in the restaurant, the name of the supplier and the current amount held in stock. The current amount is updated daily (i) The restaurant manager asked "How many ingredients are supplied by Smith?" Write an SQL query that could be used to answer this question (ii) The following query was intended to find out for which dishes on the menu there are not enough ingredients in stock to make 10 servings. SELECT DISTINCT Dish FROM Recipe WHERE Stock

Explanation / Answer

Answer:

a.i)select count(Ingredient) as 'Total COUNT' from Stores where Supplier like 'Smith';

/*This query will list down the ingredients supplied by Smith*/

Output:
Total COUNT
23

a.ii.1) The query wont return anything as the data is fetched from two different tables without using any join command.Using join we can find out the dishes are which are not enough to serve 10 people.

a.ii.2) Select Distinct(r.Dish) from Recipe r
join Stores s on s.Ingredient = r.Ingredient
where s.stock < (r.Quantity * 10) order by r.Dish;

Output:

Dish

Maggie

Bread

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

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