Phase 4: Databases & SQL for AI/ML · ~40 minutes · SQL · SQLite

SQL: Getting Data Out

SQL runs FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY — not top to bottom the way you typed it. WHERE against an aggregate fails for exactly this reason.

Hiring signal: Reaches for HAVING instead of WHERE on an aggregate without needing to be reminded, because they understand execution order, not just the symptom

What you will learn

Write SELECT/WHERE/ORDER BY/GROUP BY queries to answer real questions
Explain SQL's actual execution order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY
Diagnose the WHERE-against-an-aggregate error and fix it with HAVING
Identify what's missing when a query uses WHERE where HAVING was needed

Introduction

Type: Learn Languages: SQL Prerequisites: Lesson 01 (Why Not Just Use a File?) Time: ~40 minutes

Objective

Learning objectives

Write SELECT/WHERE/ORDER BY/GROUP BY queries to answer real questions
Explain SQL's actual execution order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY
Diagnose the WHERE-against-an-aggregate error and fix it with HAVING
Identify what's missing when a query uses WHERE where HAVING was needed

What you're building

Using this lesson's orders table (or a real dataset of your own with similar shape — at least 3 columns, one being a numeric amount, one being a category to group by):

Answer: "Which single orders are over $30?" (WHERE)
Answer: "What's the total spent per customer, highest first?" (GROUP BY, ORDER BY)
Answer: "Which customers spent over $100 in total?" (GROUP BY, HAVING)
Answer: "How many orders did each customer place, only showing customers with 2 or more?" (GROUP BY, COUNT, HAVING)
Answer: "What's the single largest order amount overall?" (aggregate with no grouping)
Contains a comment showing the real misuse of aggregate error from deliberately using WHERE instead of HAVING on one of these, and the corrected version directly after

A query reads: SELECT department, AVG(salary) FROM employees WHERE AVG(salary) > 75000 GROUP BY department;. What's wrong with it, specifically?

This is the exact WHERE-vs-HAVING trap from this lesson, with a new dataset. AVG(salary) is an aggregate — it only has a value once rows have been grouped by department, which happens in the GROUP BY step. WHERE executes before GROUP BY in SQL's real order, so at the point WHERE runs, there's no per-department average yet to compare against 75000. The fix is changing WHERE AVG(salary) > 75000 to HAVING AVG(salary) > 75000, keeping GROUP BY department in its same position — HAVING is specifically the clause that runs after grouping, when the aggregate genuinely exists.

Unlock the full lesson

You've read the first 2 sections. The rest of this lesson covers The Problem, Check Yourself, Key Terms & Next — plus a hands-on lab, quiz, and project artifact.

Create a free account to unlock Phase 0 and Phase 1 of every course — no credit card.