The Abreast of the times Guide To 70-461 tutorials Mar 2021

Proper study guides for Replace Microsoft Querying Microsoft SQL Server 2012 certified begins with Microsoft 70-461 preparation products which designed to deliver the Best Quality 70-461 questions by making you pass the 70-461 test at your first time. Try the free 70-461 demo right now.

2021 Mar 70-461 test question

Q11. CORRECT TEXT 

You have a database named Sales that contains the tables shown in the exhibit. (Click the Exhibit button). 

You need to create a query for a report. The query must meet the following requirements: 

NOT use object delimiters. 

Use the first initial of the table as an alias. 

Return the most recent order date for each customer. 

Retrieve the last name of the person who placed the order. 

The solution must support the ANSI SQL-99 standard. 

Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code. 

Answer: 

78. You are developing a database application by using Microsoft SQL Server 2012. 

An application that uses a database begins to run slowly. 

You discover that the root cause is a query against a frequently updated table that has a clustered index. The query returns four columns: three columns in its WHERE clause contained in a non-clustered index and one additional column. 

You need to optimize the statement. 

What should you do? 

A. Add a HASH hint lo the query. 

B. Add a LOOP hint to the query. 

C. Add a FORCESEEK hint to the query. 

D. Add an INCLUDE clause to the index. 

E. Add a FORCESCAN hint to the Attach query. 

F. Add a FORCESCAN hint to the Attach query. 

G. Add a columnstore index to cover the query. 

H. Enable the optimize for ad hoc workloads option. 

I. Cover the unique clustered index with a columnstore index. Include a SET FORCEPLAN ON statement before you run the query. 

J. Include a SET STATISTICS PROFILE ON statement before you run the query. 

K. Include a SET STATISTICS SHOWPLAN.XML ON statement before you run the query. 

L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query. 

M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query. 

N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query. 

Answer: C 

Q12. 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? 

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 

Answer: D 

Q13. You are developing a database that will contain price information. 

You need to store the prices that include a fixed precision and a scale of six digits. 

Which data type should you use? 

A. Real 

B. Small money 

C. Money 

D. Decimal 

Answer: D 

Q14. CORRECT TEXT 

You have an XML schema collection named Sales.InvoiceSchema. You need to declare a variable of the XML type named XML1. The solution must ensure that XML1 is validated by using Sales.InvoiceSchema. 

Which code segment should you use? 

To answer, type the correct code in the answer area. 

Answer: 

DECLARE @XML1 XML(Sales.InvoiceSchema) 

Q15. 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: 

<row OrderId="1" OrderDate="2000-01-01T00:00:00" Amount="3400.00" Name="Customer 

A" Country="Australia" /> 

<row OrderId="2" OrderDate="2001-01-01T00:00:00" Amount="4300.00" Name="Customer 

A" Country="Australia" /> 

Which Transact-SQL query should you use? 

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

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 OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO 

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

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

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

G. 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') 

H. 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') 

Answer: A 

Regenerate 70-461 exam answers:

Q16. You are developing a database that will contain price information. 

You need to store the prices that include a fixed precision and a scale of six digits. 

Which data type should you use? 

A. Float 

B. Money 

C. Smallmoney 

D. Decimal 

Answer: D 

Explanation: 

Decimal is the only one in the list that can give a fixed precision and scale. Reference: http://msdn.microsoft.com/en-us/library/ms187746.aspx 

Q17. 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. Inline function 

C. User-defined data type 

D. Stored procedure 

Answer: D 

Q18. 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 

Answer: B 

Q19. You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3. 

You have permissions on both Database1 and Database2. You plan to write and deploy a stored procedure named dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute other stored procedures in the other databases. 

You need to ensure that callers that do not have permissions on Database1 or Database2 can execute the stored procedure. 

Which Transact-SQL statement should you use? 

A. USE Database2 

B. EXECUTE AS OWNER 

C. USE Database1 

D. EXECUTE AS CALLER 

Answer: B 

Q20. You are a database developer for an application hosted on a Microsoft SQL Server 2012 server. 

The database contains two tables that have the following definitions: 

Global customers place orders from several countries. 

You need to view the country from which each customer has placed the most orders. 

Which Transact-SQL query do you use? 

A. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry, RANK() OVER (PARTITION BY CustomerID ORDER BY COUNT(OrderAmount) DESC) AS Rnk FROM Orders GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID WHERE o.Rnk = 1 

B. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM (SELECT c.CustomerID, c.CustomerName, o.ShippingCountry, RANK() OVER (PARTITION BY CustomerID ORDER BY COUNT(o.OrderAmount) ASC) AS Rnk FROM Customer c INNER JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs WHERE Rnk = 1 

C. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry, RANK() OVER (PARTITION BY CustomerID ORDER BY OrderAmount DESC) AS Rnk FROM Orders GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID WHERE o.Rnk = 1 

D. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry, COUNT(OrderAmount) DESC) AS OrderAmount FROM Orders GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID ORDER BY OrderAmount DESC 

Answer: A