w3resource

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.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.