WHERE with BETWEEN range in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL BETWEEN operator do in a WHERE clause?

The BETWEEN operator filters the results to include only rows where a column's value falls within a specified range, including the boundary values.

beginner

Write a SQL query to select all products with prices between 10 and 20 inclusive.

SELECT * FROM products WHERE price BETWEEN 10 AND 20;

beginner

Is the BETWEEN operator inclusive or exclusive of the range boundaries?

The BETWEEN operator is inclusive, meaning it includes both the start and end values of the range.

intermediate

Can BETWEEN be used with dates in SQL?

Yes, BETWEEN works with dates to filter rows where a date column falls within a specified date range.

intermediate

What is the equivalent of WHERE column BETWEEN 5 AND 10 using comparison operators?

It is equivalent to WHERE column >= 5 AND column <= 10.

Which of the following SQL queries correctly selects rows where age is between 18 and 30 inclusive?

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

B SELECT * FROM users WHERE age = 18 AND age = 30;

C SELECT * FROM users WHERE age >= 18 OR age <= 30;

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

Option D uses BETWEEN which includes both 18 and 30. Option A excludes 18 and 30. Option C uses OR which is incorrect. Option B is impossible because age cannot be both 18 and 30.

What will the query SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31'; return?

A Orders placed from January 1 to January 31, 2024, inclusive.

B Orders placed only on January 1 and January 31, 2024.

C Orders placed before January 1, 2024.

D Orders placed after January 31, 2024.

The BETWEEN operator includes all dates from January 1 to January 31, 2024, inclusive.

Which of these is NOT true about the BETWEEN operator?

A It includes the boundary values.

B It can be used with numbers, dates, and text.

C It is case-sensitive when used with text.

D It can replace multiple comparison operators.

BETWEEN itself is not case-sensitive; case sensitivity depends on the database collation settings, not the operator.

How would you rewrite WHERE score BETWEEN 50 AND 100 using only comparison operators?

A WHERE score >= 50 AND score <= 100

B WHERE score = 50 OR score = 100

C WHERE score > 50 OR score < 100

D WHERE score > 50 AND score < 100

The BETWEEN operator is inclusive, so it is equivalent to using >= and <=.

If you want to find records where a value is NOT between 10 and 20, which SQL clause would you use?

A WHERE value BETWEEN 10 AND 20

B WHERE value NOT BETWEEN 10 AND 20

C WHERE value >= 10 AND value <= 20

D WHERE value = 10 OR value = 20

NOT BETWEEN excludes values within the range, including boundaries.

Explain how the BETWEEN operator works in a SQL WHERE clause and give an example.

Think about filtering rows within a range of values.

Describe how you can use BETWEEN with dates and why it might be useful.

Consider filtering records within a specific time period.