You are a database developer on an instance of SQL Server 2008. Your Prod database contains a Salesperson table that contains information about your company’s salespeople. The Salesperson table is defined as follows:
Correct Answer: D
Explanation/Reference:
The DENSE_RANK function ranks rows based on the PARTITION BY and ORDER BY specified in the OVER clause. The basic syntax of the DENSE_RANK function is as follows:
DENSE_RANK ( ) OVER ([partition_by] order_by)
First, if a partitioned_by value is specified, the rows are partitioned. Then, within each partition, the rows are sorted and ranked based on the specified ORDER BY clause. If two rows within the same partition have the same ORDER BY value, they are assigned the same ranking, and the following ranked row is assigned the next sequential ranking value. For example, the following SELECT statement could be used in this scenario:
SELECT *, DENSE_RANK()OVER(PARTITION BY Region ORDER BY Quota DESC) AS Ranking FROM Salesperson;
This statement would partition the salespeople by region. Then, within each region, the rows would be sorted in descending order based on each salesperson’s Quota value. Finally, the Ranking value would be calculated for each salesperson. Salespeople with identical Quota values within a region would have the same ranking, and no ranking values would be skipped. With the data given for the Salesperson table, this statement would return the following result:
You should not include a RANK function in the SELECT list and a correlated subquery in the FROM clause because a subquery is not needed in this scenario. You can use the RANK function to rank a result set. A correlated subquery is a subquery that references one or more columns in the outer query. Correlated subqueries can adversely affect performance, and should be avoided when possible because the inner query will execute once for each row of the outer query. While correlated subqueries are required in some situations, in this scenario you could accomplish the desired result using the aggregate DENSE_RANK function with an OVER clause. While the RANK function is similar to the DENSE_RANK function, it skips a ranking value for each row that has an identical ORDER BY value.
You should not include an OUTER APPLY or a CROSS APPLY clause. The APPLY clause is used in the FROM clause of a query to join a table to a table-valued function. The table-valued function is called for each row returned by the outer query. The APPLY clause allows you to easily call a table-valued function for each row returned by a query. The OUTER APPLY clause returns all rows from the outer query, even if the row does not return a value for the table-valued function. The CROSS APPLY clause returns only the outer query rows for which the table-valued function returned a value.
