BETWEEN range filtering in MySQL - Mini Project: Build & Apply

Filter Products by Price Range Using BETWEEN

📖 Scenario: You are managing a small online store's database. You want to find products within a specific price range to create a special discount list.

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

📋 What You'll Learn

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

Insert exactly three products with given prices.

Define two variables for the minimum and maximum 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 online stores to show customers affordable options or special deals.

💼 Career

Knowing how to use BETWEEN for range filtering is essential for database querying in roles like data analyst, backend developer, and database administrator.

1

Create the products table and insert data

Create a table called products with columns id (integer), name (text), and price (decimal). Insert these three products exactly: (1, 'Pen', 1.20), (2, 'Notebook', 2.50), and (3, 'Backpack', 25.00).

MySQL

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

Need a hint?

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

2

Define minimum and maximum price variables

Define two variables called @min_price and @max_price and set them to 1.00 and 3.00 respectively.

MySQL

CREATE TABLE products (
  id INT,
  name VARCHAR(50),
  price DECIMAL(5,2)
);

INSERT INTO products (id, name, price) VALUES
(1, 'Pen', 1.20),
(2, 'Notebook', 2.50),
(3, 'Backpack', 25.00);

-- Define @min_price and @max_price variables
-- Your code here

Need a hint?

Use SET to assign values to variables in MySQL.

3

Write a query using BETWEEN to filter products

Write a SQL SELECT query to get all columns from products where the price is between @min_price and @max_price using the BETWEEN operator.

MySQL

CREATE TABLE products (
  id INT,
  name VARCHAR(50),
  price DECIMAL(5,2)
);

INSERT INTO products (id, name, price) VALUES
(1, 'Pen', 1.20),
(2, 'Notebook', 2.50),
(3, 'Backpack', 25.00);

SET @min_price = 1.00;
SET @max_price = 3.00;

-- Write SELECT query using BETWEEN to filter by price
-- Your code here

Need a hint?

The BETWEEN operator includes the boundary values.

4

Complete the query with ordering by price

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

MySQL

CREATE TABLE products (
  id INT,
  name VARCHAR(50),
  price DECIMAL(5,2)
);

INSERT INTO products (id, name, price) VALUES
(1, 'Pen', 1.20),
(2, 'Notebook', 2.50),
(3, 'Backpack', 25.00);

SET @min_price = 1.00;
SET @max_price = 3.00;

SELECT * FROM products WHERE price BETWEEN @min_price AND @max_price
-- Add ORDER BY price ASC here

Need a hint?

Use ORDER BY to sort query results.