training
PostgreSQL Querying: Advanced Techniques
Breid je SQL-vaardigheden uit met geavanceerde PostgreSQL-technieken voor praktische datavraagstukken.
Beschrijving
Verdiep je SQL-kennis en leer complexere querypatronen in PostgreSQL die in de praktijk worden gebruikt. Je vergelijkt meerdere oplossingsstrategieën en kiest per situatie de beste aanpak. Je leert table expressions, set operators, window functions en JSON-querytechnieken toe te passen.
Leerdoelen
 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  |
Benodigde voorkennis
Zie 'Voorkennis trainingen' hieronder.
Onderwerpen
- 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
Planning
| Startdatum | Duur | Locatie | |
|---|---|---|---|
7 januari 20278 januari 2027 | 2 dagen | Veenendaal / Remote Dit is een hybride training die remote gevolgd kan worden. Meer informatie Veenendaal / Remote Dit is een hybride training die remote gevolgd kan worden. Meer informatie | Inschrijven |
Incompany of persoonlijk advies nodig?
Onze opleidingsadviseurs denken graag met je mee om een persoonlijk advies te geven of een incompany training binnen jouw organisatie te vinden.
Voorkennis trainingen
Vervolgtrainingen
"Ontzettend goede leraar."Sander
Hoge waardering
Praktijkgerichte trainingen
Gecertificeerde trainers
Eigen docenten