Which clause 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 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)



Leave a Reply 0

Your email address will not be published. Required fields are marked *