A simple SQL query you can use to return employee records with the highest salary (easily change wordings to comply with your company’s policies).
Introduction
The query below returns all employee records where the current salary is greater than the highest salary in any other employee record.
Here is the procedure of how a SQL command is executed:
- When a SQL command is executed by any RDBMS, the system determines the best way to implement the request, and the SQL engine decides how to interpret the task.
- Various components are used in the process. Optimization Engine, Query Engine, Query Dispatcher, Classic, and so on are examples of these components.
- The classic query engine handles all non-SQL queries, but the SQL query engine does not handle logical files.
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,
…
)
- 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.
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:
- Creating a table
- Inserting values in table
- 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:
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.
select e.name,e.salary from employees e where salary in (select max(salary) from employees)
select e.name,e.salary from employees e where salary in (select max(salary) from employees)
This will return the employee record with highest salary in a table called “employees”. You can use any other query that you want to get results for.
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.
