Advanced Filtering: Multiple Conditions and Dates

The Power of Multiple Filters

The Shopping Filter Analogy

Think of AND and OR like filters on an e-commerce site.

Welcome to Module 1. In this lesson, we move beyond simple queries to handle complex, real-world business questions using multiple filters. Think of these like the sidebar on your favorite shopping site. When you select 'Red' AND 'Size 10', you're being very specific. But when you select 'Nike' OR 'Adidas', you're widening your search to include more options.

Our Data: The Orders Table

We will use the Orders table to practice these concepts. This table tracks every transaction in our store.

order_idcustomer_idorder_datetotal_amountstatus
10152023-10-01150.00Shipped
10282023-10-1545.00Pending
10352023-11-02210.00Shipped

To master these filters, we'll look at our Orders table. Notice the 'order_date' column uses a standard format, and 'total_amount' is a number we can compare. We'll use these specific columns to find high-value orders or specific timeframes.

Combining Conditions with AND & OR

Narrowing vs. Broadening

The AND operator returns records where all conditions are true. The OR operator returns records where at least one condition is true.

SELECT * 
FROM Orders 
WHERE total_amount > 100 
AND status = 'Shipped';

Let's see the syntax in action. In this query, we are looking for orders that are BOTH over 100 dollars AND have already shipped. This combined logic helps us isolate high-value, completed transactions specifically.

Filtering by Date Ranges

Dates in SQL

SQL uses the ISO format: 'YYYY-MM-DD'. You can use comparison operators or the BETWEEN operator for ranges.

SELECT * 
FROM Orders 
WHERE order_date 
BETWEEN '2023-10-01' 
AND '2023-10-31';

When working with dates, SQL expects a specific string format: Year, Month, Day. The BETWEEN operator is a clean way to find everything in a specific month, like October 2023. Remember, BETWEEN includes both the start and end dates you provide.

The Parentheses Pitfall

Operator Precedence

SQL processes AND before OR. Without parentheses, your logic might break!

Goal: Find 'Pending' or 'Shipped' orders over $100.

Correct:
WHERE (status = 'Pending' OR status = 'Shipped') AND total_amount > 100

Here is a common beginner pitfall. If you mix AND and OR without parentheses, SQL will process the AND first. This might result in showing 'Shipped' orders over 100, plus EVERY 'Pending' order regardless of price! Always wrap your OR conditions in parentheses to tell SQL exactly how to group your logic.

Exercise 1: Basic AND Filtering

Task: Find all orders where the total_amount is less than 50 AND the status is 'Pending'.

It's time for your first challenge. Write a query to find small, unpaid orders. We need the total amount to be under 50 and the status to be 'Pending'. Type your query and click 'Run'.

Exercise 2: Filtering by Date Range

Task: Retrieve all orders placed between '2023-11-01' and '2023-11-30'.

Now, let's look at the month of November. Write a query to find all orders placed between November 1st and November 30th, 2023.

Exercise 3: The Advanced Logic Challenge

Task: Find orders that are either 'Shipped' or 'Cancelled', but ONLY if the total_amount was greater than 200.

Final challenge! This one requires careful logic. We want high-value orders (over 200) that have reached a final state: either 'Shipped' or 'Cancelled'. Don't forget your parentheses!