﻿ SQL - Employees worked as a Sales Representative in the past

# 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:

## 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

## SQL: Tips of the Day

MySQL select 10 random rows from 600K rows fast:

```SELECT name
FROM random AS r1 JOIN
(SELECT CEIL(RAND() *
(SELECT MAX(id)
FROM random)) AS id)
AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1
```

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

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