Lesson 1Filtering techniques: WHERE vs HAVING, use of EXISTS, IN, correlated subqueriesMaster sharp filtering for analysis queries. Compare WHERE and HAVING, use EXISTS and IN for subquery checks, and handle linked subqueries for tricky, row-specific conditions in your data.
WHERE vs HAVING in grouped queriesUsing IN and NOT IN with subqueriesEXISTS and NOT EXISTS for semi joinsCorrelated subqueries for row-aware logicHandling NULLs in filter conditionsPerformance tips for complex filtersLesson 2SQL data types and date/time handling (DATE, TIMESTAMP, numeric precision)Get to know key SQL data types for analytics and how they impact storage, accuracy, and maths. Handle number scales, text properly, and solid date and time ops for time analysis in Kenyan contexts.
Numeric types and precision for metricsCharacter and text data considerationsDATE vs TIMESTAMP and time zonesCasting and converting between typesDate arithmetic and interval calculationsExtracting parts of dates for groupingLesson 3Aggregations and grouping: GROUP BY, HAVING, COUNT, SUM, AVG, MIN, MAXSummarise data with groupings and aggregates. Use GROUP BY and HAVING for metrics, apply COUNT, SUM, AVG, MIN, MAX, and craft strong queries for dashboards and reports in business.
GROUP BY fundamentals and syntaxAggregate functions COUNT and SUMAVG, MIN, and MAX for distributionsHAVING to filter aggregated resultsGrouping by expressions and bucketsDealing with NULLs in aggregatesLesson 4Loading CSVs into databases: COPY, LOAD DATA, sqlite import, and common pitfallsLearn hands-on ways to load CSV files into databases for analysis. Use COPY, LOAD DATA, SQLite import, manage separators and codes, and dodge common errors causing bad loads.
Preparing CSVs for reliable importsUsing COPY in PostgreSQL and similar systemsLOAD DATA for MySQL and compatible enginesSQLite .import workflow and optionsHandling encodings, delimiters, and quotesValidating row counts and rejected recordsLesson 5DDL and DML essentials: CREATE TABLE, ALTER, INSERT, UPDATE, DELETE, transaction controlSee how DDL and DML build and tweak tables for analytics. Practice making schemas, adding/updating data, safe deletes, and transactions to keep data solid in analysis flows.
Creating analytical tables with CREATE TABLEModifying schemas safely with ALTER TABLEINSERT patterns for bulk and incremental loadsUPDATE and DELETE with safe predicatesCOMMIT, ROLLBACK, and transaction scopeACID properties in analytical workloadsLesson 6Query basics: SELECT, WHERE, ORDER BY, LIMIT, DISTINCTNail basic query skills used in every analysis. SELECT grabs columns, WHERE picks rows, ORDER BY sorts, LIMIT samples, DISTINCT drops duplicates in your queries.
SELECT list design and column aliasesFiltering rows with WHERE conditionsSorting results with ORDER BYLIMIT and OFFSET for sampling dataUsing DISTINCT to remove duplicatesBasic query debugging and refinementLesson 7Joins and set operations: INNER, LEFT, RIGHT, FULL, CROSS, UNION, EXCEPT, INTERSECTSee how joins and set ops mix datasets for analysis. Know each join type, avoid dupes, and use UNION, EXCEPT, INTERSECT for deep comparisons.
INNER JOIN for intersecting datasetsLEFT, RIGHT, and FULL OUTER JOIN use casesCROSS JOIN and Cartesian products in analysisUNION vs UNION ALL for stacking dataEXCEPT and INTERSECT for set comparisonsDetecting and handling join duplicationLesson 8Relational database concepts: tables, primary/foreign keys, normalization vs denormalizationGrasp relational basics for analysis setups. Tables, primary/foreign keys, normal forms, and when to denormalise for speed in reports and BI.
Tables, rows, and columns in practicePrimary keys and uniqueness constraintsForeign keys and referential integrityNormalization forms and redundancy controlDenormalization for reporting performanceStar and snowflake schemas overviewLesson 9Performance basics: indexes, query plans, explain/analyze, simple optimization strategies for analytical queriesGet practical on query speed for analytics. Indexes, read plans, EXPLAIN/ANALYZE, and easy tweaks to keep analysis queries running smooth.
How indexes speed up lookups and joinsReading and interpreting query plansUsing EXPLAIN and ANALYZE in practiceIdentifying slow filters and joinsOptimizing GROUP BY and aggregationsBasic indexing strategies for analyticsLesson 10Window functions overview: ROW_NUMBER, RANK, DENSE_RANK, SUM() OVER(), AVG() OVER(), PARTITION BYDive into window functions for advanced analytics without losing rows. Ranking, running totals, moving averages, partitioning for cohorts, trends, segments in SQL.
Window function syntax and OVER clauseROW_NUMBER, RANK, and DENSE_RANK use casesRunning totals with SUM() OVER()Moving averages with window framesPARTITION BY for cohort and segment logicORDER BY in windows vs query ordering
