Lesson 1Filterin techniques: WHERE vs HAVING, use a EXISTS, IN, correlated subqueriesDevelop precise filterin strategies fi analytical queries. Compare WHERE an HAVING, use EXISTS an IN fi subquery filters, an apply correlated subqueries to express complex, row-aware analytical conditions in yuh work.
WHERE vs HAVING in grouped queriesUsin IN an NOT IN wid subqueriesEXISTS an NOT EXISTS fi semi joinsCorrelated subqueries fi row-aware logicHandlin NULLs in filter conditionsPerformance tips fi complex filtersLesson 2SQL data types an date/time handlin (DATE, TIMESTAMP, numeric precision)Learn di main SQL data types used in analytics an how dem affect storage, precision, an calculations. Understand numeric scales, text handlin, an robust date an timestamp operations fi time-based analysis, yuh know.
Numeric types an precision fi metricsCharacter an text data considerationsDATE vs TIMESTAMP an time zonesCastin an convertin between typesDate arithmetic an interval calculationsExtractin parts a dates fi groupinLesson 3Aggregations an groupin: GROUP BY, HAVING, COUNT, SUM, AVG, MIN, MAXLearn to summarize data wid aggregations an groupin. Use GROUP BY an HAVING to build metrics, apply COUNT, SUM, AVG, MIN, an MAX, an design robust aggregate queries fi dashboards an reports, irie.
GROUP BY fundamentals an syntaxAggregate functions COUNT an SUMAVG, MIN, an MAX fi distributionsHAVING to filter aggregated resultsGroupin by expressions an bucketsDealin wid NULLs in aggregatesLesson 4Loadin CSVs into databases: COPY, LOAD DATA, sqlite import, an common pitfallsLearn practical methods to load CSV data into databases fi analysis. Use COPY, LOAD DATA, an SQLite import, handle delimiters an encodings, an avoid common pitfalls dat cause bad or partial loads, yuh see.
Preparin CSVs fi reliable importsUsin COPY in PostgreSQL an similar systemsLOAD DATA fi MySQL an compatible enginesSQLite .import workflow an optionsHandlin encodings, delimiters, an quotesValidatin row counts an rejected recordsLesson 5DDL an DML essentials: CREATE TABLE, ALTER, INSERT, UPDATE, DELETE, transaction controlLearn how DDL an DML shape an modify tables fi analytics. Practice creatin an alterin schemas, insertin an updatin data, deletin safely, an usin transactions to ensure data integrity in analytical workflows an pipelines.
Creatin analytical tables wid CREATE TABLEModifying schemas safely wid ALTER TABLEINSERT patterns fi bulk an incremental loadsUPDATE an DELETE wid safe predicatesCOMMIT, ROLLBACK, an transaction scopeACID properties in analytical workloadsLesson 6Query basics: SELECT, WHERE, ORDER BY, LIMIT, DISTINCTMaster core query syntax used in nearly every analysis. Learn how SELECT retrieves columns, WHERE filters rows, ORDER BY sorts results, LIMIT controls sample size, an DISTINCT removes duplicates in analytical queries, mon.
SELECT list design an column aliasesFilterin rows wid WHERE conditionsSortin results wid ORDER BYLIMIT an OFFSET fi samplin dataUsin DISTINCT to remove duplicatesBasic query debuggin an refinementLesson 7Joins an set operations: INNER, LEFT, RIGHT, FULL, CROSS, UNION, EXCEPT, INTERSECTUnderstand how joins an set operations combine datasets fi analysis. Learn when to use each join type, how to avoid duplication errors, an how UNION, EXCEPT, an INTERSECT support complex analytical comparisons.
INNER JOIN fi intersectin datasetsLEFT, RIGHT, an FULL OUTER JOIN use casesCROSS JOIN an Cartesian products in analysisUNION vs UNION ALL fi stackin dataEXCEPT an INTERSECT fi set comparisonsDetectin an handlin join duplicationLesson 8Relational database concepts: tables, primary/foreign keys, normalization vs denormalizationUnderstand core relational concepts dat underpin analytical schemas. Learn tables, primary an foreign keys, normalization forms, an when to denormalize fi performance in reportin an BI workloads, yuh hear.
Tables, rows, an columns in practicePrimary keys an uniqueness constraintsForeign keys an referential integrityNormalization forms an redundancy controlDenormalization fi reportin performanceStar an snowflake schemas overviewLesson 9Performance basics: indexes, query plans, explain/analyze, simple optimization strategies fi analytical queriesGain a practical view a query performance fi analytics. Learn how indexes work, read query plans, use EXPLAIN an ANALYZE, an apply simple optimization strategies to keep analytical queries efficient, seen.
How indexes speed up lookups an joinsReadin an interpretin query plansUsin EXPLAIN an ANALYZE in practiceIdentifin slow filters an joinsOptimizin GROUP BY an aggregationsBasic indexin strategies fi analyticsLesson 10Window functions overview: ROW_NUMBER, RANK, DENSE_RANK, SUM() OVER(), AVG() OVER(), PARTITION BYExplore window functions to perform advanced analytics widout collapsin rows. Learn rankin, runnin totals, movin averages, an partitionin strategies dat power cohort, trend, an segmentation analysis in SQL.
Window function syntax an OVER clauseROW_NUMBER, RANK, an DENSE_RANK use casesRunnin totals wid SUM() OVER()Movin averages wid window framesPARTITION BY fi cohort an segment logicORDER BY in windows vs query orderin
