Question
Problem 2. Case study (adapted from Comeau, Chapter 9) This question is designed to exercise your ability to use JOIN statements to undo the often-complex
Problem 2. Case study (adapted from Comeau, Chapter 9)
This question is designed to exercise your ability to use JOIN statements to "undo" the often-complex relationships that result from normalization. We will use the Recipe Database case study from the textbook.
The associatedrecipes.sqlfile contains code that creates a new schema and populates it with associated tables and a small amount of example data.
1)(10 points)Once you run thesqlcode you'll notice that each table has data populated for two recipes, Chicken Marsala and Absolute Brownies.
Use the INSERT INTO statement to insertnewinformation abouttwo (2)completelynewrecipes of your choosing into the database.
You can make up your own recipes or copy them from a website.Be sure to insert values into each of the tables generated by therecipe.sqlfile:recipe_main,rec_ingredients, ingredients, andcategories.
Use Chicken Marsala and Absolute Brownies as your guides for the problem as the instructions follow similar steps as what was already done in the code, but remember you need to do the same thing for two new recipes.
2) giveonly one SQLquerythatreturnsallinformation ononly the two new recipesyou insertedfromallthe tables you created in step 1above.
Your query shouldshowall relevant information from all four of the tables from step 1.
Don't worry that the output table may duplicate rows, for example therecipe_maintable will duplicate rows for each of the ingredients you enter which is ok. The idea is to get you comfortable with joins.
Show the results of your queryas screenshots pasted in below:
3) give a SELECT query that identifies the recipe name, categoryname,and ingredient name, and ingredient amount.No other variables should be included.
Your output should be sortedfirst by descending categoryname, then by ascending recipe name, followed by descending ingredientname.
Recall that string variables are sorted alphabetically (ascending only) when you check your query results.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started