Urgent help required for T-SQL queries and a Stored Procedure
Posted on 2011-03-04
I have some knowledge of database design and SQL but I have some problems completing some queries and a Stored Procedure. I have the following four tables for Recipe Database.
CREATE TABLE Recipe
ReceipeID INT IDENTITY NOT NULL,
ReceipeName VARCHAR(100) NOT NULL,
ReceipeAuthorID INT NOT NULL,
CREATE TABLE RecipeAuthor
RecipeAuthorID INT IDENTITY NOT NULL,
AuthorName VARCHAR(50) NOT NULL,
CREATE TABLE RecipeIngredients
ReciepteIngredientID INT IDENTITY NOT NULL,
ReceipeID INT NOT NULL,
CREATE TABLE RecipeDirection
RecipeDirectionID INT IDENTITY NOT NULL,
RecipeID INT NOT NULL,
I have populated them with data and I have completed some queries. But I am unable to complete the following questions.
(1) Here are the questions:
What are the names of the three most used ingredients by quantity in all recipes authored by XYZ?
What is the name of all recipes that do not use egg as an ingredient?
What is the second word of each recipe title?
(2) Stored Procedure Question on the same database above.
Write a stored procedure that will add an ingredient to a recipe. The procedure should accept at a minimum the recipe name or ID, ingredient name or ID, ingredient quantity, and ingredient unit.
If the ingredient already exists, update the quantity; an error should occur if the new ingredient and quantity are the same. A return code of 0 should indicate success and -1 if an error was encountered.
Make use of an output parameter that contains a semi-colon separated list of ingredient name and units. The value should be structured similar to the following:
2 cups white sugar;1/2 cup butter;1/2 cup milk;3 tablespoons cocoa powder
Include at the bottom of the same file, SQL code that will execute the procedure and print or select the error code and ingredient list (output parameter).