Lesson 1Filtering ways: WHERE against HAVING, use of EXISTS, IN, linked subqueriesBuild good 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 against HAVING in grouped queriesUsing IN and NOT IN with subqueriesEXISTS and NOT EXISTS for half joinsLinked subqueries for row-based logicDealing with NULLs in filter rulesSpeed tips for hard filtersLesson 2SQL data kinds and date/time work (DATE, TIMESTAMP, number exactness)Learn main SQL data kinds used in 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 kinds and exactness for measuresCharacter and text data thoughtsDATE against TIMESTAMP and time areasChanging and moving between kindsDate sums and time gap countsTaking 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 tasks COUNT and SUMAVG, MIN, MAX for spreadsHAVING to filter summed resultsGrouping by sayings and groupsDealing with NULLs in sumsLesson 4Loading CSVs into databases: COPY, LOAD DATA, sqlite bring in, and common trapsLearn real ways to load CSV data into databases for analysis. Use COPY, LOAD DATA, SQLite bring in, handle separators and codes, and avoid common traps that make bad or half loads.
Getting CSVs ready for sure bringsUsing 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, putting in and updating data, deleting safe, and using deals to keep data whole in analysis steps 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 reachACID parts in analysis loadsLesson 6Query basics: SELECT, WHERE, ORDER BY, LIMIT, DISTINCTMaster main query writing used in almost every analysis. Learn how SELECT gets columns, WHERE filters rows, ORDER BY sorts results, LIMIT checks sample size, DISTINCT takes away 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 take away 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 kind, how to avoid repeat errors, and how UNION, EXCEPT, INTERSECT help complex analysis compares.
INNER JOIN for crossing datasetsLEFT, RIGHT, FULL OUTER JOIN usesCROSS JOIN and full products in analysisUNION against UNION ALL for stacking dataEXCEPT and INTERSECT for set comparesFinding and handling join repeatsLesson 8Relational database ideas: tables, main/foreign keys, normal against denormalKnow main relational ideas that hold analysis plans. Learn tables, main and foreign keys, normal forms, and when to denormal for speed in reporting and BI loads.
Tables, rows, columns in realMain keys and unique limitsForeign keys and link wholeNormal forms and repeat controlDenormal for reporting speedStar and snowflake plans overviewLesson 9Speed basics: indexes, query plans, explain/analyze, simple improve ways for analysis queriesGet 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 good.
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 high analysis without folding rows. Learn ranking, running sums, moving averages, and part ways that power group, trend, and part analysis in SQL.
Window function writing and OVER partROW_NUMBER, RANK, DENSE_RANK usesRunning sums with SUM() OVER()Moving averages with window framesPARTITION BY for group and part logicORDER BY in windows against query order
