Lesson 1Filtering methods: WHERE versus HAVING, using EXISTS, IN, linked subqueriesBuild sharp filtering skills for analysis queries. Compare WHERE and HAVING, apply EXISTS and IN for subquery checks, and use linked subqueries to handle complex, row-specific conditions in your data work.
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 conditionsSpeed tips for tricky filtersLesson 2SQL data types and date/time management (DATE, TIMESTAMP, number accuracy)Grasp main SQL data types for analytics and their impact on storage, accuracy, and sums. Understand number scales, text handling, and solid date and timestamp operations for time-related analysis in local settings.
Number types and accuracy for measuresText and character data pointsDATE versus TIMESTAMP and time zonesType changing and conversionsDate sums and period calculationsPulling date parts for groupingLesson 3Summaries and grouping: GROUP BY, HAVING, COUNT, SUM, AVG, MIN, MAXMaster data summarising with groupings and aggregates. Use GROUP BY and HAVING to create measures, apply COUNT, SUM, AVG, MIN, MAX, and craft strong summary queries for reports and dashboards.
GROUP BY basics and structureAggregate functions COUNT and SUMAVG, MIN, MAX for spreadsHAVING to filter summary resultsGrouping by formulas and rangesHandling NULLs in summariesLesson 4Importing CSVs into databases: COPY, LOAD DATA, sqlite import, and usual trapsLearn hands-on ways to bring CSV data into databases for analysis. Use COPY, LOAD DATA, SQLite import, manage separators and codes, and dodge common errors that lead to faulty or incomplete loads.
Prepping CSVs for sure importsUsing COPY in PostgreSQL and like systemsLOAD DATA for MySQL and matching enginesSQLite .import steps and choicesManaging codes, separators, quotesChecking row numbers and failed recordsLesson 5DDL and DML basics: CREATE TABLE, ALTER, INSERT, UPDATE, DELETE, transaction handlingUnderstand how DDL and DML build and change tables for analytics. Practice making and adjusting setups, adding and updating data, safe deleting, and transactions to keep data sound in analysis flows and pipes.
Creating analysis tables with CREATE TABLESafely changing setups with ALTER TABLEINSERT ways for bulk and step loadsUPDATE and DELETE with safe conditionsCOMMIT, ROLLBACK, transaction rangeACID traits in analysis tasksLesson 6Query basics: SELECT, WHERE, ORDER BY, LIMIT, DISTINCTMaster key query writing used in most analyses. Learn SELECT for columns, WHERE for rows, ORDER BY for sorting, LIMIT for samples, and DISTINCT for unique items in analysis queries.
SELECT list setup and column namesFiltering rows with WHERE conditionsSorting with ORDER BYLIMIT and OFFSET for data samplesUsing DISTINCT for uniquesBasic query fixing and improvingLesson 7Joins and set operations: INNER, LEFT, RIGHT, FULL, CROSS, UNION, EXCEPT, INTERSECTGrasp how joins and set operations mix datasets for analysis. Know when to use each join, avoid repeat errors, and how UNION, EXCEPT, INTERSECT aid complex analysis matches.
INNER JOIN for overlapping datasetsLEFT, RIGHT, FULL OUTER JOIN casesCROSS JOIN and full products in analysisUNION versus UNION ALL for stackingEXCEPT and INTERSECT for set matchesSpotting and fixing join repeatsLesson 8Relational database ideas: tables, primary/foreign keys, normalising versus denormalisingGrasp core relational ideas behind analysis setups. Learn tables, primary and foreign keys, normal forms, and when to denormalise for speed in reporting and business intelligence tasks.
Tables, rows, columns in usePrimary keys and unique rulesForeign keys and link soundnessNormal forms and repeat controlDenormalising for report speedStar and snowflake setups overviewLesson 9Performance basics: indexes, query plans, explain/analyze, simple speed strategies for analysis queriesGet a practical sense of query speed for analytics. Learn index workings, read plans, use EXPLAIN and ANALYZE, and apply easy speed strategies to keep analysis queries running well.
How indexes quicken lookups and joinsReading and understanding query plansUsing EXPLAIN and ANALYZE in practiceFinding slow filters and joinsSpeeding GROUP BY and summariesBasic index plans 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. Learn ranking, running sums, moving averages, and partitioning ways that drive group, trend, and split analysis in SQL.
Window function structure and OVER clauseROW_NUMBER, RANK, DENSE_RANK casesRunning sums with SUM() OVER()Moving averages with window rangesPARTITION BY for group and split logicORDER BY in windows versus query sort
