Hello, Please help with this! -- Table \"Recipe\" -- Uses Picture, SkillLevel ta

Question

Hello, Please help with this!

-- Table "Recipe"
--     Uses Picture, SkillLevel tables
CREATE TABLE Recipe (
   RcpRecipeID            BIGINT NOT NULL,
    RcpName                VARCHAR(48) NOT NULL,
    RcpDirections            VARCHAR(4000) NOT NULL,
    RcpPrepTimeMins           INT NOT NULL,
    RcpCookTimeMins           INT NOT NULL,
    RcpTotalTimeMins       INT NOT NULL,
    RcpYieldServingsLow       INT NOT NULL,
    RcpYieldServingsHigh   INT NOT NULL,
    RcpCalsPerServing       INT,
    RcpSource               VARCHAR(128),
    RcpSkillLevelID           BIGINT NOT NULL,
    RcpPictureID           BIGINT NOT NULL,
    RcpCreated               DATETIME   DEFAULT GETDATE(),
    RcpCreatedBy           INT,
    RcpLastModified           DATETIME   DEFAULT GETDATE(),
    RcpLastModifiedBy       INT,
    PRIMARY KEY (RcpRecipeID)
);

-- Table "Picture"
-- Stores Recipe pictures
CREATE TABLE Picture(
   PictureId               BIGINT NOT NULL,
    PictureCredit           VARCHAR(128) NOT NULL,
    Picture                   IMAGE NOT NULL,
    PictureCreated           DATETIME   DEFAULT GETDATE(),
    PictureCreatedBy       INT,
    PictureLastModified       DATETIME DEFAULT GETDATE(),
    PictureLastModifiedBy   INT,
    PRIMARY KEY (PictureId)
);

-- Table "RecipeIngredient"
-- Stores all specific ingredient configurations per recipe (refers to both Recipe and Ingredients tables)
-- Notes: As per John Brandolini's good correction: renamed all fields to start with prefix RcpIng.
CREATE TABLE RecipeIngredient(
   RcpIngRecipeIngredientId   BIGINT NOT NULL,
    RcpIngRecipeId               BIGINT NOT NULL,
    RcpIngIngredientId           BIGINT NOT NULL,
    RcpIngAmount               VARCHAR(16) NOT NULL,
    RcpIngPrepInfo               VARCHAR(64) NOT NULL,
    RcpIngCreated               DATETIME   DEFAULT GETDATE(),
    RcpIngCreatedBy               INT,
    RcpIngLastModified           DATETIME DEFAULT GETDATE(),
    RcpIngLastModifiedBy       INT,
    PRIMARY KEY (RcpIngRecipeIngredientId)
);

-- Table "Ingredient"
-- Stores all recipe ingredient details for re-use across recipes
CREATE TABLE Ingredient(
   IngIngredientID       BIGINT NOT NULL,
    IngName               VARCHAR(64) NOT NULL,
    IngDescription       VARCHAR(128),
    IngCreated           DATETIME   DEFAULT GETDATE(),
    IngCreatedBy       INT,
    IngLastModified       DATETIME DEFAULT GETDATE(),
    IngLastModifiedBy   INT,
   PRIMARY KEY (IngIngredientID)
);

-- Table "SkillLevel"
-- Stores available skill levels -- e.g. high/medium/low or "master chef/sous chef/line cook/server/bus person/plongeur"
CREATE TABLE SkillLevel(
   SkillId               BIGINT NOT NULL,
    SKillDescription   VARCHAR(16) NOT NULL,
    SkillCreated       DATETIME   DEFAULT GETDATE(),
    SkillCreatedBy       INT,
    SkillLastModified   DATETIME DEFAULT GETDATE(),
    SkillLastModifiedBy   INT,
    PRIMARY KEY (SkillId)
);

-- AppApp Foreign Keys

-- Add [Recipe | Picture] relationship
ALTER TABLE Recipe
ADD CONSTRAINT FK_Recipe_Picture FOREIGN KEY (RcpPictureId) REFERENCES Picture(PictureID);

-- Add [Recipe | SkillLevel] relationship
ALTER TABLE Recipe
ADD CONSTRAINT FK_Recipe_SkillLevel FOREIGN KEY (RcpSkillLevelID) REFERENCES SkillLevel(SkillId);

-- Add [RecipeIngredient | Recipe] relationship
-- Notes: Related RecipeIngredient records should be deleted if parent Recipe record is deleted (as per JohnB)
ALTER TABLE RecipeIngredient
ADD CONSTRAINT FK_Recipe FOREIGN KEY (RcpIngRecipeId) REFERENCES Recipe(RcpRecipeId) ON DELETE CASCADE;

-- Add [RecipeIngredient | Ingredient] Relationship
ALTER TABLE RecipeIngredient
ADD CONSTRAINT fk_ingredient FOREIGN KEY (RcpIngIngredientId) REFERENCES Ingredient(IngIngredientId);

Task 1: Update the recipes to correct a spelling error

Write UPDATE query to correct existing recipes. Several recipes include "butten" rather than "butter".

Task 2: Delete the "Avo Toast Points" recipe

Write DELETE query to delete unwanted recipe

Explanation / Answer

Task 1:-

update Recipe set RcpName='Butter' where RcpName='butter';

Update query updates the given column with the new given value rather than the existing value in the database.

Task 2:

delete from Recipe where RcpName ='Avo Toast Points';

Delete quey deletes the given row which matches the given condition;

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.