Students With Invalid Departments - Practice Coding Problems

Students With Invalid Departments - Problem

Database Easy

🎓 University Database Management Challenge

Imagine you're working as a database administrator for a university. The university has been restructuring, and some departments have been closed or merged. However, the student records still reference these non-existent departments!

Your task is to identify students who are enrolled in departments that no longer exist in the university's department registry. This is a classic data integrity issue that occurs frequently in real-world database management.

Given:

Departments table: Contains all currently active departments with their id and name
Students table: Contains student information including their department_id (which may reference deleted departments)

Goal: Find all students whose department_id doesn't match any existing department id. Return their id and name.

This is a perfect example of finding orphaned records - a common database maintenance task!

Input & Output

example_1.sql — Basic Case

$ Input: Departments: | id | name | |----|----------------| | 1 | Computer Science| | 2 | Mathematics | | 3 | Physics | Students: | id | name | department_id | |----|-------|--------------| | 1 | Alice | 7 | | 2 | Bob | 1 | | 3 | Carol | 8 |

› Output: | id | name | |----|-------| | 1 | Alice | | 3 | Carol |

💡 Note: Alice is enrolled in department 7 and Carol in department 8, but only departments 1, 2, and 3 exist. Bob is in department 1 which exists, so he's not included in the result.

example_2.sql — All Students Valid

$ Input: Departments: | id | name | |----|--------| | 1 | Biology | | 2 | Chemistry| Students: | id | name | department_id | |----|------|--------------| | 1 | John | 1 | | 2 | Jane | 2 | | 3 | Mike | 1 |

› Output: | id | name | |----|----- | (empty result)

💡 Note: All students are enrolled in valid departments (1 or 2), so no students have invalid department references.

example_3.sql — Edge Case with NULL

$ Input: Departments: | id | name | |----|------| | 1 | Art | Students: | id | name | department_id | |----|-------|--------------| | 1 | Sarah | NULL | | 2 | Tom | 5 | | 3 | Lisa | 1 |

› Output: | id | name | |----|------| | 2 | Tom |

💡 Note: Tom is in department 5 which doesn't exist. Sarah has NULL department_id (typically handled separately in real systems). Lisa is in valid department 1.

Constraints

1 ≤ Students.id, Departments.id ≤ 10⁵
1 ≤ Students.name.length, Departments.name.length ≤ 20
Students.department_id can reference non-existent departments
All department IDs in Departments table are unique
All student IDs in Students table are unique

Visualization

Tap to expand

Understanding the Visualization

Prepare Tables

Database engine loads Students and Departments tables into memory

LEFT JOIN Execution

For each student, attempt to match department_id with Departments.id

Preserve All Students

Keep all student records, set department columns to NULL when no match found

Filter Results

Apply WHERE d.id IS NULL to find students with no matching department

Return Results

Output the ID and name of students with invalid departments

Key Takeaway

🎯 Key Insight: LEFT JOIN is the perfect tool for finding orphaned records - it preserves all records from the left table while showing NULL for missing matches on the right.

Asked in

a Amazon 45 G Google 38 ⊞ Microsoft 32 f Meta 28

The optimal solution uses a LEFT JOIN between Students and Departments tables, then filters for rows where the department ID is NULL. This approach leverages SQL's built-in optimization for JOIN operations, making it much more efficient than nested queries. The LEFT JOIN preserves all student records while showing NULL for non-existent department matches, allowing us to identify orphaned records in a single database operation.

Common Approaches

Approach	Time	Space	Notes
✓ LEFT JOIN Approach (Optimal)	O(n log m)	O(1)	Use LEFT JOIN to efficiently identify students with non-existent departments in one pass
Nested Query Approach	O(n²)	O(1)	Use nested SELECT with NOT EXISTS to check each student against all departments

LEFT JOIN Approach (Optimal) — Algorithm Steps

Perform LEFT JOIN between Students and Departments tables on department_id
Filter results where department.id IS NULL (indicating no match)
Select student id and name from the filtered results
Return all orphaned student records in single operation

Visualization

Tap to expand

Step-by-Step Walkthrough

Setup JOIN operation

Database engine prepares to match Students with Departments

LEFT JOIN execution

All students preserved, matched departments added where possible

Filter NULL matches

Identify rows where department.id IS NULL

Return results

Output students with invalid departments

Code -

solution.c — C

#include <stdio.h>

// Optimal SQL query using LEFT JOIN
const char* find_invalid_students_optimal() {
    return ""
        "SELECT s.id, s.name "
        "FROM Students s "
        "LEFT JOIN Departments d ON s.department_id = d.id "
        "WHERE d.id IS NULL "
        "ORDER BY s.id";
}

// With debugging information
const char* find_invalid_students_detailed() {
    return ""
        "SELECT s.id as student_id, s.name as student_name, "
        "s.department_id as invalid_dept_id "
        "FROM Students s "
        "LEFT JOIN Departments d ON s.department_id = d.id "
        "WHERE d.id IS NULL "
        "ORDER BY s.id";
}

Time & Space Complexity

Time Complexity

⏱️

O(n log m)

Database engine can optimize JOIN with indexes, typically O(n log m) where n=students, m=departments

⚡ Linearithmic

Space Complexity

O(1)

No additional space beyond result set, leverages database engine's memory management

✓ Linear Space

42.0K Views

High Frequency

~15 min Avg. Time

1.9K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

🎓 University Database Management Challenge

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

LEFT JOIN Approach (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler