PostgreSQL JOINS: Make a join with two tables employees and departments for all the managers who achieved a working experience is more than 15 years

13. Write a query to make a join with two tables employees and departments to display department name, first_name and last_name, hire date and salary for all the managers who achieved a working experience is more than 15 years.

Sample Solution:

Code:

SELECT department_name, first_name, last_name,
hire_date, salary,date_part('year',age(now(),hire_date)) Experience 
FROM departments w1 
JOIN employees w2 
ON (w1.manager_id = w2.employee_id)
WHERE date_part('year',age(now(),hire_date))>15;

Sample table: employees

Sample table: departments

Output:

pg_exercises-# SELECT department_name, first_name, last_name,
pg_exercises-# hire_date, salary,date_part('year',age(now(),hire_date)) Experience 
pg_exercises-# FROM departments w1
pg_exercises-# JOIN employees w2
pg_exercises-# ON (w1.manager_id = w2.employee_id)
pg_exercises-# WHERE date_part('year',age(now(),hire_date))>15;
 department_name  | first_name | last_name | hire_date  |  salary  | experience
------------------+------------+-----------+------------+----------+------------
 Administration   | Jennifer   | Whalen    | 1987-09-25 |  4400.00 |         29
 Marketing        | Michael    | Hartstein | 1987-09-26 | 13000.00 |         29
 Purchasing       | Den        | Raphaely  | 1987-07-01 | 11000.00 |         29
 Human Resources  | Susan      | Mavris    | 1987-09-28 |  6500.00 |         29
 Shipping         | Adam       | Fripp     | 1987-07-08 |  8200.00 |         29
 IT               | Alexander  | Hunold    | 1987-06-20 |  9000.00 |         29
 Public Relations | Hermann    | Baer      | 1987-09-29 | 10000.00 |         29
 Sales            | John       | Russell   | 1987-08-01 | 14000.00 |         29
 Executive        | Steven     | King      | 1987-06-17 | 24000.00 |         29
 Finance          | Nancy      | Greenberg | 1987-06-25 | 12000.00 |         29
 Accounting       | Shelley    | Higgins   | 1987-09-30 | 12000.00 |         29
(11 rows)

Practice Online

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

Previous: Write a query to make a join with two tables employees and jobs to display the job title, employee name, and the difference between salary and the minimum salary of the employees.
Next: PostgreSQL Subquery - Exercises, Practice, Solution