Course Outline
1. Introduction to Relational Database Systems
- Definition and fundamentals of relational database management systems.
- PostgreSQL architectural components and system design.
- Overview of administrative interfaces including PgAdmin and psql.
- Database connection protocols and schema structure analysis.
2. SQL Fundamentals
- Core database entities: table definitions, column specifications, and constraint enforcement.
- Data type classification and NULL value handling.
- Construction of basic data retrieval queries.
- Data filtering techniques utilizing WHERE clauses, pattern recognition, and logical operators.
- Result set ordering and volume restriction methods.
3. Functionality and Expression Processing
- Application of arithmetic calculations and string manipulation functions.
- Implementation of conditional logic and CASE statement structures.
- Management of NULL values and explicit data type casting.
4. Data Integration and Filtering Strategies
- Execution of inner, outer, self, and cross join operations.
- Set-theoretic operations: UNION, INTERSECT, and EXCEPT.
- Utilization of scalar, correlated, and table-derived subqueries.
- Application of Common Table Expressions (CTEs) and recursive query logic.
5. Aggregation and Data Grouping
- Deployment of aggregate functions: SUM, AVG, COUNT, MIN, and MAX.
- Implementation of GROUP BY segmentation and HAVING filters.
- Analysis using window and ranking functions.
6. Data Modification Procedures
- Execution of record insertion, modification, and deletion commands.
- Transaction control mechanisms including rollback procedures.
- Compliance with ACID integrity principles.
- Best practices for secure and reliable data manipulation.
7. Database Object Management and Structural Design
- Schema creation, administration, and table lifecycle management.
- Enforcement of primary key, foreign key, and uniqueness constraints.
- Development and maintenance of standard and materialized views.
- Index creation strategies and their impact on query performance.
Requirements
- Foundational knowledge of IT infrastructure and data management concepts.
- No prior experience with SQL or database systems is mandatory.
- Working proficiency with software utilities on Windows or Linux operating systems is advantageous.
Target Audience for Government Training
- Novice database users and data analysts.
- Developers initiating PostgreSQL development workflows.
- Public sector professionals responsible for managing or investigating relational database systems.
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 :)
