This PostgreSQL training course is designed for individuals interested in migrating to PostgreSQL. It focuses on the platform's unique features and functionalities, aiming to enhance SQL skills specifically within the PostgreSQL environment.

Participants should have prior knowledge and experience in SQL, particularly developers proficient in SQL who wish to apply their skills to PostgreSQL.

Lab Set Up: Participants in the course will have access to their own powerful Cloud Machine for LAB-SETUP, where they can practice hands-on with:

• PostgreSQL installation and configuration in a Linux environment.
• Using pgAdmin or a CLI client for practical exercises.

Day-1:

• Postgres as a product

• Installing & managing PostgreSQL

• Overview of PostgreSQL database

• Using the psql client

• pgAdmin as GUI client

• Discuss the basic design, theoretical, and physical aspects of a relational database

• Categorize the different types of SQL statements

• Describe the data set used by the course

• Log on to the database using CLI environment

• Save queries to files and use script files in SQL Developer

• Creating a Database

• Accessing a Database

• Creating a New Table

• Populating a Table With Rows

• Database , users and schema introduction

• Retrieve Data using the SQL SELECT Statement

• Restrict and Sort Data

• Usage of Single-Row Functions to Customize Output

• Invoke Conversion Functions and Conditional Expressions

• Using Aggregate functions or One row functions for aggregation of Data (Group Functions)

• Use the aggregation functions to produce meaningful reports

Day-2:

• Divide the retrieved data in groups by using the GROUP BY clause

• Exclude groups of data by using the HAVING clause

• Difference between where clause & group by

• Sequence of execution

• Display Data From Multiple Tables Using Joins

• Different type of joins and usage

• Write SELECT statements to access data from more than one table

• Using outer joins – its types

• Join a table to itself by using a Self join

• Multi Table Joins of 3 or more tables

• The SET Operators

• Describe the SET operators

• Use a SET operator to combine multiple queries into a single query

• Control the order of rows returned

• Use Sub-queries to Solve Queries

• Describe the types of problem that sub-queries can solve

• Define sub-queries

• List the types of sub-queries

• Write single-row and multiple-row sub-queries

• Retrieve Data Using Sub-queries

• Multiple-Column Subqueries

• Pairwise and Nonpairwise Comparison

• Solve problems with Correlated Subqueries

• Update and Delete Rows Using Correlated Subqueries

• The EXISTS and NOT EXISTS operators

• Manipulate Large Data Sets

• Use Subqueries to Manipulate Data

• Retrieve Data Using a Subquery as Source

• Insert Using a Subquery as a Target

• Working with views

• Benefits and types

• Enhancements to the GROUP BY Clause

• Understand Windows functions

• Use of ROLLUP Operation to Produce Subtotal Values

• Use of CUBE Operation to Produce Cross-Tabulation Values

• Use of GROUPING Function to Identify The Row Values Created By ROLLUP Or CUBE

Day-3:

• Joins vs subqueries

• performance impact of using group by , joins or subqueries

• Practice based on grouping, joins

• Ranking Rows & Pseudocolumn

• Understanding Lead & Lag of window functions in postgreSQL

• Copy data from database to OS file as csv or flat file

• Upload csv data to tables.

• Transaction Management in PostgreSQL

• Execution plan introduction

• How to create and read execution plan

• Understand Configuration files in PostgreSQL

• Indexing Fundamentals

• When to Index

• When Not to Index

• Impact of index usage in SQL Performance