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

Need a 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

Need a 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

Need a 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

Need a hint?

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