# SQL Walkthrough — Learn by Doing

## Step 1: Your First Query

Assume a `users` table with `id`, `name`, `email`, `created_at`. Write a query to fetch all users.

<!-- hint:terminal -->
<!-- hint:code language="sql" highlight="1,2" -->

**Embed:** https://www.db-fiddle.com/

**Task:** Write `SELECT * FROM users`. Then write a query that returns only `name` and `email`.

**Question:** When would you use `SELECT *` vs listing specific columns? What are the tradeoffs?

**Checkpoint:** The user can write basic SELECT and understands column selection.

---

## Step 2: Filtering with WHERE

<!-- hint:terminal -->

**Task:** Write queries to:
- Find users created after 2024-01-01
- Find users whose email contains "@gmail.com"
- Find users with a non-null phone number

**Question:** What's the difference between `=` and `LIKE`? When would you use `IN` vs multiple `OR` conditions?

**Checkpoint:** The user can use WHERE with various operators.

---

## Step 3: INNER JOIN

Assume `orders` (id, user_id, amount) and `users` (id, name). Fetch all orders with the user's name.

<!-- hint:diagram mermaid-type="flowchart" topic="INNER JOIN vs LEFT JOIN — matching rows only vs all left rows" -->

**Task:** Write an INNER JOIN that returns `order_id`, `amount`, `user_name`.

**Question:** What happens to orders that have a `user_id` pointing to a deleted user? Would they appear? What about users with no orders?

**Checkpoint:** The user understands INNER JOIN returns only matching rows.

---

## Step 4: LEFT JOIN

<!-- hint:code language="sql" highlight="1,5" -->

**Task:** Write a query that lists **all** users and their order count (0 if none). Use LEFT JOIN and COUNT.

**Question:** Why does a LEFT JOIN from users to orders give us users with zero orders, while INNER JOIN would not?

**Checkpoint:** The user can use LEFT JOIN and understands NULL for non-matching right rows.

---

## Step 5: GROUP BY and Aggregation

**Task:** Write queries to:
- Count orders per user
- Sum order amount per user
- Find users whose total order amount exceeds 500 (use HAVING)

**Question:** What's the difference between WHERE and HAVING? Can you use aggregate functions in WHERE?

**Checkpoint:** The user understands GROUP BY and HAVING.

---

## Step 6: Subqueries

**Task:** Find all users who have placed at least one order over 100. Use a subquery with IN.

**Question:** Could you write this with a JOIN instead? When is a subquery clearer vs when is a JOIN better?

**Checkpoint:** The user can write an IN subquery and compare it to JOINs.

---

## Step 7: Schema Design

<!-- hint:celebrate -->

**Task:** Design tables for a simple blog: authors, posts, comments. Identify primary keys and foreign keys. Sketch the relationships.

**Question:** How do you decide when to normalize (split tables) vs denormalize (keep redundant data)? What problem does normalization solve?

**Checkpoint:** The user can design a minimal normalized schema with PKs and FKs.