Non-equi joins in SQL - Mini Project: Build & Apply

Using Non-equi Joins in SQL

📖 Scenario: You work for a retail company that wants to categorize products based on their price ranges. The company has a table of products with their prices and a separate table defining price categories with minimum and maximum price limits.Your task is to join these two tables to assign each product to the correct price category using a non-equi join.

🎯 Goal: Create a SQL query that uses a non-equi join to match each product with the correct price category based on its price.

📋 What You'll Learn

Create a products table with columns product_id, product_name, and price.

Create a price_categories table with columns category_id, category_name, min_price, and max_price.

Write a SQL query that joins products and price_categories using a non-equi join condition on price between min_price and max_price.

Select product_name and category_name in the final output.

💡 Why This Matters

🌍 Real World

Retail companies often categorize products by price ranges to help customers filter and compare items easily.

💼 Career

Understanding non-equi joins is important for data analysts and database developers who work with complex data relationships that are not simple equals.

1

Create the products table and insert data

Write SQL statements to create a table called products with columns product_id (integer), product_name (text), and price (integer). Then insert these exact rows: (1, 'Laptop', 1200), (2, 'Smartphone', 800), (3, 'Tablet', 400), (4, 'Headphones', 150).

SQL

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

Need a hint?

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

2

Create the price_categories table and insert data

Write SQL statements to create a table called price_categories with columns category_id (integer), category_name (text), min_price (integer), and max_price (integer). Then insert these exact rows: (1, 'Budget', 0, 300), (2, 'Midrange', 301, 900), (3, 'Premium', 901, 2000).

SQL

CREATE TABLE products (
    product_id INTEGER,
    product_name TEXT,
    price INTEGER
);

INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Laptop', 1200),
(2, 'Smartphone', 800),
(3, 'Tablet', 400),
(4, 'Headphones', 150);

-- Create the price_categories table and insert data
-- Your code here

Need a hint?

Define the price_categories table with the four columns and insert the given rows.

3

Write the non-equi join query

Write a SQL SELECT query that joins products and price_categories using a non-equi join condition where products.price is between price_categories.min_price and price_categories.max_price. Select product_name and category_name in the output.

SQL

CREATE TABLE products (
    product_id INTEGER,
    product_name TEXT,
    price INTEGER
);

INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Laptop', 1200),
(2, 'Smartphone', 800),
(3, 'Tablet', 400),
(4, 'Headphones', 150);

CREATE TABLE price_categories (
    category_id INTEGER,
    category_name TEXT,
    min_price INTEGER,
    max_price INTEGER
);

INSERT INTO price_categories (category_id, category_name, min_price, max_price) VALUES
(1, 'Budget', 0, 300),
(2, 'Midrange', 301, 900),
(3, 'Premium', 901, 2000);

-- Write the SELECT query with non-equi join below
-- Your code here

Need a hint?

Use JOIN with the ON clause using the BETWEEN operator for the non-equi join.

4

Complete the query with ordering

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

SQL

CREATE TABLE products (
    product_id INTEGER,
    product_name TEXT,
    price INTEGER
);

INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Laptop', 1200),
(2, 'Smartphone', 800),
(3, 'Tablet', 400),
(4, 'Headphones', 150);

CREATE TABLE price_categories (
    category_id INTEGER,
    category_name TEXT,
    min_price INTEGER,
    max_price INTEGER
);

INSERT INTO price_categories (category_id, category_name, min_price, max_price) VALUES
(1, 'Budget', 0, 300),
(2, 'Midrange', 301, 900),
(3, 'Premium', 901, 2000);

SELECT product_name, category_name
FROM products
JOIN price_categories ON products.price BETWEEN price_categories.min_price AND price_categories.max_price
-- Add ORDER BY clause below
-- Your code here

Need a hint?

Use ORDER BY product_name ASC to sort the results alphabetically by product name.