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.

Answer 1

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

select r.name from RECIPE r, USES u, INGREDIENT i where lower(r.country) = 'france' and r.time > 45 and r.recipe-id = u.rid and u.iid = i.ingredient-id and lower(i.name) = 'butter';

The above returns the recipe names of those recipe whose country is france (French recipe) and takes longer than 45 minutes to cook (assuming time in recipe table are specified in minutes) and then it natural joins with uses table on field recipe-id which joins with ingredient table on field ingredient-id to find the recipe that uses butter.

2.

Find the names of recipes that use butter or lard.

select r.name from RECIPE r, USES u , INGREDIENT i where r.recipe-id = u.rid and u.iid = i.ingredient-id and lower(i.name) = 'butter'
UNION
select r.name from RECIPE r, USES u , INGREDIENT i where r.recipe-id = u.rid and u.iid = i.ingredient-id and lower(i.name) = 'lard';

The first query returns the name all recipes that uses butter and the second query returns the name of all recipes that uses lard. The UNION operator combines the result of both the queries and returns the name of all recipes that uses butter or lard.

3.

Find the names of recipes that use butter and lard.

select r.name from RECIPE r, USES u , INGREDIENT i where r.recipe-id = u.rid and u.iid = i.ingredient-id and lower(i.name) = 'butter'
INTERSECT
select r.name from RECIPE r, USES u , INGREDIENT i where r.recipe-id = u.rid and u.iid = i.ingredient-id and lower(i.name) = 'lard';

The first query returns the name all recipes that uses butter and the second query returns the name of all recipes that uses lard. The INTERSECT operator returns the name which are present in both the queries i.e returns the name of those recipes that uses butter and lard.