PostgreSQL Tutorial

Learn PostgreSQL Step by Step

Build a strong database foundation with practical lessons covering installation, SQL, tables, filtering, aggregation, joins, and everyday PostgreSQL workflows.

Learning path: Start with the introduction, install PostgreSQL and pgAdmin, then practise each SQL topic using real database examples.

PostgreSQL Course Topics

Introduction to PostgreSQL

Understand what PostgreSQL is, where it is used, and why developers choose it for reliable applications. Learn the core ideas of servers, databases, schemas, tables, roles, transactions, and data integrity before writing SQL.

→ 02

Install PostgreSQL

Set up PostgreSQL on Windows using the official installer. Follow the complete process for selecting components, setting the database password, confirming the port, finishing installation, and checking that the server is ready.

→ 03

Use pgAdmin 4

Learn how to open pgAdmin, connect to a PostgreSQL server, browse databases and schemas, use the Query Tool, execute SQL, and inspect results. This provides a practical interface for managing your local database.

→ 04

Create Tables

Design tables with meaningful columns and data types. Practise primary keys, constraints, defaults, and table inspection in pgAdmin while creating a realistic products table that can support later CRUD examples.

→ 05

Insert Data

Learn how to add one or many rows with INSERT statements. Work with text, numbers, dates, defaults, and generated identifiers while understanding how constraints protect the quality of stored data.

→ 06

Fetch Data with SELECT

Read data from tables with SELECT, choose specific columns, use aliases, sort results, and combine expressions. These are the everyday query skills needed to inspect and retrieve application data.

→ 07

SELECT Queries

Retrieve the columns and rows an application needs with clear SELECT statements. Learn explicit column selection, expressions, aliases, and readable query structure.

→ 08

SELECT DISTINCT

Remove duplicate result rows with SELECT DISTINCT. Understand how PostgreSQL compares selected columns and when distinct results are useful.

→ 07

Update and Delete Data

Modify existing rows safely with UPDATE and remove records with DELETE. Learn why WHERE clauses matter, how to preview affected rows, and how transactions can reduce the risk of accidental changes.

→ 08

Alter Tables and Columns

Change an existing database design using ALTER TABLE. Add or remove columns, rename fields, adjust data types, and understand the impact of schema changes on existing data and application code.

→ 09

Operators and Conditions

Use comparison, logical, arithmetic, and pattern-matching operators to express useful conditions. Build queries that select the right rows and combine multiple rules in a readable way.

→ 10

Filtering with WHERE

Filter query results with precise conditions using WHERE. Combine filters with AND, OR, and NOT, handle NULL values correctly, and create focused queries that return only the data an application needs.

→ 11

Grouping and Aggregation

Summarize data with COUNT, SUM, AVG, MIN, and MAX. Group rows with GROUP BY and use HAVING to filter aggregate results for reports, dashboards, and business queries.

→ 12

SQL Joins

Combine related data from multiple tables using INNER, LEFT, RIGHT, FULL, and CROSS JOIN. Learn how relationships affect results and how to choose the join type that matches the business requirement.

→ 13

UNION and Set Operations

Combine compatible result sets with UNION and UNION ALL, and compare sets with related operations. Learn the column and data-type rules that make set-based queries predictable.

→ + 14

BETWEEN

Filter numbers and dates within an inclusive range. Learn how boundary values behave and how BETWEEN can make range conditions easier to read.

→ 15

IN

Match a value against a list or subquery result. Use IN for readable filters and understand how NULL values affect comparisons.

→ 16

LIKE and ILIKE

Search text with wildcard patterns using LIKE and PostgreSQL’s case-insensitive ILIKE. Build practical searches for names, codes, and user-entered values.

→ 17

LIMIT and OFFSET

Restrict result sizes and paginate query results with LIMIT and OFFSET. Combine them with ORDER BY for predictable pages.

→ 18

ORDER BY

Sort query results by one or more columns in ascending or descending order. Handle NULL ordering when result presentation matters.

→ 19

HAVING

Filter grouped results after PostgreSQL calculates aggregates. Compare WHERE and HAVING and apply both in report-style queries.

→ 20

COUNT

Count rows and non-null values with COUNT. Combine COUNT with GROUP BY to produce useful totals by category, year, or status.

→ 21

SUM

Calculate totals for numeric columns such as price or quantity. Learn how NULL values behave and how COALESCE can provide a zero result.

→ 22

AVG

Calculate averages for numeric data and group averages by another column. Understand NULL handling and rounding for reports.

→ 23

MIN and MAX

Find the smallest and largest values in a column. Use these aggregate functions across complete tables or grouped results.

→ 24

INNER JOIN

Return only rows that have matching records in both tables. Practise joining products and categories through related keys.

→ 25

LEFT JOIN

Keep every row from the left table and include matching rows from the right. Identify missing relationships with NULL values.

→ 26

RIGHT JOIN

Keep every row from the right table and match rows from the left. Understand when reversing table order with LEFT JOIN is clearer.

→ 27

FULL JOIN

Return matched and unmatched rows from both tables. Use FULL JOIN to compare datasets and identify records missing on either side.

→ 28

CROSS JOIN

Generate every possible combination between two tables. Learn why result size grows quickly and when a Cartesian product is useful.

→ 29

ALL

Compare a value with every result returned by a subquery. Understand the difference between ALL, ANY, and ordinary comparisons.

→ 30

ANY

Compare a value with at least one value returned by a subquery. Use ANY or SOME when a match against a subquery is enough.

→ 31

EXISTS

Check whether a related subquery returns at least one row. Use EXISTS for efficient relationship and presence checks.

→ 32

CASE

Return different values based on conditions. Use CASE to classify records, create readable labels, and build conditional projections.

→ 33

AS and Aliases

Give columns and tables readable aliases. Use aliases to simplify long queries and make result sets easier to understand.

→ 34

Add Columns

Extend an existing table with a new column, default, or constraint. Consider existing rows and application compatibility before changing a schema.

→ 37

Delete Data

Remove records with DELETE while protecting unrelated rows. Learn how constraints, transactions, and careful filtering prevent accidental data loss.

→ 38

Drop Columns

Remove obsolete columns from a table after checking dependencies, backups, and application code that may still use the field.

→ 39

Drop Tables

Remove an entire table only when it is no longer needed. Understand the destructive impact and use backups and migrations in real projects.

→