SQL Exercise: Find those employees who is working under a manager

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

14. From the following table, write a SQL query to find those employees who work under a manager. Return full name (first and last name), salary, and manager ID.

Sample table: employees

Sample Solution:

SELECT first_name ||' '||last_name AS Full_Name, salary, manager_id
   FROM employees
     WHERE manager_id IS NOT NULL;

Sample Output:

     full_name     |  salary  | manager_id
-------------------+----------+------------
 Neena Kochhar     | 17000.00 |        100
 Lex De Haan       | 17000.00 |        100
 Alexander Hunold  |  9000.00 |        102
 Bruce Ernst       |  6000.00 |        103
 David Austin      |  4800.00 |        103
 Valli Pataballa   |  4800.00 |        103
 Diana Lorentz     |  4200.00 |        103
 Nancy Greenberg   | 12000.00 |        101
 Daniel Faviet     |  9000.00 |        108
 John Chen         |  8200.00 |        108
 Ismael Sciarra    |  7700.00 |        108
 Jose Manuel Urman |  7800.00 |        108
 Luis Popp         |  6900.00 |        108
 Den Raphaely      | 11000.00 |        100
 Alexander Khoo    |  3100.00 |        114
 Shelli Baida      |  2900.00 |        114
 Sigal Tobias      |  2800.00 |        114
 Guy Himuro        |  2600.00 |        114
 Karen Colmenares  |  2500.00 |        114
 Matthew Weiss     |  8000.00 |        100
 Adam Fripp        |  8200.00 |        100
 Payam Kaufling    |  7900.00 |        100
 Shanta Vollman    |  6500.00 |        100
 Kevin Mourgos     |  5800.00 |        100
 Julia Nayer       |  3200.00 |        120
 Irene Mikkilineni |  2700.00 |        120
 James Landry      |  2400.00 |        120
 Steven Markle     |  2200.00 |        120
 Laura Bissot      |  3300.00 |        121
 Mozhe Atkinson    |  2800.00 |        121
 James Marlow      |  2500.00 |        121
 TJ Olson          |  2100.00 |        121
 Jason Mallin      |  3300.00 |        122
 Michael Rogers    |  2900.00 |        122
 Ki Gee            |  2400.00 |        122
 Hazel Philtanker  |  2200.00 |        122
 Renske Ladwig     |  3600.00 |        123
 Stephen Stiles    |  3200.00 |        123
 John Seo          |  2700.00 |        123
 Joshua Patel      |  2500.00 |        123
 Trenna Rajs       |  3500.00 |        124
 Curtis Davies     |  3100.00 |        124
 Randall Matos     |  2600.00 |        124
 Peter Vargas      |  2500.00 |        124
 John Russell      | 14000.00 |        100
 Karen Partners    | 13500.00 |        100
 Alberto Errazuriz | 12000.00 |        100
 Gerald Cambrault  | 11000.00 |        100
 Eleni Zlotkey     | 10500.00 |        100
 Peter Tucker      | 10000.00 |        145
 David Bernstein   |  9500.00 |        145
 Peter Hall        |  9000.00 |        145
 Christopher Olsen |  8000.00 |        145
 Nanette Cambrault |  7500.00 |        145
 Oliver Tuvault    |  7000.00 |        145
 Janette King      | 10000.00 |        146
 Patrick Sully     |  9500.00 |        146
 Allan McEwen      |  9000.00 |        146
 Lindsey Smith     |  8000.00 |        146
 Louise Doran      |  7500.00 |        146
 Sarath Sewall     |  7000.00 |        146
 Clara Vishney     | 10500.00 |        147
 Danielle Greene   |  9500.00 |        147
 Mattea Marvins    |  7200.00 |        147
 David Lee         |  6800.00 |        147
 Sundar Ande       |  6400.00 |        147
 Amit Banda        |  6200.00 |        147
 Lisa Ozer         | 11500.00 |        148
 Harrison Bloom    | 10000.00 |        148
 Tayler Fox        |  9600.00 |        148
 William Smith     |  7400.00 |        148
 Elizabeth Bates   |  7300.00 |        148
 Sundita Kumar     |  6100.00 |        148
 Ellen Abel        | 11000.00 |        149
 Alyssa Hutton     |  8800.00 |        149
 Jonathon Taylor   |  8600.00 |        149
 Jack Livingston   |  8400.00 |        149
 Kimberely Grant   |  7000.00 |        149
 Charles Johnson   |  6200.00 |        149
 Winston Taylor    |  3200.00 |        120
 Jean Fleaur       |  3100.00 |        120
 Martha Sullivan   |  2500.00 |        120
 Girard Geoni      |  2800.00 |        120
 Nandita Sarchand  |  4200.00 |        121
 Alexis Bull       |  4100.00 |        121
 Julia Dellinger   |  3400.00 |        121
 Anthony Cabrio    |  3000.00 |        121
 Kelly Chung       |  3800.00 |        122
 Jennifer Dilly    |  3600.00 |        122
 Timothy Gates     |  2900.00 |        122
 Randall Perkins   |  2500.00 |        122
 Sarah Bell        |  4000.00 |        123
 Britney Everett   |  3900.00 |        123
 Samuel McCain     |  3200.00 |        123
 Vance Jones       |  2800.00 |        123
 Alana Walsh       |  3100.00 |        124
 Kevin Feeney      |  3000.00 |        124
 Donald OConnell   |  2600.00 |        124
 Douglas Grant     |  2600.00 |        124
 Jennifer Whalen   |  4400.00 |        101
 Michael Hartstein | 13000.00 |        100
 Pat Fay           |  6000.00 |        201
 Susan Mavris      |  6500.00 |        101
 Hermann Baer      | 10000.00 |        101
 Shelley Higgins   | 12000.00 |        101
 William Gietz     |  8300.00 |        205

Code Explanation:

The said query in SQL that retrieves the full name (concatenation of first name and last name), salary, and manager id of employees who have a manager (i.e., whose manager_id is not null) from the employees table.

Practice Online

N.B.: In certain instances not null is removed in table structure, so results may vary.

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Find employees who works either in department 70 or 90.
Next SQL Exercise: Find those employees hired before June 21st, 2002.