SQL Exercises: Employees worked as a Sales Representative in the past

SQL SUBQUERY: Exercise-51 with Solution

From the following table, write a SQL query to find employees who have previously worked as 'Sales Representatives'. Return all the fields of jobs.

Sample table: jobs

Sample table: employees

Sample table: job_history

Sample Solution:

SELECT * 
	FROM jobs 
		WHERE job_id IN 
		(SELECT job_id 
			FROM employees 
				WHERE employee_id IN 
        (SELECT employee_id 
			FROM job_history 
				WHERE job_id='SA_REP'));

Sample Output:

job_id	job_title		min_salary	max_salary
SA_REP	Sales Representative	6000		12000

Code Explanation:

The said query in SQL that selects all the rows from the 'jobs' table where the job ID is held by at least one employee whose job history includes the "SA_REP" job. It does this by using a subquery to first identify the employee IDs that have held the "SA_REP" job based on the 'job_history' table.
It is then followed by another subquery in the query that identifies which job IDs correspond to which employee IDs based on the data in the table 'employees'. Finally, it selects all the rows from the 'jobs' table where the job ID is in the list of identified job IDs.

Visual Presentation:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Manager who is supervising 4 or more employees.
Next SQL Exercise: Employees who earn second lowest salary of all.