Microsoft 70-461 Exam Questions Instant Access

Question 1

You have a database that contains the tables as shown in the exhibit. (Click the Exhibit button.)

You have the following query:

You need to recreate the query to meet the following requirements:

* Reference columns by using one-part names only.

* Sort aggregates by SalesTerritoryID, and then by ProductID.

* Order the results in descending order from SalesTerritoryID to ProductID.

* The solution must use the existing SELECT clause and FROM clause.

Which code segment should you use?

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

ASELECT SalesTerritoryID,
ProductID,
AVG(UnitPrice),
MAX(OrderQty),
MAX(DiscountAmount)
FROM Sales.Details
GROUP BY SalesTerritoryID , ProductID
ORDER BY SalesTerritoryID DESC, ProductID DESC

BSELECT SalesTerritoryID,
ProductID,
AVG(UnitPrice),
MAX(OrderQty),
GROUP BY SalesTerritoryID , ProductID
ORDER BY SalesTerritoryID DESC, ProductID DESC

Answer : A

Question 2

You have a database named Sales that contains the tables shown in the exhibit. (Click the Exhibit button.)

You have an application named Appl. You have a parameter named @Count that uses the int data type. App1 is configured to pass @Count to a stored procedure.

You need to create a stored procedure named usp_Customers for App1 that returns only the number of rows specified by the @Count parameter.

The solution must NOT use BEGIN, END, or DECLARE statements.

Part of the correct Transact-SQL statement has been provided in the answer area. Complete the Transact-SQL statement

ACREATE PROCEDURE usp_Customers @Count int
SELECT TOP(@Count)
Customers.LastName
FROM Customers
ORDER BY Customers.LastName

BCREATE PROCEDURE usp_Customers #Count int
SELECT TOP(Count)
Customers.LastName
FROM Customers
ORDER BY Customers.LastName

Answer : A

Question 3

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?

ACREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS
IF CCLUMNS_CHANGED(RetailPrice, WholesalePrice)
- - Create Audit Records

BCREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS
IF EXISTS(SELECT RetailPrice from inserted) OR
EXISTS (SELECT WholeSalePnce FROM inserted)
- - Create Audit Records

CCREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS
IF COLUMNS_UPDATED(RetailPrice, WholesalePrice)
- - Create Audit Records

DCREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS
IF UPDATE(RetailPrice) OR UPDATE(WholeSalePrice)
- - Create Audit Records

Answer : D

http://msdn.microsoft.com/en-us/library/bb510663.aspx

http://msdn.microsoft.com/en-us/library/ms186329.aspx

Question 4

You administer several Microsoft SQL Server 2012 database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN). Many of the tables involved in replication use the XML and varchar (max) data types. Occasionally, merge replication fails due to timeout errors. You need to reduce the occurrence of these timeout errors. What should you do?

ASet the Merge agent on the problem subscribers to use the slow link agent profile.

BCreate a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication.

CChange the Merge agent on the problem subscribers to run continuously.

DSet the Remote Connection Timeout on the Publisher to 0.

Answer : A

Question 5

Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter.

You need to build a single process for each employee that will execute the appropriate stored procedure based on the country of residence.

Which approach should you use?

AA SELECT statement that includes CASE

BCursor

CBULK INSERT

DView

EA user-defined function

Answer : E

SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set.

Question 6

You are developing a database that will contain price information.

You need to store the prices that include a fixed precision and a scale of six digits.

Which data type should you use?

AReal

BSmall money

CMoney

DDecimal

Answer : D

Question 7

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

You create a stored procedure named dbo.ModifyData that can modify rows.

You need to ensure that when the transaction fails, dbo.ModifyData meets the following requirements:

* Does not return an error

* Closes all opened transactions

Which Transact-SQL statement should you use?

ABEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ TRANCOUNT = 0
ROLLBACK TRANSACTION;
END CATCH

BBEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ERROR != 0
ROLLBACK TRANSACTION;
THROW;
END CATCH

CBEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT = 0
ROLLBACK TRANSACTION;
THROW;
END CATCH

DBEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ERROR != 0
ROLLBACK TRANSACTION;
END CATCH

Answer : D

Microsoft Querying Microsoft SQL Server 2012/2014 70-461 Exam Questions