Self join for hierarchical data in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all employees and their managers using a self join.

SQL

SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = [1];

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

Ae.id

Bm.id

Ce.manager_id

Dm.manager_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using employee id instead of manager id in the join condition.

Joining on the wrong columns causing no matches.

The self join matches each employee's manager_id with the manager's id. So, e.manager_id = m.id is correct.

2fill in blank

medium

Complete the code to find employees who have no manager (top-level).

SQL

SELECT name FROM employees WHERE [1] IS NULL;

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

Aemployee_id

Bname

Cid

Dmanager_id

Attempts:

3 left

💡 Hint

Common Mistakes

Checking if employee id is NULL instead of manager_id.

Using wrong column names.

Employees with no manager have manager_id set to NULL.

3fill in blank

hard

Fix the error in the self join query to correctly list employees and their managers.

SQL

SELECT e.name, m.name FROM employees e JOIN employees m ON e.[1] = m.id;

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

Amanager_id

Bid

Cname

Demployee_id

Attempts:

3 left

💡 Hint

Common Mistakes

Joining on employee id instead of manager_id.

Using wrong column names causing no results.

The join should match employee's manager_id to manager's id.

4fill in blank

hard

Fill both blanks to select employees and their managers' departments.

SQL

SELECT e.name AS employee, m.name AS manager, e.[1] AS emp_dept, m.[2] AS mgr_dept FROM employees e JOIN employees m ON e.manager_id = m.id;

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

Adepartment

Bmanager_id

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Using manager_id or id instead of department for department columns.

Mixing up employee and manager columns.

Both employee and manager have a department column to show their departments.

5fill in blank

hard

Fill all three blanks to find employees, their managers, and managers' managers.

SQL

SELECT e.name AS employee, m.name AS manager, mm.name AS mgr_manager FROM employees e JOIN employees m ON e.[1] = m.id JOIN employees mm ON m.[2] = mm.[3];

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

Amanager_id

Cid

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong columns in join conditions.

Mixing up id and manager_id columns.

First join links employee to manager by manager_id. Second join links manager to their manager by manager_id and id.