070-461 by Microsoft Actual Free Exam Questions And Answers

Question 1

You use a Microsoft SQL Server 2012 database that contains a table named BlogEntry that has the following columns:

Id is the Primary Key.
You need to append the "This is in a draft stage" string to the Summary column of the recent 10 entries based on the values in EntryDateTime.
Which Transact-SQL statement should you use?

A. UPDATE TOP(10) BlogEntry
SET Summary.WRITE(N' This is in a draft stage', NULL, 0)

B. UPDATE BlogEntry
SET Summary.WRITE(N' This is in a draft stage', 0, 0)
WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)

C. UPDATE BlogEntry
SET Summary = CAST(N' This is in a draft stage' as nvarchar(max))
WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)

D. UPDATE BlogEntry
SET Summary.WRITE(N' This is in a draft stage', NULL, 0) FROM (
SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) AS s
WHERE BlogEntry.Id = s.ID

Discussion 0

Correct Answer: D Vote an answer

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.
<CUSTOMERS Name="Customer A" Country="Australia">
<ORDERS OrderID="1" OrderDate="2001-01-01" Amount="3400.00" />
<ORDERS OrderID="2" OrderDate="2002-01-01" Amount="4300.00" />
</CUSTOMERS>
Which Transact-SQL query should you use?

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

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

C. SELECT 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')

D. SELECT 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')

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

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

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

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

Discussion 0

Correct Answer: A Vote an answer

Question 3

You are designing a table for a SQL Server database. The table uses horizontal partitioning.
You have the following requirements:

You need to choose the appropriate data type for the key value.
What should you do?

A. Generate a random value that uses the char(16) data type.

B. Use the NEWID function to create a unique identifier.

C. Use the NEWSEQUENTIALID function to create a unique identifier.

D. Generate a random value that uses the bigint datatype.

Discussion 0

Correct Answer: C Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 4

You are developing a Microsoft Azure SQL Database instance to support a company's payroll system. The database contains a table named Employee defined by the following Transact-SQL statement:

The company defines five employee levels. Each employee's level is stored in the Level column. Levels 1,2, and 3 are used for non-executive employees. Levels 4 and 5 are used for executives. Each of the non-executive levels has a defined range that constrains the salary of employees at that level.
You need to create a view that allows non-executive Employees entries to be updated based on the following rules:
* Only non-executive employees can be viewed or updated.
* Employees can be promoted to a non-executive level.
* Employees cannot be promoted to an executive level.
If an employees' salary changes, the new salary must adhere to the company-defined range for that level.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

Discussion 0

Correct Answer:

Explanation:
Box 1: WITH SCHEMABINDING AS
The syntax is: CREATE VIEW
WITH SCHEMABINDING
AS
Box 2:
SELECT DISTINCT cannot be used here.
Box 3: ORDER BY
Box 4:
Create a clustered index.
References: https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-2017

Question 5

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?

A. SELECT COUNT(*) FROM SalesOrders WHERE OrderTime >= CONVERT(DATE, GETDATE()) AND OrderTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

B. SELECT COUNT(*) FROM SalesOrders WHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(), 112))

C. SELECT COUNT(*) FROM SalesOrders WHERE OrderTime = CONVERT(DATE, GETDATE())

D. SELECT COUNT(*) FROM SalesOrders WHERE OrderTime = GETDATE()

Discussion 0

Correct Answer: A Vote an answer

Question 6

You administer several Microsoft SQL Server 2012 database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN). Many of the tables involved in replication use the XML and varchar (max) data types. Occasionally, merge replication fails due to timeout errors. You need to reduce the occurrence of these timeout errors. What should you do?

A. Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication.

B. Change the Merge agent on the problem subscribers to run continuously.

C. Set the Remote Connection Timeout on the Publisher to 0.

D. Set the Merge agent on the problem subscribers to use the slow link agent profile.

Discussion 0

Correct Answer: D Vote an answer

Question 7

You have a Microsoft SQL Server database that contains two tables named SalesTax and SalesTerritory.
You need to write a query that will return all possible combinations of SalesTax and SalesTerritory, regardless of whether two particular records are related.
Which Transact-SQL statement should you run?

A. Option B

B. Option A

C. Option D

D. Option C

Discussion 0

Correct Answer: D Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 8

You are a database developer of a Microsoft SQL Server database.
The database contains a table named Instructor that has the following definition:

You are designing a new table named Course that has the following definition:

You need to ensure that the InstructorId column in the Course table contains only values that exist in the InstructorID column of the Instructor table.
Which Transact-SQL statement should you use?

A. Option B

B. Option A

C. Option D

D. Option C

E. Option E

Discussion 0

Correct Answer: D Vote an answer

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

Question 9

You use Microsoft SQL Server 2012 to develop a database application.
You need to create an object that meets the following requirements:
* Takes an input variable
* Returns a table of values
* Cannot be referenced within a view
Which object should you use?

A. Scalar-valued function

B. User-defined data type

C. Inline function

D. Stored procedure

Discussion 0

Correct Answer: D Vote an answer

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

Contact Us

Useful Links

Latest Updated