SQL: Generating a hierarchy

SQL: Generating a hierarchy

I got the following question at a job interview and it completely stumped
me, so I'm wondering if anybody out there can help explain it to me. Say I
have the following table:
employees
--------------------------
id | name | reportsTo
--------------------------
1 | Alex | 2
2 | Bob | NULL
3 | Charlie | 5
4 | David | 2
5 | Edward | 8
6 | Frank | 2
7 | Gary | 8
8 | Harry | 2
9 | Ian | 8
The question was to write a SQL query that returned a table with a column
for each employee's name and a column showing how many people are above
that employee in the organization: i.e.,
hierarchy
--------------------------
name | hierarchyLevel
--------------------------
Alex | 1
Bob | 0
Charlie | 3
David | 1
Edward | 2
Frank | 1
Gary | 2
Harry | 1
Ian | 2
I can't even figure out where to begin writing this as a SQL query (a
cursor, maybe?). Can anyone help me out in case I get asked a similar
question to this again? Thanks.