WHERE with BETWEEN range in SQL - Mini Project: Build & Apply

Filter Products by Price Range Using WHERE with BETWEEN

📖 Scenario: You are managing a small online store 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 given values using the WHERE clause with BETWEEN.

📋 What You'll Learn

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

Insert exactly these products with prices: (1, 'Pen', 5), (2, 'Notebook', 15), (3, 'Backpack', 45), (4, 'Calculator', 55), (5, 'Desk Lamp', 35).

Define two variables min_price and max_price with values 10 and 40 respectively.

Write a SQL query that selects name and price from products where price is between min_price and max_price.

💡 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 WHERE with BETWEEN helps database professionals write efficient queries for filtering data based on ranges.

1

Create the products table and insert data

Write SQL statements to create a table called products with columns id (integer), name (text), and price (integer). Then insert these exact rows: (1, 'Pen', 5), (2, 'Notebook', 15), (3, 'Backpack', 45), (4, 'Calculator', 55), and (5, 'Desk Lamp', 35).

SQL

-- Create table products and insert data
-- Your code here

Need a hint?

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

2

Define price range variables

Write SQL statements to define two variables called min_price and max_price with values 10 and 40 respectively. Use SET statements for this purpose.

SQL

CREATE TABLE products (id INTEGER, name TEXT, price INTEGER);
INSERT INTO products (id, name, price) VALUES
(1, 'Pen', 5),
(2, 'Notebook', 15),
(3, 'Backpack', 45),
(4, 'Calculator', 55),
(5, 'Desk Lamp', 35);
-- Define min_price and max_price variables
-- Your code here

Need a hint?

Use SET variable_name = value; to define variables in SQL.

3

Write the SELECT query with WHERE and BETWEEN

Write a SQL SELECT statement to get the name and price columns from the products table where the price is between the variables min_price and max_price. Use the WHERE clause with BETWEEN.

SQL

CREATE TABLE products (id INTEGER, name TEXT, price INTEGER);
INSERT INTO products (id, name, price) VALUES
(1, 'Pen', 5),
(2, 'Notebook', 15),
(3, 'Backpack', 45),
(4, 'Calculator', 55),
(5, 'Desk Lamp', 35);

SET @min_price = 10;
SET @max_price = 40;
-- Write SELECT query with WHERE price BETWEEN min_price AND max_price
-- Your code here

Need a hint?

Use SELECT columns FROM table WHERE column BETWEEN value1 AND value2; syntax.

4

Complete the query with ORDER BY price ascending

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

SQL

CREATE TABLE products (id INTEGER, name TEXT, price INTEGER);
INSERT INTO products (id, name, price) VALUES
(1, 'Pen', 5),
(2, 'Notebook', 15),
(3, 'Backpack', 45),
(4, 'Calculator', 55),
(5, 'Desk Lamp', 35);

SET @min_price = 10;
SET @max_price = 40;

SELECT name, price FROM products WHERE price BETWEEN @min_price AND @max_price
-- Add ORDER BY price ascending
-- Your code here

Need a hint?

Use ORDER BY column_name ASC to sort results in ascending order.