SQL SORTING and FILTERING on HR Database: Display job ID for those jobs that were done by two or more for more than 300 days

SQL SORTING and FILTERING on HR Database: Exercise-24 with Solution

24. From the following table, write a SQL query to find each job ids where two or more employees worked for more than 300 days. Return job id.

Sample table : job_history

Sample Solution:

SELECT job_id 
	FROM job_history 
		WHERE end_date-start_date >300 
			GROUP BY job_id 
				HAVING COUNT(*)>=2;

Sample Output:

   job_id
------------
 AC_ACCOUNT
 ST_CLERK
(2 rows)

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous: From the following table, write a SQL query to count the number of employees, sum of all salary, and difference between the highest salary and lowest salary by each job id. Return job_id, count, sum, salary_difference.
Next: From the following table, write a SQL query to count the number of cities in each country has. Return country ID and number of cities.