SQL exam 70-461 questions and answers part 2

MS - SQL exam 70-461 questions and answers dumps

QUESTION 11

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

 

QUESTION 12

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

QUESTION 13

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

QUESTION 14

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)

SELECT 'True' AS BadCast

)

CATCH(

SELECT 'False' AS BadCast

)

C. SELECT

CASE

WHEN convert(decimal(36,9), @var) IS NULL

THEN 'True'

ELSE 'False'

END

AS BadCast

D. SELECT

IIF(TRY_PARSE(@var AS decimal(36,9)) IS NULL, 'True', 'False')

AS BadCast

QUESTION 15

Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both

tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The data in the two tables is distinct from one another. Business users want a report that includes aggregate information about the total number of global sales and total sales amounts.

You need to ensure that your query executes in the minimum possible time. Which query should you use?

A. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount

FROM (

SELECT SalesOrderId, SalesAmount

FROM DomesticSalesOrders

UNION ALL

SELECT SalesOrderId, SalesAmount

FROM InternationalSalesOrders

) AS p

B. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount

FROM (

SELECT SalesOrderId, SalesAmount

FROM DomesticSalesOrders

UNION

SELECT SalesOrderId, SalesAmount

FROM InternationalSalesOrders

) AS p

C. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount

FROM DomesticSalesOrders

UNION

SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount

FROM InternationalSalesOrders

D. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount

FROM DomesticSalesOrders

UNION ALL

SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount

FROM InternationalSalesOrders

QUESTION 16

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?

A. ENCRYPTBYKEY

B. ENCRYPTION

C. ENCRYPTBYPASSPHRASE

D. ENCRYPTBYCERT

 

QUESTION 17

You use a Microsoft SQL Server 2012 database. You want to create a table to store Microsoft Word documents. You need to ensure that the documents must only be accessible via Transact-SQL queries. Which Transact-SQL statement should you use?

A. CREATE TABLE DocumentStore

(

[Id] INT NOT NULL PRIMARY KEY,

[Document] VARBINARY(MAX) NULL

)

GO

B. CREATE TABLE DocumentStore

(

[Id] hierarchyid,

[Document] NVARCHAR NOT NULL

)

GO

C. CREATE TABLE DocumentStore AS FileTable

D. CREATE TABLE DocumentStore

(

[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,

[Document] VARBINARY(MAX) FILESTREAM NULL

)

GO

 

QUESTION 18

You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistoncal. You write the following Transact-SQL query:

INSERT INTO OrdersHistorical

SELECT * FROM CompletedOrders

You need to optimize transaction logging and locking for the statement. Which table hint should you use?

A. HOLDLOCK

B. ROWLOCK

C. XLOCK

D. UPDLOCK

E. TABLOCK

QUESTION 19

Your database contains a table named Purchases. The table includes a DATETIME column named

PurchaseTime that stores the date and time each purchase is made. There is a non-clustered index

on the PurchaseTime column. The business team wants a report that displays the total number of

purchases made 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 Purchases

WHERE PurchaseTime = CONVERT(DATE, GETDATE())

B. SELECT COUNT(*)

FROM Purchases

WHERE PurchaseTime = GETDATE()

C. SELECT COUNT(*)

FROM Purchases

WHERE CONVERT(VARCHAR, PurchaseTime, 112) = CONVERT(VARCHAR, GETDATE(), 112)

D. SELECT COUNT(*)

FROM Purchases

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

AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

QUESTION 20

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

Correct Answer:

11	12	13	14	15	16	17	18	19	10
A	E	B	D	A	B	A	E	D	D