Build practical database development skills with Database Development with PostgreSQL Fundamentals Training, a hands-on course designed for developers, aspiring database professionals, and IT specialists who want to understand how to design, build, and manage relational databases using PostgreSQL.
Duration 4 Days – 28 hrs.
Overview
This four-day training course provides participants with the essential knowledge and practical skills required to develop database solutions using PostgreSQL. It covers relational database concepts, PostgreSQL architecture, database and table creation, SQL querying, data manipulation, joins, functions, transactions, views, indexing, and basic performance optimization.
Participants will work through practical exercises involving database design, data validation, complex queries, reusable database objects, and transaction management. By the end of the course, they will be able to design, build, query, and maintain functional PostgreSQL databases for common business and application-development requirements.
Learning Objectives
- Explain relational database concepts and the role of PostgreSQL.
- Install or connect to a PostgreSQL environment and use common client tools.
- Create and organize databases, schemas, tables, and other database objects.
- Select appropriate PostgreSQL data types.
- Implement primary keys, foreign keys, unique constraints, checks, and default values.
- Retrieve and filter data using SQL queries.
- Insert, update, and delete records safely.
- Combine data from multiple tables using different types of joins.
- Summarize and analyze data using aggregate, grouping, and window functions.
- Write subqueries and common table expressions.
- Create and use views, sequences, functions, and basic stored procedures.
- Manage transactions and understand data consistency principles.
- Create indexes and use query execution plans for basic optimization.
- Apply fundamental database security and development best practices.
- Develop a small PostgreSQL database solution based on business requirements.
Target Audience
- Junior database developers
- Application and software developers
- Backend and full-stack developers
- Database support personnel
- Systems and business analysts
- Data analysts who need stronger SQL skills
- Quality assurance and test engineers working with databases
- IT professionals transitioning into database development
- Technical professionals who need to work with PostgreSQL databases
Prerequisites
- Basic computer and operating-system skills
- A general understanding of data, tables, rows, and columns
- Basic programming or scripting knowledge, preferably but not required
- Familiarity with relational databases or SQL, helpful but not required
- Access to a computer with PostgreSQL and pgAdmin or an equivalent SQL client
- No prior PostgreSQL experience is required.
Course Outline
Day 1 — PostgreSQL and Relational Database Foundations
Module 1: Introduction to PostgreSQL
- Overview and common uses of PostgreSQL
- PostgreSQL features and supported workloads
- PostgreSQL server and client architecture
- Databases, schemas, roles, and database objects
- Introduction to PostgreSQL tools
- Connecting through psql and pgAdmin
- Basic PostgreSQL configuration concepts
Module 2: Relational Database Fundamentals
- Relational database terminology
- Tables, rows, columns, and relationships
- Primary and foreign keys
- One-to-one, one-to-many, and many-to-many relationships
- Entity-relationship modeling fundamentals
- Introduction to normalization
- Translating business requirements into a database structure
- Naming conventions and design best practices
Module 3: Creating Databases and Tables
- Creating and managing databases
- Creating and using schemas
- Creating, altering, and dropping tables
- PostgreSQL data types:
- Numeric
- Character and text
- Boolean
- Date and time
- UUID
- JSON and JSONB overview
- Arrays overview
- Identity columns and sequences
- Default values and generated values
Module 4: Data Integrity and Constraints
- NOT NULL constraints
- UNIQUE constraints
- Primary-key constraints
- Foreign-key constraints
- CHECK constraints
- Referential actions
- Implementing entity and referential integrity
Hands-On Activities
- Connect to a PostgreSQL server.
- Create a training database and schema.
- Design related business tables.
- Apply keys, relationships, defaults, and validation constraints.
- Load initial sample data.
Day 2 — SQL Querying and Data Manipulation
Module 5: Retrieving Data with SQL
- Structure of a SELECT statement
- Selecting columns and expressions
- Column and table aliases
- Removing duplicates with DISTINCT
- Filtering with WHERE
- Comparison and logical operators
- Working with NULL
- Pattern matching using LIKE and ILIKE
- Sorting with ORDER BY
- Limiting and paginating results
Module 6: Working with PostgreSQL Functions and Expressions
- String functions
- Numeric functions
- Date and time functions
- Type conversion and casting
- Conditional expressions using CASE
- Handling null values with COALESCE and NULLIF
- PostgreSQL operators and expressions
Module 7: Data Manipulation Language
- Inserting records
- Inserting multiple records
- Updating records
- Deleting records
- Using RETURNING
- Upsert operations using INSERT … ON CONFLICT
- Safe update and deletion practices
- Importing and exporting data using COPY
Module 8: Aggregating and Grouping Data
- Aggregate functions
- Grouping data with GROUP BY
- Filtering groups with HAVING
- Grouping by multiple columns
- Common aggregation mistakes
- Introduction to window functions
Hands-On Activities
- Build filtered and sorted queries.
- Apply string, numeric, and date functions.
- Insert, update, and delete records.
- Implement an upsert operation.
- Produce grouped business reports.
Day 3 — Advanced Queries and Reusable Database Objects
Module 9: Joining Multiple Tables
- Understanding table relationships in queries
- Qualified column references and table aliases
- INNER JOIN
- LEFT, RIGHT, and FULL OUTER JOIN
- CROSS JOIN
- Self-joins
- Joining more than two tables
- Avoiding unintended duplicate rows
Module 10: Subqueries and Set Operations
- Scalar and multi-row subqueries
- Correlated subqueries
- Using IN, EXISTS, and NOT EXISTS
- Derived tables
- Set operations:
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
- Choosing between joins and subqueries
Module 11: Common Table Expressions and Window Functions
- Creating common table expressions with WITH
- Organizing complex SQL using CTEs
- Introduction to recursive CTEs
- Window-function concepts
- OVER, PARTITION BY, and window ordering
- Ranking functions
- Running totals and moving calculations
- Accessing preceding and following rows
Module 12: Views and Database Functions
- Creating and managing views
- Simple and complex views
- Introduction to materialized views
- Refreshing materialized views
- Introduction to PostgreSQL functions
- SQL-language functions
- PL/pgSQL structure and syntax
- Parameters and return values
- Variables and conditional logic
- Basic stored-procedure concepts
Hands-On Activities
- Develop multi-table queries.
- Solve business requirements using subqueries and CTEs.
- Build ranking and running-total reports.
- Create reusable views.
- Develop a basic PostgreSQL function.
Day 4 — Transactions, Performance, Security, and Applied Development
Module 13: Transaction Management and Concurrency
- Understanding transactions
- ACID properties
- Starting, committing, and rolling back transactions
- Using savepoints
- Transaction isolation levels
- PostgreSQL multiversion concurrency control overview
- Locking and blocking fundamentals
- Preventing incomplete or inconsistent updates
Module 14: Indexing and Query Performance
- Purpose and cost of indexes
- PostgreSQL index types overview
- Creating and removing indexes
- Single-column and multicolumn indexes
- Unique and partial indexes
- Expression indexes
- When indexes may not be effective
- Reading basic execution plans with EXPLAIN
- Comparing estimated and actual execution with EXPLAIN ANALYZE
- Query-writing practices for better performance
- Table statistics and ANALYZE
- Basic VACUUM and autovacuum concepts
Module 15: Database Security and Development Practices
- PostgreSQL roles and privileges
- Login and group roles
- Granting and revoking access
- Database, schema, table, sequence, and function privileges
- Principle of least privilege
- Secure handling of application credentials
- Parameterized-query concepts and SQL injection prevention
- Database object naming and documentation
- Database change and deployment considerations
- Backup awareness for database developers
Module 16: Capstone Database Development Project
Participants will design and develop a small PostgreSQL database solution that includes:
- Business requirements analysis
- Entity and relationship design
- Database schema and related tables
- Appropriate PostgreSQL data types
- Primary keys, foreign keys, and validation constraints
- Sample data
- CRUD operations
- Multi-table queries
- Aggregate reports
- A view or materialized view
- A function or procedure
- Transaction handling
- Suitable indexes
- Basic execution-plan review
- Roles and access permissions
Project Review
- Functional testing
- Data-integrity validation
- SQL code review
- Performance observations
- Presentation of the completed database solution
