SQL, Databases and Practical Data Analysis
A comprehensive, hands-on relational database and analytical SQL program designed to build professional data analysis competence using PostgreSQL and MySQL. Master relational database design, schema n...
What you'll learn
Learning Tracks & Pricing Options
Designed for self-paced independent study, with optional 1-on-1 instructor support when needed.
| Curriculum & Support Feature | Option A: Self-Study Ksh 8,000.00 | Option B: Assisted Learning Ksh 17,600.00 |
|---|---|---|
| All 10 Structured Modules | ||
| Downloadable PDF Manuals & Starter Code | ||
| Hands-on Practical Exercises & Capstone Labs | ||
| Verified Certificate of Completion | ||
| Dedicated 1-on-1 Instructor Mentorship | Add on demand | Included |
| Live Debugging & Screen-Share Support | Add on demand | Included |
| Milestone Portfolio Code Reviews | Add on demand | Included |
| Enroll Self-Study • Ksh 8,000.00 | Enroll Assisted • Ksh 17,600.00 |
Course Content
-
The Relational Model: Entities, Attributes, Tables, and Tuples30min
-
Keys and Identifiers: Candidate, Primary, Alternate, and Foreign Keys30min
-
Cardinality and Relationships: One-to-One, One-to-Many, and Many-to-Many30min
-
Entity-Relationship Diagrams (ERDs) & Data Modeling30min
-
Database Normalization: First Normal Form (1NF)30min
-
Database Normalization: Second Normal Form (2NF)30min
-
Database Normalization: Third Normal Form (3NF) & Boyce-Codd (BCNF)30min
-
Module 1 Practical Lab: Enterprise Relational Schema Design Challenge30min
-
SQL Standards and Database Engines: PostgreSQL vs MySQL30min
-
SQL Data Types: Numeric, Characters, Dates, and Booleans30min
-
Creating Tables: The `CREATE TABLE` Statement & Primary Keys30min
-
Enforcing Integrity Constraints: `NOT NULL`, `UNIQUE`, `CHECK`, and `DEFAULT`30min
-
Referential Integrity: Foreign Keys and Cascade Actions30min
-
Modifying Schemas: The `ALTER TABLE` Statement30min
-
Removing Objects: `DROP TABLE`, `TRUNCATE TABLE`, and `IF EXISTS`30min
-
Module 2 Practical Lab: Multi-Table E-Commerce DDL Buildout30min
-
Inserting Data: `INSERT INTO`, Multi-Row Inserts, and Default Values30min
-
Modifying Existing Records: The `UPDATE` Statement & Safe Filtering30min
-
Deleting Records: `DELETE FROM` vs Soft Deletes (`deleted_at`)30min
-
Upserting Data: `ON CONFLICT` (PostgreSQL) vs `ON DUPLICATE KEY UPDATE` (MySQL)30min
-
Transaction Fundamentals: `BEGIN`, `COMMIT`, `ROLLBACK`, and Savepoints30min
-
ACID Properties of Relational Transactions30min
-
Transaction Isolation Levels & Concurrency Phenomena30min
-
Module 3 Practical Lab: Transactional Order Processing Pipeline30min
-
The `SELECT` Statement: Column Projection, Expressions, and Aliases30min
-
Filtering Data with the `WHERE` Clause: Comparison & Logical Operators30min
-
Range and Set Filtering: `BETWEEN`, `IN`, and `NOT IN`30min
-
Pattern Matching: `LIKE`, `ILIKE`, Wildcards, and Regular Expressions30min
-
Three-Valued Logic & The Nature of `NULL`: `IS NULL` vs `= NULL`30min
-
Null Coalescing and Replacement: `COALESCE()` and `NULLIF()`30min
-
Sorting Results: `ORDER BY`, Multi-Column Sorting, and Null Ordering30min
-
Pagination: `LIMIT` / `OFFSET` and Keyset (Cursor) Pagination30min
-
Aggregate Functions: `COUNT()`, `SUM()`, `AVG()`, `MIN()`, and `MAX()`30min
-
Grouping Records: The `GROUP BY` Clause Mechanics30min
-
Filtering Aggregated Groups: The `HAVING` Clause vs `WHERE` Clause30min
-
Counting Unique Occurrences: `COUNT(DISTINCT column)`30min
-
Conditional Aggregation: Combining Aggregates with `CASE WHEN`30min
-
The Logical Query Processing Order in SQL30min
-
Advanced Grouping Extensions: `ROLLUP`, `CUBE`, and `GROUPING SETS`30min
-
Module 5 Practical Lab: Executive KPI & Revenue Aggregation Dashboard30min
-
The Relational Join Concept: Combining Data via Foreign Keys30min
-
Matching Records: The `INNER JOIN` Statement30min
-
Preserving Left-Side Records: The `LEFT OUTER JOIN` Statement30min
-
Finding Orphaned & Missing Records: Anti-Join Patterns30min
-
Right Joins & Full Outer Joins (`RIGHT JOIN` and `FULL OUTER JOIN`)30min
-
Hierarchical Relationships & The Self-Join Pattern30min
-
Cartesian Products: `CROSS JOIN` and Generating Permutations30min
-
Module 6 Practical Lab: Comprehensive Multi-Table Commercial Intelligence Query30min
-
Scalar and Multi-Row Subqueries: Subqueries in `WHERE` and `SELECT`30min
-
Correlated Subqueries: Row-by-Row Evaluation Mechanics30min
-
The `EXISTS` and `NOT EXISTS` Operators30min
-
Derived Tables: Subqueries in the `FROM` Clause30min
-
Common Table Expressions (CTEs): The `WITH` Clause Syntax30min
-
Chaining Multiple CTEs for Multi-Stage Data Pipelines30min
-
Recursive Common Table Expressions: Traversing Hierarchical Data30min
-
Module 7 Practical Lab: Multi-Stage Customer Segmentation & Lifetime Value Analysis30min
-
Introduction to SQL Window Functions: The `OVER()` Clause Architecture30min
-
Partitioning Data Windows: The `PARTITION BY` Sub-Clause30min
-
Ranking Functions: `ROW_NUMBER()`, `RANK()`, and `DENSE_RANK()`30min
-
Filtering by Window Calculations: Top-N Queries via CTEs30min
-
Time-Series Analysis with Navigation Functions: `LEAD()` and `LAG()`30min
-
Cumulative Aggregates: Running Totals and Year-to-Date (YTD) Revenue30min
-
Window Framing: `ROWS BETWEEN` vs `RANGE BETWEEN` (Moving Averages)30min
-
Module 8 Practical Lab: Advanced Cohort Retention & Churn Analysis30min
-
The Query Optimizer & Execution Plans: `EXPLAIN` and `EXPLAIN ANALYZE`30min
-
Understanding Scan Types: Sequential (Table) Scans vs Index Scans30min
-
B-Tree Indexes: Architecture, Primary Key Indexing, and Search Speed30min
-
Composite Indexes & The Leftmost Prefix Rule30min
-
Covering Indexes & Index-Only Scans30min
-
The Cost of Indexing: Write Overhead and Index Bloat30min
-
Query Anti-Patterns That Defeat Indexes (SARGability)30min
-
Module 9 Practical Lab: Slow Query Diagnostic & Performance Optimization Drill30min
-
Capstone Architecture & Business Intelligence Requirements30min
-
Capstone Phase 1: Relational Schema Buildout & Constraint Enforcement30min
-
Capstone Phase 2: Data Ingestion, Cleaning & Transformation Pipeline30min
-
Capstone Phase 3: Commercial Sales & Revenue Performance Analytics30min
-
Capstone Phase 4: Customer Segmentation & RFM Analysis Modeling30min
-
Capstone Phase 5: Customer Retention & Cohort Churn Modeling30min
-
Capstone Phase 6: Query Performance Tuning & Production Indexing30min
-
Capstone Presentation: Executive BI Insights & SQL Portfolio Defense30min
Description
Recommended Learning Pathway
Basic computer literacy and basic familiarity with spreadsheet tables. No prior database, SQL, or programming experience required.
Student Reviews & Feedback
Verified ratings from students enrolled in this course.
No reviews yet for this course. Be the first enrolled student to share feedback!