Data is undoubtedly important in today's world. However, mastering the art of data management is needed to handle it effectively. That brings us to the language, SQL or Structured Query Language, which serves as the foundation for data management. SQL is the fundamental language of relational databases, which are widely used in businesses.
Donald D. Chamberlin and Raymond F. Boyce created SQL at IBM in the early 1970s. SEQUEL (Structured English Query Language) was the original name for SQL. SQL's primary goal is to update, store, modify, and retrieve data from relational databases.
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 (
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.
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.
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
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.
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.
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.
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!