Lesson 1Filtering Methods: WHERE versus HAVING, Using EXISTS, IN, Linked SubqueriesBuild sharp filtering skills for analysis queries. Compare WHERE and HAVING clauses, apply EXISTS and IN for subquery checks, and use linked subqueries to handle complex conditions that depend on individual rows in your data.
WHERE versus HAVING in grouped data queriesApplying IN and NOT IN with subqueriesEXISTS and NOT EXISTS for partial joinsLinked subqueries for row-specific logicManaging NULL values in filtersSpeed advice for tricky filtersLesson 2SQL Data Types and Handling Dates and Times (DATE, TIMESTAMP, Number Accuracy)Get to know the key SQL data types for analytics and their impact on storage, accuracy, and maths. Cover number scales, text management, and solid date and time operations for time-related analysis in Zambian contexts.
Number types and accuracy for key measuresText and character data points to considerDATE versus TIMESTAMP with time zonesChanging and converting data typesDate calculations and time intervalsPulling date parts for grouping purposesLesson 3Summing and Grouping: GROUP BY, HAVING, COUNT, SUM, AVG, MIN, MAXMaster summarising data through grouping and sums. Employ GROUP BY and HAVING to create measures, use COUNT, SUM, AVG, MIN, MAX functions, and craft strong summary queries for reports and dashboards in business settings.
Basics and structure of GROUP BYSumming functions COUNT and SUMAVG, MIN, MAX for spread analysisHAVING to sift summed outcomesGrouping via expressions and rangesNULL handling in summary functionsLesson 4Loading CSV Files into Databases: COPY, LOAD DATA, SQLite Import, and Common ErrorsPick up hands-on ways to bring CSV data into databases for review. Utilise COPY, LOAD DATA, SQLite import tools, manage separators and formats, and dodge usual mistakes that lead to faulty or incomplete data loads.
Getting CSVs ready for smooth importsCOPY command in PostgreSQL-like setupsLOAD DATA for MySQL and matching systemsSQLite import steps and choicesDealing with formats, separators, quotesChecking row numbers and failed entriesLesson 5Key DDL and DML: CREATE TABLE, ALTER, INSERT, UPDATE, DELETE, Transaction ManagementUnderstand how DDL and DML build and change tables for analytics. Try making and adjusting structures, adding and updating info, safe removal, and transactions to keep data sound in analysis flows and processes.
Building analysis tables with CREATE TABLESafe structure changes via ALTER TABLEINSERT ways for large and small loadsUPDATE and DELETE with careful conditionsCOMMIT, ROLLBACK, transaction limitsACID rules in analysis tasksLesson 6Basic Queries: SELECT, WHERE, ORDER BY, LIMIT, DISTINCTGet command of main query rules used in most analyses. See how SELECT picks columns, WHERE picks rows, ORDER BY arranges results, LIMIT sets sample size, and DISTINCT cuts repeats in analytical queries.
Designing SELECT lists and column namesRow picking with WHERE rulesArranging results via ORDER BYLIMIT and OFFSET for data samplesDISTINCT to cut duplicatesSimple query fixes and tweaksLesson 7Joins and Set Operations: INNER, LEFT, RIGHT, FULL, CROSS, UNION, EXCEPT, INTERSECTGrasp how joins and set operations blend datasets for review. Know when to pick each join kind, steer clear of repeat errors, and see how UNION, EXCEPT, INTERSECT aid tough analytical matches.
INNER JOIN for overlapping datasetsLEFT, RIGHT, FULL OUTER JOIN casesCROSS JOIN and full products in reviewUNION versus UNION ALL for piling dataEXCEPT and INTERSECT for set matchesSpotting and fixing join repeatsLesson 8Relational Database Ideas: Tables, Primary/Foreign Keys, Normalisation versus DenormalisationGrasp main relational ideas that support analytical setups. Learn about tables, main and foreign keys, normalisation levels, and when to denormalise for better speed in reporting and business intelligence tasks.
Tables, rows, columns in real usePrimary keys and unique rulesForeign keys and link soundnessNormalisation levels and repeat controlDenormalisation for report speedStar and snowflake setup overviewsLesson 9Performance Basics: Indexes, Query Plans, Explain/Analyse, Simple Speed Tips for Analytical QueriesGet a practical sense of query speed for analytics. Learn index workings, read query plans, use EXPLAIN and ANALYSE, and try basic speed boosts to keep analytical queries running well.
How indexes quicken searches and joinsReading and understanding query plansPractical use of EXPLAIN and ANALYSEFinding slow filters and joinsBoosting GROUP BY and sumsBasic 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, ongoing totals, sliding averages, and splitting tactics that drive group, trend, and split analysis in SQL.
Window function rules and OVER partROW_NUMBER, RANK, DENSE_RANK casesOngoing totals with SUM() OVER()Sliding averages with window limitsPARTITION BY for group and split logicORDER BY in windows versus main order
