If I understood you correctly you have a homework assignment related to the
recursive queries[
^]. If you follow the link you will find the example that uses exactly the same model (employee->manager).
To adapt the query to your example the result will look something like this:
;WITH DirectReports (EmployeeName, EmployeeManager, Level)
AS
(
-- Anchor member definition
SELECT e.EmployeeName, 0 AS Level
FROM HR AS e
WHERE e.ReportingManagerName IS NULL
UNION ALL
-- Recursive member definition
SELECT e.EmployeeName,
Level + 1
FROM HR AS e
INNER JOIN DirectReports AS d
ON e.ReportingManagerName = e.EmployeeName
)
-- Statement that executes the CTE
SELECT EmployeeName, EmployeeManager, Level
FROM DirectReports
ORDER BY EmployeeName;