Mastering PostgreSQL Databases: From Basics to Advanced

Module 1: Introduction to PostgreSQL

⏳ 45 minutes

• Topics: PostgreSQL overview, installation, connecting via psql, database setup

• Hands-on: Install PostgreSQL, create and connect to your first database

Module 2: Data Types & Table Creation

⏳ 1 hour

• Topics: Numeric, text, boolean, date/time types; constraints; primary/foreign keys

• Hands-on: Create tables with constraints and define column types appropriately

Module 3: Basic SQL Queries

⏳ 1.5 hours

• Topics: SELECT, WHERE, ORDER BY, LIMIT, OFFSET

• Hands-on: Retrieve and filter data with multiple criteria

Module 4: Joins & Subqueries

⏳ 2 hours

• Topics: INNER, LEFT, RIGHT, FULL joins; correlated and nested subqueries

• Hands-on: Combine tables and write subqueries for complex filters

Module 5: Aggregation & Grouping

⏳ 1.5 hours

• Topics: GROUP BY, HAVING, aggregate functions (SUM, AVG, COUNT)

• Hands-on: Generate reports from grouped data using conditions

Module 6: Views, Indexing & Performance

⏳ 2 hours

• Topics: Creating views, types of indexes, EXPLAIN, performance tuning

• Hands-on: Create views and analyze queries using index strategies

Module 7: Stored Procedures & Triggers

⏳ 1.5 hours

• Topics: Writing functions, stored procedures, defining and firing triggers

• Hands-on: Automate business logic using PL/pgSQL procedures

Module 8: Access Control & Roles

⏳ 1 hour

• Topics: Creating users, assigning roles, GRANT/REVOKE permissions

• Hands-on: Set up role-based access controls in a shared database

Module 9: Real-World Case Studies

⏳ 2 hours

• Topics: E-commerce schema design, normalization, query optimization

• Hands-on: Build a sample e-commerce database with real-world constraints

Get certificate