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
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';
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)
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".
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:
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;
COMPANY_ID EMPLOYEE 1 Anna, Bill 2 Carol, Dave
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
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