SQL Exercise: Names of the employees and the manager of JONAS

SQL subqueries on employee Database: Exercise-49 with Solution

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

49. From the following table, write a SQL query to list the name of the employees for their manager JONAS and the name of the manager of JONAS.

Sample table: employees

Sample Solution:

SELECT w.emp_name,
       m.emp_name,

  (SELECT emp_name
   FROM employees
   WHERE m.manager_id = emp_id) "his MANAGER"
FROM employees w,
     employees m
WHERE w.manager_id = m.emp_id
  AND m.emp_name = 'JONAS';

OR

SELECT e.emp_name ,
       w.emp_name as "manager for employees" ,
       m.emp_name as "manager for JONAS"
FROM employees e,
     employees w,
     employees m
WHERE e.manager_id = w.emp_id
  AND w.emp_name = 'JONAS'
  AND w.manager_id = m.emp_id;
 

Sample Output:

 emp_name | Manager for employees | manager for JONAS
----------+-----------------------+---------------------
 FRANK    | JONAS                 | KAYLING
 SCARLET  | JONAS                 | KAYLING
(2 rows)

Explanation:

The said query in SQL that retrieves the names of employees who are managed by 'JONAS', along with the name of 'JONAS' as their manager, and the name of 'JONAS''s own manager from the 'employees' table.

The subquery selects the "emp_name" column from the 'employees' table where the "manager_id" column matches the "emp_id" of 'JONAS', and aliases it as "his MANAGER".

The FROM clause joins the 'employees' table to itself based on the "manager_id" column and the "emp_id" column.

The WHERE clause filters for rows where the "emp_name" column matches the string 'JONAS'. This limits the results to only the employees who are managed by 'JONAS'.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: List all the employees who report to Blaze.
Next SQL Exercise: Sort employees with minimum salary for a designation.