SQL Query to Find Names of the Employees Whose Department Have Number of Employees Less than 2

In SQL, we need to find out the department wise information from the given table containing information about employees. One such data is the name of employee who belong to a department having less than 2(i.e. only 1) employees. We shall use the GROUP BY, IN and COUNT clause to achieve this. This is illustrated below. For this article, we will be using the Microsoft SQL Server as our database.

Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks.

Query:

CREATE DATABASE GeeksForGeeks

Output:

Step 2: Use the GeeksForGeeks database. For this use the below command.

Query:

USE GeeksForGeeks

Output:

Step 3: Create a table COMPANY inside the database GeeksForGeeks. This table has 4 columns namely EMPLOYEE_ID, EMPLOYEE_NAME, DEPARTMENT_NAME and SALARY containing the id, name, department and the salary of various employees.

Query:

CREATE TABLE COMPANY(
EMPLOYEE_ID INT PRIMARY KEY,
EMPLOYEE_NAME VARCHAR(10),
DEPARTMENT_NAME VARCHAR(10),
SALARY INT);

Output:

Step 4: Describe the structure of the table COMPANY.

Query:

EXEC SP_COLUMNS COMPANY;

Output:

Step 5: Insert 5 rows into the COMPANY table.

Query:

INSERT INTO COMPANY VALUES(1,'RAM','HR',10000);
INSERT INTO COMPANY VALUES(2,'AMRIT','MRKT',20000);
INSERT INTO COMPANY VALUES(3,'RAVI','HR',30000);
INSERT INTO COMPANY VALUES(4,'NITIN','MRKT',40000);
INSERT INTO COMPANY VALUES(5,'VARUN','IT',50000);

Output:

Step 6: Display all the rows of the COMPANY table.

Query:

SELECT * FROM COMPANY;

Output:

Step 7: Display the name of the employee and of his/her department belonging to a department having less than 2 i.e. 1 employee. We will use the IN clause here to compare the department names obtained from the outer query to department names obtained from the inner query. The inner query uses the GROUP BY clause and COUNT clause to count the number of employees belonging to each department. Then compare this returned count to ensure that the count is less than 2.

Syntax:

SELECT EMPLOYEE_NAME, DEPARTMENT_NAME 
FROM COMPANY WHERE DEPARTMENT_NAME IN
(SELECT DEPARTMENT_NAME FROM COMPANY
GROUP BY DEPARTMENT_NAME HAVING COUNT(*)<2);

Query:

SELECT EMPLOYEE_NAME, DEPARTMENT_NAME
FROM COMPANY WHERE DEPARTMENT_NAME IN
(SELECT DEPARTMENT_NAME FROM COMPANY GROUP
BY DEPARTMENT_NAME HAVING COUNT(*)<2);

Note – This query returns only 1 row i.e. VARUN and IT since IT has only 1 employee.

Output:

Method 2: Using a Common Table Expression (CTE)

Query: 

WITH EmployeeCounts AS (
    SELECT DEPTNAME, COUNT(*) AS EmployeeCount
    FROM DEPARTMENT
    GROUP BY DEPTNAME
)
SELECT EMPLOYEE_NAME,DEPTNAME
FROM DEPARTMENT
WHERE DEPTNAME IN (
    SELECT DEPTNAME
    FROM EmployeeCounts
    WHERE EmployeeCount < 2
);

Output:

sql output

My Personal Notes arrow_drop_up

Save