Category: PostgreSQL
Complex training leading from SQL language basics to advanced features appearing only in PostgreSQL system. Completing the course PostgreSQL for programmers will give you abilities essential to competently design, implement and maintain applications based on that database engine.
The training is conducted in a workshop form and it focuses on practical issues. Training materials include many examples and exercises and students do them assisted by our instructor. The exercises represent the most typical problems encountered by programmers dealing with applications based on a database.
Among the issues raised during the training, one will find relational modelling, correct usage of corectness constraints and foreign keys. We will not forget about performance aspect, suggesting how to model and build optimum SQL queries, how to use indexes and what should be especially considered while reading query plans generated by the base.
Our instructors have many years of experience with PostgreSQL, both as administrators and as programmers. One of our trainers is a PostgreSQL contributor and a regular speaker at the world’s largest conference on the topic.
Duration
5 days
Agenda
- Relational databases – basics
- Understanding relations
- Table, row, column
- Primary keys
- Foreign keys, associations between tables
- Database client programs
- Command line client psql
- Graphical client pgAdmin
- Web client phpPgAdmin
- SQL language
- Data extraction, SELECT clause
- Data types in SQL
- Functions and operators
- The method of performing SQL queries
- Filtering, WHERE clause
- Grouping, GROUP BY and HAVING clauses
- Set-theoretic operations, UNION, INTERSECT, EXCEPT
- Joining multiple tables
- DISTINCT and DISTINCT ON operations
- Advanced SQL issues
- Perspectives and subqueries
- OLAP functions (window functions)
- Common Table Expressions, recursive queries
- Transactions and isolation levels, ACID concept
- Rows blocking, concurrency control
- Databases designing
- Examples of relational modelling
- Normal forms, best practises
- Coherence constraints
- Popular methods of database notations schemas
- Data modification
- INSERT, DELETE, UPDATE commands
- Defining, modification and deleting tables
- Creating coherence constraints
- Performance
- SQL language executor implementation
- Access to the disk data
- Creating JOINs
- Queries optimization
- Indexes types and usage
- Partial and functional indexes
- Reading EXPLAIN command
- Prepared statements, PREPARE construction
- Importing bulk data, COPY command
- Genetic query optimizer
- SQL language executor implementation
- Access from programming languages level
- The most popular bindings for programming languages
- Parameterized queries
- Preventing SQL Injection attacks
- PostgreSQL binary protocol
- Stored procedures
- PL/pgSQL language
- Function types , volatile, stable, immutable
- Loops, conditional statements
- Error handling
- Triggers
- Other embedded languages
- Trusted and untrusted languages
- Example procedure in embedded Python
- PL/pgSQL language
- Advanced possibilities
- Extensions hstore i json
- Full text search
- Range types
- Exclusion constraints
- Defining one’s own types and domains
- Prepared transactions, PREPARE TRANSACTION command
- Asynchronous notifications, NOTIFY command
- Partitioning
- The objectives of the database partitioning
- Partitioning via inheritance
- Other partitioning strategies
Audience and prerequisites
The course is designed for people with no experience with relational databases and SQL language, mainly programmers or alternatively analysts interested also in programming and performance aspects.
We recommend participants are able to program in any language giving access to PostgreSQL.
Certificates
Course participants receive completion certificates signed by ALX.
