About Course
About Course
SQL is the #1 required skill in every data job posting worldwide — and PostgreSQL is the most powerful open-source database in the world. This course gives you both, from absolute zero to advanced, built around real datasets and real business scenarios.
Course Overview
- Duration: 52 hours — 10 to 11 weeks at 5 hours/week
- Level: Beginner to Advanced
- Prerequisites: Basic computer literacy — no SQL or coding experience needed
- Recommended Prior Courses: Excel for Data Analyst or Power BI
- Target Audience: Data analysts, business analysts, Power BI developers, and anyone working with data
- Tools: PostgreSQL 16+, pgAdmin 4, psql CLI
Course Objectives
By the end of this course, students will be able to:
- Write confident queries with filtering, sorting, grouping, and aggregation
- Combine data from multiple tables using all types of JOINs
- Solve complex problems using subqueries, CTEs, and window functions
- Design normalized database schemas from scratch
- Manipulate data safely using transactions and DML statements
- Use PostgreSQL-specific features: JSONB, arrays, and full-text search
- Read query execution plans and optimize slow queries
- Create views, stored procedures, functions, and triggers
- Manage roles, permissions, and database backups
- Build 4 real-world capstone projects for a job-ready portfolio
Next Steps After This Course
- Python for Data Analysis (datasciencehub.cloud) — combine SQL with Python for full analytics workflows
- Google BigQuery & Cloud SQL — scale your SQL skills to cloud data warehouses
- Advanced Analytics Engineering — dbt, data pipelines, and dimensional modeling
- Specialization tracks — Marketing, Financial, or HR Analytics
What Will You Learn?
- Database & PostgreSQL Fundamentals
- Understand how relational databases work and why SQL is essential
- Install and set up PostgreSQL, pgAdmin, and psql from scratch
- Navigate the PostgreSQL environment with confidence
- Querying & Filtering Data
- Write SELECT queries to retrieve exactly the data you need
- Filter, sort, and paginate results using WHERE, ORDER BY, and LIMIT
- Use BETWEEN, IN, LIKE, IS NULL, and pattern matching operators
- Aggregations & Grouping
- Summarize data using COUNT, SUM, AVG, MIN, and MAX
- Group data with GROUP BY and filter groups using HAVING
- Generate subtotals and grand totals with ROLLUP and CUBE
- Joining Tables
- Combine data from multiple tables using INNER, LEFT, RIGHT, and FULL JOIN
- Work with self-joins and CROSS joins for advanced scenarios
- Avoid the most common join mistakes that break reports
- Subqueries & CTEs
- Write subqueries inside SELECT, WHERE, and FROM clauses
- Simplify complex queries using CTEs (WITH clause)
- Traverse hierarchies using recursive CTEs
- Window Functions
- Rank rows using ROW_NUMBER, RANK, and DENSE_RANK
- Compare periods using LAG and LEAD
- Calculate running totals and moving averages without collapsing rows
- Data Manipulation
- Insert, update, and delete data safely
- Use UPSERT to handle insert-or-update scenarios
- Protect your data with transactions, COMMIT, and ROLLBACK
- Database Design
- Design clean, normalized schemas from a blank page
- Apply 1NF, 2NF, and 3NF normalization rules
- Read and build Entity-Relationship Diagrams (ERDs)
- Built-in Functions
- Manipulate text with string functions like TRIM, CONCAT, and REPLACE
- Work with dates using DATE_TRUNC, EXTRACT, and AGE
- Handle NULL values cleanly using COALESCE and CASE WHEN
- PostgreSQL-Specific Features
- Store and query semi-structured data using JSONB
- Work with array columns and GENERATE_SERIES
- Implement full-text search with tsvector and tsquery
- Performance & Optimization
- Read and understand EXPLAIN ANALYZE query plans
- Create the right indexes: B-tree, GIN, partial, and composite
- Identify and fix slow query anti-patterns
- Advanced PostgreSQL
- Build reusable views and materialized views
- Write functions and stored procedures using PL/pgSQL
- Automate tasks with triggers and audit logging
- Security & Administration
- Manage roles and permissions with GRANT and REVOKE
- Implement Row-Level Security for multi-user data access
- Back up and restore databases using pg_dump and pg_restore
- Real-World Capstone Projects
- Build an e-commerce analytics report with cohort and LTV analysis
- Deliver an HR workforce dashboard with attrition and tenure metrics
- Create a financial P&L report with budget vs actuals
- Analyze a marketing funnel with channel attribution
Course Content
Module 1 — Database Foundations & PostgreSQL Setup (3 Hours)
-
1.1 What is a database? Why not just use Excel or Google Sheets?
00:00 -
1.2 Relational vs non-relational databases — when to use each
27:43 -
1.3 Understanding tables, rows, columns, and relationships
25:55 -
1.4 What is SQL? History and the SQL standard
33:28 -
1.5 Installing PostgreSQL on Windows, Mac, and Linux
03:58 -
1.6 Setting up pgAdmin 4 — interface walkthrough
04:38 -
1.7 Using the Query Tool
03:48 -
1.8 Creating your first database and connecting to it
03:55 -
1.9 PostgreSQL architecture — databases, schemas, roles
00:00 -
1.10 Running your first SELECT query
08:05
Module 2 — SELECT & Filtering Data (4 Hours)
-
2.1 Anatomy of a SELECT statement
09:34 -
2.2 Selecting all columns vs specific columns
00:00 -
2.3 Column aliases with AS
04:03 -
2.4 Filtering with WHERE — comparison operators (=, !=, >, =, <=)
09:35 -
2.5 Combining conditions: AND, OR, NOT
10:51 -
2.6 BETWEEN for range filtering
08:39 -
2.7 IN and NOT IN for list matching
08:58 -
2.8 LIKE and ILIKE for pattern matching (%, _ wildcards)
13:06 -
2.9 IS NULL and IS NOT NULL
09:31 -
2.10 Removing duplicates with DISTINCT
07:01 -
2.11 Sorting results with ORDER BY (ASC, DESC, NULLS LAST)
19:16 -
2.12 Limiting results with LIMIT and OFFSET (pagination)
10:06 -
2.13 Module 2 Reference Guide (The Core Concept & Common Beginner Mistakes)
Module 3 — Data Types & Table Design (3 Hours)
-
Introuduction
02:34 -
3.1 Numeric types: INTEGER, BIGINT, NUMERIC, DECIMAL, FLOAT
07:30 -
3.2 Text types: VARCHAR, TEXT, CHAR — differences and when to use each
06:16 -
3.3 Date and time types: DATE, TIME, TIMESTAMP, TIMESTAMPTZ
06:44 -
3.4 Boolean type
02:47 -
3.5 UUID type and when to use it
03:39 -
3.6 Creating tables with CREATE TABLE
09:08 -
3.7 Column constraints: NOT NULL, UNIQUE, DEFAULT, CHECK
09:53 -
3.8 PRIMARY KEY — single and composite
11:47 -
3.9 FOREIGN KEY and referential integrity
06:52 -
3.10 Altering tables: ADD COLUMN, DROP COLUMN, ALTER COLUMN, RENAME
07:03 -
3.11 Dropping tables safely: DROP TABLE vs TRUNCATE
07:32 -
3.12 Module 3 Reference Guide (The Core Concept & Common Beginner Mistakes)
Module 4 — Aggregations & Grouping (4 Hours)
-
Introuduction
01:35 -
4.1 What are aggregate functions?
00:00 -
4.2 COUNT — counting rows and non-null values
09:00 -
4.3 SUM and AVERAGE
05:46 -
4.4 MIN and MAX
05:57 -
4.5 Rounding and formatting numbers
11:54 -
4.6 GROUP BY — grouping data for aggregation
15:32 -
4.7 Multiple columns in GROUP BY
06:29 -
4.8 HAVING — filtering aggregated results (vs WHERE)
11:23 -
4.9 DISTINCT inside aggregates: COUNT(DISTINCT column)
00:02 -
4.10 ROLLUP — subtotals and grand totals
07:09 -
4.11 CUBE — all combinations of groupings
08:16 -
4.12 GROUPING SETS — custom grouping combinations
09:08
Module 5 — Joining Tables (5 Hours)
-
Introuduction
03:29 -
The greencycle Database Schema
05:43 -
5.1 Why joins exist — the problem with flat files
10:21 -
5.2 Understanding primary keys and foreign keys in context
06:48 -
5.3 INNER JOIN — only matching rows from both tables
07:11 -
5.4 LEFT JOIN — all rows from left, matched from right (NULLs for no match)
07:19 -
5.5 RIGHT JOIN — the mirror of LEFT JOIN
04:46 -
5.6 FULL OUTER JOIN — all rows from both tables
04:35 -
5.7 CROSS JOIN — Cartesian product and practical use cases
05:25 -
5.8 Self-join — joining a table to itself (hierarchies, comparisons)
14:35 -
5.9 Joining three or more tables
09:30 -
5.10 Table aliases for cleaner queries
09:11 -
5.11 Common join mistakes: duplicates, fan-out, missing NULLs (Reading Article)
-
5.12 Using WHERE vs ON for filtering in joins
10:13 -
5.13 Hands-on: Build a complete order report joining 5 tables
07:08
Module 6 — Subqueries & CTEs (4 Hours)
-
Introuduction
03:14 -
6.1 What is a subquery?
08:05 -
6.2 Scalar subqueries — returning a single value
08:06 -
6.3 Subqueries in WHERE: filtering with a result set
13:52 -
6.4 IN and NOT IN with subqueries
15:08 -
6.5 EXISTS and NOT EXISTS — performance-friendly filtering
10:50 -
6.6 Correlated subqueries — referencing the outer query
07:44 -
6.7 Subqueries in FROM (derived tables)
07:59 -
6.8 Subqueries in SELECT
10:16 -
6.9 Introducing CTEs — WITH clause
17:50 -
6.10 Writing multiple CTEs in one query
19:28 -
6.11 When to use a CTE vs a subquery
05:02 -
6.12 Recursive CTEs — traversing hierarchies and sequences
06:50 -
6.13 Capstone Exercise
Module 7 — Window Functions (5 Hours)
-
Introuduction
03:30 -
7.1 What are window functions — how they differ from GROUP BY
10:40 -
7.2 The OVER() clause — the foundation of all window functions
14:04 -
7.3 PARTITION BY — creating groups without collapsing rows
14:20 -
7.4 ORDER BY inside OVER()
11:20 -
7.5 ROW_NUMBER — unique sequential numbering
12:19 -
7.6 RANK — ranking with gaps for ties
06:40 -
7.7 DENSE_RANK — ranking without gaps
09:06 -
7.8 NTILE — dividing rows into buckets (percentiles, quartiles)
11:37 -
7.9 LAG — accessing the previous row’s value
16:34 -
7.10 LEAD — accessing the next row’s value
14:30 -
7.11 FIRST_VALUE and LAST_VALUE
00:00 -
7.12 NTH_VALUE — value at a specific position
06:31 -
7.13 Aggregate window functions: running totals, moving averages
09:07 -
7.14 Frame clauses: ROWS BETWEEN and RANGE BETWEEN – Reading Article
-
7.15 Practical example: MoM revenue growth and 7-day rolling averages
Module 8 — Data Manipulation (DML) (3 Hours)
-
Introuduction
01:22 -
8.1 INSERT INTO — adding single and multiple rows
13:14 -
8.2 INSERT from a SELECT — copying data between tables
12:01 -
8.3 UPDATE — modifying existing rows
09:14 -
8.4 UPDATE with JOIN — updating based on another table
16:12 -
8.5 DELETE — removing rows
09:09 -
8.6 DELETE with subquery — conditional deletion
06:00 -
8.7 TRUNCATE — fast full-table deletion
17:09 -
8.8 UPSERT with ON CONFLICT — insert or update
14:45 -
8.9 Transactions: BEGIN, COMMIT, ROLLBACK
17:30 -
8.10 SAVEPOINT — partial rollback within a transaction
08:25 -
8.11 Understanding ACID properties in PostgreSQL – Reading Article
Module 9 — Schema Design & Normalization (4 Hours)
-
Introuduction
02:57 -
9.1 What is database normalization and why it matters
09:47 -
9.2 First Normal Form (1NF) — eliminating repeating groups
08:27 -
9.3 Second Normal Form (2NF) — eliminating partial dependencies
03:53 -
9.4 Third Normal Form (3NF) — eliminating transitive dependencies
05:40 -
9.5 Boyce-Codd Normal Form (BCNF)
15:40 -
9.6 When to denormalize — the trade-off
18:35 -
9.7 Entity-Relationship Diagrams (ERDs) — reading and drawing them
15:02 -
9.8 Designing a schema from a business requirement (walkthrough)
13:17 -
9.9 Surrogate keys vs natural keys
14:49 -
9.10 Sequences and SERIAL / BIGSERIAL / IDENTITY columns (Reading Article)
-
9.11 Using schemas to organize objects in PostgreSQL (Reading Article)
-
9.12 Naming conventions and best practices (Reading Article)
Module 10 — String, Date & Math Functions (4 Hours)
-
10.1 String functions: UPPER, LOWER, TRIM, LTRIM, RTRIM
10:40 -
10.2 SUBSTRING and LEFT / RIGHT
06:03 -
10.3 LENGTH and CHAR_LENGTH
04:24 -
10.4 CONCAT and the || operator
11:42 -
10.5 REPLACE and REGEXP_REPLACE
18:20 -
10.6 SPLIT_PART and STRING_TO_ARRAY
20:02 -
10.7 Date functions: NOW(), CURRENT_DATE, CURRENT_TIMESTAMP
07:08 -
10.8 EXTRACT — extracting year, month, day, hour from timestamps
07:30 -
10.9 DATE_TRUNC — truncating to week, month, quarter, year
08:17 -
10.10 AGE() — calculating intervals between dates
06:12 -
10.11 Date arithmetic: adding and subtracting intervals
07:54 -
10.12 ROUND, CEIL, FLOOR, ABS, MOD
04:15 -
10.13 CASE WHEN — conditional logic in SQL
00:00 -
10.14 COALESCE and NULLIF for NULL handling ( Reeding Article)
Module 11 — PostgreSQL-Specific Features (4 Hours) – Bonus(Data Engineer) reading article
-
11.1 JSONB — storing semi-structured data
-
11.2 Querying JSONB: ->, ->>, #>, @>, ?
-
11.3 Indexing JSONB columns with GIN indexes
-
11.4 Arrays — declaring, inserting, querying array columns
-
11.5 Array operators and functions: ANY, ALL, UNNEST
-
11.6 GENERATE_SERIES — generating date spines and number sequences
-
11.7 Full-text search: tsvector, tsquery, to_tsvector, plainto_tsquery
-
11.8 Full-text ranking with ts_rank
-
11.9 COPY command — bulk import from CSV and export to CSV
-
11.10 String formatting: FORMAT() function
-
11.11 Common Table Expressions with MATERIALIZED hint
-
11.12 LATERAL joins — powerful row-by-row subqueries
Module 12 — Indexes & Query Performance (4 Hours) – Bonus(Data Engineer) reading article
-
12.1 What is an index and how does it work?
-
12.2 B-tree indexes — the default and when to use them
-
12.3 Hash indexes
-
12.4 GIN indexes — for JSONB and full-text search
-
12.5 GiST indexes — for geometric and range types
-
12.6 Partial indexes — indexing only a subset of rows
-
12.7 Composite indexes — multi-column indexing
-
12.8 When indexes hurt performance (over-indexing)
-
12.9 EXPLAIN — reading the query execution plan
-
12.10 EXPLAIN ANALYZE — actual vs estimated row counts
-
12.11 Identifying sequential scans vs index scans
-
12.12 Cost-based optimization — how PostgreSQL decides
-
12.13 Common performance anti-patterns
-
12.14 VACUUM and ANALYZE — maintaining table statistics
-
12.15 pg_stat_statements — monitoring slow queries
Module 13 — Views, Functions & Stored Procedures (4 Hours) – Bonus(Data Engineer) reading article
-
13.1 CREATE VIEW — creating reusable query layers
-
13.2 Updating and dropping views
-
13.3 Materialized views — caching expensive query results
-
13.4 Refreshing materialized views (REFRESH MATERIALIZED VIEW)
-
13.5 Introduction to PL/pgSQL — PostgreSQL’s procedural language
-
13.6 Writing user-defined functions (UDFs)
-
13.7 Function parameters: IN, OUT, INOUT
-
13.8 Returning tables from functions (RETURNS TABLE)
-
13.9 Stored procedures with CREATE PROCEDURE
-
13.10 Variables, conditionals (IF/ELSIF/ELSE), and loops in PL/pgSQL
-
13.11 Exception handling in functions
-
13.12 Triggers — BEFORE and AFTER triggers
-
13.13 Trigger functions — auto-updating timestamps, audit logs
-
13.14 DROP and ALTER for functions and procedures
Module 14 — Security & Database Administration (3 Hours) – Bonus(Data Engineer) reading article
-
14.1 PostgreSQL roles and users — the difference
-
14.2 Creating roles and users
-
14.3 GRANT and REVOKE — controlling access to objects
-
14.4 Schema-level and table-level permissions
-
14.5 Row-Level Security (RLS) — restricting data by user
-
14.6 Column-level security
-
14.7 pg_hba.conf — authentication configuration
-
14.8 Backing up a database with pg_dump
-
14.9 Restoring from a backup with pg_restore
-
14.10 pg_dumpall — backing up the entire cluster
-
14.11 Introduction to replication concepts
-
14.12 Connection pooling with PgBouncer (overview)
Module 15 — Real-World Capstone Projects (5 Hours)
-
15.1 Project 1 — E-Commerce Analytics (1.5 hrs)
-
15.2 Project 2 — HR Workforce Dashboard (1 hr)
-
15.3 Project 3 — Financial Reporting (1.5 hrs)
-
15.4 Project 4 — Marketing Funnel Analysis (1 hr)
Student Ratings & Reviews
No Review Yet