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

# 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿