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

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

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

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

Hint

Use SELECT * FROM products WHERE price <= 0.5;

Practice

1. What does the WHERE clause do in an SQL query?

easy

A. Filters rows based on a condition

B. Sorts rows in ascending order

C. Joins two tables together

D. Creates a new table

Solution

1. Step 1: Understand the purpose of WHERE

   The WHERE clause is used to select only those rows that meet a specific condition.

2. Step 2: Compare with other SQL clauses

   Sorting is done by ORDER BY, joining by JOIN, and creating tables by CREATE TABLE, so these are not correct.

3. Final Answer:

   Filters rows based on a condition -> Option A

4. Quick Check:

   WHERE filters rows = A [OK]

Hint: WHERE filters rows; ORDER BY sorts rows [OK]

Common Mistakes:

• Confusing WHERE with ORDER BY
• Thinking WHERE creates or joins tables
• Assuming WHERE changes table structure

2. Which of the following is the correct syntax to select rows where the column age is greater than 30?

easy

A. SELECT * FROM table WHERE age >= 30;

B. SELECT * FROM table WHERE age > 30;

C. SELECT * FROM table WHERE age >> 30;

D. SELECT * FROM table WHERE age = > 30;

Solution

1. Step 1: Identify correct comparison operator

   The operator for 'greater than' is >, so age > 30 is correct.

2. Step 2: Check syntax correctness

   The option with SELECT * FROM table WHERE age = > 30; has invalid operator '= >', the one with '>>' uses bitwise shift, and the one with '>=' means 'greater or equal to', not strictly greater.

3. Final Answer:

   SELECT * FROM table WHERE age > 30; -> Option B

4. Quick Check:

   Use > for greater than, not >= or >> [OK]

Hint: Use > for greater than, >= for greater or equal [OK]

Common Mistakes:

• Using '= >' instead of '>'
• Confusing '>' with '>>'
• Using '>=' when only '>' is needed

3. Given the table Employees with columns id, name, and salary, what rows will this query return?

SELECT * FROM Employees WHERE salary <= 50000;

medium

A. All employees with salary greater than 50000

B. All employees with salary exactly 50000 only

C. All employees with salary less than or equal to 50000

D. No employees, syntax error

Solution

1. Step 1: Understand the comparison operator used

   The operator '<=' means 'less than or equal to', so it selects salaries 50000 or less.

2. Step 2: Interpret the query result

   The query returns all rows where salary is 50000 or below, not just exactly 50000 or above 50000.

3. Final Answer:

   All employees with salary less than or equal to 50000 -> Option C

4. Quick Check:

   salary <= 50000 means salary ≤ 50000 [OK]

Hint: <= means less than or equal to [OK]

Common Mistakes:

• Thinking <= means only equal
• Confusing <= with <
• Assuming syntax error in query

4. Identify the error in this SQL query:

SELECT * FROM Products WHERE price <>;

medium

A. Wrong comparison operator used

B. Incorrect table name

C. Missing SELECT keyword

D. Missing value after comparison operator

Solution

1. Step 1: Analyze the WHERE clause

   The operator '<>' means 'not equal', but it must be followed by a value to compare.

2. Step 2: Identify syntax error

   Here, '<>' is not followed by any value, causing a syntax error.

3. Final Answer:

   Missing value after comparison operator -> Option D

4. Quick Check:

   Comparison operators need a value after them [OK]

Hint: Comparison operators must have a value after them [OK]

Common Mistakes:

• Leaving out value after operator
• Confusing <> with != (both valid but must have value)
• Assuming table or SELECT keyword is missing

5. You want to select all orders from the Orders table where the order_date is after January 1, 2023, and the total_amount is at least 100. Which query correctly uses WHERE with comparison operators?

hard

A. SELECT * FROM Orders WHERE order_date > '2023-01-01' AND total_amount >= 100;

B. SELECT * FROM Orders WHERE order_date >= '2023-01-01' OR total_amount > 100;

C. SELECT * FROM Orders WHERE order_date < '2023-01-01' AND total_amount > 100;

D. SELECT * FROM Orders WHERE order_date = '2023-01-01' AND total_amount = 100;

Solution

1. Step 1: Understand the date condition

   We want orders after January 1, 2023, so use 'order_date > '2023-01-01''.

2. Step 2: Understand the amount condition

   We want total_amount at least 100, so use 'total_amount >= 100'. Both conditions must be true, so use AND.

3. Step 3: Check each option

   SELECT * FROM Orders WHERE order_date > '2023-01-01' AND total_amount >= 100; matches both conditions correctly. SELECT * FROM Orders WHERE order_date >= '2023-01-01' OR total_amount > 100; uses OR and >= for date, which is incorrect. SELECT * FROM Orders WHERE order_date < '2023-01-01' AND total_amount > 100; uses < for date, wrong direction. SELECT * FROM Orders WHERE order_date = '2023-01-01' AND total_amount = 100; uses = which is too strict.

4. Final Answer:

   SELECT * FROM Orders WHERE order_date > '2023-01-01' AND total_amount >= 100; -> Option A

5. Quick Check:

   Use > for after date, >= for at least amount, AND to combine [OK]

Hint: Use AND to combine conditions, > for after date, >= for minimum amount [OK]

Common Mistakes:

• Using OR instead of AND
• Using >= for date when strictly after is needed
• Using = instead of comparison operators