After reading this lesson, you will be able to confidently do the following three things.

• ▸✅ Write a real schema using CREATE TABLE + FK + constraints
• ▸✅ Spot the pitfall of missing WHERE in INSERT · UPDATE · DELETE
• ▸✅ Combine subquery IN vs EXISTS + GROUP BY + HAVING

Keep the learning objectives as a checklist, and close the lesson once you can answer them all.

The Starting Point of DDL

CREATE TABLE users (
    id          BIGINT       AUTO_INCREMENT,
    email       VARCHAR(255) NOT NULL UNIQUE,
    name        VARCHAR(50)  NOT NULL,
    age         INT          CHECK (age >= 0),
    created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

The 5 Constraints

• ▸PRIMARY KEY — Unique + NOT NULL. Row identifier. Only one per table.
• ▸NOT NULL — Cannot be empty.
• ▸UNIQUE — No duplicates allowed. Unlike PK, multiple are permitted.
• ▸CHECK — Validates value range. CHECK (age >= 0).
• ▸FOREIGN KEY — References another table. Ensures referential integrity.

Foreign Key — Building Relationships

CREATE TABLE orders (
    id       BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id  BIGINT NOT NULL,
    amount   INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

ON DELETE CASCADE — When a parent row is deleted, child rows are automatically deleted too. With RESTRICT (the default), deleting a parent is refused if children exist.

INSERT · UPDATE · DELETE

-- Add a row
INSERT INTO users (email, name, age) VALUES ('[email protected]', 'Alice', 30);

-- Insert multiple rows at once
INSERT INTO users (email, name, age) VALUES
    ('[email protected]', 'Bob', 25),
    ('[email protected]', 'Carol', 28);

-- Update
UPDATE users SET age = 31 WHERE email = '[email protected]';

-- Delete
DELETE FROM users WHERE age < 20;

An UPDATE/DELETE without WHERE targets every row. Accidentally omitting one line wipes the entire table. Always test inside a transaction.

Test Data

users           orders
+----+-------+  +----+---------+--------+
| id | name  |  | id | user_id | amount |
+----+-------+  +----+---------+--------+
|  1 | A     |  |  1 |       1 |  10000 |
|  2 | B     |  |  2 |       1 |   5000 |
|  3 | C     |  |  3 |       3 |   8000 |
+----+-------+  +----+---------+--------+

INNER JOIN — Only rows that exist on both sides

SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- Result: A/10000, A/5000, C/8000  (B excluded because no orders)

LEFT JOIN — All rows from the left table are included

SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Result: A/10000, A/5000, B/NULL, C/8000

Users like B who have no match are included with NULL. Commonly used for "find users with no orders":

SELECT u.name FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;
-- Result: B

RIGHT JOIN — Rarely used

Swapping the table order lets you express it as a LEFT JOIN. Many teams use only LEFT for consistency.

CROSS JOIN — All combinations

SELECT a.name, b.name FROM users a CROSS JOIN users b;
-- 9 rows (3 × 3)

Cartesian product. Almost never used in production — accidentally leaving out ON produces this disaster.

Three Types of Subqueries

IN — Is it in the list?

SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 5000);

EXISTS — Does at least one row match? (better performance)

SELECT name FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 5000
);

EXISTS stops as soon as the first matching row is found → often faster than IN on large tables.

Scalar Subquery — returns exactly one value

SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

Executes once per row in the SELECT clause — except on small tables, rewriting as a JOIN is faster.

GROUP BY — Pairs with aggregate functions

-- Total order amount per user
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id;

Using a column in SELECT that is not in GROUP BY causes an error (or non-standard behavior). The 5 aggregate functions:

• ▸COUNT(*) — row count
• ▸SUM(col) — sum
• ▸AVG(col) — average
• ▸MAX(col) / MIN(col) — maximum / minimum

HAVING vs WHERE

WHERE filters before groups are formed, HAVING filters after groups are formed.

SELECT user_id, SUM(amount) AS total
FROM orders
WHERE amount > 1000               -- only orders over 1000
GROUP BY user_id
HAVING SUM(amount) > 10000;       -- only users whose total exceeds 10,000

The key difference is that HAVING can use aggregate functions. You cannot use SUM() in WHERE.

ORDER BY · LIMIT

SELECT name FROM users
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

Fetches page 3 data (10 items per page). The fundamental pattern for pagination.

Putting It All Together — Top 5 Users

SELECT u.name, SUM(o.amount) AS total
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING SUM(o.amount) > 0
ORDER BY total DESC
LIMIT 5;

JOIN + GROUP BY + HAVING + ORDER BY + LIMIT — 90% of real-world queries use exactly this combination.

Once you understand the concepts in this lesson, you can give AI specific instructions. Instead of a vague "fix this," you can make requests with vocabulary — and that is where token savings begin.

• ▸"Design 3 tables — users, orders, products — including FK and INDEX"
• ▸"Write a query to aggregate the number of users who signed up in the last 7 days, grouped by day"
• ▸"Add EXPLAIN to this query and interpret the execution plan"

Why This Reduces Tokens

When you don't know the concepts, you have to ask "what does that mean?" again after every AI response. Those follow-up questions eat up your tokens. Learn the concepts once and the conversation ends in one round.