Open In App

SQL Query to Print the Name and Salary of the Person Having Least Salary in the Department

Improve
Improve
Like Article
Like
Save
Share
Report

In SQL, we need to find out the department wise information from the given table containing information about employees. One such data is the minimum salary of the employees of each department. We shall use the GROUP BY 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 minimum salary obtained by the employees in each department along with their employee name and department. We will use the IN clause here to compare the salaries obtained from the outer query to minimum salaries obtained from the inner query. The inner query uses the GROUP BY clause to return only 1 salary from each department i.e. the least one. MIN aggregate function is used to find the least salary in a department.

Syntax:

SELECT INFORMATION FROM TABLE_NAME WHERE
COLUMN_1 IN (SELECT AGGREGATE_FUNCTION
(COLUMN_1) FROM TABLE_NAME GROUP BY COLUMN_2);

Query:

SELECT EMPLOYEE_NAME,DEPARTMENT_NAME,
SALARY FROM COMPANY WHERE
SALARY IN (SELECT MIN(SALARY) FROM
COMPANY GROUP BY DEPARTMENT_NAME);

Output:

Method 2: Using a Common Table Expression (CTE) and ROW_NUMBER()

Query:

WITH RankedEmployees AS (
    SELECT EMPLOYEE_NAME, Salary, ROW_NUMBER() OVER (PARTITION BY DEPTNAME ORDER BY Salary ASC) AS Rank
    FROM DEPARTMENT
)
SELECT EMPLOYEE_NAME, Salary
FROM RankedEmployees
WHERE Rank = 1;

Output:

sql output


Last Updated : 26 May, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads