w3resource

MySQL Subquery Exercises: Find the name of the employees who are managers

MySQL Subquery: Exercise-4 with Solution

Write a MySQL query to find the name (first_name, last_name) of the employees who are managers.

Sample table: employees


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 employee_id is in the set of manager_ids
WHERE (employee_id IN 
    -- Subquery to select manager_ids from the employees table
    (SELECT manager_id FROM employees)
);

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 employee_id is in the set of manager_ids.
  • This is achieved by using a subquery to select manager_ids from the "employees" table, and then comparing each employee's employee_id with the manager_ids obtained from the 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 SubQuery: Find the names of the employees who are managers

 

MySQL Code Editor:

Structure of 'hr' database :

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 the employees who have a manager and worked in a USA based department.
Next:Write a MySQL query to find the name (first_name, last_name), and salary of the employees whose salary is greater than the average salary.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.