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

SQL Between Operator

SQL Between operator is a logical operator in the Structured Query Language. The Between operator is used to retrieve data within the range specified in the condition in the query.

The SELECT between operators is used to retrieve the data: text, number, or date.

Between means within the two values, so between operators includes two values: a start value and an end value.

We can use between operator with Select statement in the where clause

We can also use between operator with Delete statement and update statement.

In Delete Statement, we use between operator to delete the values between the two values or specified within the range and with Update statement to modify the values.

Syntax of BETWEEN Operator in SQL

SELECT COLUMNNAME1, COLUMNAME2 FROM TABLENAME WHERE COLUMNNAME BETWEEN VAL_1 AND VAL_2

Here, val_1 is starting value, and val_2 is its ending value

Let's understand the BETWEEN operator in SQL with examples.

BETWEEN OPERATOR WITH SELECT STATEMENT: -

SELECT statements are used to retrieve the data from the SQL table. We used BETWEEN operator with the SELECT statement to fetch the records between two values from the SQL table.

Consider the following tables along with the given records.

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERIDAGEDOJ
1001VaibhavSharma65000PUNEORACLE1232021-09-20
1002NikhilVani60000NOIDAORACLE1212021-09-23
1003VaibhaviMishra50000JAIPURFMW2222021-09-30
2001RuchikaJain55500CHANDIGARHORACLE1222021-09-30
2002PrachiSharma65500PUNEFMW2232021-09-20
2003BhaveshJain50000MUMBAITESTING4212021-09-23
3001DeepamJauhari55500PUNEJAVA3212021-09-23
3002ANUJAWANRE50500JAIPURFMW2222021-09-30
3003PranotiShende58500MUMBAIJAVA3232021-09-20
4001RAJESHGOUD60500MUMBAITESTING4232021-09-20

Example 1: Write a query to display the records from the employee table whose employee salary is between 48500 and 60000.

SELECT * FROM EMPLOYEE WHERE SALARY BETWEEN 48500 AND 60000;

In the above statement, we fetched all the details from the employee table whose employee salary is between 48500 and 60000.

Output:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERIDAGEDOJ
1002NikhilVani60000NOIDAORACLE1212021-09-23
1003VaibhaviMishra50000JAIPURFMW2222021-09-30
2001RuchikaJain55500CHANDIGARHORACLE1222021-09-30
2003BhaveshJain50000MUMBAITESTING4212021-09-23
3001DeepamJauhari55500PUNEJAVA3212021-09-23
3002ANUJAWANRE50500JAIPURFMW2222021-09-30
3003PranotiShende58500MUMBAIJAVA3232021-09-20
SQL Between Operator

Example 2: Write a query to display employee id, first name, last name, salary, and city from employee table whose employee city is between Mumbai and Pune.

SELECT EMPLOYEEID, FIRST_NAME, LAST_NAME, SALARY, CITY FROM EMPLOYEE WHERE CITY BETWEEN 'MUMBAI' AND 'PUNE';

In the above statement, we fetched the employee id, employee names, employee city, and salary from the employee table of those employees whose city standard between Mumbai and Pune.

Output:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITY
1001VaibhavSharma65000PUNE
1002NikhilVani60000NOIDA
2002PrachiSharma65500PUNE
2003BhaveshJain50000MUMBAI
3001DeepamJauhari55500PUNE
3003PranotiShende58500MUMBAI
4001RAJESHGOUD60500MUMBAI
SQL Between Operator

Example 3: Write a query to display employee id, first name, last name, salary, and department and manager id from employee table whose employee department is between Java and Oracle or salary between 45000 and 55000.

SELECT EMPLOYEEID, FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT, MANAGERID FROM EMPLOYEE WHERE DEPARTMENT BETWEEN 'JAVA' AND 'ORACLE' OR SALARY BETWEEN 45000 AND 55000;

We have used multiple BETWEEN operators and OR operators in the above statement. We fetched the employee id, first name, last name, salary, department, and manager id from the employee table. The first BETWEEN operator is used to fetch the above details of those employees whose department name is between Java and Oracle. After the first BETWEEN operator, it will switch to another BETWEEN operator, which is used to fetch the above details of those employees whose salary is between 45000 and 55000. The above statement will display both BETWEEN operator fetched records because we used OR operator in the statement.

Output:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYDEPARTMENTMANAGERID
1001VaibhavSharma65000ORACLE1
1002NikhilVani60000ORACLE1
1003VaibhaviMishra50000FMW2
2001RuchikaJain55500ORACLE1
2003BhaveshJain50000TESTING4
3001DeepamJauhari55500JAVA3
3002ANUJAWANRE50500FMW2
3003PranotiShende58500JAVA3
SQL Between Operator

Example 4: Write a query to display employee id, first name, last name, salary, and city and manager id from employee table whose employee city is between Chandigarh and Pune and salary between 45000 and 60000.

SELECT EMPLOYEEID, FIRST_NAME, LAST_NAME, SALARY, CITY, MANAGERID FROM EMPLOYEE WHERE CITY BETWEEN 'CHANDIGARH' AND 'PUNE' AND SALARY BETWEEN 45000 AND 60000;

We have used multiple BETWEEN operators and OR operators in the above statement. We fetched the employee id, first name, last name, salary, city, and manager id from the employee table. The first BETWEEN operator is used to fetch the above details of those employees whose city name is between Chandigarh and Pune. After the first BETWEEN operator, it will switch to another BETWEEN operator, which is used to fetch the above details of those employees whose salary is between 45000 and 60000. The above statement will display only those employee records whose city name between Chandigarh and Pune and Salary between 45000 and 60000 because we used AND operator.

Output:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYMANAGERID
1002NikhilVani60000NOIDA1
1003VaibhaviMishra50000JAIPUR2
2001RuchikaJain55500CHANDIGARH1
2003BhaveshJain50000MUMBAI4
3001DeepamJauhari55500PUNE3
3002ANUJAWANRE50500JAIPUR2
3003PranotiShende58500MUMBAI3
SQL Between Operator

Example 5: Write a query to display Employee id, First Name, Last Name, Salary, and city from employee table where employee salary between 50000 and 65000 order by city.

SELECT EMPLOYEEID, FIRST_NAME, LAST_NAME, SALARY, CITY, MANAGERID FROM EMPLOYEE WHERE CITY BETWEEN 'CHANDIGARH' AND 'PUNE' AND SALARY BETWEEN 45000 AND 60000;

In the above statement, we fetched the employee id, first name, last name, salary, and city from the employee table of those employees whose salary is between 50000 and 65000. We displayed the result in ascending order by the city column.

Output:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITY
2001RuchikaJain55500CHANDIGARH
1003Vaibhavi  Mishra50000JAIPUR
3002ANUJA     WANRE50500JAIPUR
2003BhaveshJain50000MUMBAI
3003PranotiShende58500MUMBAI
4001RAJESHGOUD60500MUMBAI
1002NikhilVani60000NOIDA
1001VaibhavSharma65000PUNE
3001DeepamJauhari55500PUNE
SQL Between Operator

Example 6: Write a query to display employee id, first name, last name, salary, and city where employee salary is between 45000 and 65000 and where employee city only “Mumbai”,” Pune”.

SELECT EMPLOYEEID, FIRST_NAME, LAST_NAME, SALARY, CITY, MANAGERID FROM EMPLOYEE WHERE CITY BETWEEN 'CHANDIGARH' AND 'PUNE' AND SALARY BETWEEN 45000 AND 60000;

In the above statement, we fetched the employee id, first name, last name, salary, and city from the employee table of those employees whose employee salary is between 45000 and 65000 and city include only Mumbai and Pune.

Output:

EMPLOYEEIDFIRST_NAMELAST_NAMECITYSALARY
1001VaibhavSharmaPUNE65000
2003BhaveshJainMUMBAI50000
3001DeepamJauhariPUNE55500
3003PranotiShendeMUMBAI58500
4001RAJESHGOUDMUMBAI60500
SQL Between Operator

BETWEEN OPERATOR WITH UPDATE STATEMENT

The UPDATE statement modifies the data present inside the SQL tables. We will use BETWEEN operator with the UPDATE statement to modify the records which satisfy the range specified in the statement.

Syntax of BETWEEN operator with the UPDATE statement:

UPDATE TABLE_NAME SET COLUMN_NAME = VALUES WHERE COLUMN_NAME BETWEEN VALUE1 AND VALUE2;

Consider the following tables along with the given records.

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERIDAGEDOJ
1001VaibhavSharma65000PUNEORACLE1232021-09-20
1002NikhilVani60000NOIDAORACLE1212021-09-23
1003VaibhaviMishra50000JAIPURFMW2222021-09-30
2001RuchikaJain55500CHANDIGARHORACLE1222021-09-30
2002PrachiSharma65500PUNEFMW2232021-09-20
2003BhaveshJain50000MUMBAITESTING4212021-09-23
3001DeepamJauhari55500PUNEJAVA3212021-09-23
3002ANUJAWANRE50500JAIPURFMW2222021-09-30
3003PranotiShende58500MUMBAIJAVA3232021-09-20
4001RAJESHGOUD60500MUMBAITESTING4232021-09-20

Example 1: Write a query to modify the employee salary by 1.2 times whose employee city is between Mumbai and Noida.

UPDATE EMPLOYEE SET SALARY = SALARY * 1.2 WHERE CITY BETWEEN ‘MUMBAI’ AND ‘NOIDA’;

In the above statement, we increase the employee salary by 1.2 of those whose city is between Mumbai and Pune. 

To cross-check whether the salary of employees is updated or not successfully, we will use the SELECT statement:

SELECT EMPLOYEEID, SALARY, CITY FROM EMPLOYEE WHERE CITY BETWEEN ‘MUMBAI’ AND ’NOIDA’;
EMPLOYEEIDSALARYCITY
100286400NOIDA
200386400MUMBAI
300384240MUMBAI
400187210MUMBAI
SQL Between Operator

Example 2: Write a query to update the employee city whose date of joining is between 2021-09-20 and 2021-09-22 and salary is between 78500 and 85000.

UPDATE EMPLOYEE SET CITY = 'DELHI' WHERE DOJ BETWEEN '2021-09-20' AND '2021-09-22' AND SALARY BETWEEN 78500 AND 85000;

We have used multiple BETWEEN operators in the statement in the above statement. We are setting employee city to Delhi of those employees whose joining date is between '2021-09-20' AND '2021-09-22' and another between will check whose salary is between 78500 and 85000. If both the BETWEEN operator satisfy the condition for the employee only, then the employee city name will modify.

To cross-check whether the salary of employees is updated or not successfully, we will use the SELECT statement:

SELECT EMPLOYEEID, CITY, SALARY, DOJ FROM EMPLOYEE WHERE DOJ BETWEEN '2021-09-20' AND '2021-09-22' AND SALARY BETWEEN 78500 AND 85000;
EMPLOYEEIDCITYSALARYDOJ
2002DELHI7864002021-09-20
3003DELHI842402021-09-20
SQL Between Operator

BETWEEN OPERATOR WITH DELETE STATEMENT

The DELETE statement is used to delete the records from the SQL table. We use BETWEEN operator with a DELETE statement to delete the records that satisfy the range specified in the statement.

Syntax of BETWEEN operator with the DELETE statement:

DELETE FROM TABLE_NAME WHERE COLUMN_NAME BETWEEN VALUE1 AND VALUE2;

Consider the following tables along with the given records.

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERIDAGEDOJ
1001VaibhavSharma78000PUNEORACLE1232021-09-20
1002NikhilVani86400NOIDAORACLE1212021-09-23
1003VaibhaviMishra50000JAIPURFMW2222021-09-30
2001RuchikaJain55500CHANDIGARHORACLE1222021-09-30
2002PrachiSharma78600DELHIFMW2232021-09-20
2003BhaveshJain86400MUMBAITESTING4212021-09-23
3001DeepamJauhari66600PUNEJAVA3212021-09-23
3002ANUJAWANRE60600JAIPURFMW2222021-09-30
3003PranotiShende84240DELHIJAVA3232021-09-20
4001RAJESHGOUD87120MUMBAITESTING4232021-09-20

Example 1: Write a query to delete the employee details from the employee table of those employees whose salary is between 45000 and 65000.

DELETE FROM EMPLOYEE WHERE SALARY BETWEEN 45000 AND 65000;

In the above statement, we are deleting the employee details of those employees whose salary is between 45000 and 65000.

To cross-check whether the employee details are deleted or not successfully, we will use the SELECT statement:

SELECT * FROM EMPLOYEE;
EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERIDAGEDOJ
1001VaibhavSharma78000PUNEORACLE1232021-09-20
1002NikhilVani86400NOIDAORACLE1212021-09-23
2002PrachiSharma78600DELHIFMW2232021-09-20
2003BhaveshJain86400MUMBAITESTING4212021-09-23
3001DeepamJauhari66600PUNEJAVA3212021-09-23
3003PranotiShende84240DELHIJAVA3232021-09-20
4001RAJESHGOUD87120MUMBAITESTING4232021-09-20
SQL Between Operator

Example 2: Write a query to delete the employee details from the employee table of those employees whose city is between Delhi and Pune.

DELETE FROM EMPLOYEE WHERE CITY BETWEEN ‘DELHI’ AND ‘PUNE’;

In the above statement, we are deleting the employee details of those employees whose city is between Delhi and Pune.

To cross-check whether the employee details are deleted or not successfully, we will use the SELECT statement:

SELECT * FROM EMPLOYEE;
SQL Between Operator



ADVERTISEMENT
ADVERTISEMENT