WHERE with IN list in SQL - Mini Project: Build & Apply

Filter Products Using WHERE with IN List

📖 Scenario: You are managing a small store's product database. You want to find products that belong to specific categories to prepare a special sale.

🎯 Goal: Build an SQL query that selects products from the products table where the category is one of a given list using the WHERE ... IN clause.

📋 What You'll Learn

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

Insert exactly five products with specified categories.

Create a list of categories to filter.

Write a SELECT query using WHERE category IN (...) to get products in those categories.

💡 Why This Matters

🌍 Real World

Filtering products by categories is common in online stores to show customers only relevant items.

💼 Career

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

1

Create the products table and insert data

Write SQL statements to create a table called products with columns id (integer), name (text), and category (text). Then insert these five products exactly: (1, 'Apple', 'Fruit'), (2, 'Carrot', 'Vegetable'), (3, 'Banana', 'Fruit'), (4, 'Broccoli', 'Vegetable'), (5, 'Chicken', 'Meat').

SQL

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

Need a hint?

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

2

Define the list of categories to filter

Create a variable or placeholder named categories that holds the list of categories 'Fruit' and 'Meat' to filter products by.

SQL

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

INSERT INTO products (id, name, category) VALUES
  (1, 'Apple', 'Fruit'),
  (2, 'Carrot', 'Vegetable'),
  (3, 'Banana', 'Fruit'),
  (4, 'Broccoli', 'Vegetable'),
  (5, 'Chicken', 'Meat');

-- Define the categories list to filter
-- Your code here

Need a hint?

In standard SQL, you cannot create variables like this, but for learning, write a comment line with categories = ('Fruit', 'Meat') to represent the list.

3

Write the SELECT query using WHERE with IN list

Write a SELECT statement to get all columns from products where the category is in the list ('Fruit', 'Meat') using the WHERE category IN ('Fruit', 'Meat') clause.

SQL

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

INSERT INTO products (id, name, category) VALUES
  (1, 'Apple', 'Fruit'),
  (2, 'Carrot', 'Vegetable'),
  (3, 'Banana', 'Fruit'),
  (4, 'Broccoli', 'Vegetable'),
  (5, 'Chicken', 'Meat');

-- Define categories list
-- This is a comment placeholder since SQL does not support variables in standard queries
-- We will use the list directly in the next step
-- categories = ('Fruit', 'Meat')

-- Write the SELECT query to get products in categories 'Fruit' and 'Meat'
-- Your code here

Need a hint?

Use WHERE category IN ('Fruit', 'Meat') to filter products by these categories.

4

Complete the query with ORDER BY clause

Add an ORDER BY name ASC clause at the end of the SELECT query to sort the results by product name in ascending order.

SQL

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

INSERT INTO products (id, name, category) VALUES
  (1, 'Apple', 'Fruit'),
  (2, 'Carrot', 'Vegetable'),
  (3, 'Banana', 'Fruit'),
  (4, 'Broccoli', 'Vegetable'),
  (5, 'Chicken', 'Meat');

-- Define categories list
-- This is a comment placeholder since SQL does not support variables in standard queries
-- We will use the list directly in the next step
-- categories = ('Fruit', 'Meat')

SELECT * FROM products
WHERE category IN ('Fruit', 'Meat')
-- Add ORDER BY clause here

Need a hint?

Use ORDER BY name ASC to sort the results by the product name from A to Z.