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
To correctly use the provided query part. It should be like this:
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 PriceRank
What’s with this?
SELECT ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog.ProductID,
ProductCatalog.ProdName, ProductCatalog.UnitPrice,
RANK() OVER (ORDER BY ProductCatalog.UnitPrice) AS PriceRank
FROM Sales.ProductCatalog
ORDER BY ProductCatalog.UnitPrice DESC
Because only the UnitPrice should order by descending, but not the ranking.
Exactly
ORDER BY at the end of code is not necessary, ORDER BY in RANK sorts by unitprice descending, rank gives output ascending.
SELECT ProductCatalog.CatID, ProductCatalog.CatName,
ProductCatalog.ProductID,ProductCatalog.ProdName,ProductCatalog.UnitPrice,
RANK () OVER ( ORDER BY ProductCatalog.UnitPrice DESC) as PriceRank
FROM Sales.ProductCatalog
Question says: “Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.”
So, ORDER BY PriceRank is appropriate. But then, ORDER BY ProductCatalog.UnitPrice DESC is also valid.
That would be the end of this article. Right here you will find some web sites that we feel youll appreciate, just click the links.
Here is an excellent Blog You might Uncover Fascinating that we Encourage You
we came across a cool web-site which you could delight in. Take a search in the event you want
we came across a cool web-site that you may enjoy. Take a appear when you want
Here is a good Blog You might Come across Exciting that we Encourage You
Here are a number of the web pages we suggest for our visitors
That is the very best search system in the planet
http://google.com
ya’ll forgot about the two part name is required only for tables not for columns, hence right answer should be
USE TSQL2012
GO
SELECT CatID, CatName,ProductID, ProductName, UnitPrice, RANK() OVER (Order By UnitPrice Desc) AS PriceRank
FROM Sales.ProductCatalog
ORDER BY UnitPrice
my bad, u can ignore this part
USE TSQL2012
GO