Lesson 1Filtering techniques: WHERE vs HAVING, use of EXISTS, IN, correlated subqueriesMaster accurate filtering methods for analytical queries. Compare WHERE and HAVING clauses, employ EXISTS and IN for subquery filtering, and utilise correlated subqueries to handle intricate, row-specific analytical requirements effectively.
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)Understand primary SQL data types relevant to analytics and their implications on storage, accuracy, and computations. Cover numeric scales, text management, and dependable date and timestamp functions for time-series analysis.
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, MAXAcquire skills to condense data via aggregations and grouping. Apply GROUP BY and HAVING to generate metrics, utilise COUNT, SUM, AVG, MIN, and MAX, and construct reliable aggregate queries suitable for dashboards and reports.
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 pitfallsAcquire hands-on approaches to import CSV data into databases for analytical purposes. Employ COPY, LOAD DATA, and SQLite import features, manage delimiters and character sets, and sidestep frequent errors leading to faulty or incomplete data 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 controlGrasp how DDL and DML operations structure and update tables for analytics. Practise schema creation and modifications, data insertion and updates, secure deletions, and transaction management to maintain data reliability in analytical pipelines.
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, DISTINCTCommand fundamental query structures essential for most analyses. Discover how SELECT fetches columns, WHERE sifts rows, ORDER BY arranges outputs, LIMIT manages sample sizes, and DISTINCT eliminates duplicates in analytical 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, INTERSECTComprehend how joins and set operations merge datasets for analytical tasks. Determine suitable join types, prevent duplication mistakes, and leverage UNION, EXCEPT, and INTERSECT for sophisticated analytical evaluations.
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 fundamental relational principles supporting analytical schemas. Explore tables, primary and foreign keys, normalisation techniques, and scenarios for denormalisation to enhance performance in reporting and business intelligence tasks.
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 queriesObtain a practical perspective on query efficiency for analytics. Understand index mechanisms, interpret query plans, apply EXPLAIN and ANALYZE tools, and implement straightforward optimisation tactics to sustain effective analytical queries.
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 BYDelve into window functions for sophisticated analytics without row reduction. Master ranking methods, cumulative totals, sliding averages, and partitioning approaches that enable cohort, trend, and segmentation analyses 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
