Itudk_2010
asked on
Urgent help required for T-SQL queries and a Stored Procedure
Hi all,
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,
ReceipeDate DATETIME,
ReceipeAuthorID INT NOT NULL,
)
CREATE TABLE RecipeAuthor
(
RecipeAuthorID INT IDENTITY NOT NULL,
AuthorName VARCHAR(50) NOT NULL,
Reference VARCHAR(3000)
)
CREATE TABLE RecipeIngredients
(
ReciepteIngredientID INT IDENTITY NOT NULL,
ReceipeID INT NOT NULL,
Quantity Decimal,
UnitofMeasurement VARCHAR(1000),
IngredientName VARCHAR(1000)
)
CREATE TABLE RecipeDirection
(
RecipeDirectionID INT IDENTITY NOT NULL,
RecipeID INT NOT NULL,
StepProcess VARCHAR(3000)
)
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).
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,
ReceipeDate DATETIME,
ReceipeAuthorID INT NOT NULL,
)
CREATE TABLE RecipeAuthor
(
RecipeAuthorID INT IDENTITY NOT NULL,
AuthorName VARCHAR(50) NOT NULL,
Reference VARCHAR(3000)
)
CREATE TABLE RecipeIngredients
(
ReciepteIngredientID INT IDENTITY NOT NULL,
ReceipeID INT NOT NULL,
Quantity Decimal,
UnitofMeasurement VARCHAR(1000),
IngredientName VARCHAR(1000)
)
CREATE TABLE RecipeDirection
(
RecipeDirectionID INT IDENTITY NOT NULL,
RecipeID INT NOT NULL,
StepProcess VARCHAR(3000)
)
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).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Sharath_123,
The first answer is correct But the following two questions give wrong output. The first one returns no results as it should return bread, greens etc which is in the table above except egg.
(1) What is the name of all recipes that do not use egg as an ingredient?
SELECT r.RecipeName
FROM Recipe r
LEFT JOIN RecipeIngredients ri
ON r.RecipeID = ri.RecipeID
AND ri.IngredientName <> 'egg'
WHERE ri.RecipeID IS NULL
(2) What is the second word of each recipe title?
SELECT CASE
WHEN CHARINDEX(' ',RecipeName) <> 0 THEN SUBSTRING(RecipeName,CHARI
LEN(RecipeName))
ELSE 'No Second Word'
END Second_Word
FROM Recipe
This one returns the second and third and fourth words so on. It should return only the second word but not the remaining words. Is there any idea?
I really appreciate your help. Looking forward to your reply.
The first answer is correct But the following two questions give wrong output. The first one returns no results as it should return bread, greens etc which is in the table above except egg.
(1) What is the name of all recipes that do not use egg as an ingredient?
SELECT r.RecipeName
FROM Recipe r
LEFT JOIN RecipeIngredients ri
ON r.RecipeID = ri.RecipeID
AND ri.IngredientName <> 'egg'
WHERE ri.RecipeID IS NULL
(2) What is the second word of each recipe title?
SELECT CASE
WHEN CHARINDEX(' ',RecipeName) <> 0 THEN SUBSTRING(RecipeName,CHARI
LEN(RecipeName))
ELSE 'No Second Word'
END Second_Word
FROM Recipe
This one returns the second and third and fourth words so on. It should return only the second word but not the remaining words. Is there any idea?
I really appreciate your help. Looking forward to your reply.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@derekkromm,
Thanks a lot for your help. Your answer for question 2 is correct. But question 1 is still not working?
select RecipeName from Recipe where RecipeID not in (select RecipeID from RecipeIngredients where IngredientName = 'egg')
any other ideas?
Thanks a lot for your help. Your answer for question 2 is correct. But question 1 is still not working?
select RecipeName from Recipe where RecipeID not in (select RecipeID from RecipeIngredients where IngredientName = 'egg')
any other ideas?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No comments
ASKER
Thanks for your reply. It is a project not a home work. Could you please help in this regard. Following is the data
Receipe Table
ReceipeID Name Date AuthorID createddate
1 Sandwich 1/1/2011 1 1/1/2011
2 Ham Burger 1/1/2011 2 1/1/2011
RecipeAuthor Table
AuthorID AuthorName
1 irene
2 walsh
ReciepeIngredients Table
ReciepeIngredientsID ReceipeID Ingredeint Name UnitofMeasurement quantity
1 1 Bread Cup 2
1 1 greens Tablespoon 3
2
ReceipeDirection
ReceipeStepsID ReceipeID StepProcess
1 1 wash the greens
2 1 toast the bread
3 1 apply cheese on bread
I really appreciate your help. Looking forward to your reply.
Best regards.