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.

Practice

1. What does the SQL clause WHERE column BETWEEN 10 AND 20 do?

easy

A. Selects rows where the column value is greater than 10 but less than 20, excluding 10 and 20

B. Selects rows where the column value is between 10 and 20, including 10 and 20

C. Selects rows where the column value is exactly 10 or exactly 20 only

D. Selects rows where the column value is less than 10 or greater than 20

Solution

1. Step 1: Understand BETWEEN clause behavior

   The BETWEEN clause includes both the start and end values in the range.

2. Step 2: Apply to the given range 10 to 20

   This means values equal to 10, 20, or any number between them are selected.

3. Final Answer:

   Selects rows where the column value is between 10 and 20, including 10 and 20 -> Option B

4. Quick Check:

   BETWEEN includes boundaries = B [OK]

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

Common Mistakes:

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

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

easy

A. SELECT * FROM users WHERE age BETWEEN (18 AND 30);

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

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

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

Solution

1. Step 1: Recall correct BETWEEN syntax

   The correct syntax uses BETWEEN start AND end without commas or parentheses.

2. Step 2: Check each option

   SELECT * FROM users WHERE age BETWEEN 18 AND 30; uses correct syntax: BETWEEN 18 AND 30. Others have syntax errors.

3. Final Answer:

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

4. Quick Check:

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

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

Common Mistakes:

• Using TO instead of AND
• Adding commas between values
• Using parentheses around BETWEEN clause

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

SELECT * FROM products WHERE price BETWEEN 50 AND 100;

Assume the prices in the table are: 45, 50, 75, 100, 120.

medium

A. Rows with prices 50, 75, and 100

B. Rows with prices 45, 50, 75, 100, and 120

C. Rows with prices 75 and 100 only

D. Rows with prices 50 and 100 only

Solution

1. Step 1: Identify the range in BETWEEN clause

   The query filters prices between 50 and 100, including both 50 and 100.

2. Step 2: Check which prices fall in this range

   Prices 50, 75, and 100 are within the range; 45 and 120 are outside.

3. Final Answer:

   Rows with prices 50, 75, and 100 -> Option A

4. Quick Check:

   BETWEEN includes boundaries, so 50 and 100 included = A [OK]

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

Common Mistakes:

• Excluding boundary values 50 and 100
• Including values outside the range like 45 or 120
• Confusing BETWEEN with > and < operators

4. Identify the error in this SQL query:

SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' TO '2023-01-31';

medium

A. Using TO instead of AND in BETWEEN clause

B. Dates should not be in quotes

C. BETWEEN cannot be used with dates

D. Missing semicolon at the end

Solution

1. Step 1: Check BETWEEN syntax

   BETWEEN requires AND between the two values, not TO.

2. Step 2: Validate other parts

   Dates in quotes are correct, BETWEEN works with dates, and semicolon is optional in many systems.

3. Final Answer:

   Using TO instead of AND in BETWEEN clause -> Option A

4. Quick Check:

   BETWEEN uses AND, not TO = A [OK]

Hint: BETWEEN always uses AND, never TO [OK]

Common Mistakes:

• Replacing AND with TO in BETWEEN
• Thinking dates can't be quoted
• Assuming semicolon is mandatory

5. You want to select employees hired between January 1, 2020, and December 31, 2022, inclusive. Which query correctly uses BETWEEN and also excludes employees hired on weekends?

SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2022-12-31' AND EXTRACT(DOW FROM hire_date) NOT IN (0,6);

What does this query do?

hard

A. Selects employees hired between the dates including weekends

B. Selects employees hired only on weekends between the dates

C. Selects employees hired between the dates excluding weekends

D. Syntax error due to EXTRACT usage

Solution

1. Step 1: Understand BETWEEN usage

   The query selects employees hired from 2020-01-01 to 2022-12-31 inclusive.

2. Step 2: Analyze EXTRACT(DOW FROM hire_date) condition

   EXTRACT(DOW) returns day of week (0=Sunday, 6=Saturday). NOT IN (0,6) excludes weekends.

3. Step 3: Combine conditions

   Only employees hired on weekdays within the date range are selected.

4. Final Answer:

   Selects employees hired between the dates excluding weekends -> Option C

5. Quick Check:

   BETWEEN + weekday filter = C [OK]

Hint: Use BETWEEN for range and filter weekdays with EXTRACT [OK]

Common Mistakes:

• Assuming BETWEEN excludes boundaries
• Confusing day numbers for weekend days
• Thinking EXTRACT causes syntax error