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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
Difference between natural join and inner join
One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned-
Consider:
TableA TableB +------------+----------+ +--------------------+ |Column1 | Column2 | |Column1 | Column3 | +-----------------------+ +--------------------+ | 1 | 2 | | 1 | 3 | +------------+----------+ +---------+----------+
The INNER JOIN of TableA and TableB on Column1 will return
SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1); SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;
+------------+-----------+---------------------+ | a.Column1 | a.Column2 | b.Column1| b.Column3| +------------------------+---------------------+ | 1 | 2 | 1 | 3 | +------------+-----------+----------+----------+
The NATURAL JOIN of TableA and TableB on Column1 will return:
SELECT * FROM TableA NATURAL JOIN TableB +------------+----------+----------+ |Column1 | Column2 | Column3 | +-----------------------+----------+ | 1 | 2 | 3 | +------------+----------+----------+
Ref: https://bit.ly/3AG5CId
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
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