SQL Fundamentals – Complete Course

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

  1. Foundation (Chapters 1-4): Build core SQL skills – 12-20 hours
  2. Advanced Querying (Chapters 5-8): Master complex queries – 16-23 hours
  3. Specialized Topics (Chapters 9-10): Advanced data types and transactions – 8-11 hours
  4. 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

Scroll to Top