Download Querying Microsoft SQL Server 2012-2014.70-461.SelfTestEngine.2018-09-19.130q.tqb

Vendor:	Microsoft
Exam Code:	70-461
Exam Name:	Querying Microsoft SQL Server 2012/2014
Date:	Sep 19, 2018
File Size:	13 MB

Download Exam

How to open VCEX files?

Files with VCEX extension can be opened by ProfExam Simulator.

Download
ProfExam Simulator

Purchase
Coupon: EXAM_HUB

Discount: 20%

Demo Questions

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?

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

Correct answer: C

Explanation:

LAG accesses data from a previousrow in the same result set without the use of a self-join in SQL Server 2016. LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row. Use ORDER BY Year, not ORDER BY Territory. Example: The following example uses the LAG function to return the difference in sales quotas for a specific employee over previous years. Notice that because there is no lag valueavailable for the first row, the default of zero (0) is returned.USE AdventureWorks2012; GO SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota FROM Sales.SalesPersonQuotaHistory WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006'); Incorrect Answers:A, D: LEAD accesses data from a subsequent row in the same result set without the use of a self-join in SQL Server 2016. LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.B: Use ORDER BY Year, not ORDER BY Territory.References: https://msdn.microsoft.com/en-us/library/hh231256.aspx

Use ORDER BY Year, not ORDER BY Territory.

Example: The following example uses the LAG function to return the difference in sales quotas for a specific employee over previous years. Notice that because there is no lag valueavailable for the first row, the default of zero (0) is returned.

USE AdventureWorks2012;

SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,

LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota

FROM Sales.SalesPersonQuotaHistory

WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');

Incorrect Answers:

A, D: LEAD accesses data from a subsequent row in the same result set without the use of a self-join in SQL Server 2016. LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.

B: Use ORDER BY Year, not ORDER BY Territory.

References: https://msdn.microsoft.com/en-us/library/hh231256.aspx

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

You need to rotate the unique values of the ProductName field of a table-valued expression into multiple columns in the output.

Which Transact-SQL operator should you use?

CROSS JOIN
CROSS APPLY
PIVOT
UNPIVOT

Correct answer: C

Explanation:

http://technet.microsoft.com/en-us/library/ms177634.aspx

You administer a Microsoft SQL Server database that supports a shopping application.

You need to retrieve a list of customers who live in territories that do not have a sales person.

Which Transact- SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)

SELECT CustomerID FROM Customer
WHERE TerritoryID <> SOME(SELECT TerritoryID FROM Salesperson)
SELECT CustomerID FROM Customer
WHERE TerritoryID <> ALL(SELECT TerritoryID FROM Salesperson)
SELECT CustomerID FROM Customer
WHERE TerritoryID <> ANY(SELECT TerritoryID FROM Salesperson)
SELECT CustomerID FROMCustomer
WHERE TerritoryID NOT IN(SELECT TerritoryID FROM Salesperson)