PostgreSQL – handling complex databases and ORD (object–relational database) management

Training process

Training needs analysis

If you have specific requirements regarding the training programme, we will carry out a training needs analysis for you. This will guide us on which aspects of the programme should receive greater emphasis, so that the training programme meets your specific needs.

What will you gain?

Confident PostgreSQL work - You will learn how to connect to a PostgreSQL server, navigate a database, and work in PG Admin, so you can start handling existing structures and queries with far less hesitation.

Reliable SELECT queries - You will master SELECT statements with aliases, filtering, sorting, LIMIT, and DISTINCT, which will help you build clear, practical queries for everyday data analysis tasks.

More precise filtering - You will practice operators such as IN, BETWEEN, LIKE, and conditions with AND, OR, and NOT, so you can select records accurately and avoid mistakes with NULL-related logic.

Combining data from tables - You will understand table relationships and learn INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and CROSS JOIN, enabling you to merge data from different tables into one result.

Practical SQL functions - You will use string, math, date, and time functions as well as type casting and conversion, so you can reshape raw data efficiently for reports, checks, and further processing.

Building summaries and analysis - You will learn GROUP BY with COUNT, SUM, AVG, MIN, and MAX, which will let you prepare your own summaries, grouped reports, and business-focused aggregations from raw datasets.

Editing data and objects - You will practice INSERT, UPDATE, DELETE, and core DDL for TABLE and VIEW objects, so you can not only read data but also maintain and extend a database in a controlled way.

More advanced query patterns - You will work with subqueries, UNION, INTERSECT, and window functions like OVER and ROW_NUMBER, helping you write more advanced SQL for realistic reporting and analysis needs.

Training programme

1. Introduction to relational databases

concepts related to SQL databases using PostgreSQL as an example,
division of the SQL language into DQL, DML, DDL, DCL,
connecting to the SQL server and the existing database.

2. Detailed overview of available software for managing relational databases

SQL code editor – PG Admin in creating queries,
server objects: tables, views and stored procedures.

3. Introduction to the DQL (Data Query Language) language and its key functions

Query designer in applications and creating a query to the database,
basic data types in SQL and their application.

4. Discussion of various operators used in SQL queries

operators in SQL =, <>, >=, <=, IN, BETWEEN AND, LIKE,
criteria in queries and combining multiple criteria using AND, OR, NOT,
the concept and application of NULL and NOT NULL.

5. Detailed discussion of table join operations in SQL using various types of joins

the concept of relationships between database tables and the element connecting the tables,
inner join INNER JOIN, RIGHT OUTER JOIN and LEFT OUTER JOIN,
full join FULL OUTER JOIN and cross join CROSS JOIN.

6. Overview of built-in functions in SQL that allow performing advanced operations on data

operations on character strings LEN, LEFT, RIGHT, MID, REPLACE, TRIM, SUBSTRING, UPPER, LOWER and concatenation of text strings CONCAT,
mathematical functions ROUND, time and date DATE, DATEADD DATEDIFF,
conversion and type casting: CAST(), CONVERT().

7. In-depth discussion of the SELECT statement, which is the foundation of the SQL language

column alias AS, operations on a column and sorting the result ORDER BY,
limiting the number of records LIMIT and removing duplicates DISTINCT.

8. Introduction to aggregate queries

aggregation of results in queries using GROUP BY,
functions COUNT, MIN, MAX, SUM, AVG and criteria in aggregated results.

9. Overview of methods for combining results of different SQL queries

combining tables using: UNION, UNION ALL,
common parts and INTERSECT, MINUS.

10. Discussion of nested queries

query result as a WHERE condition in a query,
a query based on another query and query optimization.

11. Introduction to the Data Modification Language (DML)

updating data using UPDATE,
adding records to a table using the INSERT command,
deleting data using the DELETE command,
outputting the query result to a table: SELECT INTO.

12. Discussion of the Data Definition Language (DDL) (Data Definition Language)

construction of database objects TABLE, VIEW using PG Admin and SQL code.

13. Overview of advanced functions available in PostgreSQL

window functions OVER() and numbering records using ROW_NUMBER(),
numbering and partitioning records using PARTITION BY.

What are the prerequisites for participating in the training?

Basic computer use - You should be comfortable using an operating system, launching applications, working with a keyboard, and moving around program windows so you can focus on learning SQL.

File handling basics - You should know how to save and open files and understand basic folder and document concepts, because this will help you work smoothly with database tools during training.

Logical thinking - You should feel comfortable with simple reasoning and organizing information, because filtering data, applying conditions, and joining tables all require careful thinking.

Readiness to work with data - You should be ready to work with tables and with text, numeric, and date values, because the course focuses on practical operations performed directly on data.