SQL Exercise: List all the employees who report to Blaze
SQL subqueries on employee Database: Exercise-48 with Solution
[An editor is available at the bottom of the page to write and execute the scripts.]
48. From the following table, write a SQL query to find those employees who work as managers. Return complete information about the employees. Use co-related subquery.
Sample table: employees
Sample Solution:
SELECT *
FROM employees
WHERE emp_id IN
(SELECT manager_id
FROM employees);
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+-----------+------------+------------+---------+------------+-------- 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 67858 | SCARLET | ANALYST | 65646 | 1997-04-19 | 3100.00 | | 2001 69062 | FRANK | ANALYST | 65646 | 1991-12-03 | 3100.00 | | 2001 (6 rows)
Explanation:
The said query in SQL that retrieves all the employees from the 'employees' table who are listed as a manager in the manager_id column.
The subquery retrieves all the employees table where the emp_id column matches any of the manager_id values.
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 employees who are subordinates to Blaze.
Next SQL Exercise: Names of the employees and the manager of JONAS.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics