O'Reilly logo
live online training icon Live Online training

Getting Started with PostgreSQL

Managing and querying PostgreSQL databases

Ami Levin

PostgreSQL has been the leading enterprise open source relational database management system for several decades. The latest version 12 provides an even more reliable, feature-rich and easy-to-use platform to gain data insights effectively. This online training is intended for developers, data analysts, IT and data practitioners, or anyone who is starting his/her journey with PostgreSQL, and needs to be able to perform basic management tasks, and to write SQL queries.

This practical, hands-on training will get you up and running quickly, armed with the basic skillset to smoothly continue your journey to SQL and relational database mastery. You will learn what PostgreSQL is, how it works, and the great tools that it has to offer. You will learn how to create and manage databases, and how to use basic SQL commands to manipulate and query data.

What you'll learn-and how you can apply it

By the end of this live, hands-on, online course, you’ll understand:

  • PostgreSQL basic architecture and tooling
  • What’s new to PostgreSQL version 12
  • PostgreSQL query fundamentals

And you’ll be able to:

  • Set up and connect to local and cloud PostgreSQL environments
  • Basic management tasks such as creating and backing up databases
  • How to create and manage tables, insert and modify data, and query it efficiently

This training course is for you because...

  • You are a developer, data analyst, IT or data practitioner with little or no experience with PostgreSQL
  • You need to be able to use and query PostgreSQL databases

Prerequisites

  • Previous exposure to SQL useful but not required

Recommended preparation:

Recommended follow-up:

About your instructor

  • Ami Levin is a seasoned trainer, data architect, database designer, and SQL wizard with over 25 years of experience.

Schedule

The timeframes are only estimates and may vary according to how the class is progressing

PostgreSQL Databases and Objects (1 hour)

  • Lecture: PostgreSQL Architecture
  • Demonstration: Installation walkthrough; Connecting to local and cloud instances; Using PgAdmin and PSQL
  • Exercise: Connect to a shared cloud PostgreSQL and run a query
  • Lecture: Creating and Managing Databases
  • Exercise: Creating and configuring databases
  • Lecture: Accessing PostgreSQL Objects
  • Exercises:Creating users; Assigning permissions
  • Q&A
  • Break (5 Minutes)

Creating Tables (1 Hour)

  • Lecture: CREATE, ALTER, and DROP TABLE
  • Exercise: Create, modify, and delete tables
  • Lecture: Column and Table Constraints
  • Exercise: Create and modify constraints
  • Demonstration: Data Types
  • Exercise: Create and modify column types
  • Q&A
  • Break (5 Minutes)

Modifying and Retrieving Data (1 Hour)

  • Demonstration: Using INSERT, UPDATE and DELETE
  • Exercise: Using INSERT, UPDATE and DELETE
  • Lecture: Understanding SELECT and Query Processing Order
  • Lecture: SQL JOIN fundamentals
  • Demonstration: Basic Join techniques
  • Exercise: Joining 2 tables
  • Lecture: Filtering, Aggregating and Limiting Result Sets
  • Demonstration: Using WHERE, GROUP BY and OFFSET-FETCH
  • Exercise: Using WHERE and GROUP BY
  • Q&A (5 minutes)
  • Break (5 Minutes)

Using PostgreSQL Supporting Objects and Functions (1 Hour)

  • Lecture: PostgreSQL Indexes
  • Demonstration: Creating and using indexes
  • Exercise: Create an index
  • Lecture: Commonly used Operators and Functions
  • Demonstration: Using common operators and functions
  • Exercise: Write a query that use operators and functions
  • Lecture: Views, Stored Procedures, Triggers, and Functions
  • Demonstration: Creating and using programming objects
  • Exercise: Create a trigger
  • Q&A