PostgreSQL for Developers

PostgreSQL for Developers

About PostgreSQL:

Postgresql is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads..

PostgreSQL, like other relational and object-oriented databases, let non-IT specialists query databases. PostgreSQL is open-source, fault-tolerant, and supports location-based services.

Known for reliability, scalability, and robustness, PostgreSQL is widely used by organizations of all sizes for managing large and complex databases. This makes it well-suited for high-performance and mission-critical applications such as data warehousing, analytics, geospatial applications, and web services.

PostgreSQL has been adopted by a variety of industries as the database management system of choice. PostgreSQL is great for handling scientific data, as well as finance and manufacturing information.

About the Course:

The course is intended for both Junior and Senior Level Engineers as well as data analysts that want to learn more about Relational Databases and SQL It covers both SQL (non-procedural) and PL/pgsql (procedural) language..

The course could be virtually divided into two main parts: writing queries using SQL and designing block structures like : Functions, Procedures and Triggers using PL/pgsql.

After completion of the course you will :

1. Apply powerful SQL commands to store, update, and retrieve information

1. 2. Build relationships between records using foreign keys between tables
2. 3. Understand PostgreSQL's numerous data types, and when to use each
3. 4. Design complex queries using different types of joins.
4. 5. Exercise your query logic skills through the use of sets and sorting operators
5. 6. Compose queries out of smaller reusable units using subqueries
6. 7. See how to divide database objects into different schemas to maintain them more easily
7. 8. Speed up your queries with indexes - you'll see exactly how they work behind the scenes!
8. 9. Dive into some of the most complex queries around with recursive common table expressions
9. 10. Deal with concurrency issues easily by applying transactions
10. 11. Explore the procedural aspect of Postgresql using PL/pgsql.
11. 12.Designing user defined Functions, procedures and Triggers.

This course is intended for -

·       Anyone willing to get a good hands-on experience with PostgreSQL .

·       Are working, or are interested in working in the data analytics or related industry.

·       Those who wants to learn the SQL concepts by solving real-world business problems and not just hypothetical problems.

·       People who wants to gain expertise to dive into data and extract important findings.

·       There is no prior coding experience mandatorily required to start this course. 

Course Content:

1.       Introduction to Relational Database Management System.

·       What is database

·       What is Relational Database

·       Key concepts of Realtional Database

2.       Introduction to PostgreSQL

·       History of PostgreSQL

·       Major Features

·       New Features of PostgreSQL

3.       Creating and Managing  Database/Schema/ Tables

·       Creating Databases

·       Creating Schema

·       Creating Tables

·       PostgreSQL Data Types in Depth

·       Applying Integrity Constraints on tables

·       Altering Existing Tables

4.       Using Psql language

·       Querying Data

·       Filtering Data

·       Joining Multiple Tables

·       Grouping Data

·       Subquery

5.       Creating other database objects

·       Views

·       Indexes

·       Sequence

6.       PostgreSQL PL/pgSQL

·       Introduction

·       Block Structure

·       Variables & constants

·       Control structures

·       Reporting messages and errors

7.       Functions

·       Create Function

·       PL/pgSQL Function Parameter Modes: IN, OUT, INOUT

·       Calling a user-defined function

·       Drop Function

8.       Procedures 

·       Create Procedure

·       Calling a stored procedure

·       PostgreSQL Drop Procedures

9.       Exception Handling

·       Handling Predefined Exceptions

·       Handling multiple exceptions

·       Handling exceptions as SQLSTATE codes

10.   Triggers

·       Create Trigger

·       Alter Trigger

·       Drop Trigger