My SQL → OLTP vs OLAP - Home

Curriculum

24 Sections
90 Lessons
20 Weeks

Expand all sectionsCollapse all sections

Data Warehouse Basics
4
- 1.1
  What is a Data Warehouse?
  20 Minutes
- 1.2
  OLTP vs OLAP
  20 Minutes
- 1.3
  ETL vs ELT Concepts
  20 Minutes
- 1.4
  Data Lake vs Data Warehouse
  20 Minutes
Data Modeling
3
- 2.1
  Dimensional Modeling
  20 Minutes
- 2.2
  ER Modeling vs Star Schema vs Snowflake Schema
  20 Minutes
- 2.3
  Fact Table & Dimension Table
  20 Minutes
Schemas
3
- 3.1
  Star Schema
- 3.2
  Snowflake Schema
- 3.3
  Advantages and Disadvantages
Normalization & Denormalization
3
- 4.1
  1NF
- 4.2
  2NF
- 4.3
  3NF
Slowly Changing Dimensions (SCD)
0
Fact Table Types
6
- 6.1
  Additive Facts
- 6.2
  Semi Additive Facts
- 6.3
  Factless Facts
- 6.4
  Additive Facts
- 6.5
  Semi Additive Facts
- 6.6
  Factless Facts
Dimension Types
4
- 7.1
  Conformed Dimension
- 7.2
  Junk Dimension
- 7.3
  Degenerate Dimension
- 7.4
  Role Playing Dimension
ETL Concepts
3
- 8.1
  ETL Overview
- 8.2
  Extraction, Transformation, Loading Steps
- 8.3
  ETL Pipeline Design Principles
Introduction to MySQL
5
- 9.1
  MySQL Architecture
- 9.2
  Client
- 9.3
  Server Model
- 9.4
  Storage Engines (InnoDB vs MyISAM)
- 9.5
  Installation & Setup
Database Basics
3
- 10.1
  Database & Table Creation
- 10.2
  Datatypes
- 10.3
  Constraints (Primary Key, Foreign Key, Unique, Not Null, Default, Check)
Basic SQL Queries
5
- 11.1
  SELECT Statement
- 11.2
  Filtering with WHERE
- 11.3
  ORDER BY
- 11.4
  DISTINCT
- 11.5
  LIMIT / OFFSET
Functions and Operators
5
- 12.1
  Aggregate Functions (SUM, AVG, COUNT, MIN, MAX)
- 12.2
  String Functions
- 12.3
  Date & Time Functions
- 12.4
  Mathematical Functions
- 12.5
  CASE WHEN Logic
Joins and Set Operations
8
- 13.1
  INNER JOIN
- 13.2
  LEFT JOIN
- 13.3
  RIGHT JOIN
- 13.4
  FULL OUTER JOIN
- 13.5
  CROSS JOIN
- 13.6
  UNION
- 13.7
  UNION ALL
- 13.8
  INTERSECT / EXCEPT
Subqueries & Nested Queries
3
- 14.1
  Scalar Subqueries
- 14.2
  Correlated Subqueries
- 14.3
  EXISTS vs IN vs JOIN
Advanced SQL Queries
6
- 15.1
  Common Table Expressions (CTE)
- 15.2
  Window Functions (ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, NTILE)
- 15.3
  Recursive CTE
- 15.4
  Derived Tables
- 15.5
  Framing
- 15.6
  Partition By
Data Manipulation (DML)
4
- 16.1
  INSERT
- 16.2
  INSERT INTO SELECT
- 16.3
  UPDATE
- 16.4
  DELETE
Data Definition (DDL)
4
- 17.1
  CREATE
- 17.2
  ALTER
- 17.3
  DROP
- 17.4
  TRUNCATE
Transaction Management
2
- 18.1
  COMMIT
- 18.2
  ROLLBACK
Views
2
- 19.1
  CREATE VIEW
- 19.2
  CREATE OR REPLACE VIEW
Indexes
5
- 20.1
  Clustered Index (Primary Key)
- 20.2
  Non-Clustered Index (Secondary Index)
- 20.3
  Composite Indexes
- 20.4
  BTree
- 20.5
  Hash Index
Stored Procedures (PL/SQL equivalent)
4
- 21.1
  CREATE PROCEDURE
- 21.2
  IN, OUT, INOUT Parameters
- 21.3
  Control Flow (IF, CASE, WHILE, LOOP, REPEAT)
- 21.4
  Error Handling
Functions
3
- 22.1
  CREATE FUNCTION
- 22.2
  RETURN Statement
- 22.3
  Deterministic vs Non-Deterministic Functions
Triggers
3
- 23.1
  BEFORE INSERT/UPDATE/DELETE
- 23.2
  AFTER INSERT/UPDATE/DELETE
- 23.3
  Use Cases and Best Practices
Cursors
2
- 24.1
  DECLARE Cursor
- 24.2
  OPEN, FETCH, CLOSE

Call Us Chat With Us Live Consultation