SQL subqueries on employee Database: Display the employee ID, name, job name, hire date, 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)

Practice Online

Structure of employee Database:

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

Previous: From the following table, write a SQL query to find the managers. Return complete information about the managers.
Next: From the following table, write a SQL query to find those employees who work as 'MANAGERS' and 'ANALYST' and working in ‘SYDNEY’ or ‘PERTH’ with an experience more than 5 years without receiving the commission. Sort the result-set in ascending order by department location. Return employee ID, employee name, salary, and department name.