Lesson 1Filtering ways: WHERE vs HAVING, using EXISTS, IN, linked subqueriesLearn exact filtering plans for analysis queries. Compare WHERE and HAVING, use EXISTS and IN for subquery filters, and use linked subqueries to show complex, row-based analysis conditions.
WHERE vs HAVING in grouped queriesUsing IN and NOT IN with subqueriesEXISTS and NOT EXISTS for semi joinsLinked subqueries for row-based logicHandling NULLs in filter conditionsSpeed tips for complex filtersLesson 2SQL data types and date/time work (DATE, TIMESTAMP, number exactness)Learn the key SQL data types for analysis and how they change storage, exactness, and sums. Know number scales, text work, and strong date and timestamp tasks for time-based analysis.
Number types and exactness for measuresCharacter and text data pointsDATE vs TIMESTAMP and time zonesChanging and converting typesDate sums and interval sumsGetting date parts for groupingLesson 3Grouping and sums: GROUP BY, HAVING, COUNT, SUM, AVG, MIN, MAXLearn to sum data with groupings and sums. Use GROUP BY and HAVING to make measures, use COUNT, SUM, AVG, MIN, MAX, and plan strong sum queries for boards and reports.
GROUP BY basics and writingSum functions COUNT and SUMAVG, MIN, MAX for spreadsHAVING to filter sum resultsGrouping by expressions and groupsDealing with NULLs in sumsLesson 4Loading CSVs into databases: COPY, LOAD DATA, sqlite import, common problemsLearn real ways to load CSV data into databases for analysis. Use COPY, LOAD DATA, SQLite import, handle separators and codes, and skip common problems that make bad or half loads.
Preparing CSVs for sure importsUsing COPY in PostgreSQL and like systemsLOAD DATA for MySQL and matching enginesSQLite .import steps and choicesHandling codes, separators, quotesChecking row counts and turned away recordsLesson 5DDL and DML basics: CREATE TABLE, ALTER, INSERT, UPDATE, DELETE, deal controlLearn how DDL and DML shape and change tables for analysis. Practice making and changing plans, adding and updating data, deleting safe, and using deals to keep data whole in analysis work and lines.
Making analysis tables with CREATE TABLEChanging plans safe with ALTER TABLEINSERT ways for bulk and step loadsUPDATE and DELETE with safe rulesCOMMIT, ROLLBACK, deal rangeACID parts in analysis loadsLesson 6Query basics: SELECT, WHERE, ORDER BY, LIMIT, DISTINCTMaster key query writing used in almost every analysis. Learn SELECT gets columns, WHERE filters rows, ORDER BY sorts results, LIMIT sets sample size, DISTINCT cuts repeats in analysis queries.
SELECT list plan and column namesFiltering rows with WHERE rulesSorting results with ORDER BYLIMIT and OFFSET for sampling dataUsing DISTINCT to cut repeatsBasic query fix and improveLesson 7Joins and set tasks: INNER, LEFT, RIGHT, FULL, CROSS, UNION, EXCEPT, INTERSECTKnow how joins and set tasks mix datasets for analysis. Learn when to use each join type, how to skip repeat errors, and how UNION, EXCEPT, INTERSECT help complex analysis compares.
INNER JOIN for crossing datasetsLEFT, RIGHT, FULL OUTER JOIN casesCROSS JOIN and full products in analysisUNION vs UNION ALL for stacking dataEXCEPT and INTERSECT for set comparesFinding and handling join repeatsLesson 8Relational database ideas: tables, main/foreign keys, normal vs denormalKnow key relational ideas that hold analysis plans. Learn tables, main and foreign keys, normal forms, and when to denormal for speed in reports and BI loads.
Tables, rows, columns in realMain keys and unique rulesForeign keys and link wholeNormal forms and repeat controlDenormal for report speedStar and snowflake plans overviewLesson 9Speed basics: indexes, query plans, explain/analyze, simple improve ways for analysis queriesGet a real view of query speed for analysis. Learn how indexes work, read query plans, use EXPLAIN and ANALYZE, and use simple improve ways to keep analysis queries fast.
How indexes speed lookups and joinsReading and knowing query plansUsing EXPLAIN and ANALYZE in realFinding slow filters and joinsImproving GROUP BY and sumsBasic index ways for analysisLesson 10Window functions overview: ROW_NUMBER, RANK, DENSE_RANK, SUM() OVER(), AVG() OVER(), PARTITION BYLook at window functions to do advanced analysis without cutting rows. Learn ranking, running totals, moving averages, and split ways that power group, trend, and split analysis in SQL.
Window function writing and OVER partROW_NUMBER, RANK, DENSE_RANK casesRunning totals with SUM() OVER()Moving averages with window framesPARTITION BY for group and split logicORDER BY in windows vs query order
