Microsoft 70-461 Exam Practice Test Instant Access

Question 1

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

Your application sends data to an NVARCHAR(MAX) variable named @var.

You need to write a Transact-SQL statement that will find out the success of a cast to a decimal (36,9).

Which code segment should you use?

ABEGIN TRY
SELECT
convert (decimal(36,9), @var) as Value,
'True' As BadCast
END TRY
BEGIN CATCH
SELECT
convert (decimal(36,9), @var) as Value,
'False' As BadCast
END CATCH

BTRY(
SELECT convert (decimal(36,9), @var)
SELECT 'True' As BadCast
)
CATCH(
SELECT 'False' As BadCast
)

CSELECT
CASE
WHEN convert (decimal(36,9), @var) IS NULL
THEN 'True'
ELSE 'False'
END
AS BadCast

DSELECT
IF(TRY_PARSE(@var AS decimal(36,9)) IS NULL,
'True',
'False'
)
AS BadCast

Answer : D

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

Question 2

You are a database developer at an independent software vendor. You create stored procedures that contain proprietary code.

You need to protect the code from being viewed by your customers.

Which stored procedure option should you use?

AENCRYPTBYKEY

BENCRYPTION

CENCRYPTBYPASSPHRASE

DENCRYPTBYCERT

Answer : B

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

http://technet.microsoft.com/en-us/library/ms174361.aspx

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

http://technet.microsoft.com/en-us/library/ms190357.aspx

http://technet.microsoft.com/en-us/library/ms188061.aspx

Question 3

You develop a Microsoft SQL Server 2012 database.

You need to create and call a stored procedure that meets the following requirements:

* Accepts a single input parameter for CustomerID.

* Returns a single integer to the calling application.

Which Transact-SQL statement or statements should you use? (Each correct answer presents part of the solution. Choose all that apply.)

ACREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT,
@CustomerRating INT OUTPUT
AS
SET NOCOUNT ON
SELECT @CustomerRating = CustomerOrders/CustomerValue
FROM Customers
WHERE CustomerID = @CustomerID
RETURN
GO

BEXECUTE dbo.GetCustomerRating 1745

CDECLARE @CustomerRatingByCustomer INT
DECLARE @Result INT
EXECUTE @Result = dbo.GetCustomerRating
1745,
@CustomerRatingByCustomer

DCREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT,
@CustomerRating INT OUTPUT
AS
SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerValue
FROM Customers
WHERE CustomerID = @CustomerID
RETURN @Result
GO

EDECLARE @CustomerRatingByCustomer INT
EXECUTE dbo.GetCustomerRating
@CustomerID = 1745,
@CustomerRating = @CustomerRatingByCustomer OUTPUT

FCREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT
AS
DECLARE @Result INT
SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerValue
FROM Customers
WHERE CustomerID = @CustomerID
RETURNS @Result
GO

Answer : A, E

Question 4

You have a database named SALES that stored the sales data and the salespeople for your company.

You need to create a function that is passed a ProductID and then returns a list of SalesOrderID values for orders that must be associated to a salesperson in the future. The function must be able to return multiple SalesOrderID values.

Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.

Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.

AWHERE header.SalesPersonID IS NULL
On line 8 add: IS NULL to get:
CREATE FUNCTION dbo.OrdersWithNoSalesPerson(@ProductID INT)
RETURNS
AS
header.SalesOrderID
FROM Sales.SalesOrderDetail detail
INNER JOIN Sales.SalesOrderHeader header
on header.SalesOrderID = detail.SalesOrderID
WHERE header.SalesPersonID IS NULL
AND detail.ProductID = @ProductID;
GO
Note: IS NULL determines whether a specified expression is NULL. If the value of expression is NULL, IS NULL returns TRUE; otherwise, it returns FALSE.
To determine whether an expression is NULL, use IS NULL or IS NOT NULL instead of comparison operators (such as = or !=). Comparison operators return UNKNOWN when either or both arguments are NULL.

BWHERE header.SalesPersonID IS NULL
On line 8 add: IS NULL to get:
CREATE FUNCTION dbo.OrdersWithNoSalesPerson(@ProductID INT)
RETURNS
AS
header.SalesOrderID
FROM Sales.SalesOrderDetail detail
INNER JOIN Sales.SalesOrderHeader header
on header.
GO
Note: IS NULL determines whether a specified expression is NULL. If the value of expression is NULL, IS NULL returns TRUE; otherwise, it returns FALSE.
To determine whether an expression is NULL, use IS NULL or IS NOT NULL instead of comparison operators (such as = or !=). Comparison operators return UNKNOWN when either or both arguments are NULL.

Answer : A

References:https://msdn.microsoft.com/en-us/library/ms188795.aspx

Question 5

You administer a Microsoft SQL Server database that includes a table named Products. The Products table has columns named ProductId, ProductName, and CreatedDateTime.

The table contains a unique constraint on the combination of ProductName and CreatedDateTime.

You need to modify the Products table to meet the following requirements:

* Remove all duplicates of the Products table based on the ProductName column.

* Retain only the oldest Products row.

Which Transact-SQL query should you run?

AOption A

BOption B

COption C

DOption D

Answer : B

References:

https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql

Question 6

You administer a Microsoft Azure SQL Database instance.

You are writing a new stored procedure that uses explicit transactions.

You need to ensure that no other process can affect the objects being used by the stored procedure while it is running.

Which Transact-SQL statement should you run?

ASET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BSET IMPLICIT_TRANSACTIONS ON

CSET CURSOR_CLOSE_ON_COMMIT ON

DSET XACT_ABORT ON

Answer : A

SERIALIZABLE specifies the following:

Statements cannot read data that has been modified but not yet committed by other transactions.

No other transactions can modify data that has been read by the current transaction until the current transaction completes.

Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

References:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql

Question 7

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?

AA recursive stored procedure

BTrigger

CAn UPDATE statement that includes CASE

DCursor

EThe foreach SQLCLR statement

Answer : D

Microsoft Querying Microsoft SQL Server 2012/2014 70-461 Exam Practice Test