Students Linked to Retired Departments

EASY10 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

students =

[{"id":101,"name":"Aria","department_id":10},{"id":102,"name":"Ben","department_id":77},{"id":103,"name":"Celine","department_id":20},{"id":104,"name":"Dario","department_id":88},{"id":105,"name":"Elin","department_id":30},{"id":106,"name":"Farah","department_id":90}]

departments =

[{"id":10,"name":"Computer Science"},{"id":20,"name":"Mechanical Engineering"},{"id":30,"name":"Applied Mathematics"}]

Students Linked to Retired Departments

Hints

Students Linked to Retired Departments

Hints