﻿ SQL: List the employees whose manager name is JONAS

# SQL Exercise: List the employees whose manager name is JONAS

## SQL employee Database: Exercise-57 with Solution

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

57. From the following table, write a SQL query to find those employees whose manager name is JONAS. Return employee id, employee name, job name, manager ID, hire date, salary, department ID, employee name.

Sample table: employees

Pictorial Presentation:

Sample Solution:

SELECT w.emp_id,
w.emp_name,
w.job_name,
w.manager_id,
w.hire_date,
w.salary,
w.dep_id,
m.emp_name
FROM employees w,
employees m
WHERE w.manager_id = m.emp_id
AND m.emp_name = 'JONAS';

Sample Output:

emp_id | emp_name | job_name | manager_id | hire_date  | salary  | dep_id | emp_name
--------+----------+----------+------------+------------+---------+--------+----------
67858 | SCARLET  | ANALYST  |      65646 | 1997-04-19 | 3100.00 |   2001 | JONAS
69062 | FRANK    | ANALYST  |      65646 | 1991-12-03 | 3100.00 |   2001 | JONAS
(2 rows)

Explanation:

The said query in SQL that retrieves emp_id, emp_name, job_name, manager_id, hire_date, salary, dep_id, and the name of the employee's manager from the 'employees' table.

The query builds a self-join on the 'employees' table with itself based on the "manager_id" column in the 'employees' table, which corresponds to the "emp_id" column of the employee's manager using the "WHERE" clause and includes all the employees whose manager's name is "JONAS".

## Practice Online

Sample Database: employee

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

Previous SQL Exercise: Employees joined before a date but not graded 4.
Next SQL Exercise: Salary of FRANK if his salary is equal to max_sal.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

## SQL: Tips of the Day

Concatenate strings of a string field in a PostgreSQL 'group by' query:

Input:

ID   COMPANY_ID   EMPLOYEE
1    1            Anna
2    1            Bill
3    2            Carol
4    2            Dave
SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;

Output:

COMPANY_ID   EMPLOYEE
1            Anna, Bill
2            Carol, Dave

Database: PostgreSQL

Ref: https://bit.ly/2XTiRjq

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook