WHERE with comparison operators in SQL - Mini Project: Build & Apply

Filtering Products with WHERE and Comparison Operators

📖 Scenario: You are managing a small store's product database. You want to find products based on their prices to decide which items to promote or reorder.

🎯 Goal: Build SQL queries step-by-step to filter products using the WHERE clause with comparison operators like =, <, >, <=, and >=.

📋 What You'll Learn

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

Insert specific product data into the products table.

Write a query to select products with price greater than a given value.

Write a query to select products with price less than or equal to a given value.

💡 Why This Matters

🌍 Real World

Filtering products by price helps store managers decide which items to promote or reorder based on cost.

💼 Career

Knowing how to use WHERE with comparison operators is essential for database querying in roles like data analyst, backend developer, and database administrator.

1

Create the products table

Write a SQL statement to create a table called products with three columns: id as an integer primary key, name as text, and price as a number.

SQL

-- Write the CREATE TABLE statement for products
-- Your code here

Need a hint?

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

2

Insert product data

Insert these exact products into the products table: (1, 'Apple', 0.5), (2, 'Banana', 0.3), (3, 'Carrot', 0.2), (4, 'Doughnut', 1.0), (5, 'Eggplant', 0.8).

SQL

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price NUMERIC
);
-- Insert the 5 products below
-- Your code here

Need a hint?

Use a single INSERT INTO products (id, name, price) VALUES statement with all 5 rows.

3

Select products with price greater than 0.5

Write a SQL query to select all columns from products where the price is greater than 0.5 using the WHERE clause and the > operator.

SQL

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price NUMERIC
);
INSERT INTO products (id, name, price) VALUES
  (1, 'Apple', 0.5),
  (2, 'Banana', 0.3),
  (3, 'Carrot', 0.2),
  (4, 'Doughnut', 1.0),
  (5, 'Eggplant', 0.8);
-- Select products with price > 0.5 below
-- Your code here

Need a hint?

Use SELECT * FROM products WHERE price > 0.5;

4

Select products with price less than or equal to 0.5

Write a SQL query to select all columns from products where the price is less than or equal to 0.5 using the WHERE clause and the <= operator.

SQL

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price NUMERIC
);
INSERT INTO products (id, name, price) VALUES
  (1, 'Apple', 0.5),
  (2, 'Banana', 0.3),
  (3, 'Carrot', 0.2),
  (4, 'Doughnut', 1.0),
  (5, 'Eggplant', 0.8);
SELECT * FROM products WHERE price > 0.5;
-- Select products with price <= 0.5 below
-- Your code here

Need a hint?

Use SELECT * FROM products WHERE price <= 0.5;