Duration
3 days
OVERVIEW
In this course, you will learn and gain the expertise needed to set up, deploy, maintain, and monitor high-performance databases for enterprise applications using open-source PostgreSQL. This course is based on the EDB PostgreSQL Associate certification and beyond.
OBJECTIVES
You will learn:
- Fundamentals of SQL and PostgreSQL
- PostgreSQL System Architecture
- Installation and Configuration
- Creating and Managing Databases
- Command Line Interface (CLI)
- GUI Tools
- Moving Data
- Postgres Data Dictionary
- Backup and Recovery
- Point-in Time Recovery
- Routine Maintenance Tasks
- Database Security
PREREQUISITES
Foundation of PostgreSQL or basic knowledge of database theory.
METHODOLOGY
This is a hands-on course training course that consist of:
- Training & lessons
- Practical coursework
- Kahoot online quizzes
- Mock test/exam sessions
DAY 1 MORNING SESSION (3 hours classes and 1 hour quiz/exercise/exam)
- Introduction to SQL
- Creating Database, Tables and Inserting Data
- String Functions
- Refining Selections
- Aggregate Functions and Logical Operators
- One to many and Many to many Instances
- Node, Webapp and Data Triggers
- Powerful SQL statements
- QUIZ, EXERCISES AND EXAMPLES OF EXAMS QUESTION
DAY 1 AFTERNOON SESSION (3 hours classes and 1 hour quiz/exercise/exam)
- Filtering Records
- Working with Tables
- Relating records with Joins
- Aggregation of Records
- Working with large datasets
- Sorting records
- Union and Intersections with sets
- Assembling queries with subqueries
- QUIZ, EXERCISES AND EXAMPLES OF EXAMS QUESTION
DAY 2 MORNING SESSION (3 hours classes and 1 hour quiz/exercise/exam)
- Selecting distinct records
- Utility operators, keywords and functions
- Local PostgreSQL installation
- PostgreSQL complex datatypes
- Database-side validation and constraints
- Database structure design patterns
- Building a 'like system'
- Building a 'mention system'
- QUIZ, EXERCISES AND EXAMPLES OF EXAMS QUESTION
DAY 2 AFTERNOON SESSION (3 hours classes and 1 hour quiz/exercise/exam)
- Building a 'hashtag system'
- Designing a 'follower system'
- Implementing database design patterns
- Approaching complex queries
- Writing complex queries
- Understanding internals of PostgreSQL
- Indexes for Performance
- QUIZ, EXERCISES AND EXAMPLES OF EXAMS QUESTION
DAY 3 MORNING SESSION (3 hours classes and 1 hour quiz/exercise/exam)
- Advanced query tuning
- Simple common table expressions
- Recursive common table expressions
- Simplifying queries with views
- Optimising queries with materialised views
- Handling concurrency and reversibility with transactions
- Managing database design with Schema migration
- Schema versus data migration
- QUIZ, EXERCISES AND EXAMPLES OF EXAMS QUESTION
DAY 3 AFTERNOON SESSION (2.5 hours classes and 1.5 hour quiz/exercise/exam/Q&A)
- Accessing PostgreSQL from APIs
- Data access pattern - Repositories
- Security around PostgreSQL
- Fast parallel testing
- QUIZ, EXERCISES, EXAMPLES OF EXAMS QUESTION, AND Q&A SESSION
WHO SHOULD ATTEND THIS COURSE
- 1. If you are interested to learn PostgreSQL DBMS
- 2. If you are intending to sit for EDB PostgreSQL 13 Associate examinations
- 3. If you intend to pursue a career in Relational Database System designer or administrator
Programme scope, delivery arrangements and suitability are confirmed during enquiry.