SQL Certificate

# SQL certificate - outline

## Part 1 - exam (60 min)

Topics:
- SELECT + WHERE + ORDER BY + LIMIT
- JOINs: INNER, LEFT/RIGHT/FULL OUTER, anti-joins
- GROUP BY + HAVING + aggregates
- Subqueries vs CTEs
- Window functions (ROW_NUMBER, RANK, LAG, LEAD, running totals)
- INSERT, UPDATE, DELETE, UPSERT
- Transactions + isolation levels
- Indexes: B-tree, partial, multi-column
- EXPLAIN + reading plans
- Constraints + foreign keys
- Date + JSON + array handling

Passing mark: 70 percent.

## Part 2 - capstone (take-home, ~8 hours)

Pick one:

Option A - Data analysis:
- Take a CSV dataset (Kaggle, gov data)
- Load into Postgres or SQLite
- Answer 5 business questions with SQL only (no app code)
- Document each query + EXPLAIN + a chart of the result
- Submit a notebook or markdown report

Option B - Schema design:
- Design a small marketplace schema (users, products, orders, reviews)
- Write the DDL (CREATE TABLE + indexes)
- Seed data
- 10+ queries showing common reads (top products, churn analysis, cohort retention)
- A migration showing how to evolve the schema safely

Submit a Git repo + a README explaining choices.

## Grading

- Correctness of queries (35 percent)
- Schema design + indexes (25 percent)
- EXPLAIN reading + performance reasoning (20 percent)
- Documentation (10 percent)
- Modern features (CTEs, window functions, JSONB) (10 percent)

## Tips

- Window functions are a force multiplier - learn them deeply
- Always EXPLAIN ANALYZE before submitting a slow query
- A clear schema + indexes beats clever queries every time

-- After finishing every lesson, claim your
-- certificate at /certificate/sql.