SQL Language for Analysts in PostgreSQL Training
Level
AdvancedDuration
24h / 3 daysDate
Individually arrangedPrice
Individually arrangedSQL Language for Analysts in PostgreSQL Training
The SQL Language for Analysts in PostgreSQL – advanced topics training is an intensive 2-3 day workshop aimed at data analysts and specialists who want to deepen their skills in working with data in a PostgreSQL environment. During the training you will learn how to build complex SQL queries, optimize their performance, use analytical functions, process data in JSON formats, and also manage more complicated database structures like partitioned tables or materialized views. This training is an excellent step for those who want to efficiently exploit PostgreSQL’s capabilities for data analysis and business reporting.
What will you learn?
- How to create and optimize advanced SQL queries in a PostgreSQL environment.
- How to use analytical and aggregate functions, complex expressions, and subqueries.
- Effective handling of large and complex datasets, including JSON data and partitioned tables.
- How to analyze query execution plans and apply best practices in optimization.
- Understanding of transaction mechanisms and data security in PostgreSQL.
Who is this training for?
Data analysts and BI developers working with PostgreSQL who want to develop the ability to write advanced SQL queries.-
SQL programmers and database administrators seeking knowledge of optimization and advanced techniques for working with large datasets.
-
IT specialists responsible for preparing data for analytics and reporting.
-
People using PostgreSQL in their daily work and wanting to process complex data effectively.
Training Program
-
Day 1: Advanced Queries and Analytical Functions
-
Module 1: Advanced Joins and Data Operations
- One-to-one, one-to-many, many-to-many joins, and non-equijoin joins
- Logical operators and advanced conditional expressions (CASE, COALESCE, NULLIF)
- Correlated and uncorrelated subqueries in SELECT, FROM, and WHERE clauses
- Working with CTEs (Common Table Expressions)
-
Module 2: Aggregate and Analytical Functions
- Grouping functions (SUM, AVG, COUNT, MAX, MIN)
- Advanced grouping (ROLLUP, CUBE, GROUPING SETS)
- Window functions and analytical windows (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, FIRST_VALUE, LAST_VALUE)
- Grouping strings with STRING_AGG and using regular expressions
- Hierarchical data processing using recursive CTEs
-
Day 2: Data Processing and Handling Distributed Structures
-
Module 3: Working with JSON Data and Data Modeling
- Handling JSON and JSONB in PostgreSQL – functions and operators
- Schema creation and relationships in the database
- Temporary tables, materialized views, and table partitioning
-
Module 4: Data Manipulation and Transaction Management
- Efficient INSERT, UPDATE, DELETE, and MERGE operations
- Transactional behavior, locking, and isolation levels in PostgreSQL
- Error handling and nested transactions
- Data archiving strategies and removing old records
-
Day 3: Query Optimization and Advanced PostgreSQL Features
-
Module 5: SQL Query Optimization and Execution Plan Analysis
- Interpreting EXPLAIN and EXPLAIN ANALYZE outputs
- Indexes: types, usage, functional, unique, and partial indexes
- Performance impact of indexes and server parameter tuning
-
Module 6: Advanced PostgreSQL Strengths
- Autovacuum – configuration, monitoring, and optimization
- Working with information schemas and advanced database management
- Advanced PL/pgSQL features (optional)
