Lesson 1Filtering Methods: WHERE vs 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 conditions that depend on each row in your data.
WHERE vs HAVING in grouped dataUsing IN and NOT IN with subqueriesEXISTS and NOT EXISTS for partial joinsLinked subqueries for row-specific logicManaging NULLs in filtersTips for fast complex 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 maths. Learn about number scales, text handling, and solid date and timestamp work for time-related analysis in your projects.
Number types and accuracy for measuresText and character data pointsDATE vs TIMESTAMP with time zonesChanging types with castingDate maths and time gapsPulling date parts for groupingLesson 3Grouping and Summaries: GROUP BY, HAVING, COUNT, SUM, AVG, MIN, MAXLearn to sum up data using groups and summaries. Apply GROUP BY and HAVING to create measures, use COUNT, SUM, AVG, MIN, MAX, and craft strong summary queries for charts and reports in your work.
Basics of GROUP BY and its formSummary functions COUNT and SUMAVG, MIN, MAX for spread analysisHAVING to check summary resultsGrouping by formulas and rangesNULLs in summary calculationsLesson 4Loading CSV Files into Databases: COPY, LOAD DATA, SQLite Import, Common ErrorsPick up hands-on ways to bring CSV data into databases for checking. Use COPY, LOAD DATA, SQLite import, manage separators and codes, and dodge usual mistakes that lead to faulty or incomplete loads.
Getting CSVs ready for safe importsCOPY in PostgreSQL and like systemsLOAD DATA for MySQL and matching toolsSQLite import steps and choicesDealing with codes, separators, quotesChecking row numbers and failed recordsLesson 5DDL and DML Basics: CREATE TABLE, ALTER, INSERT, UPDATE, DELETE, Transaction HandlingSee 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 solid in analysis flows and pipes.
Making analysis tables with CREATE TABLESafe schema changes with ALTER TABLEINSERT ways for big and step loadsUPDATE and DELETE with safe checksCOMMIT, ROLLBACK, transaction limitsACID rules in analysis tasksLesson 6Query Basics: SELECT, WHERE, ORDER BY, LIMIT, DISTINCTMaster key query forms used in most checks. See SELECT pull columns, WHERE pick rows, ORDER BY arrange results, LIMIT set sample size, and DISTINCT cut repeats in analysis queries.
SELECT list setup and column namesRow picking with WHERE checksArranging results with ORDER BYLIMIT and OFFSET for data samplesDISTINCT to drop repeatsBasic query fixes and tweaksLesson 7Joins and Set Work: INNER, LEFT, RIGHT, FULL, CROSS, UNION, EXCEPT, INTERSECTGrasp how joins and set work mix datasets for checks. Know when to pick each join kind, dodge repeat errors, and use UNION, EXCEPT, INTERSECT for deep analysis matches.
INNER JOIN for matching datasetsLEFT, RIGHT, FULL OUTER JOIN casesCROSS JOIN and full mixes in checksUNION vs UNION ALL for piling dataEXCEPT and INTERSECT for set matchesSpotting and fixing join repeatsLesson 8Relational Database Ideas: Tables, Primary/Foreign Keys, Normal vs Denormal FormsGrasp main relational ideas behind analysis setups. Learn tables, primary and foreign keys, normal forms, and when to denormal for speed in reports and business intel tasks.
Tables, rows, columns in real usePrimary keys and unique rulesForeign keys and link strengthNormal forms and repeat controlDenormal for report speedStar and snowflake setup overviewsLesson 9Performance Basics: Indexes, Query Plans, Explain/Analyze, Simple Speed Tips for Analysis QueriesGet a real sense of query speed for analytics. Learn index work, read plans, use EXPLAIN and ANALYZE, and simple speed tricks to keep analysis queries running smooth.
Indexes speeding lookups and joinsReading query plans rightEXPLAIN and ANALYZE in actionFinding 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 split strategies for group, trend, and split analysis in SQL.
Window form and OVER partROW_NUMBER, RANK, DENSE_RANK casesRunning sums with SUM() OVER()Moving averages with framesPARTITION BY for group and split logicORDER BY in windows vs main order
