Ensurepass

QUESTION 51

You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person. The tables have the following definitions:

 

clip_image001

 

You create a view named VwEmployee as shown in the following Transact-SQL statement.

 

clip_image002

 

Users are able to use single INSERT statements or INSERT…SELECT statements into this view. You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view. Which Transact-SQL statement should you use?

 

A.  CREATE TRIGGER TrgVwEmployee

    ON VwEmployee

    FOR INSERT

    AS

    BEGIN

    INSERT INTO Person(Id, FirstName, LastName)

    SELECT Id, FirstName, LastName, FROM inserted

 

    INSERT INTO Employee(PersonId, EmployeeNumber)

    SELECT Id, EmployeeNumber FROM inserted

 

    END

B.  CREATE TRIGGER TrgVwEmployee

    ON VwEmployee

    INSTEAD OF INSERT

    AS

    BEGIN

    INSERT INTO Person(Id, FirstName, LastName)

    SELECT Id, FirstName, LastName, FROM inserted

 

    INSERT INTO Employee(PersonId, EmployeeNumber)

    SELECT Id, EmployeeNumber FROM inserted

 

    END

C.  CREATE TRIGGER TrgVwEmployee

    ON VwEmployee

    INSTEAD OF INSERT

    AS

    BEGIN

 

    DECLARE @ID INT, @FirstName NVARCHAR(25), @LastName NVARCHAR(25), @PersonID INT, @EmployeeNumber NVARCHAR(15)

 

    SELECT @ID = ID, @FirstName = FirstName, @LastName = LastName, @EmployeeNumber = EmployeeNumber

    FROM inserted

 

    INSERT INTO Person(Id, FirstName, LastName)

    VALUES(@ID, @FirstName, @LastName)

 

    INSERT INTO Employee(PersonID, EmployeeNumber)

    VALUES(@PersonID, @EmployeeNumber

 

    End

D.  CREATE TRIGGER TrgVwEmployee

    ON VwEmployee

    INSTEAD OF INSERT

    AS

    BEGIN

    INSERT INTO Person(Id, FirstName, LastName)

    SELECT Id, FirstName, LastName FROM VwEmployee

 

    INSERT INTO Employee(PersonID, EmployeeNumber)

    SELECT Id, EmployeeNumber FROM VwEmployee

 

End

 

Correct Answer: B

 

 

QUESTION 52

You develop a Microsoft SQL Server 2012 database that contains a table named Products. The Products table has the following definition:

 

clip_image003

 

You need to create an audit record only when either the RetailPrice or WholeSalePrice column is updated. Which Transact-SQL query should you use?

 

A.  CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS

    IF CCLUMNS_CHANGED(RetailPrice, WholesalePrice)

    – – Create Audit Records

B.  CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS

    IF EXISTS(SELECT RetailPrice from inserted) OR

    EXISTS (SELECT WholeSalePnce FROM inserted)

    – – Create Audit Records

C.  CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS

    IF COLUMNS_UPDATED(RetailPrice, WholesalePrice)

    – – Create Audit Records

D.  CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS

    IF UPDATE(RetailPrice) OR UPDATE(WholeSalePrice)

– – Create Audit Records

 

Correct Answer: D

 

 

QUESTION 53

You develop a Microsoft SQL Server 2012 server database that supports an application. The application contains a table that has the following definition:

 

CREATE TABLE Inventory

(ItemID int NOT NULL PRIMARY KEY,

ItemsInStore int NOT NULL,

ItemsInWarehouse int NOT NULL)

 

You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row. Which Transact-SQL statement should you use?

 

A.  ALTER TABLE Inventory

    ADD TotalItems AS ItemsInStore + ItemsInWarehouse

B.  ALTER TABLE Inventory

    ADD ItemsInStore – ItemsInWarehouse = TotalItems

C.  ALTER TABLE Inventory

    ADD TotalItems = ItemsInStore + ItemsInWarehouse

D.  ALTER TABLE Inventory

ADD TotalItems AS SUM(ItemsInStore, ItemslnWarehouse);

 

Correct Answer: A

 

 

QUESTION 54

You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition.

 

clip_image004

 

You need to improve the performance of the view by persisting data to disk. What should you do?

 

A.      Create an INSTEAD OF trigger on the view.

B.      Create an AFTER trigger on the view.

C.      Modify the view to use the WITH VIEW_METADATA clause.

D.      Create a clustered index on the view.

 

Correct Answer: D

 

 

QUESTION 55

Your database contains tables named Products and ProductsPriceLog. The Products table contains columns named ProductCode and Price. The ProductsPriceLog table contains columns named ProductCode, OldPrice, and NewPrice. The ProductsPriceLog table stores the previous price in the OldPrice column and the new price in the NewPrice column. You need to increase the values in the Price column of all products in the Products table by 5 percent. You also need to log the changes to the ProductsPriceLog table. Which Transact-SQL query should you use?

 

A.  UPDATE Products SET Price = Price * 1.05

    OUTPUT inserted.ProductCode, deleted.Price, inserted.Price

      INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

B.  UPDATE Products SET Price = Price * 1.05

    OUTPUT inserted.ProductCode, inserted.Price, deleted.Price

      INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

C.  UPDATE Products SET Price = Price * 1.05

    OUTPUT inserted.ProductCode, deleted.Price, inserted.Price *

      INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

D.  UPDATE Products SET Price = Price * 1.05

    INSERT INTO ProductsPriceLog (ProductCode, CldPnce, NewPrice;

SELECT ProductCode, Price, Price * 1.05 FROM Products

 

Correct Answer: A

 

 

QUESTION 56

A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit. You need to create a report that displays the profits made by each territory for each year and its previous year. Which Transact-SQL query should you use?

 

A.  SELECT Territory, Year, Profit,

      LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit

    FROM Profits

B.  SELECT Territory, Year, Profit,

      LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit

    FROM Profits

C.  SELECT Territory, Year, Profit,

      LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit

    FROM Profits

D.  SELECT Territory, Year, Profit,

      LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit

FROM Profits

 

Correct Answer: C

 

 

QUESTION 57

You use Microsoft SQL Server 2012 database to develop a shopping cart application. You need to rotate the unique values of the ProductName field of a table-valued expression into multiple columns in the output. Which Transact-SQL operator should you use?

 

A.      CROSS JOIN

B.      CROSS APPLY

C.      PIVOT

D.      UNPIVOT

 

Correct Answer: C

 

 

QUESTION 58

You administer a Microsoft SQL Server database that supports a shopping application. You need to retrieve a list of customers who live in territories that do not have a sales person. Which Transact- SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)

 

A.  SELECT CustomerID FROM Customer

    WHERE TerritoryID <> SOME(SELECT TerritoryID FROM Salesperson)

B.  SELECT CustomerID FROM Customer

    WHERE TerritoryID <> ALL(SELECT TerritoryID FROM Salesperson)

C.  SELECT CustomerID FROM Customer

    WHERE TerritoryID <> ANY(SELECT TerritoryID FROM Salesperson)

D.  SELECT CustomerID FROM Customer

    WHERE TerritoryID NOT IN(SELECT TerritoryID FROM Salesperson)

 

Correct Answer: BD

 

 

QUESTION 59

Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a non-clustered index on the OrderTime column. The business team wants a report that displays the total number of orders placed on the current day. You need to write a query that will return the correct results in the most efficient manner. Which Transact-SQL query should you use?

 

A.  SELECT COUNT(*) FROM SalesOrders

    WHERE OrderTime = CONVERT(DATE, GETDATE())

B.  SELECT COUNT(*) FROM SalesOrders

    WHERE OrderTime = GETDATE()

C.  SELECT COUNT(*) FROM SalesOrders

    WHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(I, 112))

D.  SELECT COUNT(*) FROM SalesOrders

    WHERE OrderTime >= CONVERT(DATE, GETDATE())

AND OrderTime < DATEADD(DAY, CONVERT(DATE, GETDATE()))

 

Correct Answer: D

 

 

QUESTION 60

Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter. You plan to build a single process for each employee that will execute the stored procedure based on the country of residence. Which approach should you use?

 

A.      A.a recursive stored procedure

B.      Trigger

C.      An UPDATE statement that includes CASE

D.      Cursor

E.       The foreach SQLCLR statement

 

Correct Answer: D

 

 

Comments are closed.