SQL Query to Find All Employees Who Are Also Managers
Structured Query Language or SQL is a standard Database language that is used to create, maintain and retrieve the data from relational databases like MySQL, Oracle, etc.
Here, we are going to see how to find the details of all the employees who are also managers in SQL. We will first create a database named “geeks” then we will create a table “employees” in that database. After that, we will execute our query on that table.
Creating a Database :
Use the below SQL statement to create a database called geeks:
CREATE DATABASE geeks;
Using Database :
We have the following Employee table in our geeks database :
CREATE TABLE employees( EMPLOYEE_ID int, NAME Varchar(20), PHONE_NUMBER int, HIRE_DATE date, MANAGER_ID int);
You can use the below statement to query the description of the created table:
EXEC SP_COLUMNS employees;
Adding Data to Table:
Use the below statement to add data to the Employee table:
INSERT INTO EMPLOYEES VALUES (100, "ANURAG", 9889269997, "1987-06-17", 100); INSERT INTO EMPLOYEES VALUES (101, "harsh", 8789269986, "1987-06-20", 100); INSERT INTO EMPLOYEES VALUES (102, "SUMIT", 7689269975, "1987-07-07", 103); INSERT INTO EMPLOYEES VALUES (103, "RUHI", 9589269964, "1987-07-12", 102); INSERT INTO EMPLOYEES VALUES (104, "KAE", 8489269953, "1987-07-23", 103);
To verify the contents of the table use the below statement:
SELECT * FROM EMPLOYEES;
Now to get the details of all the employees who are also managers, we make use of the EMPLOYEE_ID field and the MANAGER_ID, and we will find out the details of employees who are also managers. The query would have the following syntax:
Syntax: SELECT * FROM table_name WHERE (column_name IN (SELECT column_name FROM table_name));
Now run the same query with on the table we created as shown below:
SELECT * FROM EMPLOYEES WHERE (EMPLOYEE_ID IN (SELECT MANAGER_ID FROM EMPLOYEES));
Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course by GeeksforGeeks.