BETWEEN range filtering in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of BETWEEN with inclusive range

Given the table Products with a column price, what rows will this query return?

SELECT * FROM Products WHERE price BETWEEN 10 AND 20;

MySQL

CREATE TABLE Products (id INT, name VARCHAR(20), price INT);
INSERT INTO Products VALUES (1, 'Pen', 5), (2, 'Notebook', 15), (3, 'Bag', 20), (4, 'Pencil', 25);

A Rows with id 2 only

B Rows with id 1, 2, and 3

C Rows with id 2 and 3

D Rows with id 3 and 4

💡 Hint

BETWEEN includes both the start and end values.

BETWEEN 10 AND 20 includes prices 10, 20 and all values in between. So products with price 15 and 20 match.

❓ query_result

intermediate

BETWEEN with dates

What rows will this query return from the Orders table?

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

MySQL

CREATE TABLE Orders (id INT, order_date DATE);
INSERT INTO Orders VALUES (1, '2022-12-31'), (2, '2023-01-01'), (3, '2023-01-15'), (4, '2023-02-01');

A Rows with id 2 and 3

B Rows with id 1, 2, and 3

C Rows with id 3 and 4

D Rows with id 2, 3, and 4

💡 Hint

BETWEEN includes the boundary dates.

BETWEEN '2023-01-01' AND '2023-01-31' includes all dates from January 1 to January 31, inclusive.

📝 Syntax

advanced

Identify the syntax error in BETWEEN usage

Which option contains a syntax error in using BETWEEN in MySQL?

A SELECT * FROM Employees WHERE salary BETWEEN 3000, 5000;

B SELECT * FROM Employees WHERE salary NOT BETWEEN 3000 AND 5000;

C SELECT * FROM Employees WHERE salary BETWEEN 3000 AND 5000 ORDER BY salary;

D SELECT * FROM Employees WHERE salary BETWEEN 3000 AND 5000;

💡 Hint

BETWEEN requires AND between two values, not a comma.

Option A uses a comma instead of AND, which is invalid syntax for BETWEEN.

❓ optimization

advanced

Optimizing range queries with BETWEEN

Which query is more efficient for filtering ages between 18 and 30 in a large table Users?

A SELECT * FROM Users WHERE age >= 18 AND age <= 30;

B SELECT * FROM Users WHERE age IN (18,19,20,21,22,23,24,25,26,27,28,29,30);

C SELECT * FROM Users WHERE age > 18 AND age < 30;

D SELECT * FROM Users WHERE age BETWEEN 18 AND 30;

💡 Hint

BETWEEN is optimized for range filtering in SQL engines.

BETWEEN is concise and often optimized internally for range checks, making it efficient and readable.

🧠 Conceptual

expert

Understanding BETWEEN with NULL values

What will be the result of this query?

SELECT * FROM Scores WHERE score BETWEEN 50 AND NULL;

Assume the table Scores has some rows with numeric score values.

A Returns all rows where score is 50 or more

B Returns no rows

C Returns all rows where score is NULL

D Raises a syntax error

💡 Hint

Any comparison with NULL results in unknown, so no rows match.

BETWEEN with NULL as an endpoint results in no matches because comparisons with NULL are unknown in SQL.