Exam Preparation Database SQL Query

Create Stored Procedure

CREATE
PROCEDURE usp_AddCustomer

@CustomerID INT,

@FirstName    VARCHAR(30),

@LastName    VARCHAR(30),

@Phone        VARCHAR(20),

@Email        VARCHAR(30)

AS

INSERT
INTO CUSTOMER(CustomerID,FirstName,LastName,Phone,Email)

VALUES

(@CustomerID, @FirstName, @LastName, @Phone,@Email);

 

EXEC usp_AddCustomer
'8',
'John','Smith','723-555-3432','John@somewhere.com';

 

SELECT
*

FROM CUSTOMER

WHERE CustomerID=8;

 

Create Trigger part1

 

CREATE
TRIGGER reminder

ON CUSTOMER

AFTER
INSERT

AS
RAISERROR ('Notify new Customer inserted', 16, 10);    

 

INSERT
INTO CUSTOMER

VALUES

(8,'Mick',
'Jager','723-599-1111','MJager@somewhere.com');

 

 

Create Trigger part2

 

CREATE
TRIGGER SetOrederItem1

ON CLOTH_ITEM

AFTER
INSERT

AS

    DECLARE

        @OrderID        INT,

        @ItemNo        INT,

        @Item            VARCHAR(30),

        @Quantity        INT,

        @UnitPrice        MONEY,

        @S_ItemNo        INT,

        @S_UnitPrice    MONEY

    SELECT @ItemNo=ItemNo, @UnitPrice=UnitPrice

    FROM inserted i

 

    SELECT @S_ItemNo=S.ItemNo,@S_UnitPrice=S.UnitPrice

    FROM
SERVICE S

    WHERE S.ItemNo=@ItemNo

 

IF (@ItemNo=@S_ItemNo)AND(@UnitPrice<>@S_UnitPrice)

BEGIN

        RAISERROR ('Price is different from SERVICE table', 16, 1)

        ROLLBACK
TRANSACTION

END

ELSE

BEGIN

        PRINT'The modification excuted'

END;

 

INSERT
INTO CLOTH_ITEM

VALUES

(2009006,6,'Suit-Mens',10,'$6.00');

 

 

INSERT
INTO CLOTH_ITEM

VALUES

(2009008,6,'Suit-Mens',10,'$9.00');

 

 

Create View

 

CREATE
VIEW OrderSummaryView

AS
SELECT OrderID,DateIn,DateOut,TotalAmount

FROM ORDER_RECORD;