Exam 70-461 topic 1 question 173 discussion

Actual exam question from Microsoft's 70-461

Question #: 173
Topic #: 1

SIMULATION -
The following is a series of questions in which you are required to input one or more lines of code.

To input your response -
Type your response into the text entry field in the Answer Area. You may input one or more lines of code. More than one solution may be correct. You will receive credit if your solution matches any of the correct solutions.

To validate code syntax -
After entering your code, click the Check Syntax button. This validates code syntax (such as SQL commands) and values (such as table names and variable names) used in your solution. If there are any errors, they will appear in the window next to the Check Syntax button. You may change your code and re-validate the syntax as many times as you want.
Note that Check Syntax does NOT validate whether you have answered the question correctly. It simply validates the accuracy of your syntax.
To view available command keywords
Click the Keywords button to view a list of command keywords. This is a general list provided for reference and is not limited to commands used in the question.
You need to create a query that meets the following requirements:
✑ The query must return a list of salespeople ranked by amount of sales and organized by postal code.
✑ The salesperson who has the highest amount of sales must be ranked first.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within code that has been provided as well as below it.

Show Suggested Answer

Suggested Answer: Please review the explanation part for this answer.
1 SELECT RowNumber() OVER(PARTITION BY PostalCode ORDER BY SalesYTd DESC) AS "Ranking",
2 p.LastName, s.SalesYTD, a.PostalCode
3 FROM Sales.SalesPerson AS a
etc

On line 1 add: RowNumber -

One line 1 add: PARTITION BY -
ROW_NUMBER() numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
SYNTAX for OVER:
OVER (
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)
Example: Using the OVER clause with the ROW_NUMBER function
The following example returns the ROW_NUMBER for sales representatives based on their assigned sales quota.
SELECT ROW_NUMBER() OVER(ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber,
FirstName, LastName,
CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota

FROM dbo.DimEmployee AS e -
INNER JOIN dbo.FactSalesQuota AS sq
ON e.EmployeeKey = sq.EmployeeKey

WHERE e.SalesPersonFlag = 1 -
GROUP BY LastName, FirstName;
Here is a partial result set.
RowNumber FirstName LastName SalesQuota
--------- --------- ------------------ -------------
1 Jillian Carson 12,198,000.00
2 Linda Mitchell 11,786,000.00
3 Michael Blythe 11,162,000.00
4 Jae Pak 10,514,000.00
References:
https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql

by levoshchypok at Sept. 23, 2020, 4:37 p.m.

Comments

Submit Cancel

levoshchypok

4 years, 10 months ago

Why not Rank() function? What if two persons had the same result?

upvoted 2 times

...