SQL Language in PostgreSQL Database Training
Level
IntermediateDuration
24h / 3 daysDate
Individually arrangedPrice
Individually arrangedSQL Language in PostgreSQL Database Training
The SQL Language in PostgreSQL Database training is a practical course that comprehensively introduces the world of relational databases using PostgreSQL. Participants will learn both the fundamentals of SQL and advanced query techniques, data manipulation, aggregation, analytical functions, and query optimization. The course combines theory with numerous practical exercises, enabling fast acquisition of skills needed for data analysis and working with production-database environments.
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.
- Fundamentals of PL/pgSQL programming and data security management.
Who is this training for?
Data analysts and BI developers working with PostgreSQL who want to develop the ability to write advanced SQL queries.-
Developers and database administrators who want hands-on SQL skills on PostgreSQL.
-
IT specialists responsible for preparing data for analytics and reporting.
-
Individuals with basic programming knowledge and familiarity with tabular data, looking for a solid foundation for further learning PostgreSQL and its advanced features.
Training Program
Day 1: SQL Basics and Working with Data
-
Module 1: Introduction to PostgreSQL and SQL Basics
- Installation and configuration of PostgreSQL environment.
- Basic SELECT queries — filtering, sorting, aliases.
- Data types, operators, built-in functions.
- The role of SQL in data management, characteristics of PostgreSQL.
- Working in pgAdmin.
-
Module 2: Data Manipulation and Basic Structure Creation
- DML operations: INSERT, UPDATE, DELETE; handling NULL values.
- Creating and modifying tables: CREATE, ALTER, DROP.
- Integrity constraints and primary keys.
- PostgreSQL data types (e.g., text, numeric, date/time).
Day 2: Advanced Queries and Aggregations
-
Module 3: Advanced SELECT Queries
- SELECT filtering (WHERE), sorting (ORDER BY), pagination (LIMIT, OFFSET).
- JOINs — internal, external, cross joins, non-standard join methods.
- Subqueries — correlated and uncorrelated; using them in different parts of queries.
- Set operators and aggregate functions (GROUP BY, HAVING).
-
Module 4: Analytical and Window Functions
- Window functions: ROW_NUMBER, RANK, LEAD, LAG and their applications.
- Extended aggregations: ROLLUP, CUBE, GROUPING SETS.
- Working with text and date/time functions.
Day 3: Optimization and Advanced Techniques
-
Module 5: Query Optimization and Indexes
- Introduction to the Query Planner and optimizer.
- Analyzing query plans (EXPLAIN, EXPLAIN ANALYZE).
- Creating and choosing indexes — B-tree, hash, GiST, GIN.
- Optimization techniques and transaction management.
-
Module 6: Advanced Objects and PostgreSQL Programming
- Views, materialized views, and partitioned tables.
- Introduction to PL/pgSQL — functions, procedures, and triggers.
- Basics of permission/privilege management and data security
