course
PostgreSQL Querying: Advanced Techniques
Extend your SQL skills with advanced PostgreSQL techniques for real-world data problems.
Description
Go beyond SQL basics and learn advanced PostgreSQL querying patterns. You will compare multiple solution strategies and pick the best approach for each situation, applying table expressions, set operators, window functions, and JSON querying techniques along the way.
Learning Goals
 Construct views, derived tables, common table expressions, and set-returning functions to structure complex queries. | Apply  |
| Use UNION, EXCEPT, and INTERSECT to combine and compare result sets. | Apply |
| Implement window functions with OVER, PARTITION BY, and frame clauses for ranking, offset, and aggregate calculations. | Apply |
| Manipulate JSON, JSONB, and array data using PostgreSQL's native operators and functions. | Apply |
| Interpret PL/pgSQL blocks, variables, control flow, and function or procedure calls. | Understand  |
Prior Knowledge
See 'Prior Knowledge Courses' below.
Subjects
- Refresher
- Table Expressions
- Set Operators
- Window Ranking, Offset and Aggregate Functions
- JSON and Arrays
- Reading PL/pgSQL
Refresher
Refresher of the common pitfalls of basic SQL querying, ensuring everyone is on the same page before diving into advanced topics. Next to this, the module serves as an introduction to the database used in class.
- Standard elements of a SELECT query
- Joins
- Grouping and Aggregating
- Subqueries
Table Expressions
Table expressions are SQL elements that are not a physical table but can be used as if they were one.
- Views
- Set-returning Functions
- Derived Tables
- Common Table Expressions (CTE)
- LATERAL joins
- When to pick which option?
Set Operators
Set operators allow you to vertically combine and compare two similar result sets. This contrasts joins, where you combine data horizontally.
- UNION (ALL)
- EXCEPT / INTERSECT
- Recursive CTEs, time permitting
Window Ranking, Offset and Aggregate Functions
Window functions are ideal for analytics scenarios like running totals and moving averages. They also allow you to look at previous or following values in your result set.
- Window Functions
- OVER
- Frame
- PARTITION BY
- Conditional aggregation and the FILTER clause
- Pivoting and multi-level aggregation, time permitting
JSON and Arrays
PostgreSQL has rich native support for semi-structured data.
- JSON and JSONB operators
- Querying with jsonpath
- Building and aggregating JSON
- Shredding JSON into rows
- Building, unnesting, and aggregating arrays
Reading PL/pgSQL
You can use SQL for querying, but you will also encounter PL/pgSQL. What is this, why is this useful, what does the syntax look like, and how do functions and procedures fit in?
- DO blocks
- Variables
- Basic control flow
- Calling functions and procedures (
SELECTvsCALL) - Exception handling and transactions
Schedule
| Start date | Duration | Location | |
|---|---|---|---|
January 7, 2027January 8, 2027 | 2 days | Veenendaal / Remote This is a hybrid training and can be followed remotely. More information Veenendaal / Remote This is a hybrid training and can be followed remotely. More information | Sign up |
All courses can also be conducted within your organization as customized or incompany training.
Our training advisors are happy to help you provide personal advice or find Incompany training within your organization.
Prior knowledge courses
Follow-up courses
"Trainer who knows his profession!"Marc
Hoge waardering
Praktijkgerichte trainingen
Gecertificeerde trainers
Eigen docenten