MySQL Date and Time Exercises: Query to get first name, hire date and experience of the employees
MySQL Date Time: Exercise-20 with Solution
Write a MySQL query to get first name, hire date and experience of the employees.
Sample table: employees
Code:
-- This SQL query retrieves the first name of employees, the current date, their hire date, and calculates their years of employment.
SELECT
FIRST_NAME, -- Selecting the 'FIRST_NAME' column from the 'employees' table.
SYSDATE(), -- Retrieving the current date using the SYSDATE() function.
HIRE_DATE, -- Selecting the 'HIRE_DATE' column from the 'employees' table.
DATEDIFF(SYSDATE(), hire_date)/365 -- Calculating the difference in days between the current date and the hire date, then dividing by 365 to get years of employment.
FROM
employees; -- Specifying the 'employees' table.
Explanation:
- This SQL query retrieves the first name of employees, the current date, their hire date, and calculates their years of employment.
- The SYSDATE() function retrieves the current date.
- The DATEDIFF() function calculates the difference in days between the current date and the hire date.
- The result of DATEDIFF() is divided by 365 to convert days into years, providing the years of employment.
- The query returns a result set containing the first name of employees, the current date, their hire date, and their years of employment.
Relational Algebra Expression:
Relational Algebra Tree:
Sample Output:
FIRST_NAME SYSDATE() HIRE_DATE DATEDIFF( SYSDATE(), hire_date )/365 Steven 2017-08-30T09:46:50.000Z 1987-06-17T04:00:00.000Z 30.2247 Neena 2017-08-30T09:46:50.000Z 1987-06-18T04:00:00.000Z 30.2219 Lex 2017-08-30T09:46:50.000Z 1987-06-19T04:00:00.000Z 30.2192 Alexander 2017-08-30T09:46:50.000Z 1987-06-20T04:00:00.000Z 30.2164 Bruce 2017-08-30T09:46:50.000Z 1987-06-21T04:00:00.000Z 30.2137 David 2017-08-30T09:46:50.000Z 1987-06-22T04:00:00.000Z 30.211 Valli 2017-08-30T09:46:50.000Z 1987-06-23T04:00:00.000Z 30.2082 Diana 2017-08-30T09:46:50.000Z 1987-06-24T04:00:00.000Z 30.2055 Nancy 2017-08-30T09:46:50.000Z 1987-06-25T04:00:00.000Z 30.2027 Daniel 2017-08-30T09:46:50.000Z 1987-06-26T04:00:00.000Z 30.2 John 2017-08-30T09:46:50.000Z 1987-06-27T04:00:00.000Z 30.1973 Ismael 2017-08-30T09:46:50.000Z 1987-06-28T04:00:00.000Z 30.1945 Jose Manuel 2017-08-30T09:46:50.000Z 1987-06-29T04:00:00.000Z 30.1918 Luis 2017-08-30T09:46:50.000Z 1987-06-30T04:00:00.000Z 30.189 Den 2017-08-30T09:46:50.000Z 1987-07-01T04:00:00.000Z 30.1863 Alexander 2017-08-30T09:46:50.000Z 1987-07-02T04:00:00.000Z 30.1836 Shelli 2017-08-30T09:46:50.000Z 1987-07-03T04:00:00.000Z 30.1808 Sigal 2017-08-30T09:46:50.000Z 1987-07-04T04:00:00.000Z 30.1781 Guy 2017-08-30T09:46:50.000Z 1987-07-05T04:00:00.000Z 30.1753 Karen 2017-08-30T09:46:50.000Z 1987-07-06T04:00:00.000Z 30.1726 Matthew 2017-08-30T09:46:50.000Z 1987-07-07T04:00:00.000Z 30.1699 Adam 2017-08-30T09:46:50.000Z 1987-07-08T04:00:00.000Z 30.1671 Payam 2017-08-30T09:46:50.000Z 1987-07-09T04:00:00.000Z 30.1644 Shanta 2017-08-30T09:46:50.000Z 1987-07-10T04:00:00.000Z 30.1616 Kevin 2017-08-30T09:46:50.000Z 1987-07-11T04:00:00.000Z 30.1589 Julia 2017-08-30T09:46:50.000Z 1987-07-12T04:00:00.000Z 30.1562 Irene 2017-08-30T09:46:50.000Z 1987-07-13T04:00:00.000Z 30.1534 James 2017-08-30T09:46:50.000Z 1987-07-14T04:00:00.000Z 30.1507 Steven 2017-08-30T09:46:50.000Z 1987-07-15T04:00:00.000Z 30.1479 Laura 2017-08-30T09:46:50.000Z 1987-07-16T04:00:00.000Z 30.1452 Mozhe 2017-08-30T09:46:50.000Z 1987-07-17T04:00:00.000Z 30.1425 James 2017-08-30T09:46:50.000Z 1987-07-18T04:00:00.000Z 30.1397 TJ 2017-08-30T09:46:50.000Z 1987-07-19T04:00:00.000Z 30.137 Jason 2017-08-30T09:46:50.000Z 1987-07-20T04:00:00.000Z 30.1342 Michael 2017-08-30T09:46:50.000Z 1987-07-21T04:00:00.000Z 30.1315 Ki 2017-08-30T09:46:50.000Z 1987-07-22T04:00:00.000Z 30.1288 Hazel 2017-08-30T09:46:50.000Z 1987-07-23T04:00:00.000Z 30.126 Renske 2017-08-30T09:46:50.000Z 1987-07-24T04:00:00.000Z 30.1233 Stephen 2017-08-30T09:46:50.000Z 1987-07-25T04:00:00.000Z 30.1205 John 2017-08-30T09:46:50.000Z 1987-07-26T04:00:00.000Z 30.1178 Joshua 2017-08-30T09:46:50.000Z 1987-07-27T04:00:00.000Z 30.1151 Trenna 2017-08-30T09:46:50.000Z 1987-07-28T04:00:00.000Z 30.1123 Curtis 2017-08-30T09:46:50.000Z 1987-07-29T04:00:00.000Z 30.1096 Randall 2017-08-30T09:46:50.000Z 1987-07-30T04:00:00.000Z 30.1068 Peter 2017-08-30T09:46:50.000Z 1987-07-31T04:00:00.000Z 30.1041 John 2017-08-30T09:46:50.000Z 1987-08-01T04:00:00.000Z 30.1014 Karen 2017-08-30T09:46:50.000Z 1987-08-02T04:00:00.000Z 30.0986 Alberto 2017-08-30T09:46:50.000Z 1987-08-03T04:00:00.000Z 30.0959 Gerald 2017-08-30T09:46:50.000Z 1987-08-04T04:00:00.000Z 30.0932 Eleni 2017-08-30T09:46:50.000Z 1987-08-05T04:00:00.000Z 30.0904 Peter 2017-08-30T09:46:50.000Z 1987-08-06T04:00:00.000Z 30.0877 David 2017-08-30T09:46:50.000Z 1987-08-07T04:00:00.000Z 30.0849 Peter 2017-08-30T09:46:50.000Z 1987-08-08T04:00:00.000Z 30.0822 Christopher 2017-08-30T09:46:50.000Z 1987-08-09T04:00:00.000Z 30.0795 Nanette 2017-08-30T09:46:50.000Z 1987-08-10T04:00:00.000Z 30.0767 Oliver 2017-08-30T09:46:50.000Z 1987-08-11T04:00:00.000Z 30.074 Janette 2017-08-30T09:46:50.000Z 1987-08-12T04:00:00.000Z 30.0712 Patrick 2017-08-30T09:46:50.000Z 1987-08-13T04:00:00.000Z 30.0685 Allan 2017-08-30T09:46:50.000Z 1987-08-14T04:00:00.000Z 30.0658 Lindsey 2017-08-30T09:46:50.000Z 1987-08-15T04:00:00.000Z 30.063 Louise 2017-08-30T09:46:50.000Z 1987-08-16T04:00:00.000Z 30.0603 Sarath 2017-08-30T09:46:50.000Z 1987-08-17T04:00:00.000Z 30.0575 Clara 2017-08-30T09:46:50.000Z 1987-08-18T04:00:00.000Z 30.0548 Danielle 2017-08-30T09:46:50.000Z 1987-08-19T04:00:00.000Z 30.0521 Mattea 2017-08-30T09:46:50.000Z 1987-08-20T04:00:00.000Z 30.0493 David 2017-08-30T09:46:50.000Z 1987-08-21T04:00:00.000Z 30.0466 Sundar 2017-08-30T09:46:50.000Z 1987-08-22T04:00:00.000Z 30.0438 Amit 2017-08-30T09:46:50.000Z 1987-08-23T04:00:00.000Z 30.0411 Lisa 2017-08-30T09:46:50.000Z 1987-08-24T04:00:00.000Z 30.0384 Harrison 2017-08-30T09:46:50.000Z 1987-08-25T04:00:00.000Z 30.0356 Tayler 2017-08-30T09:46:50.000Z 1987-08-26T04:00:00.000Z 30.0329 William 2017-08-30T09:46:50.000Z 1987-08-27T04:00:00.000Z 30.0301 Elizabeth 2017-08-30T09:46:50.000Z 1987-08-28T04:00:00.000Z 30.0274 Sundita 2017-08-30T09:46:50.000Z 1987-08-29T04:00:00.000Z 30.0247 Ellen 2017-08-30T09:46:50.000Z 1987-08-30T04:00:00.000Z 30.0219 Alyssa 2017-08-30T09:46:50.000Z 1987-08-31T04:00:00.000Z 30.0192 Jonathon 2017-08-30T09:46:50.000Z 1987-09-01T04:00:00.000Z 30.0164 Jack 2017-08-30T09:46:50.000Z 1987-09-02T04:00:00.000Z 30.0137 Kimberely 2017-08-30T09:46:50.000Z 1987-09-03T04:00:00.000Z 30.011 Charles 2017-08-30T09:46:50.000Z 1987-09-04T04:00:00.000Z 30.0082 Winston 2017-08-30T09:46:50.000Z 1987-09-05T04:00:00.000Z 30.0055 Jean 2017-08-30T09:46:50.000Z 1987-09-06T04:00:00.000Z 30.0027 Martha 2017-08-30T09:46:50.000Z 1987-09-07T04:00:00.000Z 30 Girard 2017-08-30T09:46:50.000Z 1987-09-08T04:00:00.000Z 29.9973 Nandita 2017-08-30T09:46:50.000Z 1987-09-09T04:00:00.000Z 29.9945 Alexis 2017-08-30T09:46:50.000Z 1987-09-10T04:00:00.000Z 29.9918 Julia 2017-08-30T09:46:50.000Z 1987-09-11T04:00:00.000Z 29.989 Anthony 2017-08-30T09:46:50.000Z 1987-09-12T04:00:00.000Z 29.9863 Kelly 2017-08-30T09:46:50.000Z 1987-09-13T04:00:00.000Z 29.9836 Jennifer 2017-08-30T09:46:50.000Z 1987-09-14T04:00:00.000Z 29.9808 Timothy 2017-08-30T09:46:50.000Z 1987-09-15T04:00:00.000Z 29.9781 Randall 2017-08-30T09:46:50.000Z 1987-09-16T04:00:00.000Z 29.9753 Sarah 2017-08-30T09:46:50.000Z 1987-09-17T04:00:00.000Z 29.9726 Britney 2017-08-30T09:46:50.000Z 1987-09-18T04:00:00.000Z 29.9699 Samuel 2017-08-30T09:46:50.000Z 1987-09-19T04:00:00.000Z 29.9671 Vance 2017-08-30T09:46:50.000Z 1987-09-20T04:00:00.000Z 29.9644 Alana 2017-08-30T09:46:50.000Z 1987-09-21T04:00:00.000Z 29.9616 Kevin 2017-08-30T09:46:50.000Z 1987-09-22T04:00:00.000Z 29.9589 Donald 2017-08-30T09:46:50.000Z 1987-09-23T04:00:00.000Z 29.9562 Douglas 2017-08-30T09:46:50.000Z 1987-09-24T04:00:00.000Z 29.9534 Jennifer 2017-08-30T09:46:50.000Z 1987-09-25T04:00:00.000Z 29.9507 Michael 2017-08-30T09:46:50.000Z 1987-09-26T04:00:00.000Z 29.9479 Pat 2017-08-30T09:46:50.000Z 1987-09-27T04:00:00.000Z 29.9452 Susan 2017-08-30T09:46:50.000Z 1987-09-28T04:00:00.000Z 29.9425 Hermann 2017-08-30T09:46:50.000Z 1987-09-29T04:00:00.000Z 29.9397 Shelley 2017-08-30T09:46:50.000Z 1987-09-30T04:00:00.000Z 29.937 William 2017-08-30T09:46:50.000Z 1987-10-01T04:00:00.000Z 29.9342
Pictorial Presentation of the above query:
MySQL Code Editor:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous:Write a MySQL query to get employee ID, last name, and date of first salary of the employees.
Next:Write a MySQL query to get the department ID, year, and number of employees joined.
What is the difficulty level of this exercise?
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://w3resource.com/mysql-exercises/date-time-exercises/write-a-query-to-get-first-name-hire-date-and-experience-of-the-employees.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics