SQL Exercises: Employees who work in the department Marketing

Last update on March 12 2025 12:11:49 (UTC/GMT +8 hours)

From the following table, write a SQL query to find those employees who work in the department 'Marketing'. Return first name, last name and department ID.

Sample table: employees

+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
| EMPLOYEE_ID | FIRST_NAME  | LAST_NAME   | EMAIL    | PHONE_NUMBER       | HIRE_DATE  | JOB_ID     | SALARY   | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID |
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
|         100 | Steven      | King        | SKING    | 515.123.4567       | 2003-06-17 | AD_PRES    | 24000.00 |           0.00 |          0 |            90 |
|         101 | Neena       | Kochhar     | NKOCHHAR | 515.123.4568       | 2005-09-21 | AD_VP      | 17000.00 |           0.00 |        100 |            90 |
|         102 | Lex         | De Haan     | LDEHAAN  | 515.123.4569       | 2001-01-13 | AD_VP      | 17000.00 |           0.00 |        100 |            90 |
|         103 | Alexander   | Hunold      | AHUNOLD  | 590.423.4567       | 2006-01-03 | IT_PROG    |  9000.00 |           0.00 |        102 |            60 |
|         104 | Bruce       | Ernst       | BERNST   | 590.423.4568       | 2007-05-21 | IT_PROG    |  6000.00 |           0.00 |        103 |            60 |
|         105 | David       | Austin      | DAUSTIN  | 590.423.4569       | 2005-06-25 | IT_PROG    |  4800.00 |           0.00 |        103 |            60 |
|         106 | Valli       | Pataballa   | VPATABAL | 590.423.4560       | 2006-02-05 | IT_PROG    |  4800.00 |           0.00 |        103 |            60 |
|         107 | Diana       | Lorentz     | DLORENTZ | 590.423.5567       | 2007-02-07 | IT_PROG    |  4200.00 |           0.00 |        103 |            60 |
|         108 | Nancy       | Greenberg   | NGREENBE | 515.124.4569       | 2002-08-17 | FI_MGR     | 12008.00 |           0.00 |        101 |           100 |
|         109 | Daniel      | Faviet      | DFAVIET  | 515.124.4169       | 2002-08-16 | FI_ACCOUNT |  9000.00 |           0.00 |        108 |           100 |
|         110 | John        | Chen        | JCHEN    | 515.124.4269       | 2005-09-28 | FI_ACCOUNT |  8200.00 |           0.00 |        108 |           100 |
....................
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+

View the table

Sample table: departments

+---------------+----------------------+------------+-------------+
| DEPARTMENT_ID | DEPARTMENT_NAME      | MANAGER_ID | LOCATION_ID |
+---------------+----------------------+------------+-------------+
|            10 | Administration       |        200 |        1700 |
|            20 | Marketing            |        201 |        1800 |
|            30 | Purchasing           |        114 |        1700 |
|            40 | Human Resources      |        203 |        2400 |
|            50 | Shipping             |        121 |        1500 |
|            60 | IT                   |        103 |        1400 |
|            70 | Public Relations     |        204 |        2700 |
|            80 | Sales                |        145 |        2500 |
|            90 | Executive            |        100 |        1700 |
|           100 | Finance              |        108 |        1700 |
......
+---------------+----------------------+------------+-------------+

View the table

Sample Solution:

-- Selecting specific columns (first_name, last_name, department_id) from the 'employees' table
SELECT first_name, last_name, department_id 

-- Filtering rows based on the condition that the 'department_id' matches the result of a subquery
FROM employees 

-- Subquery to find the 'department_id' where the 'department_name' is 'Marketing' in the 'departments' table
WHERE department_id = 
       (SELECT department_id 
          FROM departments 
           WHERE department_name = 'Marketing');

Sample Output:

first_name	last_name	department_id
Michael		Hartstein	20
Pat		Fay		20

Code Explanation:

The said query in SQL that retrieves the first name, last name, and department id of all employees who belong to the department named "Marketing".
In this case, the WHERE clause is checks if the department_id of each employee matches the department_id of the department named "Marketing". The condition uses a subquery in the WHERE clause to retrieve the department_id of the "Marketing" department, and then compare it to the department_id of each employee.

Visual Presentation:

Alternative Solutions:

Using JOIN:

SELECT e.first_name, e.last_name, e.department_id 
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Marketing';

Using EXISTS:

SELECT first_name, last_name, department_id 
FROM employees e1
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_name = 'Marketing' AND e1.department_id = d.department_id
);

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Employee who works in that department which ID is 40.
Next SQL Exercise: Employees earning more than departments minimum wage.