Filtering Data with WHERE Clauses

What is SQL? Basic Concepts

Before we write our first query, let's establish some fundamental concepts. In the simplest terms, SQL is the language we use to communicate with databases. As a Business Analyst, you won't be building databases, but you will frequently need to pull data from them.

To recap, a database is a structured collection of data. Inside a database, data is organized into tables, which consist of columns (attributes) and rows (records). We use a query to ask the database for specific data.

Writing Your First Query: SELECT and FROM

The most fundamental SQL query consists of two main parts:

1. SELECT: Specifies the columns you want to retrieve.
2. FROM: Specifies the table you want to retrieve the columns from.

The basic syntax is:
SELECT column1, column2 FROM table_name;

If you want to see all columns in a table, you can use an asterisk (*) as a shortcut:
SELECT * FROM table_name;

Let's imagine you're working with a table called ad_campaigns in your company's database. To see the name and budget of every campaign, you would write:

SELECT campaign_name, budget
FROM ad_campaigns;

To see all information available for every campaign, you would write:

SELECT *
FROM ad_campaigns;

A quick tip: Real-world tables can contain millions of rows. It's good practice to add a LIMIT clause at the end of your query to only retrieve a small sample, preventing you from accidentally trying to load a massive dataset.

SELECT *
FROM ad_campaigns
LIMIT 10; -- This will show only the first 10 rows.

Filtering Data with the WHERE Clause

Retrieving all the data from a table is a good start, but the real power of SQL lies in filtering. As a BA, you'll constantly need to find records that meet specific business criteria. This is where the WHERE clause comes in.

The WHERE clause is added after the FROM clause and allows you to specify conditions that rows must meet to be included in the result.

The structure is:
SELECT column(s) FROM table_name WHERE condition;

Let's watch a video that breaks down the most common ways to build these conditions.

Key Comparison Operators

As you just saw, you can use various operators in your WHERE clause:

Important Note: Text values (also called strings) must be enclosed in single quotes (e.g., 'Active'). Numbers do not need quotes.

Combining Conditions with AND, OR, and NOT

Often, a single condition isn't enough. You might need to find campaigns that are active and have a budget over $5000. Or perhaps you need to see campaigns targeting the 'USA' or 'Canada'. For this, we use logical operators.

• AND: All conditions must be true.
• OR: At least one of the conditions must be true.
• NOT: Reverses the result of a condition.

Let's continue with the video to see how these are used in practice and learn about the crucial role of parentheses ().

For example, to find all active campaigns with a budget over $5000, you would write:

SELECT *
FROM ad_campaigns
WHERE status = 'Active' AND budget > 5000;

To find campaigns in either the 'USA' or 'Canada' that are paused, you need to use parentheses to group the OR condition correctly:

SELECT *
FROM ad_campaigns
WHERE status = 'Paused' AND (country = 'USA' OR country = 'Canada');

Without the parentheses, the query would be interpreted incorrectly.

Advanced Filtering: LIKE, IN, BETWEEN, and IS NULL

Let's cover a few more powerful tools for your WHERE clause.

Pattern Matching with LIKE

The LIKE operator is used to search for a specified pattern in a column. This is incredibly useful when you don't know the exact text value. It uses two wildcards:

• %: Represents zero, one, or multiple characters.
• _: Represents a single character.

Let's see how LIKE works.

For instance, to find all campaigns whose names contain the word 'Summer', you'd write:
WHERE campaign_name LIKE '%Summer%';

IN, BETWEEN, and IS NULL

These operators provide convenient shortcuts for common filtering tasks.

Here's a summary:

• BETWEEN: Selects values within a given range. The values can be numbers, text, or dates.
  • WHERE budget BETWEEN 5000 AND 10000; (equivalent to budget >= 5000 AND budget <= 10000)
• IN: Specifies a list of possible values for a column.
  • WHERE country IN ('USA', 'Canada', 'UK'); (a much cleaner way to write country = 'USA' OR country = 'Canada' OR country = 'UK')
• IS NULL: Checks for empty or missing values. You cannot use = NULL.
  • WHERE completion_date IS NULL; (finds campaigns that have not yet been completed)
  • WHERE completion_date IS NOT NULL; (finds campaigns that have a completion date)

Time to Practice!

Let's apply what you've learned. The following exercises use a simple database for a store. The key table for now is product, which contains columns like product_name, unit_price, and category_id.

SELECT *
FROM campaign_performance
WHERE
    status IN ('Active', 'Paused')
    AND ctr < 0.02
    AND campaign_name != 'Test Campaign';

Conclusion

Congratulations on completing your first lesson in SQL! You've taken a significant step toward becoming a more data-savvy Business Analyst.

Key Takeaways:

• The basic structure of a query is SELECT (columns) FROM (table).
• The WHERE clause is your primary tool for filtering data to find specific records.
• You can filter on numbers, text, and dates using comparison operators like =, >, and !=.
• You can combine conditions using AND and OR, using parentheses () to ensure the logic is correct.
• Operators like LIKE, IN, BETWEEN, and IS NULL provide powerful and convenient ways to handle more complex filtering needs.

Preview of the Next Lesson:
Now that you can select and filter your data, the next step is to make the results clear and readable. In our next lesson, we will learn how to sort and limit query results using ORDER BY and LIMIT. This will allow you to organize your output, find top/bottom performers, and present your findings in a more structured way.