Microsoft 70-462 Exam Practice Test Instant Access

Question 1

Which of the following transaction safety setting in SQL Server 2012 mirroring forces the mirror to be synchronized with the primary at all times?

ASAFETY FULL

BSAFETY OFF

CMIRROR SYNC

DMIRROR 100

Answer : A

References:

https://msdn.microsoft.com/en-us/library/ms189852%28v=sql.110%29.aspx

Question 2

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, CountryFROM OrdersINNER JOIN Customers ON Orders.CustomerId = Customers-CustomerIdWHERE Customers.CustomerId = 1FOR XML RAW

BSELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML RAW, ELEMENTS

CSELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML AUTO

DSELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML AUTO, ELEMENTS

ESELECT Name, Country, Orderld, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML AUTO, ELEMENTS

FSELECT Name, Country, Crderld, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId =FOR XML AUTO, ELEMENTS

GSELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, AmountFROM OrdersINNER JOIN Customers ON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML PATH ('Customers')

HSELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId,OrderDate, AmountFROM OrdersINNER JOIN Customers ON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML PATH ('Customers')

Answer : E

Question 3

You install SQL Server on a new server named SQL1 and create several databases.

Users report that they fail to connect to any of the databases on SQL1.

You discover that the TCP connections are disabled for the database engine on SQL1.

You need to ensure that the users can connect to the databases on SQL1.

What should you use?

Athe SQL Server Installation Center

Bthe sp_configure stored procedure

CSQL Server Configuration Manager

Dthe ODBC Data Source Administrator

Answer : C

To enable the TCP/IP network protocol

References:

https://technet.microsoft.com/en-us/library/hh231672(v=sql.110).aspx

Question 4

Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a non-clustered index on the OrderTime column. The business team wants a report that displays the total number of orders placed 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?

ASELECT COUNT(*) FROM SalesOrdersWHERE OrderTime = CONVERT(DATE, GETDATE())

BSELECT COUNT(*) FROM SalesOrdersWHERE OrderTime = GETDATE()

CSELECT COUNT(*) FROM SalesOrdersWHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(I, 112))

DSELECT COUNT(*) FROM SalesOrdersWHERE OrderTime >= CONVERT(DATE, GETDATE())AND OrderTime < DATEADD(DAY, CONVERT(DATE, GETDATE()))

Answer : D

Question 5

You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01.

You need to diagnose deadlocks that happen when executing a specific set of stored procedures by recording events and playing them back on a different test server.

What should you create?

AAn Extended Event session

BA Policy

CA Database Audit Specification

DAn Alert

EA Server Audit Specification

FA SQL Profiler Trace

GA Resource Pool

Answer : F

Question 6

You administer two instances of Microsoft SQL Server 2012. You deploy an application that uses a database on the named instance.

The application is unable to connect to the database on the named instance.

You need to ensure that the application can connect to the named instance.

What should you do?

AUse the Data Quality Client to configure the application.

BStart the SQL Server Browser Service.

CUse the Master Data Services Configuration Manager to configure the application.

DStart the SQL Server Integration Services Service.

Answer : B

Question 7

You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person. The tables have the following definitions:

You create a view named VwEmployee as shown in the following Transact-SQL statement.

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 TrgVwEmployeeON VwEmployeeFOR INSERTASBEGININSERT INTO Person(Id, FirstName, LastName)SELECT Id, FirstName, LastName, FROM insertedINSERT INTO Employee(PersonId, EmployeeNumber)SELECT Id, EmployeeNumber FROM insertedEND

BCREATE TRIGGER TrgVwEmployeeON VwEmployeeINSTEAD OF INSERTASBEGININSERT INTO Person(Id, FirstName, LastName)SELECT Id, FirstName, LastName, FROM insertedINSERT INTO Employee(PersonId, EmployeeNumber)SELECT Id, EmployeeNumber FROM insertedEND

CCREATE TRIGGER TrgVwEmployeeON VwEmployeeINSTEAD OF INSERTASBEGINDECLARE @ID INT, @FirstName NVARCHAR(25),@LastName NVARCHAR(25), @PersonID INT,@EmployeeNumber NVARCHAR(15)SELECT @ID = ID, @FirstName = FirstName,@LastName = LastName, @EmployeeNumber = EmployeeNumberFROM insertedINSERT INTO Person(Id, FirstName, LastName)VALUES(@ID, @FirstName, @LastName)INSERT INTO Employee(PersonID, EmployeeNumber)VALUES(@PersonID, @EmployeeNumberEND

DCREATE TRIGGER TrgVwEmployeeON VwEmployeeINSTEAD OF INSERTASBEGININSERT INTO Person(Id, FirstName, LastName)SELECT Id, FirstName, LastName FROM VwEmployeeINSERT INTO Employee(PersonID, EmployeeNumber)SELECT Id, EmployeeNumber FROM VwEmployeeEND

Answer : B

Microsoft Administering Microsoft SQL Server 2012 Databases 70-462 Exam Practice Test