SQL Exercises: Employees worked as a Sales Representative in the past
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
+------------+---------------------------------+------------+------------+ | JOB_ID | JOB_TITLE | MIN_SALARY | MAX_SALARY | +------------+---------------------------------+------------+------------+ | AD_PRES | President | 20080 | 40000 | | AD_VP | Administration Vice President | 15000 | 30000 | | AD_ASST | Administration Assistant | 3000 | 6000 | | FI_MGR | Finance Manager | 8200 | 16000 | | FI_ACCOUNT | Accountant | 4200 | 9000 | | AC_MGR | Accounting Manager | 8200 | 16000 | | AC_ACCOUNT | Public Accountant | 4200 | 9000 | | SA_MAN | Sales Manager | 10000 | 20080 | | SA_REP | Sales Representative | 6000 | 12008 | | PU_MAN | Purchasing Manager | 8000 | 15000 | ......... +------------+---------------------------------+------------+------------+
Sample table: employees
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | 100 | Steven | King | SKING | 515.123.4567 | 2003-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 | | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 2005-09-21 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 2001-01-13 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 2006-01-03 | IT_PROG | 9000.00 | 0.00 | 102 | 60 | | 104 | Bruce | Ernst | BERNST | 590.423.4568 | 2007-05-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60 | | 105 | David | Austin | DAUSTIN | 590.423.4569 | 2005-06-25 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 2006-02-05 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 2007-02-07 | IT_PROG | 4200.00 | 0.00 | 103 | 60 | | 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 2002-08-17 | FI_MGR | 12008.00 | 0.00 | 101 | 100 | | 109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 2002-08-16 | FI_ACCOUNT | 9000.00 | 0.00 | 108 | 100 | | 110 | John | Chen | JCHEN | 515.124.4269 | 2005-09-28 | FI_ACCOUNT | 8200.00 | 0.00 | 108 | 100 | .................... +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
Sample table: job_history
+-------------+------------+------------+------------+---------------+ | EMPLOYEE_ID | START_DATE | END_DATE | JOB_ID | DEPARTMENT_ID | +-------------+------------+------------+------------+---------------+ | 102 | 2001-01-13 | 2006-07-24 | IT_PROG | 60 | | 101 | 1997-09-21 | 2001-10-27 | AC_ACCOUNT | 110 | | 101 | 2001-10-28 | 2005-03-15 | AC_MGR | 110 | | 201 | 2004-02-17 | 2007-12-19 | MK_REP | 20 | | 114 | 2006-03-24 | 2007-12-31 | ST_CLERK | 50 | | 122 | 2007-01-01 | 2007-12-31 | ST_CLERK | 50 | | 200 | 1995-09-17 | 2001-06-17 | AD_ASST | 90 | | 176 | 2006-03-24 | 2006-12-31 | SA_REP | 80 | | 176 | 2007-01-01 | 2007-12-31 | SA_MAN | 80 | | 200 | 2002-07-01 | 2006-12-31 | AC_ACCOUNT | 90 | +-------------+------------+------------+------------+---------------+
Sample Solution:
-- Selecting all columns (*) from the 'jobs' table
SELECT *
-- Filtering rows based on the condition that the 'job_id' is in the result set of a nested subquery
FROM jobs
-- Subquery to find 'job_id' values where the 'employee_id' is in the result set of another subquery in the 'employees' table
WHERE job_id IN
( SELECT job_id
FROM employees
-- Subquery to find 'employee_id' values where the 'job_id' is 'SA_REP' in the 'job_history' table
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:
Alternative Solutions:
Using EXISTS:
SELECT j.*
FROM jobs j
WHERE EXISTS (
SELECT 1
FROM employees e
JOIN job_history jh ON e.employee_id = jh.employee_id
WHERE j.job_id = e.job_id AND jh.job_id = 'SA_REP'
);
Using JOINs:
SELECT DISTINCT j.*
FROM jobs j
JOIN employees e ON j.job_id = e.job_id
JOIN job_history jh ON e.employee_id = jh.employee_id
WHERE jh.job_id = 'SA_REP';
Using a Scalar Subquery in the WHERE Clause:
SELECT *
FROM jobs
WHERE job_id IN (
SELECT DISTINCT job_id
FROM employees
WHERE employee_id IN (
SELECT employee_id
FROM job_history
WHERE job_id = 'SA_REP'
)
);
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.