SQL Exercise: Display details and experience of all the managers

SQL subqueries on employee Database: Exercise-2 with Solution

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

2. From the following table, write a SQL query to compute the experience of all the managers. Return employee ID, employee name, job name, joining date, and experience.

Sample table: employees

Sample Solution:

SELECT emp_id,
       emp_name,
       job_name,
       hire_date,
       age(CURRENT_DATE, hire_date) "Experience"
FROM employees
WHERE emp_id IN
    (SELECT manager_id
     FROM employees);

Sample Output:

 emp_id | emp_name | job_name  | hire_date  |       Experience
--------+----------+-----------+------------+-------------------------
  68319 | KAYLING  | PRESIDENT | 1991-11-18 | 26 years 2 mons 17 days
  66928 | BLAZE    | MANAGER   | 1991-05-01 | 26 years 9 mons 4 days
  67832 | CLARE    | MANAGER   | 1991-06-09 | 26 years 7 mons 26 days
  65646 | JONAS    | MANAGER   | 1991-04-02 | 26 years 10 mons 3 days
  67858 | SCARLET  | ANALYST   | 1997-04-19 | 20 years 9 mons 16 days
  69062 | FRANK    | ANALYST   | 1991-12-03 | 26 years 2 mons 2 days
(6 rows)

Explanation:

The given query in SQL that returns a table of information about employees who are also managers, including their ID, name, job name, hire date, and experience from the 'employees' table.

The CURRENT_DATE function calculates the "Experience" as the difference between the current date and the "hire_date" of each employee.

The WHERE clause only includes those employees in the result set who are managers.

Alternative Solution:

Using EXISTS:

-- Selecting employee details for those who have a manager
SELECT emp_id,
       emp_name,
       job_name,
       hire_date,
       age(CURRENT_DATE, hire_date) AS "Experience"
FROM employees e
WHERE EXISTS
    (
        -- Subquery checks if there is at least one record
        -- in the "employees" table where the manager_id
        -- matches the emp_id of the outer query's employee
        SELECT 1
        FROM employees
        WHERE manager_id = e.emp_id
    );

Explanation:

The EXISTS subquery is used to check if there exists a record in the employees table where the manager_id matches the emp_id of the outer query's employee, filtering the results accordingly.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: Display all the details of managers.
Next SQL Exercise: Display the list in ascending order of location.