PostgreSQL architecture (20 minutes)

• Lecture: PostgreSQL overview; system databases; the fundamental database file architecture; how to connect to a cloud PostgreSQL IaaS and PaaS
• Demo: Installation walkthrough; connecting to local and cloud instances; using PgAdmin and PSQL
• Hands-on exercises: Connect to a shared cloud PostgreSQL database and run a query

Creating and managing databases (15 minutes)

• Lecture: How to create a new database; commonly used settings; database options; how to configure existing databases; how to perform basic management tasks such as backing up and exporting data
• Demo: Creating and configuring databases
• Hands-on exercises: Create and configure databases

Accessing PostgreSQL objects (20 minutes)

• Lecture: The object hierarchy; how to access objects using fully qualified identifiers; how to use DCL, users, and roles to assign basic permissions
• Demo: Creating users; assigning permissions
• Hands-on exercises: Create users and assign permissions
• Q&A
• Break (5 minutes)

CREATE, ALTER, and DROP TABLE (15 minutes)

• Lecture: Creating tables using SQL DDL statements; modifying existing tables and reclaiming space for unused data
• Demo: Creating, modifying, and deleting tables
• Hands-on exercises: Create, modify, and delete tables

Column and table constraints (20 minutes)

• Lecture: Relational keys; how to enforce data consistency
• Demo: Creating and modifying keys and constraints
• Hands-on exercises: Create and modify constraints

Data types (20 minutes)

• Lecture: PostgreSQL data type families and their proper usage; how to avoid common mistakes regarding data type selection
• Demo: Data types
• Hands-on exercises: Create and modify column types
• Q&A
• Break (5 minutes)

Using INSERT, UPDATE, and DELETE (15 minutes)

• Lecture: Writing basic DML statements; PostgreSQL DML extensions
• Demo: Using INSERT, UPDATE and DELETE
• Hands-on exercises: Use INSERT, UPDATE and DELETE

Understanding SELECT and query processing order (10 minutes)

• Lecture: How the SQL engine processes SELECT queries; writing efficient SQL queries; how to avoid common errors

SQL JOIN fundamentals (10 minutes)

• Lecture: The various JOIN types and how they’re processed by PostgreSQL; CROSS, INNER, and OUTER; how to enforce JOIN order
• Demo: Basic JOIN techniques
• Hands-on exercises: Join two tables

Filtering, aggregating, and limiting result sets (20 minutes)

• Lecture: How to write queries using WHERE, GROUP BY, HAVING, TOP, ORDER BY, and OFFSET-FETCH; how these clauses are processed; how to avoid common mistakes
• Demo: Using WHERE, GROUP BY, and OFFSET-FETCH
• Hands-on exercises: Use WHERE and GROUP BY
• Q&A
• Break (5 minutes)

PostgreSQL indexes (15 minutes)

• Lecture: PostgreSQL index types; guidelines for efficient indexes; how to create indexes as part of table definition and for existing tables
• Demo: Creating and using indexes
• Hands-on exercises: Create an index

Commonly used operators and functions (20 minutes)

• Lecture: How to use PostgreSQL’s most commonly used built-in operators and functions; writing queries using logical operators, numeric, string, and temporal functions
• Demo: Using common operators and functions
• Hands-on exercises: Write a query that use operators and functions

Views, stored procedures, triggers, and functions (20 minutes)

• Lecture: The benefits and use cases for server-side programming objects; creating views, stored procedures, triggers, and both scalar and table valued functions
• Demo: Creating and using programming objects
• Hands-on exercises: Create a trigger

Wrap-up and Q&A (5 minutes)