SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO SELECT

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

How to use the BETWEEN operator in SQL

In this entire SQL article, we will understand and learn about the BETWEEN operator concept and how to use it in SQL.

What is the BETWEEN operator in SQL?

The Between operator fetches records which exist between the starting and ending value in the given expression query. The SELECT between operators retrieves text, number, or date data.

The BETWEEN operator includes the starting value and the ending value.

The syntax of BETWEEN operators with the SELECT statement:

SELECT Column_Name_1, Column_Name_2, Column_Name_3 FROM Table_Name WHERE Column_Name BETWEEN VALUE_1 AND VALUE_2

Here, value_1 is starting value, and value_2 is its ending value.

The syntax of BETWEEN operators with the UPDATE statement:

UPDATE Table_Name SET Column_Name = value WHERE Column_Name BETWEEN VALUE_1 AND VALUE_2; 

The syntax of BETWEEN operators with the DELETE statement:

DELETE FROM TABLE_NAME WHERE COLUMN_NAME BETWEEN VALUE_1 AND VALUE_2;

There are following steps which help to learn for how to use the BETWEEN operator in the SQL query:

1 We have to create a newly named database. If you have already created a database (existing database), then use the old database by using the USE command.

2. After selecting the database, we will create a new table or use the existing table.

3 Add records in the newly created table using the INSERT statement

4 After adding records to the newly created table, we will display the data from the table using the SELECT statement.

Step 1: New Database or use old Database.

We have an existing database. So, we will use old database name, Company.

USE Company;

We can use the below syntax to create a new database that doesn't exist in the database.

CREATE DATABASE database_name;

After creating the new database, use the newly created database using the USE command.

Step 2: New Table or use old Table.

We have an old table. So, we will use the old table named Employees.

If you don’t have the old table, then use the below syntax to create the table.

CREATE TABLE table_name(

CREATE TABLE table_name(
Column_name_1 datatype(column size),
Column_name_2 datatype(column size),
Column_name_3 datatype(column size)
);

Step 3: Add new records to the new table.

Use below syntax to insert new records in the table:

INSERT INTO Table_Name VALUES(value_1, value_2, value_3);

The below syntax is used to display the data from the table:

SELECT * FROM Table_Name;

The following query will display the records of Employees

SELECT * FROM Employees;

The output of the above SELECT query is:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65500PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAC#5
1003NIKHILVANI50500JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAIC#5
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4002ASHWINIBAGHAT54500NOIDAJAVA3
4003RUCHIKAAGARWAL60000DELHIORACLE1
5001ARCHITSHARMA55500DELHITESTING4
5002SANKETCHAUHAN70000HYDERABADJAVA3
5003ROSHANNEHTE48500CHANDIGARHC#5
6001RAHULNIKAM54500BANGALORETESTING4
6002ATISHJADHAV60500BANGALOREC#5
6003NIKITAINGALE65000HYDERABADORACLE1

Step 4: We are ready to use the BETWEEN operator in the queries.

Let’s understand the BETWEEN operator with the help of examples.

Example 1: Execute a query to fetch employee information from the employees' table where employee salary is between 48000 and 60000.

SELECT * FROM EMPLOYEES WHERE SALARY BETWEEN 48000 AND 60000;

In the above query, we fetched all the employee records from the employee table whose employees' Salary is between 48000 and 60000.

The output of the above query is shown as:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1002VAIBHAVSHARMA60000NOIDAC#5
1003NIKHILVANI50500JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2003RUCHIKAJAIN50000MUMBAIC#5
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4002ASHWINIBAGHAT54500NOIDAJAVA3
4003RUCHIKAAGARWAL60000DELHIORACLE1
5001ARCHITSHARMA55500DELHITESTING4
5003ROSHANNEHTE48500CHANDIGARHC#5
6001RAHULNIKAM54500BANGALORETESTING4
How to use the BETWEEN operator in SQL

As we can see in the output, only those employees records are displayed whose employee salary is between 48000 and 60000.

Example 2: Execute a query to fetch employee information from the employees table where the employee city is between Chandigarh and Pune.

SELECT * FROM EMPLOYEES WHERE CITY BETWEEN 'CHANDIGARH' AND 'PUNE'; 

This query will displaye all the employee information from the employee table whose employees’ city is between ‘Chandigarh’ and ‘Pune’.

The output of the above query is shown as:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65500PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAC#5
1003NIKHILVANI50500JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAIC#5
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4002ASHWINIBAGHAT54500NOIDAJAVA3
4003RUCHIKAAGARWAL60000DELHIORACLE1
5001ARCHITSHARMA55500DELHITESTING4
5002SANKETCHAUHAN70000HYDERABADJAVA3
5003ROSHANNEHTE48500CHANDIGARHC#5
6003NIKITAINGALE65000HYDERABADORACLE1
How to use the BETWEEN operator in SQL

As we can see in the output, only those employees' records are displayed whose employee city is between 'Chandigarh' and 'Pune'.

Example 3: Execute a query to modify the employee salary by 1.2 whose employee city is between ‘Delhi’ and ‘Noida’.

UPDATE EMPLOYEES SET SALARY = SALARY * 1.2 WHERE CITY BETWEEN ‘DELHI’ AND ‘NOIDA’;

In the above statement, we increase the salary of those employees by 1.2 whose city is between 'Delhi' and 'Noida’. 

We will execute the SELECT query to verify whether the employee’s information is successfully modified or not.

SELECT * FROM EMPLOYEES WHERE CITY BETWEEN ‘DELHI’ AND ’NOIDA’;

 The output of the above query is shown as:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1002VAIBHAVSHARMA72000NOIDAC#5
1003NIKHILVANI60600JAIPURFMW2
2003RUCHIKAJAIN60000MUMBAIC#5
3002ANUJAWANRE60600JAIPURFMW2
3003DEEPAMJAUHARI70200MUMBAIJAVA3
4001RAJESHGOUD72600MUMBAITESTING4
4002ASHWINIBAGHAT65400NOIDAJAVA3
4003RUCHIKAAGARWAL72000DELHIORACLE1
5001ARCHITSHARMA66600DELHITESTING4
5002SANKETCHAUHAN84000HYDERABADJAVA3
6003NIKITAINGALE72800HYDERABADORACLE1
How to use the BETWEEN operator in SQL

As we see in the output, employee records are updated whose city is between 'Delhi' and 'Noida’.

Example 4: Execute a query to remove the employee information from the employees' table of those employees whose Salary is between 50000 and 65000.

DELETE FROM EMPLOYEES WHERE SALARY BETWEEN 45000 AND 65000;

In the above statement, we are removing the employee information of those whose Salary is between 50000 and 65000.

We will execute the SELECT query to verify whether the employee’s information is successfully deleted or not.

SELECT * FROM EMPLOYEES;

The output of the above query is shown as:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65500PUNEORACLE1
1002VAIBHAVSHARMA72000NOIDAC#5
2002BHAVESHJAIN65500PUNEFMW2
3003DEEPAMJAUHARI70200MUMBAIJAVA3
4001RAJESHGOUD72600MUMBAITESTING4
4002ASHWINIBAGHAT65400NOIDAJAVA3
4003RUCHIKAAGARWAL72000DELHIORACLE1
5001ARCHITSHARMA66600DELHITESTING4
5002SANKETCHAUHAN84000HYDERABADJAVA3
5003ROSHANNEHTE48500CHANDIGARHC#5
6003NIKITAINGALE78000HYDERABADORACLE1
How to use the BETWEEN operator in SQL

In the above output, the records are deleted of those employees whose Salary is between 50000 and 65000.



ADVERTISEMENT
ADVERTISEMENT