SQL subqueries on employee Database: List the name, job name, department name, location for those 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)

Practice Online

Structure of employee Database:

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

Previous: From the following table, write a SQL query to find those employees who are not working in the department MARKETING. Return complete information about the employees.
Next: From the following table, write a SQL query to find those employees who receive the highest salary of each department. Return employee name and department ID.