Lesson 1Filtering Methods: WHERE versus HAVING, Using EXISTS, IN, and Linked SubqueriesMaster accurate filtering approaches for analytical queries. Compare WHERE and HAVING clauses, apply EXISTS and IN for subquery filtering, and use linked subqueries to handle complex, row-specific analytical requirements effectively.
WHERE vs HAVING in Grouped QueriesUsing IN and NOT IN with SubqueriesEXISTS and NOT EXISTS for Semi JoinsLinked Subqueries for Row-Specific LogicManaging NULLs in Filter ConditionsPerformance Advice for Complex FiltersLesson 2SQL Data Types and Date/Time Management (DATE, TIMESTAMP, Numeric Accuracy)Understand primary SQL data types for analytics and their impact on storage, accuracy, and computations. Cover numeric ranges, text processing, and reliable date and timestamp functions for time-related analytical tasks.
Numeric Types and Accuracy for MetricsCharacter and Text Data HandlingDATE vs TIMESTAMP and Time ZonesType Conversion and CastingDate Calculations and Interval OperationsExtracting Date Components for GroupingLesson 3Aggregations and Grouping: GROUP BY, HAVING, COUNT, SUM, AVG, MIN, MAXSummarise data using aggregations and grouping techniques. Employ GROUP BY and HAVING to create metrics, utilise COUNT, SUM, AVG, MIN, and MAX, and construct dependable aggregate queries for dashboards and reports.
GROUP BY Basics and SyntaxAggregate Functions COUNT and SUMAVG, MIN, and MAX for DistributionsHAVING to Filter Aggregated ResultsGrouping by Expressions and RangesHandling NULLs in AggregatesLesson 4Importing CSVs into Databases: COPY, LOAD DATA, SQLite Import, and Frequent ErrorsAcquire practical skills to import CSV data into databases for analysis. Utilise COPY, LOAD DATA, and SQLite import features, manage delimiters and encodings, and sidestep common issues leading to faulty or incomplete imports.
Preparing CSVs for Dependable ImportsUsing COPY in PostgreSQL and Similar SystemsLOAD DATA for MySQL and Compatible EnginesSQLite .import Process and OptionsManaging Encodings, Delimiters, and QuotesVerifying Row Counts and Rejected RecordsLesson 5DDL and DML Essentials: CREATE TABLE, ALTER, INSERT, UPDATE, DELETE, Transaction ManagementGrasp how DDL and DML operations shape and update tables for analytics. Practice schema creation and modification, data insertion and updates, safe deletions, and transaction controls to maintain data reliability in analytical pipelines.
Creating Analytical Tables with CREATE TABLESafely Modifying Schemas with ALTER TABLEINSERT Patterns for Bulk and Incremental LoadsUPDATE and DELETE with Secure ConditionsCOMMIT, ROLLBACK, and Transaction BoundariesACID Properties in Analytical TasksLesson 6Query Fundamentals: SELECT, WHERE, ORDER BY, LIMIT, DISTINCTCommand essential query syntax vital for most analyses. Discover how SELECT fetches columns, WHERE selects rows, ORDER BY arranges results, LIMIT manages sample sizes, and DISTINCT eliminates duplicates in analytical queries.
SELECT List Design and Column AliasesFiltering Rows with WHERE ConditionsSorting Results with ORDER BYLIMIT and OFFSET for Data SamplingUsing DISTINCT to Remove DuplicatesBasic Query Troubleshooting and ImprovementLesson 7Joins and Set Operations: INNER, LEFT, RIGHT, FULL, CROSS, UNION, EXCEPT, INTERSECTComprehend how joins and set operations merge datasets for analysis. Determine suitable join types, prevent duplication mistakes, and leverage UNION, EXCEPT, and INTERSECT for intricate analytical comparisons.
INNER JOIN for Overlapping DatasetsLEFT, RIGHT, and FULL OUTER JOIN ScenariosCROSS JOIN and Cartesian Products in AnalysisUNION vs UNION ALL for Data StackingEXCEPT and INTERSECT for Set ComparisonsIdentifying and Managing Join DuplicationLesson 8Relational Database Principles: Tables, Primary/Foreign Keys, Normalisation vs DenormalisationGrasp fundamental relational principles supporting analytical schemas. Study tables, primary and foreign keys, normalisation levels, and scenarios for denormalisation to enhance performance in reporting and business intelligence tasks.
Tables, Rows, and Columns in PracticePrimary Keys and Uniqueness ConstraintsForeign Keys and Referential IntegrityNormalisation Forms and Redundancy ControlDenormalisation for Reporting EfficiencyStar and Snowflake Schemas IntroductionLesson 9Performance Fundamentals: Indexes, Query Plans, EXPLAIN/ANALYZE, Basic Optimisation for Analytical QueriesObtain a practical perspective on query performance in analytics. Understand index operations, interpret query plans, apply EXPLAIN and ANALYZE, and implement straightforward optimisation tactics to maintain efficient analytical queries.
How Indexes Accelerate Lookups and JoinsReading and Interpreting Query PlansApplying EXPLAIN and ANALYZE PracticallySpotting Slow Filters and JoinsOptimising GROUP BY and AggregationsBasic Indexing Tactics for AnalyticsLesson 10Window Functions Introduction: ROW_NUMBER, RANK, DENSE_RANK, SUM() OVER(), AVG() OVER(), PARTITION BYInvestigate window functions for sophisticated analytics without row reduction. Master ranking, cumulative totals, moving averages, and partitioning methods that enable cohort, trend, and segmentation analysis in SQL.
Window Function Syntax and OVER ClauseROW_NUMBER, RANK, and DENSE_RANK ApplicationsCumulative Totals with SUM() OVER()Moving Averages with Window FramesPARTITION BY for Cohort and Segment LogicORDER BY in Windows vs Query Ordering
