MySQL Subquery Exercises: Find the name of the employees who have a manager and worked in a USA based department
MySQL Subquery: Exercise-3 with Solution
Write a MySQL query to find the name (first_name, last_name) of the employees who have a manager and worked in a USA based department.
Sample table: employees
Sample table : departments
Sample table : locations
Code:
-- Selecting the first name and last name of employees
SELECT first_name, last_name
-- Selecting data from the employees table
FROM employees
-- Filtering the result set to include only employees whose manager_id is in the set of employee_ids
-- where the department_id is in the set of department_ids associated with locations in the US
WHERE manager_id in
(SELECT employee_id
-- Subquery to select employee_ids from the employees table
FROM employees
-- Filtering the employee_ids to include only those associated with departments
-- where the location_id is in the set of location_ids associated with countries having country_id 'US'
WHERE department_id
IN
(SELECT department_id
-- Subquery to select department_ids from the departments table
FROM departments
-- Filtering the department_ids to include only those associated with locations
-- where the country_id is 'US'
WHERE location_id
IN
(SELECT location_id
-- Subquery to select location_ids from the locations table
FROM locations
-- Filtering the location_ids to include only those associated with countries
-- having country_id 'US'
WHERE country_id='US')
)
);
Explanation:
- This MySQL code selects the first name and last name of employees from a table named "employees".
- It filters the results to only include employees whose manager_id is in the set of employee_ids where the department_id is in the set of department_ids associated with locations in the US.
- This is achieved by using nested subqueries:
- The innermost subquery selects location_ids from the "locations" table where the country_id is 'US'.
- The next subquery selects department_ids from the "departments" table where the location_id is in the set obtained from the inner subquery.
- The subsequent subquery selects employee_ids from the "employees" table where the department_id is in the set obtained from the previous subquery.
- Finally, the outermost query selects the first name and last name of employees where the manager_id is in the set obtained from the inner subquery.
MySQL Subquery Syntax:
operand comparison_operator operand IN (subquery) operand comparison_operator SOME (subquery)
Where comparison_operator is one of these operators
= > < >= <= <> !=
and IN operator checks whether a value is within a set of values.
For example :
mysql> SELECT 2 IN (0,3,5,7);
-> 0
mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
-> 1
When used with a subquery, the word IN is an alias for = ANY. Thus, these two statements are the same:
SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);
IN and = ANY are not synonyms when used with an expression list. IN can take an expression list, but = ANY cannot.
MySQL Code Editor:
Structure of 'hr' database :
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous:Write a MySQL query to find the name (first_name, last_name) of all employees who works in the IT department.
Next:Write a MySQL query to find the name (first_name, last_name) of the employees who are managers.
What is the difficulty level of this exercise?
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics