w3resource

SQL JOINS on HR Database: Display the employee ID, job name, number of days worked in for all those jobs in department 80

SQL JOINS on HR Database: Exercise-23 with Solution

23. From the following tables, write a SQL query to compute 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:

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

Sample Output:

employee_id		job_title		days
176			Sales Manager		364
176			Sales Representative 	282

Pictorial Presentation:

SQL Exercises: Display the employee ID, job name, number of days worked in for all those jobs in department 80.

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the employee ID, job name, number of days worked in for all those jobs in department 80 - Duration

Rows:

Query visualization of Display the employee ID, job name, number of days worked in for all those jobs in department 80 - Rows

Cost:

Query visualization of Display the employee ID, job name, number of days worked in for all those jobs in department 80 - Cost

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

Previous: From the following tables, write a SQL query to find the department name, full name (first and last name) of the manager and their city.
Next: From the following tables, write a SQL query to find full name (first and last name), and salary of those employees who work in any department located in 'London' city.

What is the difficulty level of this exercise?