CORRECT TEXT
You have a database 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.
Which code segment should you use?
To answer, type the correct code in the answer area.
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
select *, rank() over (order by UnitPrice DESC) as PriceBank from ProductCatalog
0
0
SELECT ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog.ProductID,
ProductCatalog.ProdName, ProductCatalog.UnitPrice,
RANK() OVER (PARTITION BY ProductCatalog.UnitPrice
ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank
FROM Sales.ProductCatalog
ORDER BY ProductCatalog.UnitPrice DESC
0
0
or 2 variant:
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
0
0
the variant OVER (PARTITION BY ProductCatalog.UnitPrice
ORDER BY ProductCatalog.UnitPrice DESC) is not the same result as RANK() OVER (ORDER BY ProductCatalog.UnitPrice).
It makes more sense to use order by without partitioning.
0
0
SELECT ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog.ProductID,
ProductCatalog.ProdName, ProductCatalog.UnitPrice,
RANK() OVER (PARTITION BY ProductCatalog.UnitPrice
ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank
FROM Sales.ProductCatalog
ORDER BY ProductCatalog.UnitPrice DESC
0
0
If people just spend a little time and create the tables and some data and test the results you will find that Partition by is not required and that you can either use (in the last line) “ORDER BY ProductCatalog.UnitPrice DESC” or “ORDER BY PriceRank” which both return identical results.
PARTITION BY is NOT required as this negates the ranking by giving each Unit Price the same rank “1” thus making the results incorrect for the question.
Here is some test code where you can run the variations and see the results for yourselves…
CREATE TABLE ProductCatalog
(
CatID INT,
CatName varchar(100),
ProductID INT,
ProdName varchar(100),
UnitPrice decimal(7,2)
)
INSERT INTO ProductCatalog (CatID,CatName,ProductID,ProdName,UnitPrice)
VALUES(1,’CatOne’,10,’Prod10′,1.11);
INSERT INTO ProductCatalog (CatID,CatName,ProductID,ProdName,UnitPrice)
VALUES(2,’CatTwo’,20,’Prod20′,2.22);
INSERT INTO ProductCatalog (CatID,CatName,ProductID,ProdName,UnitPrice)
VALUES(3,’CatThree’,30,’Prod30′,3.33);
INSERT INTO ProductCatalog (CatID,CatName,ProductID,ProdName,UnitPrice)
VALUES(4,’CatFour’,40,’Prod40′,4.44);
INSERT INTO ProductCatalog (CatID,CatName,ProductID,ProdName,UnitPrice)
VALUES(5,’CatFive’,50,’Prod50′,5.55);
INSERT INTO ProductCatalog (CatID,CatName,ProductID,ProdName,UnitPrice)
VALUES(6,’CatSix’,60,’Prod60′,6.66);
INSERT INTO ProductCatalog (CatID,CatName,ProductID,ProdName,UnitPrice)
VALUES(7,’CatSeven’,70,’Prod70′,7.77);
INSERT INTO ProductCatalog (CatID,CatName,ProductID,ProdName,UnitPrice)
VALUES(8,’CatEight’,80,’Prod80′,8.88);
INSERT INTO ProductCatalog (CatID,CatName,ProductID,ProdName,UnitPrice)
VALUES(9,’CatNine’,90,’Prod90′,9.99);
INSERT INTO ProductCatalog (CatID,CatName,ProductID,ProdName,UnitPrice)
VALUES(31,’CatThreeA’,31,’Prod31′,3.33);
SELECT
ProductCatalog.CatID,
ProductCatalog.CatName,
ProductCatalog.ProdName,
ProductCatalog.ProductID,
ProductCatalog.UnitPrice,
RANK() OVER (ORDER BY ProductCatalog.UnitPrice) AS PriceRank
FROM ProductCatalog
ORDER BY ProductCatalog.UnitPrice DESC
SELECT
ProductCatalog.CatID,
ProductCatalog.CatName,
ProductCatalog.ProdName,
ProductCatalog.ProductID,
ProductCatalog.UnitPrice,
RANK() OVER (PARTITION BY ProductCatalog.UnitPrice ORDER BY ProductCatalog.UnitPrice) AS PriceRank
FROM ProductCatalog
ORDER BY ProductCatalog.UnitPrice DESC
SELECT
ProductCatalog.CatID,
ProductCatalog.CatName,
ProductCatalog.ProdName,
ProductCatalog.ProductID,
ProductCatalog.UnitPrice,
RANK() OVER (ORDER BY ProductCatalog.UnitPrice) AS PriceRank
FROM ProductCatalog
ORDER BY PriceRank DESC
SELECT
ProductCatalog.CatID,
ProductCatalog.CatName,
ProductCatalog.ProdName,
ProductCatalog.ProductID,
ProductCatalog.UnitPrice,
RANK() OVER (PARTITION BY ProductCatalog.UnitPrice ORDER BY ProductCatalog.UnitPrice) AS PriceRank
FROM ProductCatalog
ORDER BY PriceRank DESC
–When finished cleanup the table created
–DROP TABLE ProductCatalog;
1
0
P.S.
While i was testing i removed the “DESC” that should be put after the first “ORDER BY ProductCatalog.UnitPrice” in the RANK statement, so you will need to add this back into all 4 SELECT statements.
0
0
Just to clarify… here is the correct code…
CREATE TABLE ProductCatalog
(
CatID INT,
CatName varchar(100),
ProductID INT,
ProdName varchar(100),
UnitPrice decimal(7,2)
)
INSERT INTO ProductCatalog (CatID,CatName,ProductID,ProdName,UnitPrice)
VALUES(1,’CatOne’,10,’Prod10′,1.11);
INSERT INTO ProductCatalog (CatID,CatName,ProductID,ProdName,UnitPrice)
VALUES(2,’CatTwo’,20,’Prod20′,2.22);
INSERT INTO ProductCatalog (CatID,CatName,ProductID,ProdName,UnitPrice)
VALUES(3,’CatThree’,30,’Prod30′,3.33);
INSERT INTO ProductCatalog (CatID,CatName,ProductID,ProdName,UnitPrice)
VALUES(4,’CatFour’,40,’Prod40′,4.44);
INSERT INTO ProductCatalog (CatID,CatName,ProductID,ProdName,UnitPrice)
VALUES(5,’CatFive’,50,’Prod50′,5.55);
INSERT INTO ProductCatalog (CatID,CatName,ProductID,ProdName,UnitPrice)
VALUES(6,’CatSix’,60,’Prod60′,6.66);
INSERT INTO ProductCatalog (CatID,CatName,ProductID,ProdName,UnitPrice)
VALUES(7,’CatSeven’,70,’Prod70′,7.77);
INSERT INTO ProductCatalog (CatID,CatName,ProductID,ProdName,UnitPrice)
VALUES(8,’CatEight’,80,’Prod80′,8.88);
INSERT INTO ProductCatalog (CatID,CatName,ProductID,ProdName,UnitPrice)
VALUES(9,’CatNine’,90,’Prod90′,9.99);
INSERT INTO ProductCatalog (CatID,CatName,ProductID,ProdName,UnitPrice)
VALUES(31,’CatThreeA’,31,’Prod31′,3.33);
–CORRECT
SELECT
ProductCatalog.CatID,
ProductCatalog.CatName,
ProductCatalog.ProdName,
ProductCatalog.ProductID,
ProductCatalog.UnitPrice,
RANK() OVER (ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank
FROM ProductCatalog
ORDER BY ProductCatalog.UnitPrice DESC
–INCORRECT
SELECT
ProductCatalog.CatID,
ProductCatalog.CatName,
ProductCatalog.ProdName,
ProductCatalog.ProductID,
ProductCatalog.UnitPrice,
RANK() OVER (PARTITION BY ProductCatalog.UnitPrice ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank
FROM ProductCatalog
ORDER BY ProductCatalog.UnitPrice DESC
–CORRECT
SELECT
ProductCatalog.CatID,
ProductCatalog.CatName,
ProductCatalog.ProdName,
ProductCatalog.ProductID,
ProductCatalog.UnitPrice,
RANK() OVER (ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank
FROM ProductCatalog
ORDER BY PriceRank
–INCORRECT
SELECT
ProductCatalog.CatID,
ProductCatalog.CatName,
ProductCatalog.ProdName,
ProductCatalog.ProductID,
ProductCatalog.UnitPrice,
RANK() OVER (PARTITION BY ProductCatalog.UnitPrice ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank
FROM ProductCatalog
ORDER BY PriceRank
–Cleanup table after running
–DROP TABLE ProductCatalog;
0
0