Subquery in WHERE clause in SQL - Mini Project: Build & Apply

Filter Employees Using Subquery in WHERE Clause

📖 Scenario: You work in the HR department of a company. You have a database table called employees that stores employee details including their id, name, and department_id. You also have a departments table that stores department_id and department_name.Your manager wants to see the list of employees who work in the 'Sales' department.

🎯 Goal: Build a SQL query that uses a subquery in the WHERE clause to find all employees who belong to the 'Sales' department.

📋 What You'll Learn

Create a table called departments with columns department_id and department_name.

Create a table called employees with columns id, name, and department_id.

Insert the exact data provided into both tables.

Write a SQL query that selects id and name from employees where department_id matches the department_id of the 'Sales' department using a subquery in the WHERE clause.

💡 Why This Matters

🌍 Real World

Filtering employees by department is a common task in HR databases to generate reports or manage staff.

💼 Career

Understanding subqueries in WHERE clauses is essential for database querying roles, data analysis, and backend development.

1

Create the departments table and insert data

Create a table called departments with columns department_id (integer) and department_name (text). Then insert these exact rows: (1, 'Sales'), (2, 'HR'), (3, 'Engineering').

SQL

-- Create the departments table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Create the employees table and insert data

Create a table called employees with columns id (integer), name (text), and department_id (integer). Then insert these exact rows: (101, 'Alice', 1), (102, 'Bob', 2), (103, 'Charlie', 1), (104, 'Diana', 3).

SQL

CREATE TABLE departments (
    department_id INTEGER,
    department_name TEXT
);

INSERT INTO departments (department_id, department_name) VALUES
(1, 'Sales'),
(2, 'HR'),
(3, 'Engineering');

-- Create the employees table and insert data
-- Your code here

Need a hint?

Define the employees table with the correct columns and insert the given rows.

3

Write a subquery to find the department_id for 'Sales'

Write a SQL subquery that selects department_id from departments where department_name is 'Sales'. Assign this subquery to be used in the next step.

SQL

CREATE TABLE departments (
    department_id INTEGER,
    department_name TEXT
);

INSERT INTO departments (department_id, department_name) VALUES
(1, 'Sales'),
(2, 'HR'),
(3, 'Engineering');

CREATE TABLE employees (
    id INTEGER,
    name TEXT,
    department_id INTEGER
);

INSERT INTO employees (id, name, department_id) VALUES
(101, 'Alice', 1),
(102, 'Bob', 2),
(103, 'Charlie', 1),
(104, 'Diana', 3);

-- Write a subquery to select department_id for 'Sales'
-- Your code here

Need a hint?

The subquery should select department_id where the department name is exactly 'Sales'.

4

Use the subquery in WHERE clause to select employees in 'Sales'

Write a SQL query that selects id and name from employees where department_id equals the subquery that selects department_id from departments where department_name is 'Sales'.

SQL

CREATE TABLE departments (
    department_id INTEGER,
    department_name TEXT
);

INSERT INTO departments (department_id, department_name) VALUES
(1, 'Sales'),
(2, 'HR'),
(3, 'Engineering');

CREATE TABLE employees (
    id INTEGER,
    name TEXT,
    department_id INTEGER
);

INSERT INTO employees (id, name, department_id) VALUES
(101, 'Alice', 1),
(102, 'Bob', 2),
(103, 'Charlie', 1),
(104, 'Diana', 3);

-- Write a query to select id and name from employees
-- where department_id matches the subquery for 'Sales'
-- Your code here

Need a hint?

Use the subquery inside the WHERE clause to filter employees by department.