﻿ SQL: List the employees who are working as a manager

# SQL Exercise: List the employees who are working as a manager

## SQL subqueries on employee Database: Exercise-32 with Solution

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

32. From the following table, write a SQL query to find those employees who are working as a manager. Return employee name, job name, department name, and location.

Sample table: employees

Sample table: department

Sample Solution:

``````SELECT e.emp_name,
e.job_name,
d.dep_name,
d.dep_location
FROM employees e,
department d
WHERE e.dep_id = d.dep_id
AND e.emp_id IN
(SELECT manager_id
FROM employees) ;
``````

Sample Output:

``` emp_name | job_name  | dep_name  | dep_location
----------+-----------+-----------+--------------
KAYLING  | PRESIDENT | FINANCE   | SYDNEY
BLAZE    | MANAGER   | MARKETING | PERTH
CLARE    | MANAGER   | FINANCE   | SYDNEY
JONAS    | MANAGER   | AUDIT     | MELBOURNE
SCARLET  | ANALYST   | AUDIT     | MELBOURNE
FRANK    | ANALYST   | AUDIT     | MELBOURNE
(6 rows)
```

Explanation:

The said query in SQL that retrieves the name, job, department, and location for all employees from the employees and department tables who are also managers

The WHERE clause ensures that the department ID in the employees table must match the department ID in the department table and filters the rows to only those where the emp_id is in the list of manager_ids in the employees table.

## Practice Online

Structure of employee Database:

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

Previous SQL Exercise: List the employees not in the MARKETING department.
Next SQL Exercise: List the highest-paid employees in each department.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿