Course Outline
1. Introduction to Relational Databases
- Defining a Relational Database.
- Detailed examination of PostgreSQL architecture and key components.
- Overview of the PgAdmin and psql utility tools.
- Establishing database connections and grasping the concept of schemas.
2. SQL Fundamentals
- Core database objects: tables, columns, and constraints.
- Understanding data types and the handling of NULL values.
- Crafting basic SELECT queries.
- Applying filters using WHERE clauses, pattern matching, and logical operators.
- Sorting query outputs and applying limits.
3. Utilising Functions and Expressions
- Performing arithmetic and string operations via functions.
- Implementing conditional logic and case statements.
- Managing NULLs and executing data type conversions.
4. Data Combination and Filtering Techniques
- Executing inner and outer joins, self-joins, and cross joins.
- Applying set operations: UNION, INTERSECT, and EXCEPT.
- Utilising subqueries: scalar, correlated, and table-based.
- Leveraging Common Table Expressions (CTEs) and recursive query patterns.
5. Aggregation and Grouping Strategies
- Employing aggregate functions: SUM, AVG, COUNT, MIN, and MAX.
- Applying GROUP BY and HAVING clauses effectively.
- Utilising window and ranking functions.
6. Data Modification Procedures
- Executing insert, update, and delete operations on records.
- Managing transactions and implementing rollback mechanisms.
- Understanding the principles of ACID compliance.
- Adopting safe practices for data manipulation.
7. Database Objects and Logical Architecture
- Creating and overseeing schemas and tables.
- Implementing primary keys, foreign keys, and unique constraints.
- Building and managing standard views as well as materialised views.
- Introduction to indexing and its impact on performance.
Requirements
- A solid grasp of fundamental IT and data concepts.
- No previous experience with SQL or database systems is necessary.
- A general familiarity with software tools on Windows or Linux operating systems is advantageous.
Target Audience
- Database novices and data analysts.
- Developers embarking on their journey with PostgreSQL.
- Professionals responsible for managing or investigating relational database systems.
Custom Corporate Training
Training solutions designed exclusively for businesses.
- Customised Content: We adapt the syllabus and practical exercises to the real goals and needs of your project.
- Flexible Schedule: Dates and times adapted to your team's agenda.
- Format: Online (live), In-company (at your offices), or Hybrid.
Price per private group, online live training, starting from £3200 + VAT*
Contact us for an exact quote and to hear our latest promotions
Testimonials (3)
CTE and indexes. Maybe more likely to use partitioning, explain analyze and vacuum on daily-base work
Lorenzo Antonelli - Innova S.P.A
Course - PostgreSQL Fundamentals
Index understanding and usage, improvements on db performances
Matteo Baldini - Innova S.P.A
Course - PostgreSQL Fundamentals
advance topics hands on + discussion like timescaleDB and hypertable , trainer's knowledge on the subject :)