Overview of PostgreSQLAdministration
- The main purpose of the training is to understand SQL w.r.t. PostgreSQL database and
- writing SQL queries keeping performance in mind.
- Also, the part of the training covers importance of indexes, its impact on performance, its
- maintenance.
- Keeping future automation in mind training covers concepts of database programming –
- batches, procedures, triggers & overview of cursors.
Duration
4 Days (8 Half Days)
Lab Setup
HW: 1 system per participant with 8GB RAM and 300GB Hard Disk Space
OS: Windows
SW:
• PostgreSQL Installed & Configured (9.5 / 9.6)
• Acrobat & MS-Office Installed
• Open Internet Connectivity
Course Outline for PostgreSQL Administration
Day 1
Query-writing concepts (Quick Overview)
- Database concepts (Overview)
- Database Objects (Overview)
- Postgresql – as an database
- Working with postgresql
- Meta commands of postgresql
- Quick understanding of postgresql architecture
- Physical file structure of postgresql / DB (Important)
- Logical architecture of postgresql (Important)
- Creating DB & tables (Practical)
- SQL Queries
- Select / DML /DDL commands
- Select query with condition fetch
- Select with multiple conditions
- Logical operators
- AND
- OR
- NOT
- Relational operators
- Sorting data
- Aggregate functions
- Grouping data
- Condition after grouping
- Removing duplicates
Day 2
- Windows functions (Overview & Importance)
- Fetching data from multiple source(s)
- Types of Joins
- Need of Joins
- Demo
- Outer vs Inner joins
- Demo
- Self Join
- Demo
- Sub-Queries & its usages
- Multiple column Sub-query
- Join vs sub-query
- Pseudo columns & Tables (Inline views)
- Index as object of database
- Need and use of index
- Rules of making indexes
- Maintenance of DB / Objects and Indexes (w.r.t. PostgreSQL)
Views in DB
Advantages of Views
Working with Materialized View
Hands-on
Identify parts of query
Sequence of query execution
Joins (Hands-on / Questions)
- Joins?
- Types of Joins
Other Advanced Concepts
- Views & Indexes
- Query Optimization
- SubQueries
Day 3
- Variables
- Declaring variables
- SET versus SELECT
- So-called global variables
Testing conditions
- IF / ELSE statement
- Using CASE where possible
Looping
- Syntax of WHILE
- Breaking out of a loop
Stored Procedures
- Creating stored procedures
- Executing stored procedures
Parameters and return values
- Passing parameters
- Default values / WHERE clauses
- Using RETURN
Triggers
- Understand Triggers
- Need and Usage
- Types of Triggers
- Insert, update and delete triggers
- Magic tables – Using the generated tables (eg INSERTED)
- Demo
Functions
- What they are
- Example of user-defined function
Day 4
- Configuration Files in PostgreSQL
- Managing Free Space
- Tuning Free space Map Settings
- Postgresql.conf
- Administering Tablespace
- Query Optimizations, plan executions
- Implementation of table partitioning concept with hands-on