CORRECT TEXT
You have a database named Sales that contains the tables as shown in the exhibit. (Click the Exhibit
button.)
You need to create a query that returns a list of products from Sales.ProductCatalog. The solution
must meet the following requirements:
UnitPrice must be returned in descending order.
The query must use two-part names to reference the table.
The query must use the RANK function to calculate the results.
The query must return the ranking of rows in a column named PriceRank.
The list must display the columns in the order that they are defined in the table. PriceRank must
appear last.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete
code.
Answer: See the explanation
Explanation:
SELECT ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog.ProductID,
ProductCatalog.ProdName, ProductCatalog.UnitPrice,
RANK() OVER (ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank
FROM Sales.ProductCatalog
ORDER BY ProductCatalog.UnitPrice DESC
The last ORDER BY CLAUSE is unnecessary. ORDER BY in OVER clause do the thing.
SELECT ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog.ProductID, ProductCatalog.ProdName, ProductCatalog.UnitPrice,
RANK() OVER (ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank
FROM Sales.ProductCatalog
ORDER BY ProductCatalog.UnitPrice DESC
Can anyone explain why the SELECT field have the table added? The instructions only state that you must use 2 part names to reference the TABLE, not the FIELDS so I am confused why “SELECT ProductCatalog.CatID” is used (etc) instead of “SELECT CatID”.
The question also states that “Part of the CORRECT solution” is listed, which DOES NOT have table prefixes on the COLUMNS.
Therefore should the answer simply be…
SELECT CatID, CatName, ProductID, ProdName, UnitPrice,
RANK() OVER (ORDER BY UnitPrice DESC) AS PriceRank
FROM Sales.ProductCatalog
ORDER BY UnitPrice DESC