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 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:
SELECT DEPARTMENT_NAME, FIRST_NAME, SALARY
FROM departments D
JOIN employees E
ON (D.MANAGER_ID=E.MANAGER_ID)
WHERE (SYSDATE()-HIRE_DATE) / 365 > 5;
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 query to get first name of employees who joined in 1987.
Next:Write a query to get employee ID, last name, and date of first salary of the employees.
What is the difficulty level of this exercise?
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework