Tree structures in SQL
Question
Suppose you have a table called 'tree' that contains a column of nodes as well as corresponding parent nodes:
Table: tree
| node | parent |
|---|---|
| 1 | 2 |
| 2 | 5 |
| 3 | 5 |
| 4 | 3 |
| 5 | null |
Write a SQL query that will label each node as a 'root' (no parent node), 'leaf' (no child nodes), or 'inner' (contains both parent and child nodes). Given the table above, your query should return the following:
| node | label |
|---|---|
| 2 | Inner |
| 5 | Root |
| 3 | Inner |
| 1 | Leaf |
| 4 | Leaf |
You can view/query this table in an interactive SQL fiddle here.