[Solved] Given the table departments(id, name, parent_id) with data:(1, 'Head Office', NULL), (2, 'Sales', 1), (3, 'Domestic Sales', 2)What will the following query return?... | PostgreSQL

PostgreSQL - Common Table Expressions

Given the table departments(id, name, parent_id) with data:
(1, 'Head Office', NULL), (2, 'Sales', 1), (3, 'Domestic Sales', 2)
What will the following query return?

WITH RECURSIVE dept_hierarchy AS ( SELECT id, name, parent_id FROM departments WHERE id = 1 UNION ALL SELECT d.id, d.name, d.parent_id FROM departments d JOIN dept_hierarchy dh ON d.parent_id = dh.id ) SELECT * FROM dept_hierarchy;

ADepartments that have no children.

BOnly the 'Head Office' department row.

CDepartments with no parent_id (top-level only).

DAll departments starting from 'Head Office' down to its descendants.

Step-by-Step Solution

Solution:

Step 1: Identify anchor member
The anchor selects the department with id = 1 ('Head Office').
Step 2: Understand recursive member
The recursive part joins departments whose parent_id matches ids in the current hierarchy.
Step 3: Result
The query returns 'Head Office' plus all descendants: 'Sales' and 'Domestic Sales'.
Final Answer:
All departments starting from 'Head Office' down to its descendants. -> Option D
Quick Check:
Recursive CTE expands hierarchy from root [OK]

Quick Trick: Recursive CTEs return all descendants starting from anchor [OK]

Common Mistakes:

Assuming only the anchor row is returned
Confusing top-level departments with descendants
Ignoring recursive join condition

Master "Common Table Expressions" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Given the table departments(id, name, parent_id) with data:

Step 1: Identify anchor member

Step 2: Understand recursive member

Step 3: Result

Final Answer:

Quick Check:

Want More Practice?