w3resource

SQL Exercise: Find employees whose managers are hold given IDs

SQL SORTING and FILTERING on HR Database: Exercise-16 with Solution

16. From the following table, write a SQL query to find the employees whose managers hold the ID 120, 103, or 145. Return first name, last name, email, salary and manager ID.

Sample table : employees


Sample Solution:

SELECT first_name, last_name, email, 
  salary, manager_id
    FROM employees
     WHERE manager_id IN (120 , 103 , 145);

Sample Output:

 first_name  |  last_name  |  email   |  salary  | manager_id
-------------+-------------+----------+----------+------------
 Bruce       | Ernst       | BERNST   |  6000.00 |        103
 David       | Austin      | DAUSTIN  |  4800.00 |        103
 Valli       | Pataballa   | VPATABAL |  4800.00 |        103
 Diana       | Lorentz     | DLORENTZ |  4200.00 |        103
 Julia       | Nayer       | JNAYER   |  3200.00 |        120
 Irene       | Mikkilineni | IMIKKILI |  2700.00 |        120
 James       | Landry      | JLANDRY  |  2400.00 |        120
 Steven      | Markle      | SMARKLE  |  2200.00 |        120
 Peter       | Tucker      | PTUCKER  | 10000.00 |        145
 David       | Bernstein   | DBERNSTE |  9500.00 |        145
 Peter       | Hall        | PHALL    |  9000.00 |        145
 Christopher | Olsen       | COLSEN   |  8000.00 |        145
 Nanette     | Cambrault   | NCAMBRAU |  7500.00 |        145
 Oliver      | Tuvault     | OTUVAULT |  7000.00 |        145
 Winston     | Taylor      | WTAYLOR  |  3200.00 |        120
 Jean        | Fleaur      | JFLEAUR  |  3100.00 |        120
 Martha      | Sullivan    | MSULLIVA |  2500.00 |        120
 Girard      | Geoni       | GGEONI   |  2800.00 |        120
(18 rows)

Code Explanation:

The said query in SQL that retrieves the first name, last name, email, salary, and manager ID columns from the 'employees' table where the value in the "manager_id" column is one of the following values: 120, 103, or 145.

Relational Algebra Expression:

Relational Algebra Expression: Display the first and last name, email, salary and manager ID of  employees whose managers are hold the ID 120, 103 or 145.

Relational Algebra Tree:

Relational Algebra Tree: Display the first and last name, email, salary and manager ID of  employees whose managers are hold the ID 120, 103 or 145.

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the first and last name, email, salary and manager ID of employees whose managers are hold the ID 120, 103 or 145 - Duration

Rows:

Query visualization of Display the first and last name, email, salary and manager ID of employees whose managers are hold the ID 120, 103 or 145 - Rows

Cost:

Query visualization of Display the first and last name, email, salary and manager ID of employees whose managers are hold the ID 120, 103 or 145 - Cost

Contribute your code and comments through Disqus.

Previous SQL Exercise: Find those employees hired before June 21st, 2002.
Next SQL Exercise: Employees have the given letters in their first name.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

MySQL export schema without data

mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql

Ref: https://bit.ly/3xzB9dS

 





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