Question

Write the following questions as queries in SQL. Use only the operators discussed in class (no outer joins)

Consider the following database schema:

INGREDIENT(ingredient-id,name,price-ounce)

RECIPE(recipe-id,name,country,time)

USES(rid,iid,quantity)

where INGREDIENT lists ingredient information (id, name, and the price per ounce); RECIPE lists recipe information (id, name, country of origin, and time it takes to cook it); and USES tells us which ingredients (and how much of each) a recipe uses. The primary key of each table is underlined; rid is a foreign key to RECIPE and iid is a foreign key to INGREDIENT

Write the following queries in SQL.

1. Find the names of French recipes that use butter and take longer than 45 minutes to cook.

2. Find the names of recipes that use butter or lard.

3. Find the names of recipes that use butter and lard.

4. Find the name of recipes that do not use any butter.

5. Find the names of recipes that use 2 or more ingredients.

6. Fing the names of ingredients that are used in a single recipe (ie. in one recipe only).

7. The cost of an ingredient in a recipe is the price per ounce of the ingredient times the quantity of ingredient used in the recipe. The cost of a recipe is the sum of the cost of its ingredients. Calculate the cost for all recipes.

8. Find the recipe(s) with the largest number of ingredients (NOTE: there can be more than one).

9. Find the number of recipes on each country that use flour.

10. Find the number of recipes per country, but show only countries with at least 5 recipes.

11. (*) Find the names of recipes where at least 3 oz. (quantity) of every ingredient are used.

NOTE: this means that, however many ingredients are used in a recipe, at least 3 oz. of each ingredient is used.

Answer 1

1) SELECT RECIPE.name FROM RECIPE

INNER JOIN USES ON RECIPE.recipe-id = USES.rid

WHERE RECIPE.country='French' and RECIPE.time > 45 and RECIPE.recipe-id IN (SELECT USES.rid FROM USES inner join INGREDIENT on USES.iid=INGREDIENT.ingredient-id WHERE INGREDIENT.name='butter');

2)SELECT RECIPE.name FROM RECIPE

INNER JOIN USES ON RECIPE.recipe-id = USES.rid

WHERE RECIPE.recipe-id IN (SELECT USES.rid FROM USES inner join INGREDIENT on USES.iid=INGREDIENT.ingredient-id WHERE INGREDIENT.name='butter' or INGREDIENT.name='lard');

3)SELECT RECIPE.name FROM RECIPE

INNER JOIN USES ON RECIPE.recipe-id = USES.rid

WHERE RECIPE.recipe-id IN (SELECT USES.rid FROM USES inner join INGREDIENT on USES.iid=INGREDIENT.ingredient-id WHERE INGREDIENT.name='butter' and INGREDIENT.name='lard');

4)SELECT RECIPE.name FROM RECIPE

INNER JOIN USES ON RECIPE.recipe-id = USES.rid

WHERE RECIPE.recipe-id IN (SELECT USES.rid FROM USES inner join INGREDIENT on USES.iid=INGREDIENT.ingredient-id WHERE INGREDIENT.name<>'butter' );