BETWEEN for range filtering in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL BETWEEN operator do?

The BETWEEN operator filters rows where a column's value is within a specified range, including the start and end values.

beginner

Write a SQL query using BETWEEN to find all orders with amounts between 100 and 500.

SELECT * FROM orders WHERE amount BETWEEN 100 AND 500;

beginner

Does BETWEEN include the boundary values in the range?

Yes, BETWEEN includes both the start and end values in the range check.

intermediate

How is BETWEEN different from using >= and <= operators?

BETWEEN is a shorthand for using >= and <= together. For example, 'x BETWEEN a AND b' is the same as 'x >= a AND x <= b'.

beginner

Can BETWEEN be used with dates in PostgreSQL?

Yes, BETWEEN works with dates to filter rows within a date range, including the start and end dates.

Which of the following queries correctly finds products priced between 50 and 100?

A SELECT * FROM products WHERE price BETWEEN 50 AND 100;

B SELECT * FROM products WHERE price > 50 AND price < 100;

C SELECT * FROM products WHERE price = 50 OR price = 100;

D SELECT * FROM products WHERE price >= 50 OR price <= 100;

BETWEEN includes both 50 and 100, so option C correctly filters prices from 50 to 100 inclusive.

Does the BETWEEN operator include the boundary values in its range?

A It includes only the lower boundary.

B No, it excludes both boundaries.

C Yes, it includes both boundaries.

D It includes only the upper boundary.

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

Which is equivalent to 'salary BETWEEN 3000 AND 7000'?

A salary >= 3000 AND salary <= 7000

B salary = 3000 OR salary = 7000

C salary > 3000 OR salary < 7000

D salary > 3000 AND salary < 7000

BETWEEN is the same as using >= for the lower bound and <= for the upper bound.

Can BETWEEN be used to filter date columns in PostgreSQL?

A Only with special date functions.

B No, BETWEEN only works with numbers.

C Only if dates are converted to strings.

D Yes, it works with dates.

BETWEEN works with dates to filter rows within a date range.

What will this query return? SELECT * FROM sales WHERE sale_date BETWEEN '2024-01-01' AND '2024-01-31';

A Sales only on January 1 and January 31, 2024.

B Sales from January 1 to January 31, 2024, inclusive.

C Sales before January 1, 2024.

D Sales after January 31, 2024.

BETWEEN includes both start and end dates, so it returns sales during the entire month of January 2024.

Explain how the BETWEEN operator works for filtering numeric ranges in SQL.

Think about how you check if a number is between two others.

Describe how you would use BETWEEN to filter rows by date in PostgreSQL.

Dates can be compared like numbers in SQL.