WHERE with AND operator in SQL - Mini Project: Build & Apply

Filtering Data Using WHERE with AND Operator

📖 Scenario: You are working with a small store's database. The store wants to find products that are both affordable and in stock.

🎯 Goal: Build an SQL query that selects products with a price less than 20 and quantity greater than 0 using the WHERE clause with the AND operator.

📋 What You'll Learn

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

Insert exactly these rows into products: (1, 'Pen', 5, 10), (2, 'Notebook', 15, 0), (3, 'Eraser', 3, 5), (4, 'Backpack', 25, 3).

Create a variable or placeholder for the price limit set to 20.

Write a SELECT query to get name and price from products where price is less than the price limit and quantity is greater than 0 using WHERE with AND.

Complete the query with a semicolon.

💡 Why This Matters

🌍 Real World

Filtering data with multiple conditions is common in business reports, inventory checks, and customer queries.

💼 Career

Knowing how to use WHERE with AND helps in writing precise database queries for data analysis and application development.

1

Create the products table and insert data

Write SQL statements to create a table called products with columns id (integer), name (text), price (integer), and quantity (integer). Then insert these exact rows: (1, 'Pen', 5, 10), (2, 'Notebook', 15, 0), (3, 'Eraser', 3, 5), and (4, 'Backpack', 25, 3).

SQL

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

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO with multiple rows to add the data.

2

Set the price limit variable

Write an SQL statement to set a variable called price_limit to 20. Use a WITH clause or a common table expression (CTE) to hold this value.

SQL

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

INSERT INTO products (id, name, price, quantity) VALUES
(1, 'Pen', 5, 10),
(2, 'Notebook', 15, 0),
(3, 'Eraser', 3, 5),
(4, 'Backpack', 25, 3);

-- Define price_limit as 20
-- Your code here

Need a hint?

Use a WITH clause to create a named value for the price limit.

3

Write the SELECT query with WHERE and AND

Write a SELECT query to get name and price from products where price is less than price_limit.limit_value and quantity is greater than 0. Use the WHERE clause with the AND operator. Use the price_limit CTE defined earlier.

SQL

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

INSERT INTO products (id, name, price, quantity) VALUES
(1, 'Pen', 5, 10),
(2, 'Notebook', 15, 0),
(3, 'Eraser', 3, 5),
(4, 'Backpack', 25, 3);

WITH price_limit AS (SELECT 20 AS limit_value)
-- Write SELECT query here
-- Your code here

Need a hint?

Use the WHERE clause with AND to combine conditions on price and quantity.

4

Complete the query with a semicolon

Add a semicolon ; at the end of the SELECT query to complete the SQL statement.

SQL

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

INSERT INTO products (id, name, price, quantity) VALUES
(1, 'Pen', 5, 10),
(2, 'Notebook', 15, 0),
(3, 'Eraser', 3, 5),
(4, 'Backpack', 25, 3);

WITH price_limit AS (SELECT 20 AS limit_value)
SELECT name, price
FROM products, price_limit
WHERE price  0
-- Add semicolon here
-- Your code here

Need a hint?

Remember to end SQL statements with a semicolon ;.