Students Linked to Retired Departments

Easy

A university data platform stores two relational tables: Departments(id, name) id: active department identifier name: department label Students(id, name, department_id) id: student identifier name: student label department_id: student's referenced department Your task is to identify students whose department linkage is invalid because the referenced department id does not exist in Departments. Output requirements: Return exactly two columns: id, name Include only invalid student references Result order is not important Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

departments:
| id | name                    |
|----|-------------------------|
| 10 | Computer Science        |
| 20 | Mechanical Engineering  |
| 30 | Applied Mathematics     |

students:
| id  | name    | department_id |
|-----|---------|---------------|
| 101 | Aria    | 10            |
| 102 | Ben     | 77            |
| 103 | Celine  | 20            |
| 104 | Dario   | 88            |
| 105 | Elin    | 30            |
| 106 | Farah   | 90            |

Output

[
  {"id":102,"name":"Ben"},
  {"id":104,"name":"Dario"},
  {"id":106,"name":"Farah"}
]

Explanation

Students 102, 104, and 106 point to department IDs that are not present in departments.

Example 2

Input

departments:
| id | name      |
|----|-----------|
| 1  | Physics   |
| 2  | Chemistry |

students:
| id | name | department_id |
|----|------|---------------|
| 1  | Nia  | 1             |
| 2  | Omar | 2             |

Output

[]

Explanation

Every student references an active department, so no rows are returned.

Example 3

Input

departments:
| id | name      |
|----|-----------|
| 7  | Economics |

students:
| id | name   | department_id |
|----|--------|---------------|
| 9  | Riya   | 11            |
| 8  | Noor   | 7             |
| 3  | Hugo   | 12            |

Output

[
  {"id":3,"name":"Hugo"},
  {"id":9,"name":"Riya"}
]

Explanation

Department IDs 11 and 12 are missing, while department 7 is valid.

Constraints

0 <= number of rows in departments <= 100000
0 <= number of rows in students <= 200000
1 <= departments.id, students.id, students.department_id <= 1000000000
departments.id is unique
students.id is unique
Return exactly two columns: id and name
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns [id, name]

Preparing your editor. You can read the problem while it loads.

Students Linked to Retired Departments

Easy

A university data platform stores two relational tables: Departments(id, name) id: active department identifier name: department label Students(id, name, department_id) id: student identifier name: student label department_id: student's referenced department Your task is to identify students whose department linkage is invalid because the referenced department id does not exist in Departments. Output requirements: Return exactly two columns: id, name Include only invalid student references Result order is not important Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

departments:
| id | name                    |
|----|-------------------------|
| 10 | Computer Science        |
| 20 | Mechanical Engineering  |
| 30 | Applied Mathematics     |

students:
| id  | name    | department_id |
|-----|---------|---------------|
| 101 | Aria    | 10            |
| 102 | Ben     | 77            |
| 103 | Celine  | 20            |
| 104 | Dario   | 88            |
| 105 | Elin    | 30            |
| 106 | Farah   | 90            |

Output

[
  {"id":102,"name":"Ben"},
  {"id":104,"name":"Dario"},
  {"id":106,"name":"Farah"}
]

Explanation

Students 102, 104, and 106 point to department IDs that are not present in departments.

Example 2

Input

departments:
| id | name      |
|----|-----------|
| 1  | Physics   |
| 2  | Chemistry |

students:
| id | name | department_id |
|----|------|---------------|
| 1  | Nia  | 1             |
| 2  | Omar | 2             |

Output

[]

Explanation

Every student references an active department, so no rows are returned.

Example 3

Input

departments:
| id | name      |
|----|-----------|
| 7  | Economics |

students:
| id | name   | department_id |
|----|--------|---------------|
| 9  | Riya   | 11            |
| 8  | Noor   | 7             |
| 3  | Hugo   | 12            |

Output

[
  {"id":3,"name":"Hugo"},
  {"id":9,"name":"Riya"}
]

Explanation

Department IDs 11 and 12 are missing, while department 7 is valid.

Constraints

0 <= number of rows in departments <= 100000
0 <= number of rows in students <= 200000
1 <= departments.id, students.id, students.department_id <= 1000000000
departments.id is unique
students.id is unique
Return exactly two columns: id and name
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns [id, name]