How to pass Microsoft 70-461 Real Exam in 24 Hours [exam topics 31-45]

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 70-461 Exam Dumps (PDF & VCE):
Available on: https://www.certleader.com/70-461-dumps.html


70-461 Product Description:
Exam Number/Code: 70-461 vce
Exam name: Querying Microsoft SQL Server 2012
n questions with full explanations
Certification: Microsoft Certification
Last updated on Global synchronizing

Instant Access to Free VCE Files: Microsoft 70-461 Querying Microsoft SQL Server 2012

70-461 examcollection

Simulation of 70-461 free exam questions materials and keys for Microsoft certification for client, Real Success Guaranteed with Updated 70-461 pdf dumps vce Materials. 100% PASS Querying Microsoft SQL Server 2012 exam Today!

2016 Apr 70-461 Study Guide Questions:

Q31. CORRECT TEXT 

You have a database that contains the tables as shown below: 


You have a stored procedure named Procedure1. Procedure1 retrieves all order ids after a specific date. The rows for Procedure1 are not sorted. Procedure1 has a single parameter named Parameter1. Parameter1 uses the varchar type and is configured to pass the specific date to Procedure1. A database administrator discovers that OrderDate is not being compared correctly to Parameter1 after the data type of the column is changed to datetime. You need to update the SELECT statement to meet the following requirements: 

The code must NOT use aliases. 

The code must NOT use object delimiters. 

The objects called in Procedure1 must be able to be resolved by all users. 

OrderDate must be compared to Parameter1 after the data type of Parameter1 is 

changed to datetime. 

Which SELECT statement should you use? 

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

Answer: 


Q32. You use Microsoft SQL Server 2012 database to develop a shopping cart application. 

You need to invoke a table-valued function for each row returned by a query. 

Which Transact-SQL operator should you use? 

A. CROSS JOIN 

B. UNPIVOT 

C. PIVOT 

D. CROSS APPLY 

Answer: D 


Q33. You develop a database for a travel application. You need to design tables and other database objects. 

You create the Airline_Schedules table. 

You need to store the departure and arrival dates and times of flights along with time zone 

information. 

What should you do? 

A. Use the CAST function. 

B. Use the DATE data type. 

C. Use the FORMAT function. 

D. Use an appropriate collation. 

E. Use a user-defined table type. 

F. Use the VARBINARY data type. 

G. Use the DATETIME data type. 

H. Use the DATETIME2 data type. 

I. Use the DATETIMEOFFSET data type. 

J. Use the TODATETIMEOFFSET function. 

Answer: I 


Q34. 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 ensure that the following requirements are met: 

Students must be ranked based on their average marks. 

If one or more students have the same average, the same rank must be given to 

these students. 

Consecutive ranks must be skipped when the same rank is assigned. 

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: A 


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

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


70-461  free exam questions

Replace 70-461 free question:

Q36. You administer a Microsoft SQL Server 2012 database. 

The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.) 


Unless stated above, no columns in the Employee table reference other tables. 

Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. 

You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. 

On which column in the Employee table should you create a Foreign Key constraint that references a different table in the database? 

A. DateHired 

B. Departments 

C. EmployeeID 

D. EmployeeNum 

E. FirstName 

F. JobTitle 

G. LastName 

H. MiddleName 

I. ReportsToID 

Answer: C 

Explanation: 

Use the EmployeeID, which would be used as a primary key in the Employee table, when defining a foreign key constraint from another table in the database. 


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

Which Transact-SQL query should you use? 

A. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits 

B. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits 

C. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits 

D. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits 

Answer: C 


Q38. You support a database structure shown in the exhibit. (Click the Exhibit button.) 

You need to write a query that displays the following details: 

Total sales made by sales people, year, city, and country 

Sub totals only at the city level and country level 

A grand total of the sales amount 

Which Transact-SQL query should you use? 

A. SELECT SalesPerson.Name, Country, City, 

DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total 

FROM Sale INNER JOIN SalesPerson 

ON Sale.SalesPersonID = SalesPerson.SalesPersonID 

GROUP BY GROUPING SETS((SalesPerson.Name, Country, City, DatePart(yyyy, 

SaleDate)), (Country, City), (Country), ()) 

B. SELECT SalesPerson.Name, Country, City, 

DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total 

FROM Sale INNER JOIN SalesPerson 

ON Sale.SalesPersonID = SalesPerson.SalesPersonID 

GROUP BY CUBE(SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate)) 

C. SELECT SalesPerson.Name, Country, City, 

DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total 

FROM Sale INNER JOIN SalesPerson 

ON Sale.SalesPersonID = SalesPerson.SalesPersonID 

GROUP BY CUBE(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country) 

D. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY ROLLUP(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country) 

Answer: A 


Q39. DRAG DROP 

You write the following SELECT statement to get the last order date for a particular customer. 


You need to create the user-defined function to return the last order date for the specified customer. 

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


Answer: 



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


certleader.com

Refined 70-461 lab:

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


Q42. 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 a large amount of memory is consumed by single-use dynamic queries. 

You need to reduce procedure cache usage from these statements without creating any additional indexes. 

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: G 


Q43. You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.) 



Unless stated above, no columns in the Employee table reference other tables. 

Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. 

You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. 

On which column in the Employee table should you use an identity specification to include a seed of 1,000 and an increment of 1? 

A. DateHired 

B. DepartmentID 

C. EmployeeID 

D. EmployeeNum 

E. FirstName 

F. JobTitle 

G. LastName 

H. MiddleName 

I. ReportsToID 

Answer: C 


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



Q45. You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit. (Click the Exhibit button.) 

You write the following Transact-SQL query: 

You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail. 


You need to improve the performance of the query. 


What should you do? 

A. Use a FORCESCAN hint in the query. 

B. Add a clustered index on SalesOrderId in SalesOrderHeader. 

C. Use a FORCESEEK hint in the query. 

D. Update statistics on SalesOrderId on both tables. 

Answer: D 

Explanation: 

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



see more 70-461 dumps