Câu 59: 70-461: MCSA Querying Microsoft SQL Server 2012/2014

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

1. A. 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.PostalCode3 FROM Sales.SalesPerson AS aetcOn 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 ([ ][ ][ ])Example: Using the OVER clause with the ROW_NUMBER functionThe 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 SalesQuotaFROM dbo.DimEmployee AS e -INNER JOIN dbo.FactSalesQuota AS sqON e.EmployeeKey = sq.EmployeeKeyWHERE e.SalesPersonFlag = 1 -GROUP BY LastName, FirstName;Here is a partial result set.RowNumber FirstName LastName SalesQuota--------- --------- ------------------ -------------1 Jillian Carson 12,198,000.002 Linda Mitchell 11,786,000.003 Michael Blythe 11,162,000.004 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 — đáp án hiện tại