SQL for PostgreSQL

Course Description

This two-day SQL for PostgreSQL course provides a practical, hands-on introduction to querying and managing data in a PostgreSQL database. Designed for beginners and those with limited SQL experience, the course focuses on building real-world skills that can be applied immediately in data analysis, reporting and application development. Participants will learn how to retrieve, filter, sort and summarise data using SQL, before progressing to more advanced topics such as joining multiple tables, using subqueries and common table expressions (CTEs) and modifying data safely with transactions. Throughout the course, learners will work with a PostgreSQL database to reinforce concepts through guided exercises and practical challenges. The course also introduces PostgreSQL-specific features, data types and best practices, helping participants write clear, efficient and maintainable SQL queries. By the end of the course, participants will be able to confidently interact with PostgreSQL databases, understand relational data structures and produce meaningful insights from data.
2 Days
€795.00
 

Course Objectives

By the end of this course, participants will be able to:

Write SQL queries to retrieve, filter and summarise data
Use joins and subqueries effectively
Modify data safely using INSERT, UPDATE and DELETE
Understand PostgreSQL-specific features and best practices
Build readable, efficient and maintainable SQL queries

Who Should Attend

Beginners with little or no SQL experience
Analysts, developers, administrators and technical staff working with PostgreSQL
Professionals who need to query databases for reporting or decision-making

Introduction to Databases & PostgreSQL

What is a relational database?
Tables, rows, columns, primary keys
What makes PostgreSQL different?
Connecting to PostgreSQL (psql / pgAdmin / IDEs)
Understanding schemas and databases

Basic SELECT Queries

The SELECT statement
Selecting specific columns vs *
Using AS for column aliases
Sorting results with ORDER BY
Limiting results with LIMIT and OFFSET

Filtering Data

WHERE clause fundamentals
Comparison operators (=, <>, <, >, BETWEEN)
Logical operators (AND, OR, NOT)
Pattern matching with LIKE and ILIKE
Checking for NULL values

Aggregation & Grouping

Aggregate functions: COUNT, SUM, AVG, MIN, MAX
Grouping data with GROUP BY
Filtering grouped data with HAVING
Common aggregation mistakes

PostgreSQL Data Types & Functions

Common PostgreSQL data types
Numeric, text, Boolean
Date & time types
Basic built-in functions
String functions
Date/time functions
Type casting (CAST, ::)

Joins, Subqueries & Data Modification

Why joins are needed
INNER JOIN
LEFT JOIN and RIGHT JOIN
Join conditions and aliases
Common join pitfalls (duplicate rows)

Subqueries & Common Table Expressions (CTEs)

Subqueries in SELECT, WHERE, and FROM
Correlated vs non-correlated subqueries
Introduction to WITH (CTEs)
When to use CTEs vs subqueries

Modifying Data Safely

INSERT statements
UPDATE with WHERE
DELETE vs TRUNCATE
Using transactions (BEGIN, COMMIT, ROLLBACK)
PostgreSQL RETURNING clause

Constraints & Data Integrity

Primary keys and foreign keys
UNIQUE, NOT NULL, CHECK
Understanding referential integrity
What happens on constraint violations

Performance Basics & Best Practices

Writing readable SQL
Index basics (what they are, when to use them)
Avoiding common performance traps
Using EXPLAIN (intro only)

PostgreSQL-Specific Features (Optional / Time-Permitting)

DISTINCT ON
JSON / JSONB basics
UPSERT with ON CONFLICT
Case-sensitive vs case-insensitive behaviour
SQLPostgreSQL