Advanced SQL with Postgres

This hands-on course develops advanced SQL and relational database programming skills using PostgreSQL. After a brief recap of SQL fundamentals, participants explore advanced query techniques, including joins, aggregates, common table expressions, recursive CTEs, and window functions, before working with JSON, spatial data, views, materialized views, stored procedures, triggers, data import and export, transaction isolation levels, user management, and database access control.

The course combines theory and practice through guided examples and hands-on exercises using PostgreSQL and pgAdmin. Although PostgreSQL is used for instruction and practice, many of the concepts are applicable to other relational database systems such as MySQL, Oracle, and SQL Server. The course is intended for advanced SQL users, developers, and analysts who want to deepen their ability to query, manage, and program relational databases.

• Quick recap of SQL concepts
• Advanced query concepts: joins, CTEs, recursive CTEs, window functions, and aggregates
• Advanced data types: JSON and spatial data
• Views and materialized views
• Stored procedures: PL/pgSQL and PL/Python
• Triggers
• Data import and export
• Database isolation levels

• Creating users and managing access control

At the end of the training, learners will be able to...

• construct advanced SQL queries with joins, CTEs, recursive CTEs, window functions, and aggregates
• apply PostgreSQL data types such as JSON and spatial data
• implement database programmability with stored procedures and triggers
• configure database access control for PostgreSQL users

• explain transaction isolation levels in relational databases

The course uses Lecture, Real-world scenarios / examples, and Practical exercises, combining theoretical instruction with guided demonstrations and hands-on database exercises.