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.

Practice

1. What is the main purpose of using a self join in SQL when working with hierarchical data?

easy

A. To join two different tables based on a common key

B. To connect rows within the same table to show parent-child relationships

C. To combine rows from multiple tables into one result

D. To delete duplicate rows from a table

Solution

1. Step 1: Understand self join concept

   A self join connects rows within the same table, unlike regular joins that connect different tables.

2. Step 2: Apply to hierarchical data

   Hierarchical data like employees and managers require linking rows to show parent-child links, which self join does.

3. Final Answer:

   To connect rows within the same table to show parent-child relationships -> Option B

4. Quick Check:

   Self join = parent-child links [OK]

Hint: Self join links rows in one table for hierarchy [OK]

Common Mistakes:

• Confusing self join with joining different tables
• Thinking self join deletes duplicates
• Assuming self join merges tables horizontally

2. Which of the following SQL queries correctly uses a self join to find each employee's manager name from an employees table with columns id, name, and manager_id?

easy

A. SELECT e.name, m.name AS manager_name FROM employees e JOIN employees m ON e.id = m.manager_id;

B. SELECT e.name, m.name AS manager_name FROM employees e JOIN managers m ON e.manager_id = m.id;

C. SELECT e.name, m.name AS manager_name FROM employees e JOIN employees m ON e.manager_id = m.id;

D. SELECT e.name, m.name AS manager_name FROM employees e LEFT JOIN employees m ON e.id = m.manager_id;

Solution

1. Step 1: Identify correct table aliases and join condition

   We must join the employees table to itself using aliases (e and m) and match e.manager_id = m.id to get the manager's name.

2. Step 2: Check each option

   SELECT e.name, m.name AS manager_name FROM employees e JOIN employees m ON e.manager_id = m.id; correctly uses self join with proper aliases and join condition. Options B uses a non-existent table 'managers'. SELECT e.name, m.name AS manager_name FROM employees e JOIN employees m ON e.id = m.manager_id; reverses the join condition. SELECT e.name, m.name AS manager_name FROM employees e LEFT JOIN employees m ON e.id = m.manager_id; uses LEFT JOIN but with wrong condition.

3. Final Answer:

   SELECT e.name, m.name AS manager_name FROM employees e JOIN employees m ON e.manager_id = m.id; -> Option C

4. Quick Check:

   Correct self join syntax = SELECT e.name, m.name AS manager_name FROM employees e JOIN employees m ON e.manager_id = m.id; [OK]

Hint: Match child.manager_id to parent.id in self join [OK]

Common Mistakes:

• Using wrong join condition reversing keys
• Joining with a non-existent table
• Confusing LEFT JOIN with INNER JOIN in this context

3. Given the categories table:

id | name       | parent_id
---+------------+----------
1  | Electronics| NULL
2  | Computers  | 1
3  | Laptops    | 2
4  | Phones     | 1
5  | Smartphones| 4

What will be the output of this query?

SELECT c.name AS category, p.name AS parent_category
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id
ORDER BY c.id;

medium

A. [{"category": "Electronics", "parent_category": null}, {"category": "Computers", "parent_category": "Electronics"}, {"category": "Laptops", "parent_category": "Computers"}, {"category": "Phones", "parent_category": "Electronics"}, {"category": "Smartphones", "parent_category": "Phones"}]

B. [{"category": "Electronics", "parent_category": "Electronics"}, {"category": "Computers", "parent_category": "Computers"}, {"category": "Laptops", "parent_category": "Laptops"}, {"category": "Phones", "parent_category": "Phones"}, {"category": "Smartphones", "parent_category": "Smartphones"}]

C. [{"category": "Electronics", "parent_category": "Computers"}, {"category": "Computers", "parent_category": "Laptops"}, {"category": "Laptops", "parent_category": "Phones"}, {"category": "Phones", "parent_category": "Smartphones"}, {"category": "Smartphones", "parent_category": null}]

D. [{"category": "Electronics", "parent_category": "Phones"}, {"category": "Computers", "parent_category": "Smartphones"}, {"category": "Laptops", "parent_category": null}, {"category": "Phones", "parent_category": "Computers"}, {"category": "Smartphones", "parent_category": "Electronics"}]

Solution

1. Step 1: Understand the LEFT JOIN on self

   The query joins each category (c) with its parent category (p) by matching c.parent_id = p.id. If no parent, parent_category is NULL.

2. Step 2: Map each category to its parent

   Electronics has NULL parent, Computers' parent is Electronics, Laptops' parent is Computers, Phones' parent is Electronics, Smartphones' parent is Phones.

3. Final Answer:

   [{"category": "Electronics", "parent_category": null}, {"category": "Computers", "parent_category": "Electronics"}, {"category": "Laptops", "parent_category": "Computers"}, {"category": "Phones", "parent_category": "Electronics"}, {"category": "Smartphones", "parent_category": "Phones"}] -> Option A

4. Quick Check:

   Parent matches child.parent_id = parent.id [OK]

Hint: Parent name is NULL if parent_id is NULL [OK]

Common Mistakes:

• Assuming parent_category equals category name
• Mixing up parent_id and id in join condition
• Ignoring NULL parent_id results

4. Consider this SQL query intended to list employees and their managers:

SELECT e.name, m.name AS manager_name
FROM employees e
JOIN employees m ON e.id = m.manager_id;

What is the error in this query?

medium

A. The join condition is reversed; it should be e.manager_id = m.id

B. The table alias 'm' is not defined

C. The query should use LEFT JOIN instead of JOIN

D. The SELECT clause should use m.manager_name instead of m.name

Solution

1. Step 1: Analyze the join condition

   The query joins on e.id = m.manager_id, which means employee id equals manager's manager_id, which is incorrect.

2. Step 2: Correct join condition for manager lookup

   To find each employee's manager, join on e.manager_id = m.id so employee's manager_id matches manager's id.

3. Final Answer:

   The join condition is reversed; it should be e.manager_id = m.id -> Option A

4. Quick Check:

   Join on employee.manager_id = manager.id [OK]

Hint: Match child.manager_id to parent.id, not reverse [OK]

Common Mistakes:

• Reversing join keys causing wrong matches
• Confusing alias usage
• Assuming INNER JOIN always needed

5. You have a parts table with columns part_id, part_name, and parent_part_id. Write a query to list each part with its top-level ancestor part name (the root parent with parent_part_id IS NULL). Which approach correctly achieves this using self joins?

hard

A. Use UNION ALL to combine parts with their children without join

B. Use a single self join on part_id = parent_part_id to get the immediate parent only

C. Use GROUP BY part_id and MAX(parent_part_id) to find the top ancestor

D. Use multiple self joins chaining parent_part_id until parent_part_id IS NULL, selecting the top ancestor name

Solution

1. Step 1: Understand hierarchical traversal

   Finding the top-level ancestor requires following parent links repeatedly until reaching a part with no parent (parent_part_id IS NULL).

2. Step 2: Use multiple self joins or recursive CTE

   This can be done by chaining self joins or using recursive queries to climb the hierarchy to the root ancestor.

3. Final Answer:

   Use multiple self joins chaining parent_part_id until parent_part_id IS NULL, selecting the top ancestor name -> Option D

4. Quick Check:

   Top ancestor requires repeated self joins [OK]

Hint: Top ancestor needs repeated self joins or recursion [OK]

Common Mistakes:

• Using single join only finds immediate parent
• Trying to use aggregate functions incorrectly
• Ignoring recursive nature of hierarchy