﻿ SQL: Display details and experience of all the managers

# 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿