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:

-- This SQL query retrieves the employee ID, job title, and duration of employment in days for job history records associated with department ID 90.

SELECT employee_id, -- Selects the employee_id column
       job_title, -- Selects the job_title column
       end_date - start_date Days -- Calculates the duration of employment in days and labels it as "Days"
FROM job_history -- Specifies the first table from which to retrieve data, in this case, the job_history table
NATURAL JOIN jobs -- Performs a natural join with the jobs table to include associated job information
WHERE department_id = 90; -- Filters the rows to include only those associated with department ID 90

Explanation:

• This SQL query retrieves the employee ID, job title, and duration of employment in days for job history records associated with department ID 90.
• The SELECT statement selects the employee_id and job_title columns from the job_history table and calculates the duration of employment in days, labeling it as "Days".
• The FROM clause specifies the first table from which to retrieve data, which is the job_history table.
• A NATURAL JOIN operation is performed with the jobs table to include associated job information.
• The WHERE clause filters the rows to include only those associated with 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.