Associate Data Analyst in SQL – Data Science Academy

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

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?

27:58
1.2 Relational vs non-relational databases — when to use each
1.3 Understanding tables, rows, columns, and relationships
1.4 What is SQL? History and the SQL standard
1.5 Installing PostgreSQL on Windows, Mac, and Linux
1.6 Setting up pgAdmin 4 — interface walkthrough
1.7 Using the Query Tool
1.8 Creating your first database and connecting to it
1.9 PostgreSQL architecture — databases, schemas, roles
1.10 Running your first SELECT query

Module 2 — SELECT & Filtering Data (4 Hours)

2.1 Anatomy of a SELECT statement
2.2 Selecting all columns vs specific columns
2.3 Column aliases with AS
2.4 Filtering with WHERE — comparison operators (=, !=, >, =, <=)
2.5 Combining conditions: AND, OR, NOT
2.6 BETWEEN for range filtering
2.7 IN and NOT IN for list matching
2.8 LIKE and ILIKE for pattern matching (%, _ wildcards)
2.9 IS NULL and IS NOT NULL
2.10 Removing duplicates with DISTINCT
2.11 Sorting results with ORDER BY (ASC, DESC, NULLS LAST)
2.12 Limiting results with LIMIT and OFFSET (pagination)
2.13 Module 2 Reference Guide (The Core Concept & Common Beginner Mistakes)

Module 3 — Data Types & Table Design (3 Hours)

Introuduction
3.1 Numeric types: INTEGER, BIGINT, NUMERIC, DECIMAL, FLOAT
3.2 Text types: VARCHAR, TEXT, CHAR — differences and when to use each
3.3 Date and time types: DATE, TIME, TIMESTAMP, TIMESTAMPTZ
3.4 Boolean type
3.5 UUID type and when to use it
3.6 Creating tables with CREATE TABLE
3.7 Column constraints: NOT NULL, UNIQUE, DEFAULT, CHECK
3.8 PRIMARY KEY — single and composite
3.9 FOREIGN KEY and referential integrity
3.10 Altering tables: ADD COLUMN, DROP COLUMN, ALTER COLUMN, RENAME
3.11 Dropping tables safely: DROP TABLE vs TRUNCATE
3.12 Module 3 Reference Guide (The Core Concept & Common Beginner Mistakes)

Module 4 — Aggregations & Grouping (4 Hours)

Introuduction
4.1 What are aggregate functions?
4.2 COUNT — counting rows and non-null values
4.3 SUM and AVERAGE
4.4 MIN and MAX
4.5 Rounding and formatting numbers
4.6 GROUP BY — grouping data for aggregation
4.7 Multiple columns in GROUP BY
4.8 HAVING — filtering aggregated results (vs WHERE)
4.9 DISTINCT inside aggregates: COUNT(DISTINCT column)
4.10 ROLLUP — subtotals and grand totals
4.11 CUBE — all combinations of groupings
4.12 GROUPING SETS — custom grouping combinations

Module 5 — Joining Tables (5 Hours)

Introuduction
The greencycle Database Schema
5.1 Why joins exist — the problem with flat files
5.2 Understanding primary keys and foreign keys in context
5.3 INNER JOIN — only matching rows from both tables
5.4 LEFT JOIN — all rows from left, matched from right (NULLs for no match)
5.5 RIGHT JOIN — the mirror of LEFT JOIN
5.6 FULL OUTER JOIN — all rows from both tables
5.7 CROSS JOIN — Cartesian product and practical use cases
5.8 Self-join — joining a table to itself (hierarchies, comparisons)
5.9 Joining three or more tables
5.10 Table aliases for cleaner queries
5.11 Common join mistakes: duplicates, fan-out, missing NULLs (Reading Article)
5.12 Using WHERE vs ON for filtering in joins
5.13 Hands-on: Build a complete order report joining 5 tables

Module 6 — Subqueries & CTEs (4 Hours)

Introuduction
6.1 What is a subquery?
6.2 Scalar subqueries — returning a single value
6.3 Subqueries in WHERE: filtering with a result set
6.4 IN and NOT IN with subqueries
6.5 EXISTS and NOT EXISTS — performance-friendly filtering
6.6 Correlated subqueries — referencing the outer query
6.7 Subqueries in FROM (derived tables)
6.8 Subqueries in SELECT
6.9 Introducing CTEs — WITH clause
6.10 Writing multiple CTEs in one query
6.11 When to use a CTE vs a subquery
6.12 Recursive CTEs — traversing hierarchies and sequences
6.13 Practical example: org chart traversal with recursive CTE

Module 7 — Window Functions (5 Hours)

7.1 What are window functions — how they differ from GROUP BY
7.2 The OVER() clause — the foundation of all window functions
7.3 PARTITION BY — creating groups without collapsing rows
7.4 ORDER BY inside OVER()
7.5 ROW_NUMBER — unique sequential numbering
7.6 RANK — ranking with gaps for ties
7.7 DENSE_RANK — ranking without gaps
7.8 NTILE — dividing rows into buckets (percentiles, quartiles)
7.9 LAG — accessing the previous row’s value
7.10 LEAD — accessing the next row’s value
7.11 FIRST_VALUE and LAST_VALUE
7.12 NTH_VALUE — value at a specific position
7.13 Aggregate window functions: running totals, moving averages
7.14 Frame clauses: ROWS BETWEEN and RANGE BETWEEN
7.15 Practical example: MoM revenue growth and 7-day rolling averages

Module 8 — Data Manipulation (DML) (3 Hours)

8.1 INSERT INTO — adding single and multiple rows
8.2 INSERT from a SELECT — copying data between tables
8.3 UPDATE — modifying existing rows
8.4 UPDATE with JOIN — updating based on another table
8.5 DELETE — removing rows
8.6 DELETE with subquery — conditional deletion
8.7 TRUNCATE — fast full-table deletion
8.8 UPSERT with ON CONFLICT — insert or update
8.9 Transactions: BEGIN, COMMIT, ROLLBACK
8.10 SAVEPOINT — partial rollback within a transaction
8.11 Understanding ACID properties in PostgreSQL

Module 9 — Schema Design & Normalization (4 Hours)

9.1 What is database normalization and why it matters
9.2 First Normal Form (1NF) — eliminating repeating groups
9.3 Second Normal Form (2NF) — eliminating partial dependencies
9.4 Third Normal Form (3NF) — eliminating transitive dependencies
9.5 Boyce-Codd Normal Form (BCNF)
9.6 When to denormalize — the trade-off
9.7 Entity-Relationship Diagrams (ERDs) — reading and drawing them
9.8 Designing a schema from a business requirement (walkthrough)
9.9 Surrogate keys vs natural keys
9.10 Sequences and SERIAL / BIGSERIAL / IDENTITY columns
9.11 Using schemas to organize objects in PostgreSQL
9.12 Naming conventions and best practices

Module 10 — String, Date & Math Functions (3 Hours)

10.1 String functions: UPPER, LOWER, TRIM, LTRIM, RTRIM
10.2 SUBSTRING and LEFT / RIGHT
10.3 LENGTH and CHAR_LENGTH
10.4 CONCAT and the || operator
10.5 REPLACE and REGEXP_REPLACE
10.6 SPLIT_PART and STRING_TO_ARRAY
10.7 Date functions: NOW(), CURRENT_DATE, CURRENT_TIMESTAMP
10.8 EXTRACT — extracting year, month, day, hour from timestamps
10.9 DATE_TRUNC — truncating to week, month, quarter, year
10.10 AGE() — calculating intervals between dates
10.11 Date arithmetic: adding and subtracting intervals
10.12 ROUND, CEIL, FLOOR, ABS, MOD
10.13 CASE WHEN — conditional logic in SQL
10.14 COALESCE and NULLIF for NULL handling

Module 11 — PostgreSQL-Specific Features (4 Hours)

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)

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)

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)

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)

Student Ratings & Reviews

No Review Yet

About Course

About Course

Course Overview

Course Objectives

Next Steps After This Course

What Will You Learn?

Course Content

Module 1 — Database Foundations & PostgreSQL Setup (3 Hours)

1.1 What is a database? Why not just use Excel or Google Sheets?

1.2 Relational vs non-relational databases — when to use each

1.3 Understanding tables, rows, columns, and relationships

1.4 What is SQL? History and the SQL standard

1.5 Installing PostgreSQL on Windows, Mac, and Linux

1.6 Setting up pgAdmin 4 — interface walkthrough

1.7 Using the Query Tool

1.8 Creating your first database and connecting to it

1.9 PostgreSQL architecture — databases, schemas, roles

1.10 Running your first SELECT query

Module 2 — SELECT & Filtering Data (4 Hours)

2.1 Anatomy of a SELECT statement

2.2 Selecting all columns vs specific columns

2.3 Column aliases with AS

2.4 Filtering with WHERE — comparison operators (=, !=, >, =, <=)

2.5 Combining conditions: AND, OR, NOT

2.6 BETWEEN for range filtering

2.7 IN and NOT IN for list matching

2.8 LIKE and ILIKE for pattern matching (%, _ wildcards)

2.9 IS NULL and IS NOT NULL

2.10 Removing duplicates with DISTINCT

2.11 Sorting results with ORDER BY (ASC, DESC, NULLS LAST)

2.12 Limiting results with LIMIT and OFFSET (pagination)

2.13 Module 2 Reference Guide (The Core Concept & Common Beginner Mistakes)

Module 3 — Data Types & Table Design (3 Hours)

Introuduction

3.1 Numeric types: INTEGER, BIGINT, NUMERIC, DECIMAL, FLOAT

3.2 Text types: VARCHAR, TEXT, CHAR — differences and when to use each

3.3 Date and time types: DATE, TIME, TIMESTAMP, TIMESTAMPTZ

3.4 Boolean type

3.5 UUID type and when to use it

3.6 Creating tables with CREATE TABLE

3.7 Column constraints: NOT NULL, UNIQUE, DEFAULT, CHECK

3.8 PRIMARY KEY — single and composite

3.9 FOREIGN KEY and referential integrity

3.10 Altering tables: ADD COLUMN, DROP COLUMN, ALTER COLUMN, RENAME

3.11 Dropping tables safely: DROP TABLE vs TRUNCATE

3.12 Module 3 Reference Guide (The Core Concept & Common Beginner Mistakes)

Module 4 — Aggregations & Grouping (4 Hours)

Introuduction

4.1 What are aggregate functions?

4.2 COUNT — counting rows and non-null values

4.3 SUM and AVERAGE

4.4 MIN and MAX

4.5 Rounding and formatting numbers

4.6 GROUP BY — grouping data for aggregation

4.7 Multiple columns in GROUP BY

4.8 HAVING — filtering aggregated results (vs WHERE)

4.9 DISTINCT inside aggregates: COUNT(DISTINCT column)

4.10 ROLLUP — subtotals and grand totals

4.11 CUBE — all combinations of groupings

4.12 GROUPING SETS — custom grouping combinations

Module 5 — Joining Tables (5 Hours)

Introuduction

The greencycle Database Schema

5.1 Why joins exist — the problem with flat files

5.2 Understanding primary keys and foreign keys in context

5.3 INNER JOIN — only matching rows from both tables

5.4 LEFT JOIN — all rows from left, matched from right (NULLs for no match)

5.5 RIGHT JOIN — the mirror of LEFT JOIN

5.6 FULL OUTER JOIN — all rows from both tables

5.7 CROSS JOIN — Cartesian product and practical use cases

5.8 Self-join — joining a table to itself (hierarchies, comparisons)

5.9 Joining three or more tables

5.10 Table aliases for cleaner queries

5.11 Common join mistakes: duplicates, fan-out, missing NULLs (Reading Article)

5.12 Using WHERE vs ON for filtering in joins

5.13 Hands-on: Build a complete order report joining 5 tables

Module 6 — Subqueries & CTEs (4 Hours)

Introuduction

6.1 What is a subquery?

6.2 Scalar subqueries — returning a single value