You have a table named ProductCounts that contains 1000 products as well as the number of units that have been sold for each product. You need to write a query that displays the top 5% of products that have been sold most frequently.
Which Transact-SQL code segments should you use?

You need to write a query that uses a ranking function that returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
Which Transact-SQL statement should you use?

You need to write a query that allows you to rank total sales for each salesperson into four groups, where the top 25 percent of results are in group 1, the next 25 percent are in group 2, the next 25 percent are in group 3, and the lowest 25 percent are in group 4.
Which Transact-SQL statement should you use?

You have two views named Sales.SalesSummaryOverall and Sales.CustomerAndSalesSummary.
They are defined as follows:

CREATE VIEW Sales.SalesSummaryOverall
AS
SELECT CustomerId, SUM(SalesTotal) AS OverallTotal
FROM Sales.SalesOrder
GROUP BY CustomerId

GO

CREATE VIEW Sales.CustomerAndSalesSummary
AS
SELECT Customer.Name, SalesSummaryOverall.OverallTotal, (SELECT AVG(OverallTotal)
FROM Sales.SalesSummaryOverall
WHERE SalesSummaryOverall.CustomerId = Customer.CustomerId) AS avgOverallTotal,
(SELECT MAX(OverallTotal) FROM Sales.SalesSummaryOverall
WHERE SalesSummaryOverall.CustomerId =Customer.CustomerId) AS maxOverallTotal,
FROM Sales.Customer
LEFT OUTER JOIN Sales. Sales.SalesSummaryOverall
ON SalesSummaryByYear.CustomerId = Customer.CustomerId
GO

You have been tasked to modify the Sales.CustomerAndSalesSummary view to remove references to other views.
You need to identify a feature to use in the modified version of the Sales.CustomerAndSalesSummary object to achieve the task.
Which feature should you use?

You create and populate a table named SiteNavigation by using the following statements:
CREATE TABLE SiteNavigation (
SiteNavigationId INT PRIMARY KEY,
Linktext VARCHAR(10),
LinkUrl VARCHAR(40),
ParentSiteNavigationId INT NULL REFERENCES SiteNavigation(SiteNavigationId)
)
INSERT INTO SiteNavigation VALUES (1,’First’,’http://first’,NULL)
,(2,’Second’,’http://second’,1)
,(3,’Third’,’http://third’,1)
,(4,’Fourth’,’http://fourth’,2)
,(5,’Fifth’,’http://fifth’,2)
,(6,’Sixth’,’http://sixth’,2)
,(7,’Seventh’,’http://seventh’,6)
,(8,’Eighth’,’http://eighth’,7)

You are tasked to write a query to list all site references that are more than two levels from the root node.
The query should produce the following results:
LinkText LinkUrl DistanceFromRoot
Fourth http://fourth 2
Fifth http://fifth 2
Sixth http://sixth 2
Seventh http://seventh 3
Eighth http://eighth 4

You have written the following query:

WITH DisplayHierarchy AS (SELECT LinkText, LinkUrl, SiteNavigationId, ParentSiteNavigationId, 0 AS DistanceFromRoot
FROM SiteNavigation
WHERE ParentSiteNavigationId IS NULL
UNION ALL
SELECT SiteNavigation.LinkText, SiteNavigation.LinkUrl, SiteNavigation.SiteNavigationId, SiteNavigation.ParentSiteNavigationId,
dh.DistanceFromRoot + 1 AS DistanceFromRoot
FROM SiteNavigation
INNER JOIN DisplayHierarchy dh
ON SiteNavigation.ParentSiteNavigationId = dh.SiteNavigationId)

SELECT LinkText, LinkUrl, DistanceFromRoot FROM DisplayHierarchy

You need to append a WHERE clause to the query. Which clause should you use?

You need to implement a common table expression (CTE). Which code segment should you use?