You administer a Microsoft SQL Server 2008 database that contains a table named dbo.SalesOrders.
The table has the following definition:
CREATE TABLE [dbo].[SalesOrder](
[SalesOrderNumber] NVARCHAR(20) NOT NULL,
[FullDateAlternateKey] DATETIME NOT NULL,
[CustomerName] NVARCHAR(100) NOT NULL,
[AddressLine] NVARCHAR(120) NOT NULL,
[City] NVARCHAR(30) NOT NULL,
[StateProvinceName] NVARCHAR(50) NOT NULL,
[CountryName] NVARCHAR(50) NOT NULL,
[SalesAmount] MONEY NOT NULL,
CONSTRAINT [PK_SalesOrderNumber] PRIMARY KEY CLUSTERED ([SalesOrderNumber] ASC) ON [PRIMARY]) ON [PRIMARY]
GO
The SalesOrder table contains one million rows. You want to create a report that meets the following requirements:
•Only the states of the Unites States are ranked against each other based on the total number of orders received from each state.
•When two states have the same rank, the rank of the subsequent state is one plus the number of ranks that come before that row, as shown in the exhibit. (Click the Exhibit button.)
You need to execute a Transact-SQL query to generate the report.
Which Transact-SQL query should you use?
Exhibit:
A. SELECT RANK() OVER (ORDER BY StateProvinceName DESC) AS Ranking, StateProvinceName, TotalOrders
FROM
(SELECT StateProvinceName, count(*) AS TotalOrders
FROM dbo.SalesOrder
WHERE CountryName=’United States’
GROUP BY StateProvinceName) AS
B. SELECT DENSE_RANK() OVER (ORDER BY StateProvinceName DESC) AS Ranking, StateProvinceName, TotalOrders
FROM
(SELECT StateProvinceName, count(*) AS TotalOrders
FROM dbo.SalesOrder
WHERE CountryName=’United States’
GROUP BY StateProvinceName) AS
C. SELECT DENSE_RANK() OVER (ORDER BY TotalOrders DESC) AS Ranking, StateProvinceName, TotalOrders
FROM
(SELECT StateProvinceName, count(*) AS TotalOrders
FROM dbo.SalesOrder
WHERE CountryName=’United States’
GROUP BY StateProvinceName) AS
D. SELECT RANK() OVER (ORDER BY TotalOrders DESC) AS Ranking, StateProvinceName, TotalOrders
FROM
(SELECT StateProvinceName, count(*) AS TotalOrders
FROM dbo.SalesOrder
WHERE CountryName=’United States’
GROUP BY StateProvinceName) AS
E. SELECT RANK() OVER (PARTITION BY CountryName ORDER BY TotalOrders DESC) AS Ranking, StateProvinceName, TotalOrders
FROM
(SELECT CountryName, StateProvinceName, count(*) AS TotalOrders
FROM dbo.SalesOrder
GROUP BY CountryName,StateProvinceName) AS
F. SELECT DENSE_RANK() OVER (PARTITION BY CountryName ORDER BY TotalOrders DESC) AS Ranking, StateProvinceName, TotalOrders
FROM
(SELECT CountryName, StateProvinceName, count(*) AS TotalOrders
FROM dbo.SalesOrder
GROUP BY CountryName,StateProvinceName) AS
G. SELECT RANK() OVER (PARTITION BY CountryName ORDER BY TotalOrders DESC) AS Ranking, StateProvinceName, TotalOrders
FROM
(SELECT CountryName, StateProvinceName, count(*) AS TotalOrders
FROM dbo.SalesOrder
WHERE CountryName=’United States’
GROUP BY CountryName,StateProvinceName) AS
H. SELECT DENSE_RANK() OVER (PARTITION BY CountryName ORDER BY TotalOrders DESC) AS
Ranking,StateProvinceName,TotalOrdersFROM(SELECTCountryName,StateProvinceName,count(*)
AS TotalOrdersFROMdbo.SalesOrderWHERECountryName=’United States’GROUP
BYCountryName,StateProvinceName) AS
