w3resource

SQL Exercise: Jobs 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)

Code Explanation:

The said query in SQL that retrieves job_id values from the 'job_history' table where the duration of a job (calculated as end_date - start_date) is greater than 300 days. The query then groups the results by job_id and returns only those groups that have at least two records (HAVING COUNT(*) >= 2).
The final result of this calculation will result in a list of the job_ids that have been held by at least two employees for more than 300 days.

Relational Algebra Expression:

Relational Algebra Expression: Display job ID for those jobs that were done by two or more for more than 300 days.

Relational Algebra Tree:

Relational Algebra Tree: Display job ID for those jobs that were done by two or more for more than 300 days.

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display job ID for those jobs that were done by two or more for more than 300 days - Duration

Rows:

Query visualization of Display job ID for those jobs that were done by two or more for more than 300 days - Rows

Cost:

Query visualization of Display job ID for those jobs that were done by two or more for more than 300 days - Cost

Contribute your code and comments through Disqus.

Previous SQL Exercise: Difference between highest and lowest salary for a job.
Next SQL Exercise: Country ID and number of cities in country has.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Difference between natural join and inner join

One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned-

Consider:

TableA                           TableB
+------------+----------+        +--------------------+    
|Column1     | Column2  |        |Column1  |  Column3 |
+-----------------------+        +--------------------+
| 1          |  2       |        | 1       |   3      |
+------------+----------+        +---------+----------+

The INNER JOIN of TableA and TableB on Column1 will return

SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1);
SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;
+------------+-----------+---------------------+    
| a.Column1  | a.Column2 | b.Column1| b.Column3|
+------------------------+---------------------+
| 1          |  2        | 1        |   3      |
+------------+-----------+----------+----------+

The NATURAL JOIN of TableA and TableB on Column1 will return:

SELECT * FROM TableA NATURAL JOIN TableB
+------------+----------+----------+    
|Column1     | Column2  | Column3  |
+-----------------------+----------+
| 1          |  2       |   3      |
+------------+----------+----------+

Ref: https://bit.ly/3AG5CId

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook