BETWEEN for range filtering in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of BETWEEN with inclusive range

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

SELECT * FROM products WHERE price BETWEEN 10 AND 20;

PostgreSQL

CREATE TABLE products (id INT, name TEXT, price INT);
INSERT INTO products VALUES (1, 'Pen', 5), (2, 'Notebook', 10), (3, 'Backpack', 15), (4, 'Calculator', 20), (5, 'Chair', 25);

A Rows with price less than 20

B Rows with price 10, 15, and 20

C Rows with price 10 and 20 only

D Rows with price 11 to 19 only

💡 Hint

BETWEEN includes both the start and end values.

The BETWEEN operator in SQL is inclusive, so it includes rows where price is exactly 10 and exactly 20, as well as any values in between.

📝 Syntax

intermediate

Correct syntax for BETWEEN with dates

Which of the following queries correctly filters rows with order_date between January 1, 2023 and January 31, 2023 (inclusive)?

A SELECT * FROM orders WHERE order_date BETWEEN '01/01/2023' AND '01/31/2023';

B SELECT * FROM orders WHERE order_date BETWEEN 2023-01-01 AND 2023-01-31;

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

D SELECT * FROM orders WHERE order_date BETWEEN '2023/01/01' AND '2023/01/31';

💡 Hint

Date literals in PostgreSQL should be in ISO format and enclosed in single quotes.

Option C uses the correct ISO date format 'YYYY-MM-DD' with quotes, which PostgreSQL recognizes as dates. Other options either lack quotes or use non-standard formats.

❓ optimization

advanced

Optimizing BETWEEN for indexed columns

You have a large table sales with an index on sale_date. Which query is most efficient to find sales between '2023-01-01' and '2023-01-31'?

A SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31';

B SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date <= '2023-01-31';

C SELECT * FROM sales WHERE sale_date > '2023-01-01' AND sale_date < '2023-01-31';

D SELECT * FROM sales WHERE sale_date IN ('2023-01-01', '2023-01-31');

💡 Hint

BETWEEN is often optimized by the database engine for range queries on indexed columns.

BETWEEN is equivalent to >= and <= but is clearer and often optimized by PostgreSQL to use the index efficiently. Option A excludes boundary dates, and D only matches exact dates.

🔧 Debug

advanced

Why does this BETWEEN query return no rows?

Given the table events with a start_time column of type TIMESTAMP, why does this query return no rows?

SELECT * FROM events WHERE start_time BETWEEN '2023-03-10' AND '2023-03-11';

Assume there are rows with start_time on '2023-03-11 15:00:00'.

A Because the query syntax is invalid for TIMESTAMP columns

B Because BETWEEN does not work with TIMESTAMP columns

C Because the dates are in the wrong format

D Because '2023-03-11' is treated as '2023-03-11 00:00:00', excluding later times that day

💡 Hint

Consider how date strings without time default in TIMESTAMP comparisons.

The string '2023-03-11' is interpreted as midnight at the start of that day, so rows with times later on '2023-03-11' are excluded. To include the whole day, the end boundary should be '2023-03-11 23:59:59' or use < '2023-03-12'.

🧠 Conceptual

expert

BETWEEN behavior with NULL values

Consider a table employees with a column age that can be NULL. What will this query return?

SELECT * FROM employees WHERE age BETWEEN 30 AND 40;

Specifically, what happens to rows where age is NULL?

A Rows with NULL age are excluded because NULL comparisons return UNKNOWN

B Rows with NULL age are included because BETWEEN treats NULL as a wildcard

C The query returns an error due to NULL values in BETWEEN

D Rows with NULL age are included only if 30 or 40 is NULL

💡 Hint

Remember how SQL treats NULL in comparisons.

In SQL, any comparison with NULL returns UNKNOWN, which is treated as false in WHERE clauses. Therefore, rows with NULL in the age column are excluded from the result.