| Course reference: | POSTGRES-DEV |
|---|---|
| prerequisite: | Participants should have prior knowledge and experience in SQL, particularly developers proficient in SQL who wish to apply their skills to PostgreSQL. |
| Duration of Instructor-Present Course (in Days): | 3 |
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.
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:
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
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
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
