MySQL Date and Time Exercises: Query to get department name, manager name, and salary of the manager for all managers whose experience is more than 5 years

MySQL Date Time: Exercise-18 with Solution

Write a MySQL query to get department name, manager name, and salary of the manager for all managers whose experience is more than 5 years.

Sample table: department

Code:

-- This SQL query retrieves the department name, first name, and salary of employees who have been working for more than 5 years and are also managers of their respective departments.

SELECT 
    DEPARTMENT_NAME, -- Selecting the 'DEPARTMENT_NAME' column from the 'departments' table.
    FIRST_NAME, -- Selecting the 'FIRST_NAME' column from the 'employees' table.
    SALARY -- Selecting the 'SALARY' column from the 'employees' table.
FROM 
departments D -- Specifying the 'departments' table and aliasing it as 'D'.
JOIN 
employees E -- Joining the 'employees' table and aliasing it as 'E'.
ON 
    (D.MANAGER_ID=E.MANAGER_ID) -- Joining the 'departments' and 'employees' tables based on the manager ID to associate managers with their departments.
WHERE 
    (SYSDATE()-HIRE_DATE) / 365 > 5; -- Filtering the result to include only records where the duration of employment is more than 5 years.
 

Explanation:

• This SQL query retrieves the department name, first name, and salary of employees who have been working for more than 5 years and are also managers of their respective departments.
• It performs an inner join between the 'departments' and 'employees' tables based on the manager ID to associate managers with their departments.
• The WHERE clause filters the result set to include only records where the duration of employment (calculated by subtracting the hire date from the current date and dividing by 365) is more than 5 years.

Sample Output:

DEPARTMENT_NAME		FIRST_NAME	SALARY
Marketing		Pat		6000
Purchasing		Alexander	3100
Purchasing		Shelli		2900
Purchasing		Sigal		2800
Purchasing		Guy		2600
Purchasing		Karen		2500
Shipping		Laura		3300
Shipping		Mozhe		2800
Shipping		James		2500
Shipping		TJ		2100
Shipping		Nandita		4200
Shipping		Alexis		4100
Shipping		Julia		3400
Shipping		Anthony		3000
IT			Bruce		6000
IT			David		4800
IT			Valli		4800
IT			Diana		4200
Sales			Peter		10000
Sales			David		9500
Sales			Peter		9000
Sales			Christopher	8000
Sales			Nanette		7500
Sales			Oliver		7000
Executive		Neena		17000
Executive		Lex		17000
Executive		Den		11000
Executive		Matthew		8000
Executive		Adam		8200
Executive		Payam		7900
Executive		Shanta		6500
Executive		Kevin		5800
Executive		John		14000
Executive		Karen		13500
Executive		Alberto		12000
Executive		Gerald		11000
Executive		Eleni		10500
Executive		Michael		13000
Finance			Daniel		9000
Finance			John		8200
Finance			Ismael		7700
Finance			Jose Manuel	7800
Finance			Luis		6900
Accounting		William		8300
Treasury		Steven		24000
Corporate Tax		Steven		24000
Control And Credit	Steven		24000
Shareholder Services	Steven		24000
Benefits		Steven		24000
Manufacturing		Steven		24000
Construction		Steven		24000
Contracting		Steven		24000
Operations		Steven		24000
IT Support		Steven		24000
NOC			Steven		24000
IT Helpdesk		Steven		24000
Government Sales	Steven		24000
Retail Sales		Steven		24000
Recruiting		Steven		24000
Payroll			Steven		24000

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 first name of employees who joined in 1987.
Next:Write a MySQL query to get employee ID, last name, and date of first salary of the employees.