[Free] Get all latest Microsoft 70-433 Actual Tests 21-30

Ensurepass

QUESTION 31

You are using TRY…CATCH error handling. You need to raise an error that will pass control to the CATCH block. Which severity level should you use?

 

A.

0

B.

9

C.

10

D.

16

 

Correct Answer: D

Explanation:

A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.

Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs.

Severity levels less than 0 are interpreted as 0. Severity levels greater than 25 are interpreted as 25.

 

 

QUESTION 32

You have a table named Orders. You have been tasked to modify your company’s main database to remove all inactive order rows. You are developing a stored procedure that will enable you to delete these rows. You have written the following code segment to accomplish this task. (Line numbers are included for reference only.)

 

01 BEGIN TRY

 

02 DECLARE @RowCount INT = 1000

 

03 WHILE @RowCount = 1000

 

04 BEGIN

 

05 DELETE TOP (1000) FROM Orders WHERE Status = ‘Inactive’;

 

06 SET @RowCount = @@ROWCOUNT

 

07 …

 

08 END

 

09 END TRY

 

10 BEGIN CATCH

 

11 PRINT ERROR_MESSAGE()

 

12 END CATCH

 

You need to insert a Transact-SQL statement that will notify you immediately after each batch of rows is deleted. Which Transact-SQL statement should you insert at line 07?

 

A.

RAISERROR (‘Deleted %i rows’, 6, 1, @RowCount)

B.

RAISERROR (‘Deleted %i rows’, 16, 1, @RowCount)

C.

RAISERROR (‘Deleted %i rows’, 10, 1, @RowCount) WITH NOWAIT

D.

RAISERROR (‘Deleted %i rows’, 11, 1, @RowCount) WITH NOWAIT

 

Correct Answer: C

Explanation:

RAISERROR can be used as an alternative to PRINT to return messages to calling applications.

Because RAISERROR run with a severity of 11 to 19 in a TRY block transfers control to the associated CATCH block, specify a severity of 10 or lower to use RAISERROR to return a message from a TRY block without invoking the CATCH block. NOWAIT option sends messages immediately to the client.

 

 

QUESTION 33

You have a transaction that uses the repeatable read isolation level. This transaction causes frequent blocking problems. You need to reduce blocking. You also need to avoid dirty reads and non-repeatable reads. Which transaction isolation level should you use?

 

A.

SNAPSHOT

B.

SERIALIZABLE

C.

READ COMMITTED

D.

READ UNCOMMITTED

 

Correct Answer: A

 

 

 

 

 

 

 

QUESTION 34

You are writing a batch that contains multiple UPDATE statements to modify existing products. You have placed these updates into one explicit transaction. You need to set an option at the beginning of the transaction to roll back all changes if any of the updates in the transaction fail. Which option should you enable?

 

A.

ARITHABORT

B.

XACT_ABORT

C.

IMPLICIT_TRANSACTIONS

D.

REMOTE_PROC_TRANSACTIONS

 

Correct Answer: B

 

 

QUESTION 35

You have a table named JobCandidate. You are tasked to delete a row in the JobCandidate table. You need to write a transaction that allows the database to be restored to the exact point the record was deleted without knowing the time of execution. Which query should you use?

 

A.

BEGIN TRANSACTION

DELETE FROM JobCandidate

WHERE JobCandidateID = 10;

COMMIT TRANSACTION;

B.

BEGIN TRANSACTION

WITH MARK N’Deleting a Job Candidate’;

DELETE FROM JobCandidate

WHERE JobCandidateID = 10;

COMMIT TRANSACTION

C.

BEGIN TRANSACTION Delete_Candidate WITH MARK

DELETE FROM JobCandidate

WHERE JobCandidateID = 10;

COMMIT TRANSACTION Delete_Candidate;

D.

DECLARE @CandidateName varchar(50) = ‘Delete_Candidate’ BEGIN TRANSACTION @CandidateName

DELETE FROM JobCandidate

WHERE JobCandidateID = 10;

COMMIT TRANSACTION @CandidateName;

 

Correct Answer: C

Explanation:

BEGIN { TRAN | TRANSACTION }

[ { transaction_name | @tran_name_variable }

[ WITH MARK [ ‘description’ ] ]

]

[ ; ]

 

WITH MARK [ ‘description’ ] – specifies that the transaction is marked in the log. description is a string that describes the mark.

If WITH MARK is used, a transaction name must be specified. When restoring a database to an earlier state, the marked transaction can be used in place of a date and time.

The mark is placed in the transaction log only if the database is updated by the marked transaction.

Transactions that do not modify data are not marked.

BEGIN TRAN new_name WITH MARK can be nested within an already existing transaction that is not marked.

Upon doing so, new_name becomes the mark name for the transaction, despite the name that the transaction may already have been given.

 

 

QUESTION 36

You have the following table named Sales. You need to return sales data ordered by customer name and date of sale. For each customer, the most recent sale must be listed first. Which query should you use?

 

A.

SELECT CustomerName,

SalesDate

FROM Sales

ORDER BY CustomerName,

SalesDate;

B.

SELECT CustomerName,

SalesDate

FROM Sales

ORDER BY SalesDate DESC,

CustomerName;

C.

SELECT CustomerName,

SalesDate

FROM Sales

ORDER BY CustomerName,

SalesDate DESC;

D.

SELECT CustomerName,

SalesDate

FROM Sales

ORDER BY CustomerName DESC;

 

Correct Answer: C

 

 

QUESTION 37

You have a table named Sales.SalesOrderHeader and a table named Person.Person. You are tasked to write a query that returns SalesOrderID and SalesPersonName that have an OrderDate greater than 20040101. SalesPersonName should be made up by concatenating the columns named FirstName and LastName from the table named Person.Person. You need to write a query to return data, sorted in alphabetical order, by the concatenation of FirstName and LastName. Which Transact-SQL statement should you use?

 

A.

SELECT SalesOrderID, FirstName + ‘ ‘ + LastName as SalesPersonName FROM Sales.SalesOrderHeader H

JOIN Person.Person P on

B.

BusinessEntityID = H.SalesPersonID

WHERE OrderDate > ‘20040101’

ORDER BY FirstName ASC, LastName ASC

C.

SELECT SalesOrderID, FirstName + ‘ ‘ + LastName as SalesPersonName FROM Sales.SalesOrderHeader H

JOIN Person.Person P on

D.

BusinessEntityID = H.SalesPersonID

WHERE OrderDate > ‘20040101’

ORDER BY FirstName DESC, LastName DESC

E.

SELECT SalesOrderID, FirstName +’ ‘ + LastName as SalesPersonName FROM Sales.SalesOrderHeader H

JOIN Person.Person P on

F.

BusinessEntityID = H.SalesPersonID

WHERE OrderDate > ‘20040101’

ORDER BY SalesPersonName ASC

G.

SELECT SalesOrderID, FirstName + ‘ ‘ + LastName as SalesPersonName FROM Sales.SalesOrderHeader H

JOIN Person.Person P on

H.

BusinessEntityID = H.SalesPersonID

WHERE OrderDate > ‘20040101’

ORDER BY SalesPersonName DESC

 

Correct Answer: C

 

 

QUESTION 38

You have a table named Sales.PotentialClients. This table contains a column named EmailAddress. You are tasked to develop a report that returns valid “.com” email addresses from Sales.PotentialClients. A valid email address must have at least one character before the @ sign, and one character after the @ sign and before the “.com.” You need to write a Transact-SQL statement that returns data to meet the business requirements. Which Transact-SQL statement should you use?

 

A.

select * from Sales.PotentialClients

where EmailAddress like ‘_%@_%.com’

B.

select * from Sales.PotentialClients

where EmailAddress like ‘%@%.com’

C.

select * from Sales.PotentialClients

where EmailAddress like ‘_%@_.com’

D.

select * from Sales.PotentialClients

where EmailAddress like ‘%@%[.]com’

 

Correct Answer: C

Explanation:

If “at least” expression refer to both “one character before…” and “one character after…”, then A is correct.

If “at least” expression refer to only “one character before…” which seems the case, then C is correct.

 

 

QUESTION 39

You have a table named Orders. OrderID is defined as an IDENTITY(1,1). OrderDate has a default value of 1.

 

clip_image001

 

You need to write a query to insert a new order into the Orders table for CustomerID 45 with today’s date and a cost of 89.00. Which statement should you use?

 

A.

INSERT INTO Orders

(CustomerId, OrderDate, Cost)

VALUES (45, DEFAULT, 89.00);

B.

INSERT INTO Orders

(OrderID, CustomerId, OrderDate, Cost)

VALUES (1, 45, DEFAULT, 89.00);

C.

INSERT INTO Orders

(CustomerId, OrderDate, Cost)

VALUES (45, CURRENT_TIMESTAMP, 89.00);

D.

INSERT INTO Orders

(OrderID, CustomerId, OrderDate, Cost)

VALUES (1, 45, CURRENT_TIMESTAMP, 89.00);

 

Correct Answer: C

 

 

QUESTION 40

You have the following two tables.

 

clip_image002

 

The foreign key relationship between these tables has CASCADE DELETE enabled. You need to remove all records from the Orders table. Which Transact-SQL statement should you use?

 

A.

DROP TABLE Orders

B.

DELETE FROM Orders

C.

TRUNCATE TABLE Orders

D.

DELETE FROM OrderDetails

 

Correct Answer: B

 

Instant Access to Download Testing Software & PDF File for Microsoft 70-433 Real Exam

Instant Access to Try Microsoft 70-433 Free Demo

This entry was posted in 70-433 Actual Tests and tagged , , , , , . Bookmark the permalink.