Recursive CTEs in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start a recursive CTE with the base query.

MySQL

WITH RECURSIVE cte AS (SELECT id, parent_id FROM employees WHERE parent_id IS [1])

Drag options to blanks, or click blank then click option'

AFALSE

BNOT NULL

CTRUE

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using NOT NULL instead of NULL causes no rows to be selected.

Using TRUE or FALSE is not valid syntax for this condition.

The base query selects rows where parent_id is NULL, which usually means top-level employees without a manager.

2fill in blank

medium

Complete the recursive part to join the CTE with the employees table.

MySQL

SELECT id, parent_id FROM employees UNION ALL SELECT e.id, e.parent_id FROM employees e JOIN cte ON e.parent_id = cte.[1]

Drag options to blanks, or click blank then click option'

Aid

Bparent_id

Cname

Demployee_id

Attempts:

3 left

💡 Hint

Common Mistakes

Joining on parent_id = parent_id causes a self-join with no hierarchy.

Using name or employee_id which are not part of the CTE columns.

The recursive part joins employees where their parent_id matches the id from the CTE, to find children.

3fill in blank

hard

Fix the error in the recursive CTE by completing the anchor member.

MySQL

WITH RECURSIVE cte AS (SELECT id, parent_id FROM employees WHERE [1]) SELECT * FROM cte;

Drag options to blanks, or click blank then click option'

Aparent_id IS NULL

Bid = 0

Cparent_id = id

Did IS NULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using id = 0 may not match any rows.

Using parent_id = id causes a wrong condition.

Using id IS NULL is usually false for primary keys.

The anchor member must select the root rows where parent_id is NULL to start recursion properly.

4fill in blank

hard

Fill both blanks to complete the recursive CTE that finds all descendants of employee 5.

MySQL

WITH RECURSIVE descendants AS (SELECT id, parent_id FROM employees WHERE id = [1] UNION ALL SELECT e.id, e.parent_id FROM employees e JOIN descendants d ON e.[2] = d.id) SELECT * FROM descendants;

Drag options to blanks, or click blank then click option'

A5

Bparent_id

Cid

Demployee_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using e.id = d.id causes no recursion.

Using wrong employee id in anchor member.

Joining on e.employee_id which is not a valid column.

The anchor selects employee with id = 5. The recursive part joins on e.parent_id = d.id to find children.

5fill in blank

hard

Fill all three blanks to create a recursive CTE that calculates the depth level of each employee in the hierarchy.

MySQL

WITH RECURSIVE hierarchy AS (SELECT id, parent_id, 1 AS level FROM employees WHERE parent_id IS [1] UNION ALL SELECT e.id, e.parent_id, h.level [2] 1 FROM employees e JOIN hierarchy h ON e.parent_id = h.[3]) SELECT * FROM hierarchy;

Drag options to blanks, or click blank then click option'

ANULL

B+

Cid

D-

Attempts:

3 left

💡 Hint

Common Mistakes

Using parent_id IS NOT NULL in anchor selects wrong rows.

Using - instead of + decreases level incorrectly.

Joining on wrong column like parent_id instead of id.

The anchor selects root employees with parent_id IS NULL. The recursive part increments the level by 1 and joins on h.id.