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:

-- This SQL query retrieves department name, employee details, and years of experience for employees whose experience is greater than 15 years.

SELECT department_name, -- Selects the department_name column from the departments table
       first_name, -- Selects the first_name column from the employees table
       last_name, -- Selects the last_name column from the employees table
       hire_date, -- Selects the hire_date column from the employees table
salary, -- Selects the salary column from the employees table
       date_part('year', age(now(), hire_date)) Experience -- Calculates the years of experience for each employee and labels it as "Experience"
FROM departments w1 -- Specifies the first table from which to retrieve data, aliasing it as 'w1'
JOIN employees w2 -- Joins the departments table with the employees table, specifying the second table and aliasing it as 'w2'
ON (w1.manager_id = w2.employee_id) -- Specifies the join condition based on the manager_id and employee_id columns
WHERE date_part('year', age(now(), hire_date)) > 15; -- Filters the rows to include only those where the years of experience are greater than 15

Explanation:

• This SQL query retrieves department name, employee details, and years of experience for employees whose experience is greater than 15 years.
• The SELECT statement selects department name, employee first name, last name, hire date, salary, and calculates the years of experience for each employee.
• The FROM clause specifies the first table from which to retrieve data, which is the departments table, aliasing it as 'w1'.
• A JOIN operation is performed with the employees table, specifying the second table and aliasing it as 'w2'.
• The ON clause specifies the join condition where the manager_id from the departments table matches the employee_id from the employees table.
• The WHERE clause filters the rows to include only those where the years of experience are greater than 15, calculated using the age() function.

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