How to Find Second Highest Salary in SQL

How to Write a Query?

Now, let’s take a look at how you can write an SQL query.

• Understand the Process

A query is parsed into a tree as soon as it is entered. A parser, or computer program that converts SQL statements into a parse tree, facilitates in determining if the query meets the syntactical and semantic specifications, i.e., is recognizable.

The parser then generates an internal query, which is then passed to the rewrite engine if everything is right.

After that, the task optimizer determines the best execution plan for your query by analysing how many execution plans it has. The algorithm that will be used to execute the command is represented by this program.

You will get the answers you need if the query is written correctly.

• Getting Database Ready

To write a SQL query, you must first establish an environment for it by launching a database, as previously mentioned.

If you already have your DBMS software downloaded, you can use the ‘CREATE DATABASE' statement to create a database.

Syntax for creating a database:

CREATE DATABASE   database_name;

• Creating a Table

You can now proceed to create a table if the database is ready. Tables, as previously said, assist you in structuring your queries and making data more manageable.

The ‘CREATE TABLE' statement is required to create a table.

Syntax for Creating a Table in SQL:

CREATE TABLE table_name (

column1_name_constraint,

…

)

Learn 15+ In-Demand Tools and Skills!

Automation Testing Masters ProgramExplore Program

• Starting With Basic SQL Statements

You can now begin writing SQL queries by inserting data into the table once it is ready and operational.

Here are some basic SQL statements that will help you to enter data into the table and manage it efficiently.

1. INSERT Statement

This statement is used to initialize a table with new rows. In this case, the syntax would be as follows:

INSERT (column 1, column 2, column 3) INTO table name;

2. SELECT Clause

This clause is used to select data from one or more tables and extract it. The syntax for this clause is as follows:

FROM table name, SELECT column1 name, column2 name

3. WHERE Clause

You can pick data based on a condition by inserting this clause in a table. It's useful if you want to update or completely erase one of the records in a row or column based on a condition such as a name, birth date, or other details.

The syntax for WHERE clause is as follows:

SELECT column1_name FROM table_name WHERE condition

4. UPDATE statement

After you have inserted and selected the data, you can update the already present data using the UPDATE command. The syntax for the same is given below.

UPDATE table_name

SET column1_name = value 1…

WHERE condition1, condition2…

There are many more commands and statements in SQL, but for now we'll learn how to find the second highest salary in SQL.

Want a Top Software Development Job? Start Here!

Full Stack Development-MEANExplore Program

How to Write an SQL Query to Find the Second Highest Salary?

We will execute the above problem in 3 steps to find the second highest salary in SQL, that are:

1. Creating a table
2. Inserting values in table
3. Writing query to find second highest salary

Now, let’s look at each of the above steps in detail.

Creating Table

The first step is to create a table. Here, we’ll create a table named as Employee having attributes ID, NAME and SALARY.

SQL query for creating the schema for our table is given below.

CREATE TABLE Employee 

( 

ID INTEGER, 

NAME VARCHAR(20), 

SALARY INTEGER 

)

Output

Now that we have the schema for the table, let’s move on to the next step where we will insert some values in the schema to get the desired end result.

Inserting Values in Table

In this step, we will insert some sample records into the table.

INSERT INTO Employee VALUES(1,'Mandy',12000);

INSERT INTO Employee VALUES(2,'Chris',15000);

INSERT INTO Employee VALUES(3,'Henry',10000);

INSERT INTO Employee VALUES(4,'Katy',10000);

INSERT INTO Employee values(5,'Adams',11000);

The above query will give you the table as follows:

Become a Creator of Products that Delight Users

Free Webinar | 29 March, Wednesday | 8 PM ISTRegister Now

Writing Query to Find Second Highest Salary in SQL

To find the second highest salary in the above table, we will use the concept of subquery, which means that firstly, we will find the highest salary in the table and then we will nest that query to a subquery to find the second highest salary in SQL.

To find the highest salary in the table, write the following query.

SELECT MAX(SALARY) FROM Employee;

This will give you the output as 15000, i.e the highest salary in the table above.

Output-

Now, to find the second highest salary, we nest the above query into another query as written below.

SELECT MAX(SALARY) FROM Employee WHERE SALARY < (SELECT MAX(SALARY) FROM Employee);

This query will give you the desired output i.e 12000, which is the second highest salary.

Output-

Conclusion

Hope this tutorial was helpful for you to understand the basics of SQL and how you can find out the second highest salary in SQL. There are many more commands and concepts in SQL that help us in managing the database effectively. Checkout Simplilearn’s SQL certification training to learn SQL in depth and get yourself industry ready.

If you are looking to enhance your coding skills further, we would recmmend you to check Simplilearn's Post Graduate Program in Full Stack Web Development. This program can help you gain the right skills and make you job-ready.

If you have any doubts, please feel free to drop them in the comments section, and our experts will help you out right away!