DAY 4: SQL - WHERE Clause & Filtering

📝 Setup Instructions:

1. Open SQLite Browser with your Superstore dataset (from Day 2)
2. Navigate to the "Execute SQL" tab
3. Today you'll learn to filter data - this is like Excel's AutoFilter but with code!

💡 WHERE Clause = SQL's Filtering Power: While Day 3 (Excel) used AutoFilter clicking, SQL uses WHERE to write filter rules in code!

Exercise 1: Simple WHERE with Text Comparison

Step-by-Step Instructions:

1. Type this query to find all East region orders:

   SELECT * FROM Orders
   WHERE Region = 'East'
   LIMIT 20;

2. Click Execute (▶️)
3. You should see only East region orders

⚠️ Important SQL Rules:

• Text values must use single quotes: 'East' not "East"
• SQL is case-sensitive for values: 'East' ≠ 'east'
• WHERE comes AFTER FROM, BEFORE ORDER BY

Try these variations:

SELECT ProductName, Sales, Region FROM Orders
WHERE Region = 'West'
LIMIT 15;

SELECT * FROM Orders
WHERE Category = 'Technology';

✅ Expected Result: Only rows matching your WHERE condition

Exercise 2: WHERE with Numeric Comparisons

Step-by-Step Instructions:

1. Find high-value orders (Sales > 1000):

   SELECT OrderID, ProductName, Sales
   FROM Orders
   WHERE Sales > 1000
   ORDER BY Sales DESC;

2. Find small quantities (Quantity <= 2):

   SELECT ProductName, Quantity, Sales
   FROM Orders
   WHERE Quantity <= 2
   LIMIT 20;

3. Find discounted orders (Discount > 0):

   SELECT ProductName, Discount, Sales
   FROM Orders
   WHERE Discount > 0;

🔢 Comparison Operators in SQL:

• = Equal to
• > Greater than
• < Less than
• >= Greater than or equal to
• <= Less than or equal to
• <> or != Not equal to

✅ Expected Result: Only rows where the numeric condition is TRUE

Exercise 3: Combining Conditions with AND

Step-by-Step Instructions:

1. Find West region AND high-value orders:

   SELECT ProductName, Region, Sales
   FROM Orders
   WHERE Region = 'West' AND Sales > 500
   ORDER BY Sales DESC;

   Both conditions must be true!
2. Find Technology products with discount:

   SELECT ProductName, Category, Discount, Sales
   FROM Orders
   WHERE Category = 'Technology' AND Discount > 0;

3. Three conditions - profitable high-quantity orders:

   SELECT ProductName, Quantity, Sales, Profit
   FROM Orders
   WHERE Quantity > 5 AND Sales > 100 AND Profit > 0
   LIMIT 20;

💡 AND Logic:

• ALL conditions must be TRUE for row to appear
• Narrows your results (fewer rows)
• Like applying multiple Excel filters at once

✅ Expected Result: Only rows matching ALL conditions

Exercise 4: Using OR for Alternative Conditions

Step-by-Step Instructions:

1. Find orders from East OR West:

   SELECT ProductName, Region, Sales
   FROM Orders
   WHERE Region = 'East' OR Region = 'West'
   LIMIT 30;

   Either condition can be true!
2. Find extreme values (very high or very low sales):

   SELECT ProductName, Sales
   FROM Orders
   WHERE Sales > 5000 OR Sales < 10
   ORDER BY Sales DESC;

3. Combining AND with OR (use parentheses!):

   SELECT ProductName, Region, Category, Sales
   FROM Orders
   WHERE (Region = 'East' OR Region = 'West')
     AND Category = 'Furniture';

   Parentheses ensure OR is evaluated first, then AND

💡 OR Logic:

• AT LEAST ONE condition must be TRUE
• Broadens your results (more rows)
• Use parentheses to control evaluation order

✅ Expected Result: Rows matching ANY of the OR conditions (and all AND conditions if combined)

Exercise 5: Advanced Filtering with IN, BETWEEN, LIKE

Step-by-Step Instructions:

1. IN - Match multiple values (cleaner than multiple ORs):

   SELECT ProductName, Region, Sales
   FROM Orders
   WHERE Region IN ('East', 'West', 'Central')
   LIMIT 20;

   This is cleaner than: WHERE Region = 'East' OR Region = 'West' OR Region = 'Central'
2. BETWEEN - Range of values:

   SELECT ProductName, Sales
   FROM Orders
   WHERE Sales BETWEEN 100 AND 500
   ORDER BY Sales;

   Includes both 100 and 500 (inclusive)
3. LIKE - Pattern matching with wildcards:

   SELECT ProductName, Sales
   FROM Orders
   WHERE ProductName LIKE '%Chair%'
   LIMIT 20;

   % = any characters. This finds products with "Chair" anywhere in the name
4. More LIKE examples:

   -- Products starting with 'Table'
   SELECT ProductName FROM Orders
   WHERE ProductName LIKE 'Table%';
   
   -- Products ending with 'Kit'
   SELECT ProductName FROM Orders
   WHERE ProductName LIKE '%Kit';
   
   -- CustomerName containing 'Smith'
   SELECT DISTINCT CustomerName FROM Orders
   WHERE CustomerName LIKE '%Smith%';

💡 Special Operators:

• IN (value1, value2, ...) - Matches any value in the list
• BETWEEN x AND y - Inclusive range
• LIKE pattern - Pattern matching (% = any chars, _ = single char)
• IS NULL / IS NOT NULL - Check for missing values

✅ Expected Result: Rows matching the specified patterns or ranges

🎯 Practice Goal Checkpoint:

By completing these 5 exercises, you should now be able to:

• Use WHERE clause to filter data by specific conditions
• Apply comparison operators (=, >, <, >=, <=, <>)
• Combine multiple conditions with AND/OR
• Use IN for matching multiple values
• Use BETWEEN for range filtering
• Use LIKE for pattern matching with wildcards
• Understand the parallel between WHERE and Excel's AutoFilter

Key Insight: WHERE in SQL = Programmatic filtering. Instead of clicking filter dropdowns (Excel Day 3), you write logical conditions in code!

Take a 5-minute break, then move on to Independent Practice!