MySQL Joins Exercises: Display the name, hire date, salary of the manager for all managers whose experience is more than 15 years

MySQL Joins: Exercise-13 with Solution

Write a query to display the first name, last name, hire date, salary of the manager for all managers whose experience is more than 15 years.

Sample table: employees

Sample table: departments

Code:

SELECT first_name, last_name, hire_date, salary, 
(DATEDIFF(now(), hire_date))/365 Experience 
FROM departments d JOIN employees e 
ON (d.manager_id = e.employee_id) 
WHERE (DATEDIFF(now(), hire_date))/365>15;

Sample Output:

first_name	last_name	hire_date			salary	Experience
Steven		King		1987-06-17T04:00:00.000Z	24000	30.1699
Alexander	Hunold		1987-06-20T04:00:00.000Z	9000	30.1616
Nancy		Greenberg	1987-06-25T04:00:00.000Z	12000	30.1479
Den		Raphaely	1987-07-01T04:00:00.000Z	11000	30.1315
Adam		Fripp		1987-07-08T04:00:00.000Z	8200	30.1123
John		Russell		1987-08-01T04:00:00.000Z	14000	30.0466
Jennifer	Whalen		1987-09-25T04:00:00.000Z	4400	29.8959
Michael		Hartstein	1987-09-26T04:00:00.000Z	13000	29.8932
Susan		Mavris		1987-09-28T04:00:00.000Z	6500	29.8877
Hermann		Baer		1987-09-29T04:00:00.000Z	10000	29.8849
Shelley		Higgins		1987-09-30T04:00:00.000Z	12000	29.8822

MySQL Code Editor:

Structure of 'hr' database :

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

Previous:Write a query to display the job history that were done by any employee who is currently drawing more than 10000 of salary.
Next:Date and Time functions