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?
A.
WHERE DistanceFromRoot =2
B.
WHERE DistanceFromRoot < 2
C.
WHERE DistanceFromRoot >= 2
D.
WHERE DistanceFromRoot IN (2,3)