EIEIOTECHNOVATION
← Training programmes

EIEIO / DATA SCIENCE

PostgreSQL

Course outline

3 days
Discuss this programme →

Duration

3 days

OVERVIEW

Postgres is a sophisticated open source relational database management system. Postgres supports the structured query language (SQL) with a wide variety of data types and support for triggers, a number of embedded programming languages, and full transactional processing.

This is a course for PostgreSQL database developers or users who need to manipulate data by interacting with PostgreSQL relational databases using Structured Query Language (SQL) and who need to progress their SQL skills beyond the basics. It is aimed at those who need to extract data from, and insert data into, existing PostgreSQL databases to an in-depth level and who also need to create and modify PostgreSQL databases and tables. The course is highly practical in nature and the focus throughout is on coding PostgreSQL-based SQL by hand. On completion, a comprehensive set of course notes, examples, tutor and attendee scripts are made available online for each attendee.

PROGRAM OBJECTIVES

Participants will learn

  • Understand the features and benefits of postgresql
  • Be able to install and start the postgresql server
  • Understand the basics of Relational Databases
  • Understand the SQL and postgresql languages
  • Understand Data/Column types with regard to efficient database design
  • Be able to view a database design structure/content
  • Create a database design using an efficient structure
  • Be able to extract basic database information using the SELECT command
  • Be able to troubleshoot syntax and understand typical warnings and errors
  • Be able to delete or modify a database, and table row data
  • Grouping query data with Aggregation
  • Connect data from multiple table rows using JOIN
  • Perform nested sub-queries
  • Use of Simple Functions (String, Date, Numerical)
  • Describe postgresql connectors, their major features and differences
  • Understand postgresql storage engines, transactions and features of the common engines

WHO SHOULD ATTEND

This course is aimed at an audience of Software Developers, Database Analysts, Data Architects, and System Architects.

PREREQUISITE

No prior SQL, PostgreSQL or relational database experience is assumed.

COURSE OUTLINE

  • Introduction to PostgreSQL
  • A Brief History of PostgreSQL
  • Getting StartedPostgresql Administrator Training
  • Installation
  • Architectural Fundamentals
  • Using the psql terminal based client
  • PostgreSQL SQL Language specifics
  • Creating a Database
  • Accessing a Database
  • Creating a New Table
  • Populating a Table With Rows
  • Querying a Table
  • Joins Between Tables
  • Aggregate Functions
  • Updates
  • Deletions
  • Advanced SQL
  • Views
  • Foreign Keys
  • Transactions
  • Window Functions
  • Inheritance
  • Indexes
  • Multicolumn Indexes
  • Indexes and ORDER BY
  • Combining Multiple Indexes
  • Unique Indexes
  • Indexes on Expressions
  • Partial Indexes
  • Setting up a Partial Index to Exclude Common Values
  • Setting up a Partial Index to Exclude Uninteresting Values
  • Setting up a Partial Unique Index
  • Operator Classes and Operator Families
  • Examining Index Usage
  • Full Text Search
  • Transaction Isolation and Concurrency
  • SQL Transaction Isolation Levels
  • Read Committed Isolation Level
  • Serializable Isolation Level
  • Serializable Isolation versus True Serializability
  • Explicit Locking
  • Table-Level Locks
  • Table-level lock modes
  • Conflicting lock modes
  • Row-Level Locks
  • Deadlocks
  • Advisory Locks
  • Using EXPLAIN
  • Server Set-up and Operation
  • The PostgreSQL User Account
  • Creating a Database Cluster
  • Starting the Database Server
  • Server Start-up Failures
  • Client Connection Problems
  • Shutting Down the Server
  • Server Configuration
  • Setting Parameters
  • File Locations
  • Security
  • Preventing Server Spoofing
  • Encryption Options
  • Secure TCP/IP Connections with SSL
  • Using client certificates
  • SSL Server File Usage
  • Creating a Self-Signed Certificate
  • Connections and Authentication
  • Connection Settings
  • Security and Authentication
  • Resource Consumption
  • Memory
  • Kernel Resource Usage
  • Cost-Based Vacuum Delay
  • Background Writer
  • Asynchronous Behaviour
  • Write Ahead Log
  • Settings
  • Checkpoints
  • Archiving/ Log shipping
  • Point-in-time-recovery
  • Query Planning
  • Planner Method Configuration
  • Planner Cost Constants
  • Genetic Query Optimizer
  • Other Planner Options
  • Error Reporting and Logging
  • Where to Log
  • When to Log
  • Message severity levels
  • What to Log
  • Using CSV-Format Log Output
  • Run-Time Statistics
  • Query and Index Statistics Collector
  • Statistics Monitoring

Programme scope, delivery arrangements and suitability are confirmed during enquiry.