PostgreSQL Essentials to Practitioner: Beginner-to-Intermediate SQL & Database Administration

Duration 3 days – 21 hours

 

Overview

 

This Beginner-to-Intermediate PostgreSQL Training Course is designed to build strong foundational skills in PostgreSQL while preparing participants to confidently work with real-world database tasks in modern environments.

 

Participants will learn how PostgreSQL works, how to write efficient SQL queries, how to design and manage database structures, and how to handle basic administration tasks such as user access, performance basics, backup/restore, and troubleshooting.

Since your organization is already exposed to PostgreSQL, this course also includes practical comparisons to other databases (MySQL, SQL Server, Oracle concepts) so learners understand transferable skills and can adapt faster as technology evolves.

 

Objectives

 

• Understand PostgreSQL architecture and core database concepts
• Create and manage tables, relationships, and constraints properly
• Write SQL queries using filtering, joins, grouping, and functions
• Use PostgreSQL features like views, indexes, sequences, and transactions
• Apply intermediate querying techniques (subqueries, CTEs, window functions intro)
• Perform basic administration tasks: users, roles, access control, schemas
• Execute simple performance tuning techniques using EXPLAIN and indexing basics
• Perform database backup and restore using PostgreSQL tools
• Understand how PostgreSQL compares with other DB platforms and where it fits best

 

Target Audience

 

• Junior Developers / Software Engineers
• QA / Test Engineers who query databases
• Business Analysts / Reporting Analysts
• IT Support / Application Support Engineers
• DevOps / Cloud Engineers (who handle database deployments)
• Junior DBAs / Database Administrators
• Anyone transitioning into database roles

 

Prerequisites

• Basic computer skills

• Basic understanding of data concepts (tables, rows, columns) is helpful

• No prior PostgreSQL experience required

• No programming background required (but helpful)

 

Course Content

Day 1 — PostgreSQL Fundamentals + Core SQL

 

Module 1: Database & PostgreSQL Fundamentals

• What is PostgreSQL and why it’s widely used
• RDBMS concepts: tables, rows, columns, keys
• PostgreSQL vs MySQL vs SQL Server (high-level comparison)
• PostgreSQL ecosystem tools overview
  • pgAdmin, psql, DBeaver (optional)

 

Module 2: PostgreSQL Setup & Navigation

• Installing PostgreSQL (local or remote setup overview)
• Connecting to database servers
• Understanding databases, schemas, and namespaces
• Using pgAdmin and psql basics

 

Module 3: SQL Basics (CRUD)

• SELECT, FROM, WHERE
• ORDER BY, LIMIT
• INSERT, UPDATE, DELETE
• Handling NULL values properly
• Common operators and expressions

 

Module 4: Filtering + Built-in Functions

• LIKE / ILIKE, IN, BETWEEN
• String functions, numeric functions
• Date/time functions (NOW, CURRENT_DATE, interval)
• Formatting output for reporting needs

Hands-on Lab: Query a sample dataset + mini reporting exercise

 

Day 2 — Data Modeling + Joins + Constraints

 

Module 5: Data Types & Table Design Best Practices

• Key PostgreSQL data types (text, numeric, boolean, timestamps, UUID)
• Choosing correct data types for performance & integrity
• Normalization basics: 1NF–3NF (practical version)
• Naming conventions and schema design standards

Module 6: Creating Objects (DDL Mastery)

• CREATE TABLE and ALTER TABLE
• PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT
• Identity/auto-increment (SERIAL vs GENERATED AS IDENTITY)
• Understanding referential integrity

 

Module 7: Joins & Relationships

• INNER JOIN, LEFT JOIN, RIGHT JOIN
• Join patterns (1-to-many, many-to-many)
• JOIN pitfalls and how to avoid duplicates
• When to use EXISTS vs JOIN

 

Module 8: Aggregation and Grouping

• COUNT, SUM, AVG, MIN/MAX
• GROUP BY + HAVING
• Aggregation with joins (real-world reporting scenario)

Hands-on Lab: Build a small relational schema + generate summary reports

 

Day 3 — Intermediate SQL + Performance + Basic Admin

 

Module 9: Intermediate Querying Techniques

• Subqueries (scalar, correlated subqueries)
• Common Table Expressions (CTEs)
• Intro to window functions (ROW_NUMBER, RANK, PARTITION BY)
• Views vs Materialized Views (concept + use case)

 

Module 10: Indexing & Query Performance Basics

• Why indexes matter
• Creating and using indexes (B-tree intro)
• EXPLAIN and EXPLAIN ANALYZE basics
• Common performance mistakes (SELECT *, missing indexes, wrong joins)

 

Module 11: Transactions & Concurrency Basics

• ACID fundamentals
• BEGIN / COMMIT / ROLLBACK
• Isolation levels overview (simple explanation)
• Locking behavior and common issues

Module 12: Basic PostgreSQL Administration

• Users, roles, privileges (GRANT/REVOKE)
• Database security basics (least privilege mindset)
• Backup and restore basics
  • pg_dump / pg_restore overview
• Monitoring overview (what to check when DB is slow)

 

Module 13: PostgreSQL for the Future (Skills Transferability)

• How PostgreSQL skills translate to other DBs
• When to use PostgreSQL vs NoSQL options
• Trends: cloud-managed databases, performance scaling, AI workloads

Final Lab: End-to-end exercise (design + query + optimize + backup simulation)