Microsoft 70-461 Exam Practice Test Instant Access

Question 1

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.

Which Transact-SQL query should you use?

ASELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW

BSELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW, ELEMENTS

CSELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO

DSELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO, ELEMENTS

ESELECT Name, Country, OrderId, OrderDate, Amount FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO

FSELECT Name, Country, OrderId, OrderDate, Amount FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO, ELEMENTS

GSELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM
Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML PATH ('Customers')

HSELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId,
OrderDate, Amount FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML PATH ('Customers')

Answer : F

Question 2

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?

ASET XACT_ABORT ON

BSET ARITHABORT ON

CTRY

DBEGIN

ESET ARITHABORT OFF

FSET XACT_ABORT OFF

Answer : A

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

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

Question 3

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.

Which Transact-SQL query should you use?

ASELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW

BSELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW, ELEMENTS

CSELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO

DSELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO, ELEMENTS

ESELECT Name, Country, OrderId, OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO

FSELECT Name, Country, OrderId, OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO, ELEMENTS

GSELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML PATH ('Customers')

HSELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML PATH ('Customers')

Answer : E

Question 4

You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person.

The tables have the following definitions:

Users are able to use single INSERT statements or INSERT...SELECT statements into this view.

You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view.

Which Transact-SQL statement should you use?

ACREATE TRIGGER TrgVwEmployee
ON VwEmployee
FOR INSERT
AS
BEGIN
INSERT INTO Person(Id, FirstName, LastName)
SELECT Id, FirstName, LastName, FROM inserted
INSERT INTO Employee(PersonId, EmployeeNumber)
SELECT Id, EmployeeNumber FROM inserted
END

BCREATE TRIGGER TrgVwEmployee
ON VwEmployee
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Person(Id, FirstName, LastName)
SELECT Id, FirstName, LastName, FROM inserted
INSERT INTO Employee(PersonId, EmployeeNumber)
SELECT Id, EmployeeNumber FROM inserted
END

CCREATE TRIGGER TrgVwEmployee
ON VwEmployee
INSTEAD OF INSERT
AS
BEGIN
DECLARE @ID INT, @FirstName NVARCHAR(25), @LastName NVARCHAR(25), @PersonID
INT, @EmployeeNumber NVARCHAR(15)
SELECT @ID = ID, @FirstName = FirstName, @LastName = LastName, @EmployeeNumber
= EmployeeNumber
FROM inserted
INSERT INTO Person(Id, FirstName, LastName)
VALUES(@ID, @FirstName, @LastName)
INSERT INTO Employee(PersonID, EmployeeNumber)
VALUES(@PersonID, @EmployeeNumber
End

DCREATE TRIGGER TrgVwEmployee
ON VwEmployee
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Person(Id, FirstName, LastName)
SELECT Id, FirstName, LastName FROM VwEmployee
INSERT INTO Employee(PersonID, EmployeeNumber)
SELECT Id, EmployeeNumber FROM VwEmployee
End

Answer : B

Question 5

You have three tables that contain data for dentists, psychiatrists, and physicians. You create a view that is used to look up their email addresses and phone numbers.

The view has the following definition:

You need to ensure that users can update only the phone numbers and email addresses by using this view.

What should you do?

AAlter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.

BCreate an INSTEAD OF UPDATE trigger on the view.

CDrop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.

DCreate an AFTER UPDATE trigger on the view.

Answer : B

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

Question 6

You develop a stored procedure for a wholesale glass manufacturing company. The stored procedure uses a cursor to read all row-based combinations of the following tables:

You observe that the stored procedure returns results very slowly.

You need to improve the execution speed of the stored procedure.

Which cursor type should you use?

AGLOBAL FORWARD_ONLY

BLOCAL FAST_FORWARD

CLOCAL STATIC READ_ONLY FORWARD_ONLY

DGLOBAL STATIC

Answer : B

FAST_FORWARD specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled.

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

Question 7

You administer a Microsoft SQL Server database that contains a table named Products.

In a bit column named Discontinued, a value of 1 indicates a product is inactive, and a value of 0 indicates the product is active.

You need to create a view that returns a column with a full product status description.

Which expression should you use to define the column?

AIF(Discontinued = 0, 'Active', 'Inactive')

BCASE Discontinued
WHEN 0 THEN 'Active'
WHEN 1 THEN 'Inactive'
END

CIF Discontinued = 0
'Active'
ELSE
'Inactive'

DDECODE (Discontinued, 0, 'Active', 1, 'Inactive', 'Unknown')

Answer : B

The CASE statement evaluates a list of conditions and returns one of multiple possible result expressions.

The CASE expression has two formats:

The simple CASE expression compares an expression to a set of simple expressions to determine the result.

The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Both formats support an optional ELSE argument.

CASE can be used in any statement or clause that allows a valid expression.

References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017

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