Employees Whose Manager Left the Company solution and explanation

SELECT emp.employee_id FROM Employees emp LEFT JOIN Employees mgr ON emp.manager_id = mgr.employee_id WHERE emp.salary < 30000 AND mgr.employee_id IS NULL ORDER BY emp.employee_id;

SELECT employee_id FROM Employees WHERE salary < 30000 AND manager_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM Employees e WHERE e.employee_id = Employees.manager_id) ORDER BY employee_id;

import sqlite3

# Connect to SQLite database
connection = sqlite3.connect(':memory:')
cursor = connection.cursor()
cursor.execute('''
CREATE TABLE Employees(
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT,
    salary INT
);
''')

# Insert the example data
cursor.executemany('''
INSERT INTO Employees (employee_id, name, manager_id, salary) VALUES (?, ?, ?, ?)
''', [
    (3, 'Mila', 9, 60301),
    (12, 'Antonella', None, 31000),
    (13, 'Emery', None, 67084),
    (1, 'Kalel', 11, 21241),
    (9, 'Mikaela', None, 50937),
    (11, 'Joziah', 6, 28485)
])

# SQL Query to get the desired result
cursor.execute('''
SELECT E1.employee_id
FROM Employees E1
LEFT JOIN Employees E2 ON E1.manager_id = E2.employee_id
WHERE E2.employee_id IS NULL AND E1.salary < 30000
ORDER BY E1.employee_id;
''')

# Fetch and display results
results = cursor.fetchall()
print(results)

# Close the connection
connection.close()

import java.sql.*;

public class EmployeeManagerIssue {
    public static void main(String[] args) throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:sqlite::memory:");
        Statement statement = connection.createStatement();

        String createTableSQL = "CREATE TABLE Employees(" +
                "employee_id INT PRIMARY KEY," +
                "name VARCHAR(100)," +
                "manager_id INT," +
                "salary INT);
                ";
        statement.execute(createTableSQL);

        String insertSQL = "INSERT INTO Employees(employee_id, name, manager_id, salary) VALUES " +
                "(3, 'Mila', 9, 60301)," +
                "(12, 'Antonella', NULL, 31000)," +
                "(13, 'Emery', NULL, 67084)," +
                "(1, 'Kalel', 11, 21241)," +
                "(9, 'Mikaela', NULL, 50937)," +
                "(11, 'Joziah', 6, 28485);";
        statement.execute(insertSQL);

        String query = "SELECT E1.employee_id FROM Employees E1 " +
                "LEFT JOIN Employees E2 ON E1.manager_id = E2.employee_id " +
                "WHERE E2.employee_id IS NULL AND E1.salary < 30000 " +
                "ORDER BY E1.employee_id;";

        ResultSet rs = statement.executeQuery(query);
        while(rs.next()) {
            System.out.println(rs.getInt("employee_id"));
        }

        statement.close();
        connection.close();
    }
}

const sqlite3 = require('sqlite3').verbose();

let db = new sqlite3.Database(':memory:', (err) => {
  if (err) {
    return console.error(err.message);
  }
  console.log('Connected to the in-memory SQlite database.');
});

db.serialize(() => {
  db.run(`CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    name TEXT,
    manager_id INT,
    salary INT
  );`);

  db.run(`INSERT INTO Employees (employee_id, name, manager_id, salary) VALUES
    (3, 'Mila', 9, 60301),
    (12, 'Antonella', NULL, 31000),
    (13, 'Emery', NULL, 67084),
    (1, 'Kalel', 11, 21241),
    (9, 'Mikaela', NULL, 50937),
    (11, 'Joziah', 6, 28485)
  ;`);

  db.all(`SELECT E1.employee_id
          FROM Employees E1
          LEFT JOIN Employees E2 ON E1.manager_id = E2.employee_id
          WHERE E2.employee_id IS NULL AND E1.salary < 30000
          ORDER BY E1.employee_id;`, [], (err, rows) => {
    if (err) {
      throw err;
    }
    rows.forEach((row) => {
      console.log(row.employee_id);
    });
  });
});

db.close((err) => {
  if (err) {
    return console.error(err.message);
  }
  console.log('Close the database connection.');
});

#include <iostream>
#include <sqlite3.h>

void executeQuery(sqlite3* db) {
    const char* sql = "SELECT employee_id FROM Employees "
                      "WHERE salary < 30000 AND manager_id NOT IN "
                      "(SELECT employee_id FROM Employees) "
                      "ORDER BY employee_id;";
    sqlite3_stmt* stmt;
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc != SQLITE_OK) {
        std::cerr << "Failed to fetch data: " << sqlite3_errmsg(db) << std::endl;
        return;
    }

    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        std::cout << sqlite3_column_int(stmt, 0) << std::endl;
    }

    sqlite3_finalize(stmt);
}

int main() {
    sqlite3* db;
    int rc = sqlite3_open(":memory:", &db);
    if (rc) {
        std::cerr << "Cannot open database: " << sqlite3_errmsg(db) << std::endl;
        return 1;
    }

    const char* createTableSQL = "CREATE TABLE Employees(" "employee_id INT PRIMARY KEY,name VARCHAR(100),manager_id INT,salary INT);";
    rc = sqlite3_exec(db, createTableSQL, 0, 0, 0);

    const char* insertSQL = "INSERT INTO Employees(employee_id, name, manager_id, salary) VALUES "
                            "(3, 'Mila', 9, 60301),"
                            "(12, 'Antonella', NULL, 31000),"
                            "(13, 'Emery', NULL, 67084),"
                            "(1, 'Kalel', 11, 21241),"
                            "(9, 'Mikaela', NULL, 50937),"
                            "(11, 'Joziah', 6, 28485);";
    rc = sqlite3_exec(db, insertSQL, 0, 0, 0);

    executeQuery(db);

    sqlite3_close(db);
    return 0;
}

#include <sqlite3.h>
#include <stdio.h>

void executeQuery(sqlite3* db) {
    const char* sql = "SELECT employee_id FROM Employees "
                      "WHERE salary < 30000 AND manager_id NOT IN "
                      "(SELECT employee_id FROM Employees) "
                      "ORDER BY employee_id;";
    sqlite3_stmt* stmt;
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc != SQLITE_OK) {
        printf("Failed to fetch data: %s\n", sqlite3_errmsg(db));
        return;
    }

    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        printf("%d\n", sqlite3_column_int(stmt, 0));
    }

    sqlite3_finalize(stmt);
}

int main() {
    sqlite3* db;
    int rc = sqlite3_open(":memory:", &db);
    if (rc) {
        printf("Cannot open database: %s\n", sqlite3_errmsg(db));
        return 1;
    }

    const char* createTableSQL = "CREATE TABLE Employees(
        employee_id INT PRIMARY KEY,name VARCHAR(100), manager_id INT, salary INT);";
    rc = sqlite3_exec(db, createTableSQL, 0, 0, 0);

    const char* insertSQL = "INSERT INTO Employees(employee_id, name, manager_id, salary) VALUES "
                            "(3, 'Mila', 9, 60301),"
                            "(12, 'Antonella', NULL, 31000),"
                            "(13, 'Emery', NULL, 67084),"
                            "(1, 'Kalel', 11, 21241),"
                            "(9, 'Mikaela', NULL, 50937),"
                            "(11, 'Joziah', 6, 28485);";
    rc = sqlite3_exec(db, insertSQL, 0, 0, 0);

    executeQuery(db);

    sqlite3_close(db);
    return 0;
}