CASE in WHERE clause in SQL - Mini Project: Build & Apply

Using CASE in WHERE Clause in SQL

📖 Scenario: You are managing a small store's database. You want to filter products based on their category and price using a flexible condition.

🎯 Goal: Build an SQL query that uses a CASE expression inside the WHERE clause to filter products differently depending on their category.

📋 What You'll Learn

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

Insert exactly 5 products with specified values.

Create a variable price_limit with value 100.

Write a SELECT query that uses CASE inside the WHERE clause to filter products:

If category is 'Electronics', select products with price less than price_limit.

Otherwise, select products with price less than 50.

💡 Why This Matters

🌍 Real World

Filtering products dynamically based on category and price is common in e-commerce and inventory management.

💼 Career

Understanding CASE in WHERE clauses helps write flexible SQL queries for reports and data analysis.

1

Create the products table and insert data

Create a table called products with columns id (integer), name (text), category (text), and price (integer). Then insert these 5 products exactly: (1, 'Laptop', 'Electronics', 120), (2, 'Mouse', 'Electronics', 25), (3, 'Shirt', 'Clothing', 40), (4, 'Book', 'Books', 30), (5, 'Headphones', 'Electronics', 80).

SQL

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

Need a hint?

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

2

Set the price limit variable

Create a variable called price_limit and set it to 100. Use a SQL variable declaration or a common table expression (CTE) to hold this value.

SQL

CREATE TABLE products (
  id INTEGER,
  name TEXT,
  category TEXT,
  price INTEGER
);

INSERT INTO products (id, name, category, price) VALUES
  (1, 'Laptop', 'Electronics', 120),
  (2, 'Mouse', 'Electronics', 25),
  (3, 'Shirt', 'Clothing', 40),
  (4, 'Book', 'Books', 30),
  (5, 'Headphones', 'Electronics', 80);

-- Declare price_limit variable with value 100
-- Your code here

Need a hint?

Use a CTE with WITH price_limit AS (SELECT 100 AS limit_value) to create the variable.

3

Write the SELECT query with CASE in WHERE clause

Write a SELECT query to get all columns from products. Use the price_limit CTE. In the WHERE clause, use a CASE expression to filter rows: if category is 'Electronics', select products where price < price_limit.limit_value, else select products where price < 50.

SQL

CREATE TABLE products (
  id INTEGER,
  name TEXT,
  category TEXT,
  price INTEGER
);

INSERT INTO products (id, name, category, price) VALUES
  (1, 'Laptop', 'Electronics', 120),
  (2, 'Mouse', 'Electronics', 25),
  (3, 'Shirt', 'Clothing', 40),
  (4, 'Book', 'Books', 30),
  (5, 'Headphones', 'Electronics', 80);

WITH price_limit AS (SELECT 100 AS limit_value)
-- Write SELECT query using CASE in WHERE clause
-- Your code here

Need a hint?

Use CASE WHEN category = 'Electronics' THEN price_limit.limit_value ELSE 50 END inside the WHERE clause.

4

Complete the query with proper join syntax

Modify the query to use an explicit CROSS JOIN between products and price_limit instead of the comma join. Keep the WHERE clause with the CASE expression unchanged.

SQL

CREATE TABLE products (
  id INTEGER,
  name TEXT,
  category TEXT,
  price INTEGER
);

INSERT INTO products (id, name, category, price) VALUES
  (1, 'Laptop', 'Electronics', 120),
  (2, 'Mouse', 'Electronics', 25),
  (3, 'Shirt', 'Clothing', 40),
  (4, 'Book', 'Books', 30),
  (5, 'Headphones', 'Electronics', 80);

WITH price_limit AS (SELECT 100 AS limit_value)
-- Replace comma join with CROSS JOIN
-- Your code here

Need a hint?

Use CROSS JOIN instead of comma to join products and price_limit.