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
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'));
job_id job_title min_salary max_salary SA_REP Sales Representative 6000 12000
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.
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
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
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