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
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | 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 | 1987-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 | | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 1987-06-18 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 1987-06-19 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 1987-06-20 | IT_PROG | 9000.00 | 0.00 | 102 | 60 | | 104 | Bruce | Ernst | BERNST | 590.423.4568 | 1987-06-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60 | | 105 | David | Austin | DAUSTIN | 590.423.4569 | 1987-06-22 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 1987-06-23 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 1987-06-24 | IT_PROG | 4200.00 | 0.00 | 103 | 60 | | 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 1987-06-25 | FI_MGR | 12000.00 | 0.00 | 101 | 100 | ......... | 206 | William | Gietz | WGIETZ | 515.123.8181 | 1987-10-01 | AC_ACCOUNT | 8300.00 | 0.00 | 205 | 110 | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
Sample table: departments
+---------------+----------------------+------------+-------------+ | DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID | +---------------+----------------------+------------+-------------+ | 10 | Administration | 200 | 1700 | | 20 | Marketing | 201 | 1800 | | 30 | Purchasing | 114 | 1700 | | 40 | Human Resources | 203 | 2400 | | 50 | Shipping | 121 | 1500 | | 60 | IT | 103 | 1400 | | 70 | Public Relations | 204 | 2700 | | 80 | Sales | 145 | 2500 | | 90 | Executive | 100 | 1700 | | 100 | Finance | 108 | 1700 | .......... | 270 | Payroll | 0 | 1700 | +---------------+----------------------+------------+-------------+Sample table: locations
location_id street_address postal_code city state_province country_id ----------- -------------------- ----------- ---------- -------------- ---------- 1000 1297 Via Cola di Rie 989 Roma IT 1100 93091 Calle della Te 10934 Venice IT 1200 2017 Shinjuku-ku 1689 Tokyo Tokyo Prefectu JP 1300 9450 Kamiya-cho 6823 Hiroshima JP 1400 2014 Jabberwocky Rd 26192 Southlake Texas US 1500 2011 Interiors Blvd 99236 South San California US 1600 2007 Zagora St 50090 South Brun New Jersey US 1700 2004 Charade Rd 98199 Seattle Washington US 1800 147 Spadina Ave M5V 2L7 Toronto Ontario CA 1900 6092 Boxwood St YSW 9T2 Whitehorse Yukon CA ....... 3200 Mariano Escobedo 999 11932 Mexico Cit Distrito Feder MX
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.
Go to:
PREV :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.
Structure of 'hr' database :
MySQL Code Editor:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?