Grokking SQL for Tech Interviews
Ask Author
Back to course home

0% completed

Vote For New Content
32. Employee Department Exclusion (Easy)
On this page

Problem Statement

Example

Try It Yourself

Solution

SQL Query

Step by Step Approach

Problem Statement

Table: Employee
Each row in this table represents an individual employee, detailing their unique ID, name, and the department ID they belong to.

+---------------+---------+
| Column Name   | Type    | 
+---------------+---------+
| id            | int     | 
| name          | varchar |
| department_id | int     |
+---------------+---------+
id is the primary key for this table.
department_id refers to the ID of the department the employee works in.

Develop a solution to find the names of employees who do not work in a department with id = 3. The output should be sorted in any order.

Example

Input:

Employee table:
+----+-------+--------------+
| id | name  | department_id|
+----+-------+--------------+
| 1  | Alice | 1            |
| 2  | Bob   | 3            |
| 3  | Cindy | 2            |
| 4  | Dave  | 3            |
| 5  | Eve   | 1            |
+----+-------+--------------+

Output:

+-------+
| name  |
+-------+
| Alice |
| Cindy |
| Eve   |
+-------+

In this example, Alice, Cindy, and Eve do not work in department where id = 3.

Try It Yourself

MYSQL
MYSQL
. . . .

Solution

To solve this problem, we need to identify employees in the Employee table who are not part of a specified department (id = 3). SQL queries will be utilized to filter and retrieve the necessary data.

  1. Select Employee Names: Begin by executing a SELECT statement to fetch the name column from the Employee table.
  2. Exclude Specific Department: Introduce a WHERE clause to filter out employees associated with the department id = 3.

SQL Query

The definitive SQL query is as follows:

SELECT name
FROM Employee
WHERE department_id <> 3;

Step by Step Approach

Step 1: Select Employee Names

Initiate the process by selecting the name from the Employee table, which forms the essence of our output.

SELECT name
FROM Employee

Output After Step 1:

+-------+
| name  |
+-------+
| Alice |
| Bob   | 
| Cindy |
| Dave  |
| Eve   |
+-------+

Step 2: Exclude Specific Department

Refine the selection by applying a condition to exclude employees from department id = 3.

SELECT name
FROM Employee
WHERE department_id <> 3

Final Output:

+-------+
| name  |
+-------+
| Alice | 
| Cindy |
| Eve   |
+-------+

.....

.....

.....

Like the course? Get enrolled and start learning!

On this page

Problem Statement

Example

Try It Yourself

Solution

SQL Query

Step by Step Approach