SQL Basics Part 2: SELECT, WHERE, Sorting, and GROUP BY

This is Part 2 of my series on learning SQL from the ground up. In Part 1: DDL, DML, Constraints & Keys, I covered how to build tables, insert data, and enforce rules with primary and foreign keys.

Once data is sitting inside a table, though, the real work is asking questions about it. That brings us to DQL (Data Query Language): filtering rows with WHERE, matching text patterns with LIKE, sorting with ORDER BY, summarizing rows with GROUP BY and HAVING, and understanding the actual order SQL runs a query in behind the scenes.

Filtering Rows with WHERE (and Why Mixing AND with OR Is Tricky)

The WHERE clause is how you tell the database to only return rows that satisfy a specific condition. Combining multiple conditions comes down to AND and OR, and mixing the two without being careful is one of the easiest ways to get wrong results silently.

Using AND: Every Condition Must Be True

SELECT *
FROM dim_customer
WHERE (gender = 'F')
  AND (country = 'France')
  AND (join_date > '2022-01-01');

Here, a row only makes it through if the customer is female, and lives in France, and joined after January 1, 2022. If even one of those three checks fails, the row is skipped.

Using OR: At Least One Condition Must Be True

Now look at what happens when we swap that last AND for an OR:

SELECT *
FROM dim_customer
WHERE (gender = 'F')
  AND (country = 'France')
   OR (join_date > '2022-01-01');

This tripped me up the first time I ran it. Because SQL evaluates AND before OR (just like multiplication happens before addition in math), this query returns:

1. Any female customer from France (regardless of when she joined), OR
2. Anyone who joined after January 1, 2022, even a male customer from a completely different country.

If what you actually wanted was “female customers who are either from France or joined after 2022-01-01,” you have to wrap the OR condition in parentheses:

SELECT *
FROM dim_customer
WHERE gender = 'F'
  AND (country = 'France' OR join_date > '2022-01-01');

Whenever I mix AND and OR now, I always parenthesize the exact grouping I mean rather than relying on default operator precedence.

Searching Text Patterns with the LIKE Operator

An = check only matches an exact string. When you want to search for a pattern inside text, SQL gives you the LIKE operator along with two wildcard characters:

• % matches any number of characters (including zero characters).
• _ matches exactly one single character.

Names Starting with “T”

SELECT *
FROM dim_customer
WHERE first_name LIKE 'T%';

Names Starting with “T” and Ending with “y”

SELECT *
FROM dim_customer
WHERE first_name LIKE 'T%y';

This matches "Timothy", "Tony", or "Tiffany": anything that begins with T and ends with y, no matter how many letters sit in the middle.

Combining _ and % for Exact Positions

SELECT *
FROM dim_customer
WHERE first_name LIKE 'T__f%y';

Reading that pattern from left to right:

1. Starts with T
2. Followed by exactly two characters (__)
3. Followed by the letter f
4. Followed by any number of characters (%)
5. Ends with y (for example, "Tiffany").

Sorting Results with ORDER BY and LIMIT

Relational tables don’t have a guaranteed built-in order unless you ask for one explicitly with ORDER BY:

SELECT *
FROM dim_product
ORDER BY unit_price DESC;

• DESC sorts highest to lowest (descending).
• ASC (or leaving the keyword out) sorts lowest to highest (ascending).

If you only want the top 3 most expensive products, you chain LIMIT onto the end:

SELECT *
FROM dim_product
ORDER BY unit_price DESC
LIMIT 3;

One limitation I ran into here: ORDER BY ... LIMIT 3 gives you the top 3 products across the entire table, not the top 3 products inside each category. Doing “top N per group” requires window functions (ROW_NUMBER(), RANK()), which I’m planning to cover in a later post once we get past joins.

Renaming Output Columns with Aliases (AS)

When you run calculations or pull columns with clunky names, AS lets you give the column a clean label in your query output without altering the underlying table:

SELECT
    product_key,
    product_name AS product_title,
    category
FROM dim_product;

Inside dim_product, the column is still named product_name. The alias only exists in the result set returned by this query.

Summarizing Data with GROUP BY and HAVING

Up to this point, every query has returned individual rows. GROUP BY changes the granularity of your result: it collapses rows that share a value into a single summary row per group so you can run aggregate functions like COUNT(), AVG(), SUM(), MIN(), and MAX().

SELECT
    category,
    AVG(unit_price) AS average_price,
    SUM(unit_price) AS total_price
FROM dim_product
GROUP BY category
HAVING AVG(unit_price) > 500
   AND SUM(unit_price) > 45000;

Here is what happens step by step:

1. GROUP BY category buckets all rows from dim_product by their category.
2. AVG(unit_price) and SUM(unit_price) compute the average price and total price inside each category bucket.
3. HAVING filters out entire category buckets that don’t meet the threshold, keeping only categories where the average price is over 500 and the sum of prices is over 45,000.

Why Can’t We Just Use WHERE Instead of HAVING?

This confused me at first too. The difference comes down to when the filtering happens:

• WHERE filters individual rows before grouping happens. At the moment WHERE runs, the rows haven’t been grouped yet, so aggregate values like AVG(unit_price) and SUM(unit_price) don’t exist yet.
• HAVING filters whole groups after GROUP BY has aggregated them.

(One MySQL detail worth knowing: in MySQL, you can actually write HAVING average_price > 500 using the column alias from SELECT, and MySQL will accept it as a convenience feature. However, in standard SQL and engines like PostgreSQL or SQL Server, referencing a SELECT alias inside HAVING throws an error because HAVING logically executes before SELECT. Writing the full HAVING AVG(unit_price) > 500 works everywhere.)

How SQL Actually Executes a Query Behind the Scenes

Learning the logical execution order of a SQL query was the single biggest lightbulb moment for me in Part 2.

Even though we write SELECT on the very first line of a query, the database engine does not evaluate SELECT first. Logically, SQL runs your query in this order:

1. FROM       -> Choose and join the source table(s)
2. WHERE      -> Filter individual rows before any grouping
3. GROUP BY   -> Bucket the surviving rows into groups
4. HAVING     -> Filter out groups based on aggregate conditions
5. SELECT     -> Compute expressions and assign column aliases
6. ORDER BY   -> Sort the final result rows
7. LIMIT      -> Return only the first N rows

Once you keep that 1-to-7 pipeline in your head, three common SQL errors suddenly make complete sense:

1. Why you cannot use a SELECT alias inside WHERE: Step 2 (WHERE) runs long before Step 5 (SELECT) ever creates the alias.
2. Why WHERE rejects SUM() or AVG(): Aggregation doesn’t happen until Step 3 (GROUP BY), so Step 2 (WHERE) only sees raw individual rows.
3. Why ORDER BY can use a SELECT alias: Step 6 (ORDER BY) runs right after Step 5 (SELECT), so all your aliases are already defined and ready to use.

Coming Up in Part 3

Right now, every query we’ve written pulls from a single table (dim_customer or dim_product). Next up in Part 3 is SQL Joins (INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN), which is where the foreign keys from Part 1 finally come to life by connecting data across multiple tables.