Ensurepass

QUESTION 36
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 preceding year. Which Transact-SQL query should you use?
A. SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS
NextProfit
FROM Profits
B. SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS
NextProfit
FROM Profits
C. SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS
NextProfit
FROM Profits
D. SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS
NextProfit
FROM Profits
Explanation/Reference:
Reference: http://msdn.microsoft.com/en-us/library/hh231256.aspx
Reference: http://msdn.microsoft.com/en-us/library/hh213125.aspx

QUESTION 37
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?select
A. BEGIN 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
B. TRY(
SELECT convert(decimal(36,9), @var)
)
CATCH(
)
C. SELECT CASE
WHEN convert(decimal(36,9), @var) IS NULL THEN ‘True’
ELSE ‘False’ END
D. SELECT
IIF(TRY_PARSE(@var AS decimal(36,9)) IS NULL, ‘True’, ‘False’) AS BadCast
Explanation/Reference:
Reference: http://msdn.microsoft.com/en-us/library/hh213126.aspx

QUESTION 38
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 run-time 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
Explanation/Reference:
Reference: http://msdn.microsoft.com/en-us/library/ms188792.aspx

QUESTION 39
You have a Microsoft SQL Server 2012 database that contains tables named Customers and Orders. The tables are related by a column named CustomerID. You need to create a query that meets the following requirements:
Returns the CustomerName for all customers and the OrderDate for any orders that they have placed. Results must include customers who have not placed any orders.
Which Transact-SQL query should you use? A. SELECT CustomerName, OrderDate
FROM Customers
RIGHT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
B. SELECT CustomerName, CrderDate
FROM Customers
JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
C. SELECT CustomerName, OrderDate
FROM Customers
CROSS JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
D. SELECT CustomerName, OrderDate
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
Section: (none)
Explanation
Explanation/Reference:
Reference: http://msdn.microsoft.com/en-us/library/ms177634.aspx

QUESTION 40
You create a stored procedure that will update multiple tables within a transaction. You need to ensure that if the stored procedure raises a run-time 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 XACT_ABORT ON B. SET ARITHABORT ON C. TRY
D. BEGIN
E. SET ARITHABORT OFF F. SET XACT_ABORT OFF