w3resource

SQL Exercise: Display all the details of managers

SQL subqueries on employee Database: Exercise-1 with Solution

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

1. From the following table, write a SQL query to find the managers. Return complete information about the managers.

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 selects all columns from the 'employees' table where the "emp_id" value is found in the results of a subquery. The subquery selects the "manager_id" values from the 'employees' table. The query likely returns a list of employees who are also managers.

Alternative Solutions:

Using EXISTS:


-- This query selects all columns from the 'employees' table, aliasing it as 'e'
SELECT *
-- It filters the results to include only those rows where there exists a record in the subquery
FROM employees e
-- The subquery checks for the existence of at least one record where 'manager_id' matches 'emp_id' from the outer query
WHERE EXISTS (
    SELECT 1
    FROM employees
    WHERE manager_id = e.emp_id
);
-- The main query selects all records from 'employees' where there exists a matching 'manager_id' for the current 'emp_id'

Explanation:

This query uses the EXISTS clause to check for the existence of rows in the subquery where the manager_id matches the emp_id of the outer query.

Using a Correlated Subquery:


-- This query selects all columns from the 'employees' table, aliasing it as 'e'
SELECT *
-- It filters the results to include only those rows where 'emp_id' is equal to ANY value in the subquery
FROM employees e
-- The subquery selects 'manager_id' values from the 'employees' table, excluding NULL values
WHERE e.emp_id = ANY (
    SELECT manager_id
    FROM employees
    WHERE manager_id IS NOT NULL
);
-- The main query selects all records from 'employees' where 'emp_id' matches ANY 'manager_id' value from the subquery

Explanation:

This query uses a correlated subquery with the ANY operator to compare each emp_id in the outer query with the manager_id in the subquery, returning rows where a match is found.

Practice Online


Structure of employee Database

employee database structure

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

Previous SQL Exercise: SQL Subqueries - Exercises, Practice, Solution
Next SQL Exercise: Display details and experience of all the managers.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.