SQL Between

SQL Between

The BETWEEN is a logical operator in SQL, which fetches the records from the table within the range specified in the query. We can use this operator to select numbers, text, or dates. The BETWEEN operator in SQL includes the starting and ending values.

Example:

SELECT column_Name1, column_Name2 …., column_NameN FROM table_Name WHERE column_name BETWEEN value1 and value2 ;

The BETWEEN operator returns True if the value of column_name is greater than or equal to the value1 and less than or equal to the value2.

Let’s take the following STUDENT table, which helps you to understand the BETWEEN operator:

Suppose, we want to fetch the record of those students from the below student table whose ‘marks’ are greater than or equal to 80 and less than or equal to 100. For this operation, we have to perform the following query in Structured Query Language:

SELECT * FROM STUDENT WHERE Marks BETWEEN 80 and 100 ; 

This query shows the following table:

Let’s take the following STUDENT table, which helps you to understand the NOT BETWEEN operator: 

Suppose, we want to fetch the record of those students from the below student table whose marks are neither greater than 80 nor less than 100. For this operation, we have to perform the following query in Structured Query Language:

SELECT * FROM STUDENT WHERE Marks NOT BETWEEN 80 and 100 ; 

This query shows the following table:

Let’s take the following EMPLOYEE table, which helps you to understand the BETWEEN operator with dates:

Suppose, we want to fetch the record of those employees from the above employee table whose joining data is between 2019/06/05 and 2020/12/20. For this operation, we have to type the following query in SQL:

SELECT * FROM EMPLOYEE WHERE Emp_Joining BETWEEN ‘2019/06/05’ and ‘2020/12/20’ ; 

This query shows the following table:

Course Video

NOTE: Practice below questions on site editor.

1.Write SQL query to find the modified between ‘2011-05-31’ and ‘2012-05-30’. (44 row count)

Table: (Production_ProductListPriceHistory) 

Output:

2.Write SQL query to get all Detail from Sales_Customer table where TerritoryID  = ‘1′;

Output:

3. Make a SQL Query to find modified Date between ‘2007-11-11’ and ‘2009-01-14’.

Table :(HumanResources_EmployeeDepartmentHistory)

Output:

4. Write a simple SQL query to get contactTypeID between ‘1’ and ‘5’.

Table :(Person_ContactType)(5 row count)

Output:

5.Make a of SQL to find the ProductPhotoID ‘69’ and ‘76’.

Table:(Production_ProductPhoto) Output:

6. Craft a SQL query to retrieve comprehensive data pertaining to employees whose sick leave hours fall within the specified range of 50 to 100 hours . [OUTPUT: 35ROWS]

7. Formulate a SQL query to extract records from the Production.ProductModel table, specifically targeting product models whose modification dates coincide with the year 2013. [ OUTPUT: 41 ROWS]

8. Develop a SQL query to retrieve records of sales customers from the database, focusing on those whose associated store IDs fall within the inclusive range of 1000 to 2000. [OUTPUT:53 ROWS]

9. Craft a SQL query to fetch sales order header records from the database, specifically excluding those with sales order numbers falling outside the range delineated by ‘SO43718’ and ‘SO43918’. [OUTPUT: 59ROWS]

10. Construct a SQL query to extract purchase order details from the database, precisely excluding those with order quantities falling within the range of 500 to 550 units. [output: 55 rows]