70-461 Exam Questions - Online Test

70-461 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

Master the 70 461 exam dumps Querying Microsoft SQL Server 2012 content and be ready for exam day success quickly with this Testking microsoft 70 461 answers. We guarantee it!We make it a reality and give you real exam 70 461 dumps questions in our Microsoft exam 70 461 braindumps.Latest 100% VALID Microsoft 70 461 querying microsoft sql server 2012 Exam Questions Dumps at below page. You can use our Microsoft 70 461 exam dumps braindumps and pass your exam.

Q31. 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 = CAST(N' This is in a draft stage' as nvarchar(max)) 

WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) 

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

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

Answer: C 

Q32. 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) 

Q33. Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non-clustered index on the PurchaseTime column. 

The business team wants a report that displays the total number of purchases made 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 Purchases 

WHERE PurchaseTime = CONVERT(DATE, GETDATE()) 

B. SELECT COUNT(*) 

FROM Purchases 

WHERE PurchaseTime = GETDATE() 

C. SELECT COUNT(*) 

FROM Purchases 

WHERE CONVERT(VARCHAR, PurchaseTime, 112) = CONVERT(VARCHAR, 

GETDATE(), 112) 

D. SELECT COUNT(*) 

FROM Purchases 

WHERE PurchaseTime >= CONVERT(DATE, GETDATE()) 

AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE())) 

Answer: D 

Explanation: 

Two answers will return the correct results (the "WHERE CONVERT..." and "WHERE ... AND ... " answers). The correct answer for Microsoft would be the answer that is most "efficient". Anybody have a clue as to which is most efficient? In the execution plan, the one that I've selected as the correct answer is the query with the shortest duration. Also, the query answer with "WHERE CONVERT..." threw warnings in the execution plan...something about affecting CardinalityEstimate and SeekPlan. 

I also found this article, which leads me to believe that I have the correct Answer: http://technet.microsoft.com/en-us/library/ms181034.aspx 

Q34. CORRECT TEXT 

You have an XML schema collection named Sales.InvoiceSchema. 

You need to declare a variable of the XML type named invoice. The solution must ensure 

that the invoice is validated by using Sales.InvoiceSchema. 

Provide the correct code in the answer area. 

Answer: 

DECLARE @invoice XML(Sales.InvoiceSchema) 

Q35. CORRECT TEXT 

You have a database 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. 

Return the most recent orders first. 

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. 

Return the order date in a column named MostRecentOrderDate that appears as 

the last column in the report. The solution must support the ANSI SQL-99 standard. 

Which code segment should you use? 

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

Answer: 

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

An application that uses a database begins to run slowly. 

Your investigation shows the root cause is a query against a read-only table that has a 

clustered index. 

The query returns the following six columns: 

... 

One column in its WHERE clause contained in a non-clustered index · Four additional columns One COUNT (*) column based on a grouping of the four additional columns 

You need to optimize the statement. 

What should you do? 

A. Add a HASH hint to 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 columnstore index to cover the query. 

G. Enable the optimize for ad hoc workloads option. 

H. Cover the unique clustered index with a columnstore index. 

I. 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: F 

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

Q38. DRAG DROP 

You use Microsoft SQL Server 2012 to develop a database that has two tables named Div1Cust and Div2Cust. 

Each table has columns named DivisionID and CustomerId . None of the rows in Div1Cust exist in Div2Cust. 

You need to write a query that meets the following requirements: 

The rows in Div1Cust must be combined with the rows in Div2Cust. The result set must have columns named Division and Customer. Duplicates must be retained. 

Which three Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.) 

Answer: 

Q39. CORRECT TEXT 

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

You have an application named Appl. You have a parameter named @Count that uses the 

int data type. App1 is configured to pass @Count to a stored procedure. 

You need to create a stored procedure named usp_Customers for App1 that returns only 

the number of rows specified by the @Count parameter. 

The solution must NOT use BEGIN and END statements. 

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

complete code. 

Answer: 

Q40. You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects. 

You need to retrieve the students who scored the highest marks for each subject along with the marks. 

Which Transact-SQL query should you use? 

A. SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode 

B. SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank FROM StudentMarks 

C. SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode 

D. SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode 

E. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

F. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

G. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

H. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANXO OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

Answer: F