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?

You need to determine the result of executing this code segment.
DECLARE @RangeStart INT = 0;
DECLARE @RangeEnd INT = 10000;
DECLARE @RangeStep INT = 1;

WITH NumberRange(ItemValue)
AS (
SELECT ItemValue
FROM (SELECT @RangeStart AS ItemValue) AS t
UNION ALL
SELECT ItemValue + @RangeStep
FROM NumberRange
WHERE ItemValue < @RangeEnd)

SELECT ItemValue
FROM NumberRange
OPTION (MAXRECURSION 100)

Which result will be returned?

You have a table named Employee.
You document your company’s organizational hierarchy by inserting the EmployeeID of each employee’s manager in the ReportsTo column.
You need to write a recursive query that produces a list of employees and their manager.
The query must also include the employee’s level in the hierarchy.
You write the following code segment. (Line numbers are included for reference only.)

01 WITH EmployeeList (EmployeeID, FullName, ManagerName, Level)
02 AS (
03 ………
04 )
05 SELECT EmployeeID, FullName, ManagerName, Level
06 FROM EmployeeList;

Which code segment should you insert at line 3?

You have the following rows in the
Customer Table:
CustomerId Status
1 Active
2 Active
3 Inactive
4 NULL
5 Dormant
6 Dormant

You write the following query to return all customers that do not have NULL or ‘Dormant’ for their status:

SELECT * FROM Customer
WHERE Status NOT IN (NULL, ‘Dormant’)

You need to identify the results of the query.
Which result should you expect?

Your company stores vendor and price information in a database. All items in the database have a list price.
You need to increase the list price for all products of only the vendor named Fabrikam by 20.00.
Which query should you use?

Your database contains sales information for millions of orders.
You need to identify the orders with the highest average unit price and an order total greater than 10,000.
The list should contain no more than 20 orders.
Which query should you use?