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.

Practice

1. What does the BETWEEN operator do in a MySQL query?

easy

A. Filters rows where a column's value is NULL.

B. Filters rows where a column's value is within a specified range, inclusive.

C. Filters rows where a column's value is outside a specified range.

D. Filters rows where a column's value is exactly equal to one value.

Solution

1. Step 1: Understand the purpose of BETWEEN

   The BETWEEN operator is used to filter rows where a column's value falls between two values, including both ends.

2. Step 2: Compare options with definition

   Filters rows where a column's value is within a specified range, inclusive. correctly describes this inclusive range filtering. Other options describe different filters.

3. Final Answer:

   Filters rows where a column's value is within a specified range, inclusive. -> Option B

4. Quick Check:

   BETWEEN means inclusive range filter = D [OK]

Hint: BETWEEN includes both start and end values in range [OK]

Common Mistakes:

• Thinking BETWEEN excludes the end values
• Confusing BETWEEN with equality (=)
• Assuming BETWEEN works only with numbers

2. Which of the following is the correct syntax to select rows where the column age is between 18 and 30?

easy

A. SELECT * FROM users WHERE age BETWEEN 18 & 30;

B. SELECT * FROM users WHERE age BETWEEN (18, 30);

C. SELECT * FROM users WHERE age BETWEEN 18 TO 30;

D. SELECT * FROM users WHERE age BETWEEN 18 AND 30;

Solution

1. Step 1: Recall correct BETWEEN syntax

   The correct syntax uses BETWEEN value1 AND value2 without parentheses or other connectors.

2. Step 2: Check each option

   SELECT * FROM users WHERE age BETWEEN 18 AND 30; uses correct syntax. Options B, C, and D use invalid syntax with commas, TO, or &.

3. Final Answer:

   SELECT * FROM users WHERE age BETWEEN 18 AND 30; -> Option D

4. Quick Check:

   BETWEEN ... AND ... syntax = A [OK]

Hint: BETWEEN syntax always uses AND, no commas or TO [OK]

Common Mistakes:

• Using commas inside BETWEEN
• Replacing AND with TO or &
• Adding parentheses around range values

3. Given the table products with a column price, what will this query return?

SELECT * FROM products WHERE price BETWEEN 50 AND 100;

medium

A. All products with price greater than 50 and less than 100, excluding 50 and 100.

B. All products with price exactly 50 or 100 only.

C. All products with price from 50 up to 100, including both 50 and 100.

D. All products with price less than 50 or greater than 100.

Solution

1. Step 1: Understand BETWEEN inclusiveness

   BETWEEN includes both boundary values, so prices 50 and 100 are included.

2. Step 2: Analyze query result

   The query returns products priced from 50 to 100 inclusive, matching All products with price from 50 up to 100, including both 50 and 100..

3. Final Answer:

   All products with price from 50 up to 100, including both 50 and 100. -> Option C

4. Quick Check:

   BETWEEN includes boundaries = C [OK]

Hint: BETWEEN includes start and end values in results [OK]

Common Mistakes:

• Thinking BETWEEN excludes boundary values
• Confusing BETWEEN with > and < operators
• Assuming BETWEEN works only for numbers

4. Identify the error in this query:

SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'

medium

A. No error; the query is correct.

B. BETWEEN cannot be used with date values.

C. The date format is incorrect for BETWEEN.

D. Missing semicolon at the end of the query.

Solution

1. Step 1: Check BETWEEN usage with dates

   BETWEEN works perfectly with date values in MySQL when dates are in 'YYYY-MM-DD' format.

2. Step 2: Verify query syntax

   The query syntax is correct; semicolon is optional in many clients and not an error here.

3. Final Answer:

   No error; the query is correct. -> Option A

4. Quick Check:

   BETWEEN works with dates correctly = A [OK]

Hint: BETWEEN works with numbers, dates, and text [OK]

Common Mistakes:

• Thinking BETWEEN can't filter dates
• Expecting semicolon is mandatory always
• Using wrong date formats like MM-DD-YYYY

5. You want to find all employees hired between January 1, 2020, and December 31, 2022, but your query returns no rows:

SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2022-12-31 23:59:59';

What is the most likely reason and how to fix it?

hard

A. hire_date column stores only dates without time; remove time part from end date.

B. BETWEEN does not work with datetime values; use >= and <= instead.

C. The date format is wrong; use MM-DD-YYYY format.

D. The query is correct; the table is empty.

Solution

1. Step 1: Understand data type of hire_date

   If hire_date stores only DATE (no time), comparing with '2022-12-31 23:59:59' fails because time part is ignored or mismatched.

2. Step 2: Fix the query

   Use '2022-12-31' without time to match date-only values correctly.

3. Final Answer:

   hire_date column stores only dates without time; remove time part from end date. -> Option A

4. Quick Check:

   Match data type format in BETWEEN range = B [OK]

Hint: Match data types exactly when using BETWEEN with dates [OK]

Common Mistakes:

• Adding time to date-only columns in BETWEEN
• Assuming BETWEEN fails with datetime
• Using wrong date formats