w3resource

SQL Exercise: List employees in ascending order on their experiences

SQL employee Database: Exercise-80 with Solution

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

80. From the following table, write a SQL query to list the employee ID, name, hire date, current date and experience of the employees in ascending order on their experiences.

Pictorial Presentation:

SQL exercises on employee Database: List the employee id, name, hire_date, current date and experience of the employees in ascending order on their experiences

Sample table: employees


Sample Solution:

SELECT emp_id,
       emp_name,
       hire_date,
       CURRENT_DATE,
       age(CURRENT_DATE, hire_date) EXP
FROM employees
ORDER BY EXP ASC;

Sample Output:

 emp_id | emp_name | hire_date  |    date    |           exp
--------+----------+------------+------------+-------------------------
  68736 | ADNRES   | 1997-05-23 | 2018-02-01 | 20 years 8 mons 9 days
  67858 | SCARLET  | 1997-04-19 | 2018-02-01 | 20 years 9 mons 12 days
  69324 | MARKER   | 1992-01-23 | 2018-02-01 | 26 years 9 days
  69062 | FRANK    | 1991-12-03 | 2018-02-01 | 26 years 1 mon 29 days
  69000 | JULIUS   | 1991-12-03 | 2018-02-01 | 26 years 1 mon 29 days
  68319 | KAYLING  | 1991-11-18 | 2018-02-01 | 26 years 2 mons 13 days
  66564 | MADDEN   | 1991-09-28 | 2018-02-01 | 26 years 4 mons 3 days
  68454 | TUCKER   | 1991-09-08 | 2018-02-01 | 26 years 4 mons 23 days
  67832 | CLARE    | 1991-06-09 | 2018-02-01 | 26 years 7 mons 22 days
  66928 | BLAZE    | 1991-05-01 | 2018-02-01 | 26 years 9 mons
  65646 | JONAS    | 1991-04-02 | 2018-02-01 | 26 years 9 mons 29 days
  65271 | WADE     | 1991-02-22 | 2018-02-01 | 26 years 11 mons 7 days
  64989 | ADELYN   | 1991-02-20 | 2018-02-01 | 26 years 11 mons 9 days
  63679 | SANDRINE | 1990-12-18 | 2018-02-01 | 27 years 1 mon 14 days
(14 rows)

Explanation:

The said query in SQL that selects the emp_id, emp_name, hire_date, CURRENT_DATE, and EXP columns for each employee in the employees table, sorted in ascending order by their years of experience.

The age() function calculates the difference in years between the hire_date and CURRENT_DATE and the resulting column is aliased as EXP.

Practice Online


Sample Database: employee

employee database structure

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

Previous SQL Exercises: Employees work as SALESMEN, sorted by their salary.
Next SQL Exercise: Sort employees by designation, joining after 1991.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql-exercises/employee-database-exercise/sql-employee-database-exercise-80.php