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.
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
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)
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.
What is the difficulty level of this exercise?