how to create Database, Tables and write Stored procedure

Design and implement a relational database for recipes as per the instructions listed below in Parts 1 - 6. The table structure should be able to support information about the recipe such as its name, who wrote the recipe and when, as well as the recipe’s ingredients, ingredient quantities (and unit), and directions. After the database is created, demonstrate knowledge of DML, queries, and stored procedures.
 
When instructed to create scripts, keep in mind that they should run cleanly on a fresh instance of SQL Server 2005 or higher. Also a comment block at the top of the file should list the applicant’s name, date, and description of the file. Place in-line comments where appropriate.
 
Part 1 – Database Creation
Write a script that will create a database. Include the creation of a user named user1 with a password of pword1. Save the script as create_database.sql.
 
Part 2 – Table Creation
Write a script that will create the tables necessary to support recipes. Save the script as create_tables.sql.
 
Part 3 - DML
Write appropriate INSERT statements to add the following two recipes into the database. Save the script as recipes.sql.
 
Kitchen Sink Cookies
Submitted By: Cara
Reference: allrecipes.com (http://allrecipes.com/Recipe/Kitchen-Sink-Cookies/Detail.aspx)
 
INGREDIENTS
•          1 cup butter, softened
•          2 cups packed brown sugar
•          2 eggs
•          2 teaspoons vanilla extract
•          2 1/3 cups all-purpose flour
•          1 teaspoon baking soda
•          1 teaspoon salt
•          2 cups rolled oats
•          1 cup semisweet chocolate chips
•          1 cup vanilla baking chips
•          1/2 cup butterscotch chips
•          1 cup chopped pecans
 
DIRECTIONS
1.        Preheat the oven to 375 degrees F (190 degrees C).
2.        In a large bowl, cream together the butter and brown sugar until smooth. Beat in the eggs one at a time, then stir in the vanilla. Combine the flour, baking soda and salt; stir into the creamed mixture. Mix in the oats, chocolate chips, vanilla chips, butterscotch chips and chopped pecans. Drop by tablespoonfuls onto ungreased cookie sheets. Cookies should be at least 2 inches apart.
3.        Bake for 8 to 10 minutes in the preheated oven. Allow cookies to cool on baking sheet for 5 minutes before removing to a wire rack to cool completely.
 
No Bake Cookies II
Submitted By: Sandy
Reference: allrecipes.com (http://allrecipes.com/Recipe/No-Bake-Cookies-II/Detail.aspx)
 
INGREDIENTS
•          2 cups white sugar
•          1/2 cup butter
•          1/2 cup milk
•          3 tablespoons cocoa powder
•          3 cups quick cooking oats
•          1 teaspoon vanilla extract
 
DIRECTIONS
1.        Mix together sugar, butter or margarine, and milk in a saucepan. Bring to a boil and boil for one minute, stirring constantly.
2.        Remove from heat and mix in cocoa, quick oatmeal and vanilla. Drop by spoonfuls on waxed paper.
 
Part 4 – Queries
Write queries that will return the information requested below. Each query should be numbered and saved into a new file called queries.sql.
 
1.      How many ingredients are in “Kitchen Sink Cookies”?
2.      What are the names of the three most used ingredients by quantity in all recipes authored by Cara?
3.      What is the name of all recipes that do not use egg as an ingredient?
4.      List all ingredient names in the database sorted by name. Exclude duplicates.
5.      What are the directions, in order of the steps, for “No Bake Cookies II”?
6.      What is the second word of each recipe title?
 
Part 5 – Stored Procedures
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). Name the file stored_proc.sql.
 
Part 6 - Conclusion
Compress all SQL files into a single file of ZIP format. Name the ZIP file your first initial and last name (Matt Smith ¿ msmith.zip). Return the file to the person who sent you the exam.

SQL-questions.docx