CYBERTEC Logo

Introduction to PostgreSQL

LEVEL: Expert
This workshop has been designed for people who want to get familiar with SQL and PostgreSQL. You will learn how to use PostgreSQL and how to write proper SQL statements. We start with basic topics like normalization, foreign keys and work our way step by step to topics like joins, subselects, analytics and co. The aim is to teach the participants SQL needed for working with PostgreSQL using practical examples and thus give them the tools for their daily work.
Book Now

Course Topics

Installing PostgreSQL

  • Connection to the database
  • Usage of pgAdmin4 and similar tools

Relational data models

  • Normalization
  • Creating tables and using data types
  • Primary Keys and Foreign Keys
  • Check constraints

Basic SQL commands

  • INSERT: Insert data
  • SELECT: Simple queries
  • UPDATE: Modify data
  • DELETE and SELECT: Delete data
  • Filter data
  • NULL Handling
  • Sort data with ORDER BY
  • LIKE queries
  • UNION ALL, UNION, EXCEPT
  • DISTINCT and DISTINCT ON
  • LIMIT and OFFSET

Aggregate functions

  • Aggregate data (count, sum, etc.)
  • Minimum and maximum values
  • Group data (GROUP BY … HAVING …)
  • Use ordered sets (WITHIN GROUP …)

Subqueries and CTEs

  • Nested queries
  • Common Table Expressions (CTEs)
  • Use IN and NOT IN
  • EXISTS and NOT EXISTS

Joins

  • Understand inner joins
  • Use outer joins
  • Semi joins, anti joins

Date

  • Calculating with date and time
  • Understanding intervals
  • Include range types

Windowing and Analytics

  • OVER-clauses
  • Partitioning
  • Define sliding windows
  • Important analytical functions (rank, lag, etc.)
  • Time series analysis

Views and materialized views

  • Basic views
  • Dependency handling
  • Using materialized views

Transactions

  • BEGIN, ROLLBACK
  • Row locking and table locking
  • Transaction isolation levels (read committed, etc)
  • SELECT … FOR UPDATE
  • Deadlocks

Cursor

  • Declare cursor
  • WITH HOLD cursor

Indexing

  • Creating and deleting indexes
  • Identify missing indexes
  • Avoiding unnecessary indexes

Course INFO

Level Expert

Language

English or German

Duration

3 Days

Location & Date

Currently there is no date and time available for this certain training, if you are interested please contact us.
Contact us
CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram