As an example I use employee table. The superssn is the supervisor's ssn number.
You must define super_empssn and then provide a valid ssn number for super_empssn variable.
-- Total count of first level subordinates of a given employee
SELECT *
FROM employee parent, employee child
WHERE parent.ssn = super_empssn
AND parent.ssn = child.superssn;
-- Second level subordinates of a given employee
SELECT *
FROM
(
SELECT child.*
FROM employee parent, employee child
WHERE parent.ssn = super_empssn
AND parent.ssn = child.superssn
ORDER BY child.ssn) children, employee grandchild
WHERE children.ssn = grandchild.superssn;
-- Third level subordinates of a given employee
SELECT *
FROM
(
SELECT grandchild.*
FROM (
SELECT child.*
FROM employee parent, employee child
WHERE parent.ssn = super_empssn
AND parent.ssn = child.superssn
ORDER BY child.ssn) children, employee grandchild
WHERE children.ssn = grandchild.superssn
ORDER BY grandchild.ssn) greatchildren, employee greatgrandchild
WHERE greatchildren.ssn = greatgrandchild.superssn;
