BETWEEN Command in SQL - sql - sql tutorial - learn sql




 various operation in sql data
  • The BETWEEN operator is used when the filtering criteria is a continuous range with a maximum value and a minimum value.
  • It is always used in the WHERE clause.
  • The BETWEEN operator selects values within a given range.
  • The values can be numbers, text, or dates.
  • The BETWEEN operator is inclusive: begin and end values are included.
 between operator process in sql

Syntax

  • The syntax for the BETWEEN operator is as follows:
SELECT "column_name"
FROM "table_name"
WHERE "column_name" BETWEEN 'value1' AND 'value2';
  • This will select all rows whose column has a value between 'value1' and 'value2.'

Examples

  • We use the following table for our examples.
  • Table Store_Information
Store_Name Sales Txn_Date
Alabama 700 March-06-2017
Ohio 1500 March -08-2017
Texas 250 March-09-2017
Wyoming 500 March-09-2017

Example 1

  • To select view all sales information between March-07-2017, and March-10-2017, we key in,
SELECT *
FROM Store_Information
WHERE Txn_Date BETWEEN ' March-07-2017' AND ' March-10-2017;
  • Note that date may be stored in different formats in different databases.
  • This tutorial simply choose one of the formats.

Result:

Store_Name Sales Txn_Date
Ohio 1500 March -08-2017
Texas 250 March-09-2017
Wyoming 500 March-09-2017
  • BETWEEN is an inclusive operator, meaning that 'value1' and 'value2' are included in the result.
  • If we wish to exclude 'value1' and 'value2' but include everything in between, we need to change the query to the following:
SELECT "column_name"
FROM "table_name"
WHERE ("column_name" > 'value1')
AND ("column_name" < 'value2');

Example 2

  • We can also use the BETWEEN operator to exclude a range of values by adding NOT in front of BETWEEN.
  • In the above example, if we want to show all rows where the Sales column is not between 280 and 1000, we will use the following SQL:
SELECT *
FROM Store_Information
WHERE Sales NOT BETWEEN 280 and 1000;

Result:

Store_Name Sales Txn_Date
Ohio 1500 March -08-2017
Texas 250 March-09-2017

Exercises

  • For these exercises,
  • assume we have a table called User_Sales with the following data:
  • Table User_Sales
First_Name Last_Name Birth_Date Gender Join_Date Total_Sales
Jill Wilkes Jan-05-1960 F Apr-05-2017 500
Jamal Santo Jan-07-1975 M Apr-05-2017 200
Casey Healy Oct-08-1983 M Apr-09-2017 350
Sophie Lee Sep-20-1969 F Apr-09-2017 80
Richard Brown Nov-20-1979 M Apr-15-2017 210

Interview questions for BETWEEN command in SQL

  • Which of the following SQL statement is valid? (There can be more than one answer)
    • SELECT * FROM User_Sales WHERE Total_Sales BETWEEN 200 OR 300;
    • SELECT * FROM User_Sales WHERE Total_Sales IS BETWEEN 200 OR 300;
    • SELECT * FROM User_Sales WHERE Total_Sales IS BETWEEN 200 AND 300;
    • SELECT * FROM User_Sales WHERE Total_Sales BETWEEN 200 AND 300;

Answer: d

SELECT * FROM User_Sales WHERE Join_Date BETWEEN 'Apr-05-2015' AND 'Apr-10-2015';

Answer: 4 records are returned. They are,

First_Name Last_Name Birth_Date Gender Join_Date Total_Sales
Jill Wilkes Jan-05-1960 F Apr-05-2017 500
Jamal Santo Jan-07-1975 M Apr-05-2017 200
Casey Healy Oct-08-1983 M Apr-09-2017 350
Sophie Lee Sep-20-1969 F Apr-09-2017 80
  • How many records will be returned by the following query?
SELECT * FROM User_Sales WHERE Gender = 'F' OR Total_Sales BETWEEN 50 AND 100;

Answer: 3 record are returned. They are,

First_Name Last_Name Birth_Date Gender Join_Date Total_Sales
Jill Wilkes Jan-05-1960 F Apr-05-2017 500
Sophie Lee Sep-20-1969 F Apr-09-2017 80
Richard Brown Nov-20-1979 M Apr-15-2017 210

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to BETWEEN Command in SQL