Lesson 1Filtering Methods: WHERE versus HAVING, Using EXISTS, IN, and Linked SubqueriesBuild accurate filtering approaches for analytical queries. Compare WHERE and HAVING clauses, apply EXISTS and IN for subquery filtering, and use linked subqueries to handle complex conditions that depend on individual rows in analysis.
WHERE versus HAVING in grouped queriesUsing IN and NOT IN with subqueriesEXISTS and NOT EXISTS for semi joinsLinked subqueries for row-specific logicManaging NULLs in filter conditionsPerformance advice for complex filtersLesson 2SQL Data Types and Handling Dates and Times (DATE, TIMESTAMP, Numeric Accuracy)Understand the primary SQL data types essential for analytics and their impact on storage, accuracy, and computations. Cover numeric ranges, text management, and reliable date and timestamp functions for time-related analysis tasks.
Numeric types and accuracy for measurementsCharacter and text data considerationsDATE versus TIMESTAMP and time zonesCasting and converting between typesDate calculations and interval operationsExtracting date parts for groupingLesson 3Aggregations and Grouping: GROUP BY, HAVING, COUNT, SUM, AVG, MIN, MAXMaster summarising data through aggregations and grouping techniques. Employ GROUP BY and HAVING to create metrics, utilise COUNT, SUM, AVG, MIN, and MAX, and construct dependable aggregate queries for dashboards and reports in analytics.
GROUP BY basics and structureAggregate functions COUNT and SUMAVG, MIN, and MAX for distributionsHAVING to filter aggregated resultsGrouping by expressions and rangesDealing with NULLs in aggregatesLesson 4Loading CSVs into Databases: COPY, LOAD DATA, SQLite Import, and Common ChallengesAcquire practical skills to import CSV data into databases for analytical purposes. Utilise COPY, LOAD DATA, and SQLite import features, manage delimiters and encodings, and steer clear of typical errors that lead to incomplete or faulty data loads.
Preparing CSVs for dependable importsUsing COPY in PostgreSQL and similar systemsLOAD DATA for MySQL and compatible enginesSQLite .import process and optionsHandling encodings, delimiters, and quotesValidating row counts and rejected recordsLesson 5DDL and DML Essentials: CREATE TABLE, ALTER, INSERT, UPDATE, DELETE, Transaction ManagementGrasp how DDL and DML operations shape and update tables for analytical needs. Practice building and modifying schemas, inserting and updating data, safe deletions, and transactions to maintain data reliability in analytical processes and 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, DISTINCTMaster the essential query syntax used in most analyses. Learn how SELECT fetches columns, WHERE filters rows, ORDER BY arranges results, 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 purposes. Determine suitable join types, prevent duplication mistakes, and use UNION, EXCEPT, and INTERSECT for intricate analytical comparisons.
INNER JOIN for intersecting datasetsLEFT, RIGHT, and FULL OUTER JOIN use casesCROSS JOIN and Cartesian products in analysisUNION versus UNION ALL for stacking dataEXCEPT and INTERSECT for set comparisonsDetecting and handling join duplicationLesson 8Relational Database Concepts: Tables, Primary/Foreign Keys, Normalization versus DenormalizationGrasp fundamental relational principles that support analytical schemas. Explore tables, primary and foreign keys, normalization techniques, and scenarios for denormalization 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 for Analytical QueriesObtain a practical perspective on query performance in analytics. Understand index functions, interpret query plans, apply EXPLAIN and ANALYZE, and implement straightforward optimization methods to maintain efficient 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 advanced analytics without reducing row counts. Master ranking, cumulative totals, moving averages, and partitioning methods that enable cohort, trend, and segmentation analysis using 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 versus query ordering
