SQL Fundamentals – Complete PostgreSQL Training Course

Duration: 40-60 hours | Level: Beginner to Intermediate | 12 Comprehensive Chapters

Course Overview

The SQL Fundamentals module is a comprehensive 12-chapter PostgreSQL training curriculum designed to take learners from SQL basics to advanced querying techniques. This course builds a strong foundation in Structured Query Language and relational database concepts using PostgreSQL as the teaching platform.

Target Audience: Beginners to intermediate database professionals
Prerequisites: Basic computer literacy; no prior SQL experience required
Delivery Format: Hands-on with theory and practical exercises

Complete Course Curriculum

Chapter 01: Introduction to SQL and Relational Databases

Duration: 3-5 hours

What You’ll Learn:

SQL basics and declarative programming nature
Relational database fundamentals (tables, rows, columns)
Primary keys, foreign keys, and database relationships
PostgreSQL architecture and ACID compliance
SQL command categories (DDL, DML, DQL, DCL, TCL)
Basic SELECT syntax and queries
PostgreSQL-specific features (RETURNING, arrays, JSON)
SQL syntax rules and best practices

Key Topics:

Declarative vs imperative languages
One-to-many, many-to-many, one-to-one relationships
Database schemas and namespaces
ACID properties (Atomicity, Consistency, Isolation, Durability)
Common beginner mistakes and how to avoid them

Chapter 02: Data Manipulation Language (DML)

Duration: 3-5 hours

What You’ll Learn:

INSERT operations (single row, multiple rows, from SELECT)
UPDATE statements (basic, multiple columns, with calculations)
DELETE operations (basic, with conditions, using subqueries)
TRUNCATE vs DELETE comparison
UPSERT operations using ON CONFLICT clause
COPY operations for bulk data loading/export
Transaction basics (BEGIN, COMMIT, ROLLBACK)
Best practices for data modifications

Key Topics:

DML statement execution
RETURNING clause for immediate results
Transaction atomicity
ON CONFLICT for duplicate handling
Bulk operations for performance optimization

Chapter 03: Advanced Filtering & Sorting

Duration: 3-5 hours

What You’ll Learn:

WHERE clause mastery (comparison operators, BETWEEN, IN)
Pattern matching (LIKE, ILIKE, SIMILAR TO)
Regular expressions (POSIX operators)
NULL handling (IS NULL, IS NOT NULL)
Combining conditions (AND, OR, NOT)
DISTINCT for removing duplicates
ORDER BY (ascending, descending, multiple columns, expressions)
NULL handling in sorting (NULLS FIRST/LAST)
LIMIT and OFFSET for pagination
FETCH FIRST (SQL standard alternative)

Key Topics:

Comparison and logical operators
Pattern matching with wildcards
Regular expressions in queries
NULL semantics in SQL
Pagination strategies and performance

Chapter 04: Aggregations & Grouping

Duration: 3-5 hours

What You’ll Learn:

Aggregate functions (COUNT, SUM, AVG, MIN, MAX)
GROUP BY fundamentals and multiple column grouping
HAVING clause (filtering after aggregation)
Advanced aggregates (STRING_AGG, ARRAY_AGG, BOOL_AND/OR)
Statistical functions (STDDEV, VARIANCE, PERCENTILE)
ROLLUP, CUBE, and GROUPING SETS
GROUPING function
FILTER clause for conditional aggregation
Window functions in aggregation context

Key Topics:

Aggregate computation semantics
GROUP BY rules and constraints
WHERE vs HAVING distinction
Advanced grouping operations
Performance optimization for aggregations

Chapter 05: JOINs Mastery

Duration: 4-6 hours

What You’ll Learn:

INNER JOIN fundamentals
LEFT JOIN (LEFT OUTER JOIN)
RIGHT JOIN (RIGHT OUTER JOIN)
FULL JOIN (FULL OUTER JOIN)
CROSS JOIN and Cartesian products
SELF JOIN patterns
Multiple table joins
Complex join conditions (multiple conditions, OR conditions)
JOIN with aggregates
USING vs ON syntax
LATERAL JOIN (advanced)
Semi-join and anti-join patterns (EXISTS, NOT EXISTS)
JOIN performance optimization

Key Topics:

Join mechanics and matching
NULL behavior in joins
Performance implications
Join vs subquery alternatives
Cartesian product prevention

Chapter 06: Subqueries Deep Dive

Duration: 4-5 hours

What You’ll Learn:

Scalar subqueries (single value)
Row subqueries (single row, multiple columns)
Table subqueries (multiple rows)
Subqueries in SELECT, FROM, WHERE, HAVING clauses
Correlated vs non-correlated subqueries
IN/NOT IN operators
EXISTS/NOT EXISTS operators
ANY/SOME and ALL operators
Common patterns (finding duplicates, top-N queries, running totals)
Subquery performance issues and optimization
Lateral subqueries

Key Topics:

Subquery execution and scoping
Correlated subquery performance
NULL handling in subqueries
Optimization strategies
When to use subqueries vs JOINs

Chapter 07: Common Table Expressions (CTEs)

Duration: 4-6 hours

What You’ll Learn:

Basic CTE syntax (WITH clause)
Multiple CTEs in one query
CTE vs subquery comparison
Recursive CTEs fundamentals
Base case and recursive case patterns
Employee hierarchy traversal
Category tree structures
Factorial calculations
Graph traversal and pathfinding
Bill of Materials queries
CTE materialization (PostgreSQL 12+)
Performance considerations

Key Topics:

CTE definition and scoping
Recursive query mechanics
Termination conditions
Graph algorithms in SQL
Named windows and code reusability

Chapter 08: Window Functions Mastery

Duration: 5-6 hours

What You’ll Learn:

Window function basics and syntax
PARTITION BY and ORDER BY clauses
Ranking functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE)
Navigation functions (LAG, LEAD, FIRST_VALUE, LAST_VALUE)
Aggregate window functions
Running totals and cumulative calculations
Moving averages and rolling sums
Frame specifications (ROWS vs RANGE)
Top-N per group patterns
Year-over-year growth calculations
Cumulative distribution functions
Gap and islands detection
Named windows for code reuse

Key Topics:

Window semantics and execution
Frame boundaries and inclusivity
Ranking vs numbering
Performance characteristics
Window vs aggregate function differences

Chapter 09: Advanced Data Types & Operations

Duration: 5-6 hours

What You’ll Learn:

JSON and JSONB (binary vs text)
Accessing and modifying JSON data
JSON operators and functions
JSONB indexing with GIN
Array types and operations
Array functions (unnest, array_agg, any, all)
Date and time types (DATE, TIME, TIMESTAMP, TIMESTAMPTZ)
Date arithmetic and functions
Interval types and calculations
Time zones handling
Range types (DATERANGE, TSRANGE, NUMRANGE)
UUID types
ENUM custom types
Composite types
Network address types (INET, CIDR)

Key Topics:

Semi-structured vs structured data
Type system extensibility
Specialized data handling
Performance with advanced types
Indexing strategies for complex types

Chapter 10: Transactions & Concurrency Control

Duration: 4-5 hours

What You’ll Learn:

Transaction basics (BEGIN, COMMIT, ROLLBACK)
ACID properties in depth
Isolation levels (Read Committed, Repeatable Read, Serializable)
Transaction phenomena (dirty reads, non-repeatable reads, phantom reads)
Explicit table locks
Row-level locks (FOR UPDATE, FOR SHARE, NOWAIT, SKIP LOCKED)
Advisory locks
Savepoints
Deadlock prevention and handling
Transaction timeouts
Practical examples (bank transfers, inventory reservation)
Monitoring transactions and locks
Serialization failure handling

Key Topics:

Concurrency control mechanisms
Lock modes and compatibility
Deadlock detection
Consistency guarantees
Performance vs isolation trade-offs

Chapter 11: Views & Materialized Views

Duration: 3-5 hours

What You’ll Learn:

Regular views (virtual tables)
Creating views with joins and aggregations
Modifying view definitions
Updatable views
View update rules and WITH CHECK OPTION
Materialized views (pre-computed results)
Refreshing materialized views
Concurrent refresh
View benefits (simplification, security, consistency)
View performance considerations
Cascading views
Indexing materialized views
Scheduling refreshes

Key Topics:

Virtual vs physical storage
View optimization
Query rewriting
Materialization strategies
Security through views

Chapter 12: SQL Best Practices & Optimization

Duration: 4-6 hours

What You’ll Learn:

Query optimization principles
Column selection best practices (avoiding SELECT *)
WHERE vs HAVING usage
Function impacts on indexes
Subquery optimization
Indexing strategies (foreign keys, frequent filters, composite indexes)
Partial indexes
Over-indexing and index monitoring
Transaction length management
Appropriate isolation level selection
Query writing with CTEs
Avoiding correlated subqueries
UNION ALL vs UNION
Batch operations
SQL injection prevention
Parameterized queries
Principle of least privilege
Row-level security
Code formatting and style
EXPLAIN ANALYZE usage
Slow query monitoring
Anti-patterns to avoid (EAV, delimited lists, FLOAT for money)

Key Topics:

Query optimization principles
Index design strategies
Transaction management
Security best practices
Code quality and maintainability
Performance monitoring

Learning Outcomes

Upon completion of SQL Fundamentals, you will be able to:

✅ Write efficient SELECT queries with complex filtering, sorting, and aggregation
✅ Manipulate data using INSERT, UPDATE, DELETE, and UPSERT operations
✅ Join multiple tables and combine data using subqueries and CTEs
✅ Perform advanced analytics with window functions and recursive queries
✅ Work with PostgreSQL’s advanced data types (JSON, arrays, ranges)
✅ Implement transactions with appropriate isolation levels
✅ Design and optimize database queries
✅ Apply security best practices in SQL
✅ Use views for data abstraction and security
✅ Monitor and optimize database performance

Prerequisites

Basic computer literacy
Understanding of databases (conceptual level)
Familiarity with command-line tools (helpful but not required)
No prior SQL experience required

Course Format

Each chapter includes:

Theory: Comprehensive notes with code examples
Practical Examples: Real-world scenarios and use cases
Best Practices: Tips, warnings, and anti-patterns highlighted
Lab Exercises: Hands-on practice exercises
Solutions: Detailed solutions with explanations
Assessments: Quizzes and hands-on projects

Recommended Learning Path

Foundation (Chapters 1-4): Build core SQL skills – 12-20 hours
Advanced Querying (Chapters 5-8): Master complex queries – 16-23 hours
Specialized Topics (Chapters 9-10): Advanced data types and transactions – 8-11 hours
Professional Skills (Chapters 11-12): Views and best practices – 7-11 hours

Total Duration: 40-60 hours (self-paced)

Get Started

Ready to master SQL? This course takes you from absolute beginner to confident SQL developer. Each chapter builds on the previous one, ensuring a solid foundation while progressively introducing advanced concepts.

Start with Chapter 01 and work your way through systematically for the best learning experience!

Course Version: 1.0 | Platform: PostgreSQL 12+ | Updated: February 2026