Self join for hierarchical data in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find all employees and their managers

Given a table Employees with columns EmployeeID, Name, and ManagerID (which references EmployeeID), what is the output of the following query?

SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID
ORDER BY e.EmployeeID;

SQL

SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID
ORDER BY e.EmployeeID;

A [{"Employee": "Alice", "Manager": null}, {"Employee": "Bob", "Manager": "Alice"}, {"Employee": "Charlie", "Manager": "Bob"}]

B [{"Employee": "Alice", "Manager": "Alice"}, {"Employee": "Bob", "Manager": "Bob"}, {"Employee": "Charlie", "Manager": "Charlie"}]

C [{"Employee": "Alice", "Manager": "Bob"}, {"Employee": "Bob", "Manager": "Charlie"}, {"Employee": "Charlie", "Manager": null}]

D [{"Employee": "Alice", "Manager": null}, {"Employee": "Bob", "Manager": null}, {"Employee": "Charlie", "Manager": null}]

💡 Hint

Think about how the ManagerID links to EmployeeID in the same table.

The query joins the Employees table to itself to find each employee's manager by matching ManagerID to EmployeeID. Employees without a manager have ManagerID as null, so their manager name is null.

📝 Syntax

intermediate

Identify the syntax error in self join query

Which option contains a syntax error in this self join query to find employees and their managers?

SELECT e.Name, m.Name
FROM Employees e
JOIN Employees m ON e.ManagerID = m.EmployeeID;

A SELECT e.Name, m.Name FROM Employees e JOIN Employees m ON e.ManagerID = m.EmployeeID;

B SELECT e.Name, m.Name FROM Employees e JOIN Employees m ON e.ManagerID = m.EmployeeID WHERE;

C SELECT e.Name, m.Name FROM Employees e JOIN Employees m ON e.ManagerID = m.EmployeeID ORDER BY e.Name;

D SELECT e.Name, m.Name FROM Employees e JOIN Employees m ON e.ManagerID = m.EmployeeID LIMIT 5;

💡 Hint

Look for incomplete clauses or misplaced keywords.

Option B ends with WHERE; which is incomplete and causes a syntax error. The WHERE clause must be followed by a condition.

🧠 Conceptual

advanced

Understanding recursive hierarchy with self join

In a self join used to represent hierarchical data, what is the main purpose of joining the table to itself?

A To combine rows from two different tables into one result set.

B To create a backup of the table before updating data.

C To compare rows within the same table to find relationships like manager and employee.

D To filter rows based on a condition in a single table.

💡 Hint

Think about how hierarchical relationships are stored in one table.

Self join allows comparing rows within the same table to find hierarchical relationships, such as which employee reports to which manager.

❓ query_result

advanced

Output of recursive self join for two-level hierarchy

Given the table Categories with columns CategoryID, Name, and ParentID, what is the output of this query?

SELECT c.Name AS Child, p.Name AS Parent
FROM Categories c
LEFT JOIN Categories p ON c.ParentID = p.CategoryID
WHERE p.Name IS NOT NULL
ORDER BY c.Name;

SQL

SELECT c.Name AS Child, p.Name AS Parent
FROM Categories c
LEFT JOIN Categories p ON c.ParentID = p.CategoryID
WHERE p.Name IS NOT NULL
ORDER BY c.Name;

A [{"Child": "Computers", "Parent": null}, {"Child": "Electronics", "Parent": null}]

B [{"Child": "Computers", "Parent": "Laptops"}, {"Child": "Electronics", "Parent": "Smartphones"}]

C [{"Child": "Laptops", "Parent": null}, {"Child": "Smartphones", "Parent": null}]

D [{"Child": "Laptops", "Parent": "Computers"}, {"Child": "Smartphones", "Parent": "Electronics"}]

💡 Hint

Remember that ParentID links to CategoryID of the parent category.

The query finds child categories with their parent names, excluding those without parents by filtering p.Name IS NOT NULL.

🔧 Debug

expert

Why does this self join query return no rows?

Consider this query on Employees table:

SELECT e.Name, m.Name
FROM Employees e
INNER JOIN Employees m ON e.ManagerID = m.EmployeeID
WHERE e.ManagerID IS NULL;

Why does it return no rows?

A Because <code>e.ManagerID IS NULL</code> filters out all rows that have a manager, but the join requires matching managers, so no rows match.

B Because the join condition is incorrect; it should be <code>e.EmployeeID = m.ManagerID</code>.

C Because the table has no employees with <code>ManagerID</code> set to <code>null</code>.

D Because <code>INNER JOIN</code> always returns all rows regardless of the <code>WHERE</code> clause.

💡 Hint

Think about how INNER JOIN and WHERE conditions interact.

The WHERE e.ManagerID IS NULL filters employees without managers, but the join requires matching managers, so no rows satisfy both conditions.