SQL Exercise: Find those employees who is working under a manager
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+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | 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 | 2003-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 | | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 2005-09-21 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 2001-01-13 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 2006-01-03 | IT_PROG | 9000.00 | 0.00 | 102 | 60 | | 104 | Bruce | Ernst | BERNST | 590.423.4568 | 2007-05-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60 | | 105 | David | Austin | DAUSTIN | 590.423.4569 | 2005-06-25 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 2006-02-05 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 2007-02-07 | IT_PROG | 4200.00 | 0.00 | 103 | 60 | | 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 2002-08-17 | FI_MGR | 12008.00 | 0.00 | 101 | 100 | | 109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 2002-08-16 | FI_ACCOUNT | 9000.00 | 0.00 | 108 | 100 | ...... | 206 | William | Gietz | WGIETZ | 515.123.8181 | 2002-06-07 | AC_ACCOUNT | 8300.00 | 0.00 | 205 | 110 | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
Sample Solution:
-- Selecting a concatenated string of 'first_name' and 'last_name' as 'Full_Name',
-- along with 'salary' and 'manager_id' columns from the 'employees' table
SELECT first_name || ' ' || last_name AS Full_Name, salary, manager_id
-- Specifying the table to retrieve data from ('employees')
FROM employees
-- Filtering the results based on the condition that 'manager_id' is not NULL
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.
Go to:
PREV : Find employees who works either in department 70 or 90.
NEXT : Find those employees hired before June 21st, 2002.
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.