SQL Exercise: Number of days worked for all jobs in department 80

SQL JOINS on HR Database: Exercise-23 with Solution

23. From the following tables, write a SQL query to calculate the number of days worked by employees in a department of ID 80. Return employee ID, job title, number of days worked.

Sample table: jobs

Sample table: job_history

Sample Solution:

-- Selecting specific columns (employee_id, job_title, end_date - start_date DAYS) from the 'job_history' table
SELECT employee_id, job_title, end_date - start_date DAYS 

-- Performing a NATURAL JOIN between the 'job_history' table and the 'jobs' table, where the join is implicitly based on columns with the same name in both tables
FROM job_history 

NATURAL JOIN jobs 

-- Filtering rows based on the condition that 'department_id' is equal to 80
WHERE department_id = 80;

Sample Output:

employee_id		job_title		days
176			Sales Manager		364
176			Sales Representative 	282

Code Explanation:

The said query in SQL that will return a list of employee IDs, job titles, and the number of days each employee worked in the department with department ID 80, based on data from the job_history and jobs tables.
The JOIN clause joins the job_history table with the jobs table using the NATURAL JOIN operator, which automatically matches job_id columns with the same name in both tables.
The WHERE clause filters the results to only include rows where the department ID is 80.

visual Presentation:

Alternative Solutions:

Using INNER JOIN with Explicit Column Names and WHERE Clause:

SELECT job_history.employee_id, jobs.job_title, (job_history.end_date - job_history.start_date) AS DAYS 
FROM job_history
JOIN jobs ON job_history.job_id = jobs.job_id
WHERE job_history.department_id = 80;

Explanation:

This query uses INNER JOINs with explicitly specifies column names. It calculates the difference in days between the end date and start date. The WHERE clause filters the results to include only records with department_id equal to 80.

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Display the department, manager name, and their city.
Next SQL Exercise: Employees who work in any department located in London.