SQL Exercise: List all employees who are subordinates to Blaze

SQL subqueries on employee Database: Exercise-47 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

47. From the following table, write a SQL query to find those employees who are sub-ordinates of BLAZE. Return complete information about the employees.

Sample table: employees

Sample Solution:

SELECT *
FROM employees
WHERE manager_id IN
    (SELECT emp_id
     FROM employees
     WHERE emp_name = 'BLAZE');

Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  64989 | ADELYN   | SALESMAN |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
  65271 | WADE     | SALESMAN |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
  66564 | MADDEN   | SALESMAN |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
  68454 | TUCKER   | SALESMAN |      66928 | 1991-09-08 | 1600.00 |       0.00 |   3001
  69000 | JULIUS   | CLERK    |      66928 | 1991-12-03 | 1050.00 |            |   3001
(5 rows)

Explanation:

The said query in SQL that retrieves all the employees from the employees table who are managed by the employee named 'BLAZE'.

The query that retrieves the employees where the manager_id column matches any of the emp_id values returned by the subquery. The subquery finds the emp_id of the employee named 'BLAZE'.

Practice Online

Structure of employee database:

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

Previous SQL Exercise: List managers on lower salaries than their employees.
Next SQL Exercise: List all the employees who report to Blaze.