WHERE with BETWEEN range in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of BETWEEN with inclusive range

Given the table Employees with a column Age, what rows will be returned by this query?

SELECT * FROM Employees WHERE Age BETWEEN 25 AND 30;

SQL

CREATE TABLE Employees (ID INT, Name VARCHAR(20), Age INT);
INSERT INTO Employees VALUES (1, 'Alice', 24), (2, 'Bob', 25), (3, 'Carol', 28), (4, 'Dave', 30), (5, 'Eve', 31);

A Rows with Age 25, 26, 27, 28, 29, 30, 31

B Rows with Age 26, 27, 28, 29

C Rows with Age 25, 28, and 30

D Rows with Age 24, 25, 26, 27, 28, 29, 30

💡 Hint

BETWEEN includes the boundary values.

The BETWEEN operator includes both the start and end values, so ages 25, 26, 27, 28, 29, and 30 are included. Ages 24 and 31 are outside the range.

🧠 Conceptual

intermediate

Understanding BETWEEN with dates

Which of the following queries correctly selects records from Orders where OrderDate is between January 1, 2023 and January 31, 2023 inclusive?

A SELECT * FROM Orders WHERE OrderDate BETWEEN '2023-01-01' AND '2023-01-31';

B SELECT * FROM Orders WHERE OrderDate > '2023-01-01' AND OrderDate < '2023-01-31';

C SELECT * FROM Orders WHERE OrderDate >= '2023-01-01' AND OrderDate <= '2023-01-31';

D SELECT * FROM Orders WHERE OrderDate > '2023-01-01' AND OrderDate <= '2023-01-31';

💡 Hint

BETWEEN includes both start and end dates.

BETWEEN is inclusive, so option A correctly includes orders on January 1 and January 31. Option A is equivalent but option A uses BETWEEN syntax.

📝 Syntax

advanced

Identify the syntax error in BETWEEN usage

Which option contains a syntax error in the use of BETWEEN in this query?

SELECT * FROM Products WHERE Price BETWEEN 10 AND;

A SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

B SELECT * FROM Products WHERE Price BETWEEN 10 AND 20 OR Stock > 0;

C SELECT * FROM Products WHERE Price BETWEEN 10;

D SELECT * FROM Products WHERE Price BETWEEN 10 AND 20 AND Stock > 0;

💡 Hint

BETWEEN requires two values separated by AND.

Option C is missing the second value after AND, causing a syntax error.

❓ optimization

advanced

Optimizing BETWEEN with indexes

Given a large table Sales with an index on SaleDate, which query will best use the index to filter sales between two dates?

A SELECT * FROM Sales WHERE SaleDate > '2023-01-01' AND SaleDate < '2023-01-31';

B SELECT * FROM Sales WHERE SaleDate >= '2023-01-01' AND SaleDate <= '2023-01-31';

C SELECT * FROM Sales WHERE YEAR(SaleDate) = 2023 AND MONTH(SaleDate) = 1;

D SELECT * FROM Sales WHERE SaleDate BETWEEN '2023-01-01' AND '2023-01-31';

💡 Hint

Functions on indexed columns can prevent index use.

Option D uses functions on SaleDate which disables index usage. Options A, B, and D use the index well. Option D excludes boundary dates.

🔧 Debug

expert

Why does this BETWEEN query return no rows?

Consider this query:

SELECT * FROM Events WHERE EventDate BETWEEN '2023-05-01' AND '2023-04-30';

Why does it return no rows even though the table has events in April and May 2023?

A Because the dates are in the wrong format and cause an error.

B Because the start date is after the end date, BETWEEN returns no rows.

C Because BETWEEN only works with numeric columns, not dates.

D Because the query needs to use >= and <= instead of BETWEEN.

💡 Hint

BETWEEN expects the first value to be less than or equal to the second.

BETWEEN requires the first value to be less than or equal to the second. Here, '2023-05-01' is after '2023-04-30', so no rows match.