Private: Mastering SQL using Postgresql

Getting Started
9 Topics
Connecting to Database
Using psql
Setup Postgres using Docker
Setup SQL Workbench
SQL Workbench and Postgres
SQL Workbench Features
Data Loading Utilities
Loading Data – Docker
Exercise – Loading Data
DML or CRUD Operations
10 Topics
Normalization Principles
Tables as Relations
Database Operations – Overview
CRUD Operations
Creating Table
Inserting Data
Updating Data
Deleting Data
Overview of Transactions
Exercises – Database Operations
Writing Basic SQL Queries
12 Topics
Standard Transformations
Overview of Data Model
Define Problem Statement – Daily Product Revenue
Preparing Tables
Selecting or Projecting Data
Filtering Data
Joining Tables – Inner
Joining Tables – Outer
Performing Aggregations
Sorting Data
Solution – Daily Product Revenue
Exercises – Basic SQL Queries
Creating Tables and Indexes
11 Topics
DDL – Data Definition Language
Overview of Data Types
Adding or Modifying Columns
Different Types of Constraints
Managing Constraints
Indexes on Tables
Indexes for Constraints
Overview of Sequences
Truncating Tables
Dropping Tables
Exercises – Managing Database Objects
Partitioning Tables and Indexes
12 Topics
Overview of Partitioning
List Partitioning
Managing Partitions – List
Manipulating Data
Range Partitioning
Managing Partitions – Range
Repartitioning – Range
Hash Partitioning
Managing Partitions – Hash
Usage Scenarios
Sub Partitioning
Exercises – Partitioning Tables
Predefined Functions
8 Topics
Overview of Pre-Defined Functions
String Manipulation Functions
Date Manipulation Functions
Overview of Numeric Functions
Data Type Conversion
Handling NULL Values
Using CASE and WHEN
Exercises – Pre-Defined Functions
Writing Advanced SQL Queries
15 Topics
Overview of Views
Named Queries – Using WITH Clause
Overview of Sub Queries
CTAS – Create Table as Select
Advanced DML Operations
Merging or Upserting Data
Pivoting Rows into Columns
Overview of Analytic Functions
Analytic Functions – Aggregations
Cumulative or Moving Aggregations
Analytic Functions – Windowing
Analytic Functions – Ranking
Analytic Functions – Filtering
Ranking and Filtering – Recap
Exercises – Analytics Functions
Query Performance Tuning
9 Topics
Preparing Database
Interpreting Explain Plans
Overview of Cost Based Optimizer
Performance Tuning using Indexes
Criteria for Indexing
Criteria for Partitioning
Writing Queries – Partition Pruning
Overview of Query Hints
Exercises – Tuning Queries
Previous Topic
Next Topic

SQL Workbench and Postgres

Private: Mastering SQL using Postgresql Getting Started SQL Workbench and Postgres

Let us connect to Postgres Database using SQL Workbench.

  • Download the JDBC Driver

  • Get the database connectivity information

  • Configure the connection using SQL Workbench

  • Validate the connection and save the profile

Connecting to Postgres

Here are the steps to connect to Postgres running on your PC or remote machine without Docker.

  • We are trying to connect to Postgres Database that is running as part of remote machine or on your PC.

  • We typically use ODBC or JDBC to connect to a Database from remote machines (our PC).

  • Here are the pre-requisites to connect to a Database.

    • Make sure 5432 port is opened as part of the firewall.

    • If you have telnet configured on your system on which SQL Workbench is installed, make sure to validate by running telnet command using ip or DNS Alias and port number 5432.

    • Ensure that you have downloaded right JDBC Driver for Postgres.

    • Make sure to have right credentials (username and password).

    • Ensure that you have database created on which the user have permissions.

  • Once you have all the information required along with JDBC jar, ensure to save the information as part of the profile. You can also validate before saving the details by using Test option.

Postgres on Docker

Here are the steps to connect to Postgres running as part of Docker container.

  • We are trying to connect to Postgres Database that is running as part of Docker container running in a Ubuntu 18.04 VM provisioned from GCP.

  • We have published Postgres database port to port 5433 on Ubuntu 18.04 VM.

  • We typically use ODBC or JDBC to connect to a Database from remote machines (our PC).

  • Here are the pre-requisites to connect to a Database on GCP.

    • Make sure 5432 port is opened as part of the firewall.

    • If you have telnet configured on your system on which SQL Workbench is installed, make sure to validate by running telnet command using ip or DNS Alias and port number 5433.

    • Ensure that you have downloaded right JDBC Driver for Postgres.

    • Make sure to have right credentials (username and password).

    • Ensure that you have database created on which the user have permissions.

  • You can validate credentials and permissions to the database by installing postgres client on Ubuntu 18.04 VM and then by connecting to the database using the credentials.

  • Once you have all the information required along with JDBC jar, ensure to save the information as part of the profile. You can also validate before saving the details by using Test option.

Previous Topic
Back to Lesson
Next Topic