Learn with Yasir

Share Your Feedback

PostgreSQL Complete Course Outline


PostgreSQL Complete Course Outline

Beginner to Advanced Student Learning Modules

The following structure organizes the PostgreSQL lectures into progressive modules. Students begin with database fundamentals, learn to create and manage databases, practise SQL queries, and gradually move towards advanced PostgreSQL features.

The sequence is designed for beginners, with practical examples and exercises in each module.

Part 1 – Understand PostgreSQL

Module 1: Introduction to Databases and PostgreSQL

Lesson Lesson Title
1.1 Introduction to Databases
1.2 What Is a Database?
1.3 What Is a Database Management System (DBMS)?
1.4 Introduction to Relational Databases
1.5 Introduction to PostgreSQL
1.6 PostgreSQL Features and Advantages
1.7 Common Database Tasks in PostgreSQL
1.8 Introduction to pgAdmin and PostgreSQL Tools
1.9 Understanding Tables, Rows, Columns and Records
1.10 Introduction to the dvdrental Sample Database

Module 2: Database Management

Lesson Lesson Title
2.1 Creating a Database
2.2 Viewing Available Databases
2.3 Connecting to a Database
2.4 Renaming a Database
2.5 Deleting a Database
2.6 Creating and Managing Schemas
2.7 Understanding the public Schema
2.8 Viewing Database Objects

Part 2 – Working with Tables and Data

Module 3: Tables and Data Types

Lesson Lesson Title
3.1 Introduction to Tables
3.2 Creating Tables
3.3 Understanding Columns and Rows
3.4 PostgreSQL Data Types
3.5 Integer and Numeric Data Types
3.6 Character and Text Data Types
3.7 Boolean Data Type
3.8 Date and Time Data Types
3.9 Creating Tables with Different Data Types
3.10 Viewing Table Structure

Module 4: Table Constraints and Keys

Lesson Lesson Title
4.1 Introduction to Constraints
4.2 NOT NULL Constraint
4.3 UNIQUE Constraint
4.4 PRIMARY KEY Constraint
4.5 FOREIGN KEY Constraint
4.6 CHECK Constraint
4.7 DEFAULT Constraint
4.8 Understanding Composite Primary Keys
4.9 Understanding Relationships Between Tables
4.10 Practical Examples of Table Constraints

Module 5: Inserting and Managing Records

Lesson Lesson Title
5.1 Introduction to INSERT Statement
5.2 Inserting a Single Record
5.3 Inserting Multiple Records
5.4 Inserting Data into Specific Columns
5.5 Understanding NULL Values
5.6 Introduction to UPDATE Statement
5.7 Updating a Single Record
5.8 Updating Multiple Records
5.9 Introduction to DELETE Statement
5.10 Deleting Specific Records
5.11 Deleting All Records from a Table
5.12 Difference Between DELETE, TRUNCATE and DROP

Part 3 – Retrieving and Filtering Data

Module 6: Basic SQL Queries

Lesson Lesson Title
6.1 Introduction to SELECT Statement
6.2 Selecting All Columns
6.3 Selecting Specific Columns
6.4 Using Column Aliases
6.5 Using DISTINCT
6.6 Introduction to WHERE Clause
6.7 Comparison Operators
6.8 Logical Operators (AND, OR, NOT)
6.9 BETWEEN Operator
6.10 IN Operator
6.11 LIKE and ILIKE Operators
6.12 IS NULL and IS NOT NULL
6.13 Combining Multiple Conditions

Module 7: Sorting and Limiting Records

Lesson Lesson Title
7.1 Introduction to ORDER BY
7.2 Sorting in Ascending Order
7.3 Sorting in Descending Order
7.4 Sorting by Multiple Columns
7.5 Introduction to LIMIT
7.6 Introduction to OFFSET
7.7 Pagination Using LIMIT and OFFSET
7.8 Practical Sorting and Filtering Exercises

Part 4 – Data Analysis with SQL

Module 8: Aggregate Functions

Lesson Lesson Title
8.1 Introduction to Aggregate Functions
8.2 COUNT() Function
8.3 SUM() Function
8.4 AVG() Function
8.5 MIN() Function
8.6 MAX() Function
8.7 Using Multiple Aggregate Functions
8.8 Practical Aggregate Function Exercises

Module 9: Grouping and Filtering Groups

Lesson Lesson Title
9.1 Introduction to GROUP BY
9.2 Grouping Records by One Column
9.3 Grouping Records by Multiple Columns
9.4 Using Aggregate Functions with GROUP BY
9.5 Introduction to HAVING
9.6 Difference Between WHERE and HAVING
9.7 Practical Grouping and Reporting Exercises

Module 10: Joining Multiple Tables

Lesson Lesson Title
10.1 Introduction to SQL JOINs
10.2 Understanding Relationships Between Tables
10.3 INNER JOIN
10.4 LEFT JOIN
10.5 RIGHT JOIN
10.6 FULL OUTER JOIN
10.7 CROSS JOIN
10.8 SELF JOIN
10.9 Joining Three or More Tables
10.10 JOINs with WHERE and ORDER BY
10.11 Practical JOIN Exercises

Part 5 – Intermediate SQL

Module 11: Subqueries and Set Operations

Lesson Lesson Title
11.1 Introduction to Subqueries
11.2 Single-Row Subqueries
11.3 Multiple-Row Subqueries
11.4 Subqueries with WHERE
11.5 Subqueries with FROM
11.6 Subqueries with SELECT
11.7 Introduction to UNION
11.8 UNION vs UNION ALL
11.9 INTERSECT
11.10 EXCEPT
11.11 Practical Subquery and Set Operation Exercises

Module 12: Conditional Expressions

Lesson Lesson Title
12.1 Introduction to Conditional Expressions
12.2 CASE Expression
12.3 Simple CASE Expression
12.4 Searched CASE Expression
12.5 CASE with Aggregate Functions
12.6 COALESCE Function
12.7 NULLIF Function
12.8 Practical Conditional Query Exercises

Module 13: Common Table Expressions (CTEs)

Lesson Lesson Title
13.1 Introduction to CTEs
13.2 Creating a CTE Using WITH
13.3 Using CTEs in SELECT Queries
13.4 Multiple CTEs
13.5 CTEs with JOINs
13.6 CTEs with Aggregate Functions
13.7 Introduction to Recursive CTEs
13.8 Practical CTE Exercises

Module 14: Window Functions

Lesson Lesson Title
14.1 Introduction to Window Functions
14.2 Understanding OVER()
14.3 PARTITION BY
14.4 ORDER BY in Window Functions
14.5 ROW_NUMBER()
14.6 RANK() and DENSE_RANK()
14.7 LAG() and LEAD()
14.8 Aggregate Window Functions
14.9 Practical Window Function Exercises

Part 6 – PostgreSQL Functions and Advanced Data Types

Module 15: String Functions

Lesson Lesson Title
15.1 Introduction to String Functions
15.2 LENGTH()
15.3 UPPER() and LOWER()
15.4 CONCAT()
15.5 SUBSTRING()
15.6 TRIM() and REPLACE()
15.7 POSITION()
15.8 Practical String Function Exercises

Module 16: Date and Time Functions

Lesson Lesson Title
16.1 Introduction to Date and Time in PostgreSQL
16.2 CURRENT_DATE and CURRENT_TIME
16.3 NOW() and CURRENT_TIMESTAMP
16.4 EXTRACT()
16.5 DATE_PART()
16.6 AGE()
16.7 Date Arithmetic and Intervals
16.8 Formatting Dates Using TO_CHAR()
16.9 Practical Date and Time Exercises

Module 17: Mathematical Functions

Lesson Lesson Title
17.1 Introduction to Mathematical Functions
17.2 ABS()
17.3 ROUND()
17.4 CEIL() and FLOOR()
17.5 POWER() and SQRT()
17.6 MOD()
17.7 RANDOM()
17.8 Practical Mathematical Function Exercises

Module 18: JSON and JSONB

Lesson Lesson Title
18.1 Introduction to JSON
18.2 Understanding JSON and JSONB
18.3 Creating JSON Columns
18.4 Inserting JSON Data
18.5 Retrieving JSON Values
18.6 JSON Operators
18.7 JSON Functions
18.8 Updating JSON Data
18.9 Practical JSON and JSONB Exercises

Part 7 – Database Objects and Programming

Module 19: Views and Materialized Views

Lesson Lesson Title
19.1 Introduction to Views
19.2 Creating Views
19.3 Retrieving Data from Views
19.4 Updating Data Through Views
19.5 Replacing Views
19.6 Renaming and Dropping Views
19.7 Introduction to Materialized Views
19.8 Creating and Refreshing Materialized Views
19.9 Views vs Materialized Views
19.10 Practical View Exercises

Module 20: Indexes and Query Performance

Lesson Lesson Title
20.1 Introduction to Indexes
20.2 How Indexes Work
20.3 Creating and Dropping Indexes
20.4 Unique Indexes
20.5 Composite Indexes
20.6 Partial Indexes
20.7 Expression Indexes
20.8 GIN, GiST and BRIN Indexes
20.9 Covering Indexes
20.10 Understanding EXPLAIN
20.11 Understanding EXPLAIN ANALYZE
20.12 Index Maintenance and REINDEX
20.13 Practical Query Performance Exercises

Module 21: Functions and Stored Procedures

Lesson Lesson Title
21.1 Introduction to PostgreSQL Functions
21.2 Creating a Simple Function
21.3 Function Parameters
21.4 Returning Values from Functions
21.5 Functions Returning Tables
21.6 Introduction to PL/pgSQL
21.7 Variables and Statements in PL/pgSQL
21.8 IF and ELSE Conditions
21.9 Loops in PL/pgSQL
21.10 Introduction to Stored Procedures
21.11 Creating and Calling Procedures
21.12 Functions vs Stored Procedures

Module 22: Triggers

Lesson Lesson Title
22.1 Introduction to Triggers
22.2 Understanding Trigger Events
22.3 BEFORE and AFTER Triggers
22.4 Creating Trigger Functions
22.5 Creating Triggers
22.6 INSERT Triggers
22.7 UPDATE Triggers
22.8 DELETE Triggers
22.9 Audit Log Using Triggers
22.10 Managing and Removing Triggers

Module 23: Transactions

Lesson Lesson Title
23.1 Introduction to Transactions
23.2 Understanding ACID Properties
23.3 BEGIN Statement
23.4 COMMIT Statement
23.5 ROLLBACK Statement
23.6 SAVEPOINT
23.7 Transaction Isolation Levels
23.8 Practical Transaction Exercises

Part 8 – Database Administration

Module 24: Sequences and Identity Columns

Lesson Lesson Title
24.1 Introduction to Sequences
24.2 Creating Sequences
24.3 Using NEXTVAL() and CURRVAL()
24.4 SERIAL Data Type
24.5 GENERATED AS IDENTITY
24.6 SERIAL vs IDENTITY
24.7 Managing Sequences

Module 25: Roles, Users and Permissions

Lesson Lesson Title
25.1 Introduction to PostgreSQL Security
25.2 Understanding Roles and Users
25.3 Creating Users and Roles
25.4 Altering and Deleting Roles
25.5 GRANT Statement
25.6 REVOKE Statement
25.7 Database Permissions
25.8 Schema and Table Permissions
25.9 Role Membership
25.10 Practical User Management Exercises

Module 26: Importing and Exporting Data

Lesson Lesson Title
26.1 Introduction to Data Import and Export
26.2 Importing CSV Files
26.3 Exporting Data to CSV
26.4 Using COPY Command
26.5 Using pgAdmin Import/Export
26.6 Exporting Query Results
26.7 Practical Data Import and Export Exercises

Module 27: Backup and Restore

Lesson Lesson Title
27.1 Introduction to Database Backup
27.2 Backup Using pgAdmin
27.3 Backup Using pg_dump
27.4 Restoring Using pg_restore
27.5 Restoring SQL Backup Files
27.6 Understanding Custom and Plain Formats
27.7 Database Backup and Recovery Practice

Module 28: PostgreSQL System and Information Functions

Lesson Lesson Title
28.1 Introduction to System Information
28.2 Current Database and User Information
28.3 Viewing Database Objects
28.4 Using Information Schema
28.5 Introduction to PostgreSQL System Catalogs
28.6 Viewing Table and Column Information
28.7 Monitoring Database Activity

Part 9 – Practical Projects

Module 29: SQL Practice with the DVD Rental Database

Lesson Lesson Title
29.1 Understanding the DVD Rental Database
29.2 Exploring Tables in the Public Schema
29.3 Basic SELECT Query Practice
29.4 Filtering and Sorting Exercises
29.5 Aggregate and Grouping Exercises
29.6 JOIN-Based Exercises
29.7 Subquery-Based Exercises
29.8 Views and Reporting Exercises
29.9 Advanced SQL Challenge

Module 30: Database Design and Mini Projects

Lesson Lesson Title
30.1 Introduction to Database Design
30.2 Understanding Entity Relationship Diagrams (ERD)
30.3 Identifying Entities and Attributes
30.4 One-to-One Relationships
30.5 One-to-Many Relationships
30.6 Many-to-Many Relationships
30.7 Introduction to Database Normalization
30.8 First Normal Form (1NF)
30.9 Second Normal Form (2NF)
30.10 Third Normal Form (3NF)
30.11 Designing a Student Management Database
30.12 Creating Tables and Relationships
30.13 Inserting and Managing Student Records
30.14 Generating Student Reports
30.15 Final Database Project

Recommended teaching progression

For beginners, I recommend dividing the course into three learning stages:

Stage 1

Beginner – Understand and Use SQL

Modules 1–10

Students learn database fundamentals, tables, data types, constraints, CRUD operations, filtering, sorting, grouping and JOINs.

Stage 2

Intermediate – Analyse and Work with Data

Modules 11–18

Students learn subqueries, CTEs, window functions, conditional expressions and PostgreSQL functions.

Stage 3

Advanced – Develop and Manage Databases

Modules 19–30

Students learn views, indexes, programming, triggers, transactions, administration, database design and practical projects.

Recommended lesson format

Use the same teaching structure for every lesson to make the course consistent.

  1. Learning Objectives – What students will learn.
  2. Introduction – Explain the concept in simple language.
  3. Real-Life Example – Connect the concept with everyday situations.
  4. Syntax – Explain the SQL command.
  5. Understand the Code – Explain the example step by step.
  6. Practical Examples – Demonstrate multiple examples.
  7. Modify the Code – Ask students to change the example.
  8. Practice Exercises – Give students tasks to solve independently.
  9. Review Questions – Check understanding.
  10. Quick Revision – Summarize the main points.

My recommendation for your course website

Organize the website using this hierarchy:

PostgreSQL Course
│
├── Module 01 - Introduction to PostgreSQL
│   ├── Lesson 01 - Introduction to Databases
│   ├── Lesson 02 - What Is PostgreSQL?
│   └── Lesson 03 - Common Database Tasks
│
├── Module 02 - Database Management
│   ├── Lesson 01 - Creating a Database
│   ├── Lesson 02 - Connecting to a Database
│   └── Lesson 03 - Renaming a Database
│
├── Module 03 - Tables and Data Types
│   ├── Lesson 01 - Introduction to Tables
│   ├── Lesson 02 - Creating Tables
│   └── Lesson 03 - PostgreSQL Data Types
│
└── Continue with remaining modules...

Important: The 30 modules above are a comprehensive course outline, not a requirement to teach every lesson in the same semester. For a first beginner-level course, prioritize Modules 1–10 and selected lessons from Modules 11–14. Introduce advanced administration and programming topics in a subsequent course.

This structure also gives you a clear distinction between beginner SQL, intermediate SQL, advanced PostgreSQL and database administration.