Finding Far out 70-461 dump

♥♥ 2021 NEW RECOMMEND ♥♥

Free VCE & PDF File for Microsoft 70-461 Real Exam (Full Version!)

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 70-461 Exam Dumps (PDF & VCE):
Available on: http://www.surepassexam.com/70-461-exam-dumps.html

Q11. You have three tables that contain data for vendors, customers, and agents. You create a view that is used to look up telephone numbers for these companies.

The view has the following definition:

You need to ensure that users can update only the phone numbers by using this view.

What should you do?

A. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.

B. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.

C. Create an AFTER UPDATE trigger on the view.

D. Create an INSTEAD OF UPDATE trigger on the view.

Answer: D

Q12. You use Microsoft SQL Server 2012 to develop a database application.

You create a stored procedure named DeleteJobCandidate.

You need to ensure that if DeleteJobCandidate encounters an error, the execution of the stored procedure reports the error number.

Which Transact-SQL statement should you use?

A. DECLARE @ErrorVar INT;

DECLARE @RowCountVar INT;

EXEC DeleteJobCandidate

SELECT @ErrorVar = @@ERROR, @RowCountVar = @@ROWCOUNT;

IF (@ErrorVar <> 0)

PRINT N'Error = ' + CAST(@@ErrorVar AS NVARCHAR(8)) +

N', Rows Deleted = ' + CAST(@@RowCountVar AS NVARCHAR(8));

B. DECLARE @ErrorVar INT;

DECLARE @RowCountVar INT;

EXEC DeleteJobCandidate

SELECT @ErrorVar = ERROR_STATE(), @RowCountVar = @@ROWCOUNT;

IF (@ErrorVar <> 0)

PRINT N'Error = ' + CAST(ERRORSTATE() AS NVARCHAR(8)) +

N', Rows Deleted = ' + CAST(@@RowCountVar AS NVARCHAR(8));

C. EXEC DeleteJobCandidate

IF (ERROR_STATE() != 0)

PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)) +

N', Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8));

D. EXEC DeleteJobCandidate

PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)) +

N', Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8));

Answer: A

Q13. DRAG DROP

You use Microsoft SQL Server 2012 to develop a database application. You create two tables by using the following table definitions.

Which six Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)

Answer:

Q14. You use a Microsoft SQL Server 2012 database that contains a table named BlogEntry that has the following columns:

Id is the Primary Key.

You need to append the "This is in a draft stage" string to the Summary column of the recent 10 entries based on the values in EntryDateTime.

Which Transact-SQL statement should you use?

A. UPDATE TOP(10) BlogEntry

SET Summary.WRITE(N' This is in a draft stage', NULL, 0)

B. UPDATE BlogEntry

SET Summary = CAST(N' This is in a draft stage' as nvarchar(max))

WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)

C. UPDATE BlogEntry

SET Summary.WRITE(N' This is in a draft stage', NULL, 0) FROM (

SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) AS s WHERE BlogEntry.Id = s.ID

D. UPDATE BlogEntry

SET Summary.WRITE(N' This is in a draft stage', 0, 0)

WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)

Answer: C

Q15. You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products.

You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data.

You need to ensure that the following requirements are met:

. Future modifications to the table definition will not affect the applications' ability to access data. . The new object can accommodate data retrieval and data modification.

You need to achieve this goal by using the minimum amount of changes to the existing applications.

What should you create for each application?

A. table partitions

B. views

C. table-valued functions

D. stored procedures

Answer: B

Q16. You are writing a set of queries against a FILESTREAM-enabled database.

You create a stored procedure that will update multiple tables within a transaction.

You need to ensure that if the stored procedure raises a runtime error, the entire

transaction is terminated and rolled back.

Which Transact-SQL statement should you include at the beginning of the stored

procedure?

A. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

B. SET XACT_ABORT OFF

C. SET TRANSACTION ISOLATION LEVEL SNAPSHOT

D. SET IMPLICIT_TRANSACTIONS ON

E. SET XACT_ABORT ON

F. SET IMPLICIT TRANSACTIONS OFF

Answer: E

Q17. CORRECT TEXT

You have an XML schema collection named Sales.InvoiceSchema.

You need to declare a variable of the XML type named invoice. The solution must ensure

that the invoice is validated by using Sales.InvoiceSchema.

Provide the correct code in the answer area.

Answer:

DECLARE @invoice XML(Sales.InvoiceSchema)

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

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

Answer: D

Q19. You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the

CustomerId value set to 1 in the following XML format.

</CUSTOMERS>

Which Transact-SQL query should you use?

A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW

B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW, ELEMENTS

C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO

D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS

E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO

F. SELECT Name, Country, Orderld, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS

G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers')

H. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers')

Answer: E

Q20. CORRECT TEXT

You have an XML schema collection named Sales.InvoiceSchema. You need to declare a variable of the XML type named XML1. The solution must ensure that XML1 is validated by using Sales.InvoiceSchema.

Which code segment should you use?

To answer, type the correct code in the answer area.

Answer:

DECLARE @XML1 XML(Sales.InvoiceSchema)

To see all sample questions go to: 70-461 test prep

Related 70-461 posts: