BETWEEN for range filtering in PostgreSQL - Mini Project: Build & Apply

Filter Products by Price Range Using BETWEEN

📖 Scenario: You work for an online store that sells various products. The store manager wants to see all products priced within a specific range to plan promotions.

🎯 Goal: Build a SQL query that selects products with prices between two given values using the BETWEEN operator.

📋 What You'll Learn

Create a table called products with columns id, name, and price

Insert exactly 5 products with specified prices

Define two variables min_price and max_price for the price range

Write a SQL query using BETWEEN to select products within the price range

💡 Why This Matters

🌍 Real World

Filtering products by price range is common in e-commerce to show customers relevant items within their budget.

💼 Career

Knowing how to use BETWEEN for range filtering is essential for database querying and reporting tasks in many data-related jobs.

1

Create the products table and insert data

Create a table called products with columns id (integer), name (text), and price (numeric). Then insert these 5 products exactly: (1, 'Pen', 1.20), (2, 'Notebook', 2.50), (3, 'Backpack', 25.00), (4, 'Calculator', 15.75), and (5, 'Desk Lamp', 18.00).

PostgreSQL

-- Create the products table and insert 5 products
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO with multiple rows to add products.

2

Define price range variables

Define two variables called min_price and max_price and set them to 10.00 and 20.00 respectively. Use WITH clause to define these variables for use in the next query.

PostgreSQL

CREATE TABLE products (
    id INTEGER,
    name TEXT,
    price NUMERIC
);

INSERT INTO products (id, name, price) VALUES
(1, 'Pen', 1.20),
(2, 'Notebook', 2.50),
(3, 'Backpack', 25.00),
(4, 'Calculator', 15.75),
(5, 'Desk Lamp', 18.00);

-- Define min_price and max_price variables using WITH clause
-- Your code here

Hint

Use a WITH clause to create a temporary table named params holding min_price and max_price.

3

Write the query using BETWEEN

Using the params CTE, write a query to select id, name, and price from products where price is between min_price and max_price using the BETWEEN operator.

PostgreSQL

CREATE TABLE products (
    id INTEGER,
    name TEXT,
    price NUMERIC
);

INSERT INTO products (id, name, price) VALUES
(1, 'Pen', 1.20),
(2, 'Notebook', 2.50),
(3, 'Backpack', 25.00),
(4, 'Calculator', 15.75),
(5, 'Desk Lamp', 18.00);

WITH params AS (
    SELECT 10.00 AS min_price, 20.00 AS max_price
)
-- Write a SELECT query joining products and params to filter prices using BETWEEN
-- Your code here

Hint

Use BETWEEN params.min_price AND params.max_price in the WHERE clause to filter.

4

Complete the query with ordering

Add an ORDER BY clause to the query to sort the results by price in ascending order.

PostgreSQL

CREATE TABLE products (
    id INTEGER,
    name TEXT,
    price NUMERIC
);

INSERT INTO products (id, name, price) VALUES
(1, 'Pen', 1.20),
(2, 'Notebook', 2.50),
(3, 'Backpack', 25.00),
(4, 'Calculator', 15.75),
(5, 'Desk Lamp', 18.00);

WITH params AS (
    SELECT 10.00 AS min_price, 20.00 AS max_price
)
SELECT p.id, p.name, p.price
FROM products p, params
WHERE p.price BETWEEN params.min_price AND params.max_price
-- Add ORDER BY price ascending
-- Your code here

Hint

Use ORDER BY p.price ASC to sort results by price from low to high.

Practice

1. What does the BETWEEN operator do in PostgreSQL?

easy

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

B. Filters rows where a column's value is exactly equal to a single value.

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

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

Solution

1. Step 1: Understand the purpose of BETWEEN

   The BETWEEN operator checks if a value lies between two given values, including both ends.

2. Step 2: Compare with other filtering options

   Unlike equality or NULL checks, BETWEEN covers a range inclusively.

3. Final Answer:

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

4. Quick Check:

   BETWEEN means inclusive range filter [OK]

Hint: BETWEEN includes both range ends, like a closed interval [OK]

Common Mistakes:

• Thinking BETWEEN excludes the range ends
• 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 (inclusive)?

easy

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

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

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

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

Solution

1. Step 1: Recall correct BETWEEN syntax

   The correct syntax uses BETWEEN followed by the lower and upper bounds separated by AND.

2. Step 2: Check each option

   SELECT * FROM users WHERE age BETWEEN 18 AND 30; uses BETWEEN 18 AND 30 correctly; others have syntax errors or wrong keywords.

3. Final Answer:

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

4. Quick Check:

   BETWEEN ... AND ... is correct syntax [OK]

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

Common Mistakes:

• Using TO instead of AND
• Using incomplete comparison operators
• Misplacing parentheses or commas

3. Given the table orders with a column order_date of type DATE, what will this query return?

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

medium

A. Orders placed exactly on January 1, 2023, only.

B. All orders placed before January 1, 2023.

C. All orders placed after January 31, 2023.

D. All orders placed from January 1, 2023, to January 31, 2023, inclusive.

Solution

1. Step 1: Understand the BETWEEN date range

   The query filters orders where order_date is between '2023-01-01' and '2023-01-31', including both dates.

2. Step 2: Interpret the result set

   It returns all orders placed on any date from January 1 to January 31, 2023, inclusive.

3. Final Answer:

   All orders placed from January 1, 2023, to January 31, 2023, inclusive. -> Option D

4. Quick Check:

   BETWEEN includes both start and end dates [OK]

Hint: BETWEEN dates includes both start and end days [OK]

Common Mistakes:

• Assuming BETWEEN excludes the end date
• Confusing BETWEEN with less than or greater than only
• Thinking it returns only one date

4. Identify the error in this query:

SELECT * FROM products WHERE price BETWEEN 100, 500;

medium

A. The BETWEEN clause uses a comma instead of AND between values.

B. The price column cannot be used with BETWEEN.

C. BETWEEN requires parentheses around the values.

D. The query is correct and will run without errors.

Solution

1. Step 1: Check BETWEEN syntax

   BETWEEN requires two values separated by AND, not a comma.

2. Step 2: Identify the syntax error

   The query uses a comma between 100 and 500, which is invalid syntax.

3. Final Answer:

   The BETWEEN clause uses a comma instead of AND between values. -> Option A

4. Quick Check:

   BETWEEN needs AND, not commas [OK]

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

Common Mistakes:

• Using commas instead of AND
• Adding unnecessary parentheses
• Assuming BETWEEN works with any separator

5. You want to find all employees hired between January 1, 2020, and December 31, 2022, but exclude those hired exactly on January 1, 2020. Which query achieves this?

hard

A. All of the above queries will work correctly.

B. SELECT * FROM employees WHERE hire_date > '2020-01-01' AND hire_date <= '2022-12-31';

C. SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-02' AND '2022-12-31';

D. SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2022-12-31' AND hire_date <> '2020-01-01';

Solution

1. Step 1: Understand the requirement

   We want employees hired after January 1, 2020, up to December 31, 2022, excluding January 1, 2020 itself.

2. Step 2: Analyze each query

   SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2022-12-31' AND hire_date <> '2020-01-01'; uses BETWEEN including '2020-01-01' but excludes it with <> condition.
   SELECT * FROM employees WHERE hire_date > '2020-01-01' AND hire_date <= '2022-12-31'; uses > and <= to exclude '2020-01-01' naturally.
   SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-02' AND '2022-12-31'; shifts the BETWEEN range to start from '2020-01-02', excluding '2020-01-01'.

3. Step 3: Confirm all queries meet the condition

   All three queries exclude '2020-01-01' and include dates up to '2022-12-31'.

4. Final Answer:

   All of the above queries will work correctly. -> Option A

5. Quick Check:

   Multiple ways to exclude start date in range [OK]

Hint: Exclude start date by > or adjust BETWEEN start or add <> [OK]

Common Mistakes:

• Assuming BETWEEN excludes start date by default
• Using BETWEEN without excluding the start date
• Confusing BETWEEN with exclusive range