Lesson 1Filtering Methods: WHERE versus HAVING, Application of EXISTS, IN, Linked SubqueriesBuild accurate filtering approaches for analytical queries. Contrast WHERE and HAVING, employ EXISTS and IN for subquery filters, and utilize linked subqueries to convey intricate, row-specific analytical requirements in local data environments.
WHERE versus HAVING in grouped queriesEmploying 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)Grasp the primary SQL data types utilized in analytics and their impact on storage, accuracy, and computations. Comprehend numeric ranges, text management, and dependable date and timestamp operations for time-oriented analysis in Eritrean businesses.
Numeric types and accuracy for metricsCharacter and text data considerationsDATE versus TIMESTAMP and time zonesCasting and converting between typesDate arithmetic and interval calculationsExtracting parts of dates for groupingLesson 3Aggregations and Grouping: GROUP BY, HAVING, COUNT, SUM, AVG, MIN, MAXGrasp how to condense data using aggregations and grouping. Apply GROUP BY and HAVING to construct metrics, utilize COUNT, SUM, AVG, MIN, and MAX, and create sturdy aggregate queries for dashboards and reports in Eritrean commerce.
GROUP BY basics and structureAggregate functions COUNT and SUMAVG, MIN, and MAX for distributionsHAVING to filter aggregated resultsGrouping by expressions and bucketsDealing with NULLs in aggregatesLesson 4Importing CSVs into Databases: COPY, LOAD DATA, SQLite Import, and Frequent ErrorsGrasp practical ways to import CSV data into databases for analysis. Employ COPY, LOAD DATA, and SQLite import, manage separators and encodings, and evade common errors that lead to faulty or incomplete imports in local systems.
Preparing CSVs for dependable importsUsing COPY in PostgreSQL and similar systemsLOAD DATA for MySQL and compatible enginesSQLite .import workflow and optionsHandling encodings, delimiters, and quotesValidating row counts and rejected recordsLesson 5DDL and DML Essentials: CREATE TABLE, ALTER, INSERT, UPDATE, DELETE, Transaction ManagementGrasp how DDL and DML form and adjust tables for analytics. Practice building and modifying schemas, inserting and updating data, deleting securely, and applying transactions to maintain data reliability in analytical processes and pipelines in Eritrea.
Creating analytical tables with CREATE TABLEModifying schemas safely with ALTER TABLEINSERT patterns for bulk and incremental loadsUPDATE and DELETE with safe predicatesCOMMIT, ROLLBACK, and transaction scopeACID properties in analytical workloadsLesson 6Query Basics: SELECT, WHERE, ORDER BY, LIMIT, DISTINCTMaster essential query structures used in almost every analysis. Learn how SELECT fetches columns, WHERE filters rows, ORDER BY arranges results, LIMIT manages sample size, and DISTINCT eliminates duplicates in analytical queries for Eritrean data.
SELECT list design and column aliasesFiltering rows with WHERE conditionsSorting results with ORDER BYLIMIT and OFFSET for sampling dataUsing DISTINCT to remove duplicatesBasic query debugging and refinementLesson 7Joins and Set Operations: INNER, LEFT, RIGHT, FULL, CROSS, UNION, EXCEPT, INTERSECTComprehend how joins and set operations merge datasets for analysis. Learn when to apply each join type, how to prevent duplication mistakes, and how UNION, EXCEPT, and INTERSECT aid complex analytical comparisons in local contexts.
INNER JOIN for intersecting datasetsLEFT, RIGHT, and FULL OUTER JOIN use casesCROSS JOIN and Cartesian products in analysisUNION versus UNION ALL for stacking dataEXCEPT and INTERSECT for set comparisonsDetecting and handling join duplicationLesson 8Relational Database Concepts: Tables, Primary/Foreign Keys, Normalization versus DenormalizationComprehend core relational ideas that support analytical schemas. Learn tables, primary and foreign keys, normalization forms, and when to denormalize for efficiency in reporting and business intelligence tasks in Eritrea.
Tables, rows, and columns in practicePrimary keys and uniqueness constraintsForeign keys and referential integrityNormalization forms and redundancy controlDenormalization for reporting performanceStar and snowflake schemas overviewLesson 9Performance Basics: Indexes, Query Plans, Explain/Analyze, Simple Optimization for Analytical QueriesAcquire a practical perspective on query performance for analytics. Learn how indexes function, interpret query plans, use EXPLAIN and ANALYZE, and implement basic optimization tactics to maintain efficient analytical queries in Eritrean databases.
How indexes speed up lookups and joinsReading and interpreting query plansUsing EXPLAIN and ANALYZE in practiceIdentifying slow filters and joinsOptimizing GROUP BY and aggregationsBasic indexing strategies for analyticsLesson 10Window Functions Overview: ROW_NUMBER, RANK, DENSE_RANK, SUM() OVER(), AVG() OVER(), PARTITION BYInvestigate window functions to execute advanced analytics without reducing rows. Learn ranking, cumulative totals, moving averages, and partitioning methods that enable cohort, trend, and segmentation analysis in SQL for Eritrean enterprises.
Window function syntax and OVER clauseROW_NUMBER, RANK, and DENSE_RANK use casesRunning totals with SUM() OVER()Moving averages with window framesPARTITION BY for cohort and segment logicORDER BY in windows versus query ordering
