SQL for Data Analysis and AI syllabus
Course purpose
Learn to turn a business question into a checked SQL analysis and a carefully defined AI feature table. By the end, you should be able to explain why the result is valid, not only produce SQL that runs.
Audience: beginners who are comfortable with a table's rows and columns. No prior SQL, Python, Oracle, PL/SQL, machine learning or paid AI account is required. Basic arithmetic and comfort editing a text file are enough to begin. Installation is explained separately; do not assume a lab database already exists.
Dialect: PostgreSQL. Tested practice uses PGlite 0.5.9 with a PostgreSQL 18.6 WebAssembly engine. Some examples use PostgreSQL-specific casts, date_trunc and pg_input_is_valid; the latter requires PostgreSQL 16 or later. Do not expect unchanged execution on SQLite, MySQL, Oracle or SQL Server.
Suggested pace: about 14–18 hours of reading and guided practice plus 2–3 hours for the capstone; allow additional setup and review time. Study time is a design estimate, not a measured learner completion time. Take one or two modules per session and repeat exercises that reveal a gap.
Ordered learning sequence
| Module | What you will understand and be able to do | Evidence of learning |
|---|---|---|
| 1 Start with a question and a safe lab | Define a metric, identify table grain and keys, set up the synthetic lab and inspect counts | Explain 14 orders versus 19 lines; verify setup |
| 2 Select only the evidence you need | Select columns, use aliases and DISTINCT, sort reliably and take a bounded sample | Stable top-product query and distinct channel list |
| 3 Filter rows without losing the unknowns | Combine predicates, write half-open date ranges and handle NULL correctly | Boundary-aware filters and explicit unknown-city inclusion |
| 4 Calculate amounts and time periods | Use historical prices, total line discounts, CASE, date keys and safe decimal division | Hand-check line sales and a March indicator |
| 5 Summarise at a declared grain | Use COUNT, SUM and other aggregates, GROUP BY, HAVING and completeness checks | Status counts and repeat-buyer groups |
| 6 Join relationships and preserve missing matches | Use inner and left joins, key relationships and correct zero-match counts | Line descriptions and customers with no orders |
| 7 Prevent join multiplication and reconcile money | Diagnose fan-out and pre-aggregate independent children | Demonstrate and repair a doubled payment total |
| 8 Build readable analysis with subqueries and CTEs | Use scalar subqueries, EXISTS and named intermediate results | Refund-customer selection and checked net sales by channel |
| 9 Compare rows with window functions | Use frames, LAG, ranking, deterministic ties and outer filtering | Top-two customers and cautious month-over-month comparison |
| 10 Audit quality before trusting the result | Profile duplicates, normalize labels, flag invalid references and dates, apply a stated acceptance policy | Issue report and latest-valid-record result |
| 11 Prepare AI features without future leakage | Define population, cutoff, feature and label windows and preserve missingness | Six-row feature table with a leakage-risk explanation |
| 12 Use AI as a checked SQL assistant | Specify a schema contract, verify generated SQL, inspect plans and apply layered read-only safety | Repair a flawed draft and verify the correct average denominator |
| 13 Deliver a checked analysis capstone | Produce reproducible reports and a decision-useful summary with limits | Monthly report, all-customer report, audit, summary and feature appendix |
How each module works
Read the explanation before the query. Predict one or two output values. Run the named SQL file using the setup instructions. Compare the full expected result. Explain the result in ordinary language, then attempt both independent exercises in the starter files. The separate solutions include reasoning, not just answer code.
Each module connects to the previous one. Subqueries used for the fan-out repair are introduced as derived tables in module 7 before deeper CTE work in module 8. No stored procedure, trigger, package or Oracle-specific PL/SQL knowledge is required. This course focuses on analytical SELECT queries and the evidence behind them.
Assessment and boundaries
Use the ASSESSMENT.md rubric. A pass requires correct grains, populations, date boundaries, monetary reconciliation and safe handling of SQL; these are not optional style points. The capstone uses a small synthetic retail ledger and is not a real company analysis. The AI feature exercise teaches extraction design, not model training or production ML readiness.
The scope does not include database administration, index tuning at scale, transactions that move real money, production deployments, vector databases, stored programming or automatic execution of AI-generated SQL. Those require additional prerequisites and operational controls.
Alignment with the public course page
The live page reviewed on 14 October 2026 describes a planned course for people working with structured data and lists comfort with tables, rows and columns as preparation. It does not currently publish a detailed syllabus. This 13-module sequence is a proposed complete teaching design for that stated goal. An older search-indexed catalogue mentioned 13 modules, but it is not treated as the current authoritative outline. See SOURCES.md.