Open In App

Displaying the Employees in Increasing Order of their Salaries in SQL Server

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 name of the department having the highest average salary of employees working in it. We shall use the ORDER 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 details of the employees in the increasing order of their salaries. We will use the ORDER BY clause to sort the rows according to the increasing salaries of the employees. The column name SALARY must be mentioned after the ORDER BY clause to specify the basis of sorting.

Syntax:

SELECT * FROM TABLE_NAME ORDER BY COLUMN;

Query:

SELECT * FROM COMPANY ORDER BY SALARY;

Note: This query returns all the rows in the sorted order.

Output:

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

Query:

WITH RankedEmployees AS (
    SELECT EMPLOYEE_NAME, Salary, ROW_NUMBER() OVER (ORDER BY Salary ASC) AS Rank
    FROM DEPARTMENT
)
SELECT EMPLOYEE_NAME, Salary
FROM RankedEmployees
ORDER BY Rank;

Output:

sql output


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