This hands-on course teaches advanced SQL and relational database capabilities using Postgres, the most popular and most admired open-source database in the world. We will cover basic concepts, such as tables, queries, primary keys, foreign keys and normal forms, before diving into more advanced topics such as stored procedures, common table expressions and window functions. The course aims for a balance of theory and practice. All code and data samples will be provided to the participants.

We will use the most recent version of PostgreSQL, one of the most popular open-source databases, to illustrate and exercise the concepts. The exercises will leverage pgAdmin, the leading open-source GUI tool for data analysts, DBAs and developers working with Postgres. Significant parts of the course will use psql, Postgres’ native command line tool.

While this course primarily uses Postgres to illustrate concepts, some features and examples may be specific to Postgres. Participants working with other relational database systems (e.g., MySQL, Oracle, or SQL Server) should be aware that certain functionalities may vary depending on the database system used.

The course is focused on data analysts and developers who have a basic understanding of SQL or other databases.

• Quick intro to key concepts of relational databases
• Connecting to the Postgres database w. pgAdmin and psql
• Tables and query basics: Insert, update, delete, select
• Operators in the WHERE clause
• Primary Keys, constraints and relationships
• Organizing data with schemas and entity-relationship diagrams
• Data Normalization
• Queries revisited (joins, views, and aggregates)
• Stored procedures and functions
• Indexes and the Explain Tool
• Transactions and ACID compliance
• Common Table Expressions (CTE)
• Window Functions
• Using JSON to represent documents

Participants will learn how to use advanced concepts of SQL for transactional and analytical purposes.

Mix of presentation and hands-on-keyboard exercises.