w3resource

SQL SORTING and FILTERING on HR Database: Display the ID for those employees who did two or more jobs in the past

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

22. From the following table, write a SQL query to find those employees who worked more than two jobs in the past. Return employee id.

Sample table : job_history


Sample Solution:

SELECT employee_id 
	FROM job_history 
		GROUP BY employee_id 
			HAVING COUNT(*) >=2;

Sample Output:

 employee_id
-------------
         101
         176
         200
(3 rows)

Relational Algebra Expression:

Relational Algebra Expression: Display the ID for those employees who did two or more jobs in the past.

Relational Algebra Tree:

Relational Algebra Tree: Display the ID for those employees who did two or more jobs in the past.

Practice Online


HR database model
HR database model

Query Visualization:

Duration:

Query visualization of Display the ID for those employees who did two or more jobs in the past - Duration

Rows:

Query visualization of Display the ID for those employees who did two or more jobs in the past - Rows

Cost:

Query visualization of Display the ID for those employees who did two or more jobs in the past - Cost

Contribute your code and comments through Disqus.

Previous: From the following table, write a SQL query to find those employees whose department numbers are included in 30 or 40 or 90. Return employee id, first name, job id, department id.
Next: 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.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

MySQL export schema without data

mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql

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