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.