Self join patterns in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select employee names and their managers' names using a self join.

PostgreSQL

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

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

Am.id

Be.id

Cm.manager_id

De.manager_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using employee's own id instead of manager's id.

Mixing up the join condition columns.

The self join matches each employee's manager_id with the manager's id. So, e.manager_id = m.id links employees to their managers.

2fill in blank

medium

Complete the code to find pairs of employees who share the same manager.

PostgreSQL

SELECT e1.name AS employee1, e2.name AS employee2 FROM employees e1 JOIN employees e2 ON e1.[1] = e2.[1] WHERE e1.id <> e2.id;

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

Adepartment_id

Bmanager_id

Cname

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Joining on employee ids instead of manager ids.

Not excluding pairs where employee1 equals employee2.

Employees who share the same manager have the same manager_id. The join condition matches e1.manager_id = e2.manager_id.

3fill in blank

hard

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

PostgreSQL

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

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

Aid

Bmanager_id

Cname

Ddepartment_id

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the columns in the join condition.

Joining on non-key columns like name or department_id.

The join should match the employee's manager_id to the manager's id. So, e.manager_id = m.id is correct.

4fill in blank

hard

Fill both blanks to select employees and their managers' departments using a self join.

PostgreSQL

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

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

Adepartment

Bmanager_id

Cid

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Using employee's id in place of manager_id in join.

Selecting wrong column for department.

The query selects the manager's department by joining on e.manager_id = m.id.

5fill in blank

hard

Fill all three blanks to find employees who manage others and count how many they manage.

PostgreSQL

SELECT m.name AS manager, COUNT(e.[1]) AS team_size FROM employees e JOIN employees m ON e.[2] = m.[3] GROUP BY m.name HAVING COUNT(e.id) > 0;

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

Aid

Bmanager_id

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Counting manager ids instead of employee ids.

Mixing join columns.

The count is on employee id. The join matches e.manager_id = m.id to find employees managed by each manager.