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

Adblocker detected! Please consider reading this notice.

We've detected that you are using AdBlock Plus or some other adblocking software which is preventing the page from fully loading.

We don't have any banner, Flash, animation, obnoxious sound, or popup ad. We do not implement these annoying types of ads!

We need money to operate the site, and almost all of it comes from our online advertising.

Please add wikitechy.com to your ad blocking whitelist or disable your adblocking software.

×