PostgreSQL JOINS: Make a join with tables job_history and jobs to find the employee ID, job title number of days worked in a department which ID is 90

7. Write a query to make a join to find the employee ID, job title and number of days an employee worked, for all the employees who worked in a department which ID is 90.

Sample Solution:

Code:

SELECT employee_id, job_title, end_date-start_date Days 
FROM job_history 
NATURAL JOIN jobs 
WHERE department_id=90;

Sample table: employees

Output:

pg_exercises=# SELECT employee_id, job_title, end_date-start_date Days
pg_exercises-# FROM job_history
pg_exercises-# NATURAL JOIN jobs
pg_exercises-# WHERE department_id=90;
 employee_id |        job_title         | days
-------------+--------------------------+------
         200 | Administration Assistant | 2100
         200 | Public Accountant        | 1644
(2 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 departments to get the department name and number of employees working in each department.
Next: Write a query to make a join with two tables employees and departments to display the department ID, department name and the first name of the manager.