PostgreSQL JOINS: Make a join with a table employees and itself to find the name and hire date of the employees who hired after a specific employee

5. Write a query to make a join with a table employees and itself to find the name, including first_name and last_name and hire date for those employees who were hired after the employee Jones.

Sample Solution:

Code:

SELECT e.first_name, e.last_name, e.hire_date 
FROM employees e 
JOIN employees davies 
ON (davies.last_name = 'Jones') 
WHERE davies.hire_date < e.hire_date;

Sample table: employees

Output:

pg_exercises=# SELECT e.first_name, e.last_name, e.hire_date
pg_exercises-# FROM employees e
pg_exercises-# JOIN employees davies
pg_exercises-# ON (davies.last_name = 'Jones')
pg_exercises-# WHERE davies.hire_date < e.hire_date;

 first_name | last_name | hire_date
------------+-----------+------------
 Alana      | Walsh     | 1987-09-21
 Kevin      | Feeney    | 1987-09-22
 Donald     | OConnell  | 1987-09-23
 Douglas    | Grant     | 1987-09-24
 Jennifer   | Whalen    | 1987-09-25
 Michael    | Hartstein | 1987-09-26
 Pat        | Fay       | 1987-09-27
 Susan      | Mavris    | 1987-09-28
 Hermann    | Baer      | 1987-09-29
 Shelley    | Higgins   | 1987-09-30
 William    | Gietz     | 1987-10-01
(11 rows)

Relational Algebra Expression:

Relational Algebra Tree:

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 itself to find the employee id, last_name as Employee along with their manager_id and last name as Manager.
Next: Write a query to make a join with two tables employees and departments to get the department name and number of employees working in each department.