Part 6: Mastering the BETWEEN Command | SQL Challenges & Capstone
SQL Filtering & Search Commands Practice · Part 6 · Lecture Notes
Mastering the BETWEEN Command
How to filter records within defined ranges — numeric thresholds, date windows, and financial boundaries, always inclusive of both boundary values.
Part 6 of 6100% Complete
BETWEEN
Numeric Ranges
Date Ranges
Financial Thresholds
8 Practice Questions
Learning Objectives
Use BETWEEN to filter records whose column value falls within a defined numeric or date range
Understand that BETWEEN is inclusive — both boundary values are included in the results
Apply BETWEEN to date columns using YYYY-MM-DD format in single quotes
Combine BETWEEN with ORDER BY to produce sorted, range-filtered business reports
Click any section header to open or close it
BETWEEN filters records that fall within a defined range — inclusive of both boundary values. It works on numbers, dates, and any orderable data type. BETWEEN is the cleaner, more readable alternative to writing WHERE column >= value1 AND column <= value2.
In industry, analysts use BETWEEN to analyze financial ranges, filter date periods, isolate pricing thresholds, review business activity windows, and identify records that fall within defined operational boundaries.
The Basic Structure — and Inclusive Boundaries
BETWEEN is always inclusive — both the lower and upper boundary values are included in the results. WHERE amount BETWEEN 40 AND 120 returns rows where amount equals 40, equals 120, and everything in between.
Basic BETWEEN Syntax
SELECT column_name
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
BETWEEN on numeric columns is the most common use case — filtering prices, amounts, quantities, or IDs within a specific range. The database returns all rows where the column value falls at or between the two boundary numbers.
BETWEEN vs >= AND <=
WHERE unit_price BETWEEN 30 AND 100 is equivalent to WHERE unit_price >= 30 AND unit_price <= 100. Both produce identical results. BETWEEN is preferred for readability when the intent is a range check.
Products Priced Between 30 and 100
SELECT *
FROM products
WHERE unit_price BETWEEN 30 AND 100;
Transactions Between 40 and 120
SELECT *
FROM transactions
WHERE amount BETWEEN 40 AND 120;
BETWEEN is one of the most useful date filtering tools in SQL. By specifying a start date and an end date, analysts can isolate all records that fall within a business period — a month, a quarter, a campaign window, or any custom date range.
Date Format in MySQL
Dates in MySQL BETWEEN clauses must be written as strings in YYYY-MM-DD format inside single quotes. BETWEEN ‘2024-02-01’ AND ‘2024-03-31’ returns all records from February 1 through March 31, 2024 inclusive.
Orders Placed February to March 2024
SELECT *
FROM orders
WHERE order_date BETWEEN ‘2024-02-01’ AND ‘2024-03-31’;
Financial analysis frequently requires isolating records within a value range — transactions between a minimum and maximum threshold, subscriptions within a fee band, or products within a price tier. BETWEEN handles all of these cleanly in a single clause.
Combining BETWEEN with ORDER BY
BETWEEN filters which rows appear; ORDER BY controls how they are arranged. Adding ORDER BY unit_price DESC after a BETWEEN filter shows the most expensive qualifying products first — a common pattern for pricing analysis.
Subscriptions Within a Monthly Fee Range
SELECT *
FROM subscriptions
WHERE monthly_fee BETWEEN 20 AND 50;
Products in Range — Sorted Highest to Lowest
SELECT
product_name,
ROUND(unit_price, 2) AS unit_price
FROM products
WHERE unit_price BETWEEN 40 AND 150
ORDER BY unit_price DESC;
Work through each question below. Run your query first, then use Check Answer. A wrong answer reveals the correct SQL with a Copy button; a correct result earns a congratulations message.
Practice 1Retrieve products with unit_price between 30 and 100 rounded to 2 decimal places
AI Prompt
Write a MySQL query to show products with unit_price between 30 and 100 rounded to 2 decimal places.
SQL Answer
SELECT
product_name,
ROUND(unit_price, 2) AS unit_price
FROM products
WHERE unit_price BETWEEN 30 AND 100;
Practice 2Retrieve transactions with amount between 40 and 130 rounded to 2 decimal places
AI Prompt
Write a MySQL query to show transactions with amount between 40 and 130 rounded to 2 decimal places.
SQL Answer
SELECT
transaction_id,
ROUND(amount, 2) AS amount
FROM transactions
WHERE amount BETWEEN 40 AND 130;
Practice 3Retrieve orders placed between 2024-02-01 and 2024-03-31
AI Prompt
Write a MySQL query to show orders placed between 2024-02-01 and 2024-03-31.
SQL Answer
SELECT *
FROM orders
WHERE order_date BETWEEN ‘2024-02-01’ AND ‘2024-03-31’;
Practice 4Retrieve users who signed up between 2024-01-01 and 2024-03-31
AI Prompt
Write a MySQL query to show users who signed up between 2024-01-01 and 2024-03-31.
SQL Answer
SELECT *
FROM users
WHERE signup_date BETWEEN ‘2024-01-01’ AND ‘2024-03-31’;
Practice 5Retrieve subscriptions with monthly_fee between 19 and 50 rounded to 2 decimal places
AI Prompt
Write a MySQL query to show subscriptions with monthly_fee between 19 and 50 rounded to 2 decimal places.
SQL Answer
SELECT
subscription_id,
ROUND(monthly_fee, 2) AS monthly_fee
FROM subscriptions
WHERE monthly_fee BETWEEN 19 AND 50;
Practice 6Retrieve campaign events between 2024-03-01 and 2024-04-30
AI Prompt
Write a MySQL query to show campaign events between 2024-03-01 and 2024-04-30.
SQL Answer
SELECT *
FROM campaign_events
WHERE event_date BETWEEN ‘2024-03-01’ AND ‘2024-04-30’;
Practice 7Retrieve support tickets created between 2024-01-01 and 2024-03-31
AI Prompt
Write a MySQL query to show support tickets created between 2024-01-01 and 2024-03-31.
SQL Answer
SELECT *
FROM support_tickets
WHERE created_date BETWEEN ‘2024-01-01’ AND ‘2024-03-31’;
Practice 8Retrieve products with unit_price between 40 and 150 rounded to 2 decimal places sorted from highest to lowest price
AI Prompt
Write a MySQL query to show products with unit_price between 40 and 150 rounded to 2 decimal places sorted from highest to lowest price.
SQL Answer
SELECT
product_name,
ROUND(unit_price, 2) AS unit_price
FROM products
WHERE unit_price BETWEEN 40 AND 150
ORDER BY unit_price DESC;
Knowledge Check
Five questions — select the best answer for each.
1. What does BETWEEN do in a WHERE clause?
AIt sorts results between two column values
BIt filters records whose column value falls within a defined range, inclusive of boundaries
CIt removes values between two numbers
DIt finds records exactly equal to both boundary values
Correct. BETWEEN returns rows where the column value is greater than or equal to the lower boundary and less than or equal to the upper boundary — both boundaries are included.
Not quite. BETWEEN filters records that fall within a range, inclusive of both boundary values.
2. Is BETWEEN inclusive or exclusive of the boundary values?
AExclusive — boundary values are not included
BIt depends on the data type
CInclusive — both boundary values are included
DThe lower boundary is included but the upper is excluded
Correct. BETWEEN is always inclusive. WHERE amount BETWEEN 40 AND 120 returns rows where amount = 40, amount = 120, and everything in between.
Not quite. BETWEEN is inclusive — both boundary values are included in the results.
3. Which query correctly returns orders placed in February and March 2024?
AWHERE order_date BETWEEN 2024-02-01 AND 2024-03-31
BWHERE order_date BETWEEN ‘2024-02-01’ AND ‘2024-03-31’
CWHERE order_date IN (‘2024-02-01’, ‘2024-03-31’)
DWHERE order_date > ‘2024-02-01’ OR order_date < '2024-03-31'
Correct. Dates in MySQL BETWEEN clauses must be in YYYY-MM-DD format inside single quotes. Option A omits the quotes; options C and D use the wrong approach.
Not quite. MySQL dates in BETWEEN must be written as quoted strings in YYYY-MM-DD format: BETWEEN ‘2024-02-01’ AND ‘2024-03-31’.
4. WHERE amount BETWEEN 40 AND 130 is equivalent to which condition?
AWHERE amount > 40 AND amount < 130
BWHERE amount >= 40 AND amount <= 130
CWHERE amount >= 40 OR amount <= 130
DWHERE amount IN (40, 130)
Correct. BETWEEN is shorthand for >= lower AND <= upper. Both boundaries are included, which is why >= and <= (not > and <) are the equivalent operators.
Not quite. BETWEEN is equivalent to >= lower AND <= upper — both boundaries inclusive.
5. An analyst wants to filter transactions sorted highest to lowest within a range. Which query structure is correct?
AWHERE amount BETWEEN 50 AND 150 THEN ORDER BY amount DESC
BORDER BY amount DESC WHERE amount BETWEEN 50 AND 150
CSELECT … FROM … WHERE amount BETWEEN 50 AND 150 ORDER BY amount DESC
DSELECT … FROM … ORDER BY amount DESC BETWEEN 50 AND 150
Correct. WHERE comes before ORDER BY. BETWEEN goes inside the WHERE clause; ORDER BY sorts the filtered results afterward.
Not quite. The correct order is WHERE (with BETWEEN) followed by ORDER BY: WHERE amount BETWEEN 50 AND 150 ORDER BY amount DESC.
Part 6 Summary
The key ideas from this lecture — keep these in mind as you move forward.
BETWEEN helps analysts isolate business records that fall within numeric, financial, and date-based ranges. BETWEEN is always inclusive of both boundary values. Dates must be quoted strings in YYYY-MM-DD format. BETWEEN can be combined with ORDER BY for sorted, ranged output.
