PostgreSQL for Developers

by Evan Leybourn on      2 minutes read

If you would like to speak to Evan about this course, please contact him here.

CC BY SAAs part of an ongoing release of all my training material following the launch of my new book, I am releasing "PostgreSQL for Developers" under a Creative Commons Attribution Share-Alike license. That is, you are free to share, copy, and adapt any part of this training course for your own purposes. All materials, including examples are available for download from this page.

This tutorial is aimed at developers and database administrators (DBA's) who write database driven applications and who want to take advantage of the more advanced features available. This course focuses on PostgreSQL, but most of the techniques will be applicable to any SQL 2003 compliant database.

We will be looking at relational theory, and good database design, but we will also look at successful models which break good design principles. We will also be examining SQL, including some of the more advanced features used to optimise complex queries. Finally we will then look at how the DBMS; how it parses SQL and how to optimise the DBMS configuration parameters.

Course Notes

PostgreSQL for Developers.zip

Participant Benefits

  • Gain a clear understanding of relational theory, and how that affects database queries in practice.
  • Learn how to optimise queries and DBMS for their specific applications.
  • Discover the hidden capabilities of DBMS’s and how to make use of them.
  • Learn how to extend the functionality of databases through the use of complex functions and triggers.

Who Should Attend

  • Server side Application Developers
  • Internet/Intranet Software Developers
  • Database Administrators
  • Business Intelligence Database Staff

Outline

  • Design
    • Relational Theory
    • Entity Relation Diagrams
    • Referential Integrity
    • When to Break Good Design
  • Dealing with Data
    • Large Datasets (100,000,000 + records)
    • Mathematical algorithms
    • Geo-spatial Functions
  • SELECT Statements
    • NULL Operators
    • Advanced Joins
    • Subqueries
    • Set theory (Union, Intersection, Difference)
    • Aggregation in subqueries
    • Views
  • Data Modification
    • Dynamically updating information
    • Updates
  • DBMS
    • Information Schema
    • Templates
    • Transactions
    • Multi-Version Concurrency Control
    • User-defined Data Types
    • EXPLAIN
  • User Roles
    • Access Control
    • Access Privileges
  • Functions and Triggers
    • PLSQL
    • Other DBMS Scripting Languages (Perl, Python, Java)
    • Overloading
    • Invoking a Function
    • Exception Handling
    • Dynamically Generated Queries
    • Triggers