Skip to content

Latest commit

 

History

History
138 lines (117 loc) · 3.89 KB

1350. Students With Invalid Departments.md

File metadata and controls

138 lines (117 loc) · 3.89 KB

1350. Students With Invalid Departments

1. Description

SQL Schema: 
CREATE TABLE IF NOT EXISTS Departments (
    id            int,
    name          varchar(30))
CREATE TABLE IF NOT EXISTS Students (
    id            int,
    name          varchar(30),
    department_id int)

TRUNCATE TABLE Departments
INSERT INTO Departments (id, name)
    VALUES ('1', 'Electrical Engineering')
INSERT INTO Departments (id, name)
    VALUES ('7', 'Computer Engineering')
INSERT INTO Departments (id, name)
    VALUES ('13', 'Bussiness Administration')

TRUNCATE TABLE Students
INSERT INTO Students (id, name, department_id)
    VALUES ('23', 'Alice', '1')
INSERT INTO Students (id, name, department_id)
    VALUES ('1', 'Bob', '7')
INSERT INTO Students (id, name, department_id)
    VALUES ('5', 'Jennifer', '13')
INSERT INTO Students (id, name, department_id)
    VALUES ('2', 'John', '14')
INSERT INTO Students (id, name, department_id)
    VALUES ('4', 'Jasmine', '77')
INSERT INTO Students (id, name, department_id)
    VALUES ('3', 'Steve', '74')
INSERT INTO Students (id, name, department_id)
    VALUES ('6', 'Luis', '1')
INSERT INTO Students (id, name, department_id)
    VALUES ('8', 'Jonathan', '7')
INSERT INTO Students (id, name, department_id)
    VALUES ('7', 'Daiana', '33')
INSERT INTO Students (id, name, department_id)
    VALUES ('11', 'Madelynn', '1')

Table: Departments

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+

id is the primary key of this table.
The table has information about the id of each department of a university.

Table: Students

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
| department_id | int     |
+---------------+---------+

id is the primary key of this table.
The table has information about the id of each student at a university and the id of the department he/she studies at.

Write an SQL query to find the id and the name of all students who are enrolled in departments that no longer exist.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input:
Departments table:
+------+--------------------------+
| id   | name                     |
+------+--------------------------+
| 1    | Electrical Engineering   |
| 7    | Computer Engineering     |
| 13   | Bussiness Administration |
+------+--------------------------+
Students table:
+------+----------+---------------+
| id   | name     | department_id |
+------+----------+---------------+
| 23   | Alice    | 1             |
| 1    | Bob      | 7             |
| 5    | Jennifer | 13            |
| 2    | John     | 14            |
| 4    | Jasmine  | 77            |
| 3    | Steve    | 74            |
| 6    | Luis     | 1             |
| 8    | Jonathan | 7             |
| 7    | Daiana   | 33            |
| 11   | Madelynn | 1             |
+------+----------+---------------+
Output:
+------+----------+
| id   | name     |
+------+----------+
| 2    | John     |
| 7    | Daiana   |
| 4    | Jasmine  |
| 3    | Steve    |
+------+----------+
Explanation:
John, Daiana, Steve, and Jasmine are enrolled in departments 14, 33, 74, and 77 respectively. department 14, 33, 74, and 77 do not exist in the Departments table.

2. Solutions

Solution 1: Language: SQL

  • Monday, 12 September, 2022
  • Runtime: 823 ms, faster than 70.73% of MySQL online submissions for Students With Invalid Departments.
  • Memory Usage: 0B, less than 100.00% of MySQL online submissions for Students With Invalid Departments.
SELECT id, name
FROM Students
WHERE department_id
    NOT IN (SELECT id FROM Departments);