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

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

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

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

Hint

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

Practice

1. What does the SQL clause WHERE column_name IN (value1, value2, value3) do?

easy

A. It selects rows where column_name matches any value in the list.

B. It updates rows with values in the list.

C. It deletes rows not matching the list.

D. It creates a new table with listed values.

Solution

1. Step 1: Understand the IN clause purpose

   The IN clause checks if a column's value matches any value inside the parentheses list.

2. Step 2: Identify the effect on row selection

   Rows with the column value equal to any listed value are selected; others are ignored.

3. Final Answer:

   It selects rows where column_name matches any value in the list. -> Option A

4. Quick Check:

   WHERE with IN filters rows by listed values [OK]

Hint: IN checks if value matches any in the list [OK]

Common Mistakes:

• Thinking IN updates or deletes rows
• Confusing IN with creating tables
• Assuming IN matches all values at once

2. Which of the following is the correct syntax to select rows where status is either 'active', 'pending', or 'closed'?

easy

A. SELECT * FROM orders WHERE status = IN ('active', 'pending', 'closed');

B. SELECT * FROM orders WHERE status IN ('active', 'pending', 'closed');

C. SELECT * FROM orders WHERE status IN 'active', 'pending', 'closed';

D. SELECT * FROM orders WHERE status IN ('active' 'pending' 'closed');

Solution

1. Step 1: Check correct use of IN syntax

   The correct syntax is column_name IN (value1, value2, ...) without an equals sign.

2. Step 2: Identify correct punctuation and parentheses

   Values must be inside parentheses and separated by commas.

3. Final Answer:

   SELECT * FROM orders WHERE status IN ('active', 'pending', 'closed'); -> Option B

4. Quick Check:

   IN syntax = column IN (values) [OK]

Hint: No '=' before IN; use commas inside parentheses [OK]

Common Mistakes:

• Using '=' before IN
• Missing commas between values
• Omitting parentheses around values

3. Given the table employees with column department, what rows will this query return?

SELECT * FROM employees WHERE department IN ('HR', 'Sales');

medium

A. Rows where department is 'HR' or 'Sales'.

B. Rows where department is 'HR' only.

C. Rows where department is not 'HR' or 'Sales'.

D. All rows regardless of department.

Solution

1. Step 1: Understand the IN filter on department

   The query filters rows where department matches any value in ('HR', 'Sales').

2. Step 2: Determine which rows match

   Only rows with department 'HR' or 'Sales' are returned; others are excluded.

3. Final Answer:

   Rows where department is 'HR' or 'Sales'. -> Option A

4. Quick Check:

   IN filters rows matching listed values [OK]

Hint: IN returns rows matching any listed value [OK]

Common Mistakes:

• Thinking it returns rows not in the list
• Assuming it returns only one value
• Confusing IN with NOT IN

4. Identify the error in this SQL query:

SELECT * FROM products WHERE category IN 'Electronics', 'Toys', 'Books';

medium

A. Incorrect table name.

B. Using IN instead of = operator.

C. Missing parentheses around the list of values.

D. Missing commas between values.

Solution

1. Step 1: Check syntax of IN clause

   The list of values after IN must be enclosed in parentheses.

2. Step 2: Identify missing parentheses

   The query has values listed without parentheses, causing syntax error.

3. Final Answer:

   Missing parentheses around the list of values. -> Option C

4. Quick Check:

   IN requires parentheses around values [OK]

Hint: Always put parentheses around IN value list [OK]

Common Mistakes:

• Omitting parentheses around values
• Using IN with equals sign
• Forgetting commas between values

5. You want to select customers from the orders table whose country is either 'USA', 'Canada', or 'Mexico', but exclude those with status 'cancelled'. Which query correctly uses WHERE with IN and excludes cancelled orders?

hard

A. SELECT * FROM orders WHERE country IN 'USA', 'Canada', 'Mexico' AND status != 'cancelled';

B. SELECT * FROM orders WHERE country IN ('USA', 'Canada', 'Mexico') OR status != 'cancelled';

C. SELECT * FROM orders WHERE country = IN ('USA', 'Canada', 'Mexico') AND status != 'cancelled';

D. SELECT * FROM orders WHERE country IN ('USA', 'Canada', 'Mexico') AND status != 'cancelled';

Solution

1. Step 1: Use IN correctly for country filter

   The country column must be checked with IN ('USA', 'Canada', 'Mexico') without equals sign.

2. Step 2: Exclude cancelled orders with AND condition

   Use AND status != 'cancelled' to exclude those rows.

3. Final Answer:

   SELECT * FROM orders WHERE country IN ('USA', 'Canada', 'Mexico') AND status != 'cancelled'; -> Option D

4. Quick Check:

   IN filters list, AND excludes cancelled [OK]

Hint: Use AND to combine IN filter and exclusion [OK]

Common Mistakes:

• Using OR instead of AND causing wrong results
• Adding '=' before IN
• Omitting parentheses around IN values