• Post category:StudyBullet-12
  • Reading time:12 mins read


Learn key SQL concepts such as how to create database objects, write effective queries and many more

What you will learn

Setup Postgres Database using Docker

Connect to Postgres using different interfaces such as psql, SQL Workbench, Jupyter with SQL magic etc.

Understand utilities to load the data

Performing CRUD or DML Operations

Writing basic SQL Queries such as filtering, joins, aggregations, sorting etc

Creating tables, constraints and indexes

Different partitioning strategies while creating tables

Using pre-defined functions provided by Postgresql

Writing advanced SQL queries using analytic functions

Description

About Postgresql

Postgresql is one of the leading datatabase. It is an open source database and used for different types of applications.

  • Web Applications
  • Mobile Applications
  • Data Logging Applications

Even though it is relational database and best suited for transactional systems (OLTP), it’s flavors such as Redshift are extensively used for Analytical or Decision Support Systems.

Course Details

This course is primarily designed to go through basic and advanced SQL using Postgres Database. You will be learning following aspects of SQL as well as Postgres Database.

  • Setup Postgres Database using Docker
  • Connect to Postgres using different interfaces such as psql, SQL Workbench, Jupyter with SQL magic etc.
  • Understand utilities to load the data
  • Performing CRUD or DML Operations
  • Writing basic SQL Queries such as filtering, joins, aggregations, sorting etc
  • Creating tables, constraints and indexes
  • Different partitioning strategies while creating tables
  • Using pre-defined functions provided by Postgresql
  • Writing advanced SQL queries using analytic functions

Desired Audience

Here are the desired audience for this course.


Get Instant Notification of New Courses on our Telegram channel.


  • College students and entry level professionals to get hands on expertise with respect to SQL to be prepared for the interviews.
  • Experienced application developers to understand key aspects of Databases to improve their productivity.
  • Data Engineers and Data Warehouse Developers to understand the relevance of SQL and other key concepts.
  • Testers to improve their query writing abilities to validate data in the tables as part of running their test cases.
  • Business Analysts to write ad-hoc queries to understand data better or troubleshoot data quality issues.
  • Any other hands on IT Professional who want to improve their query writing and tuning capabilities.

Developers from non CS or IT background at times struggle in writing queries and this course will provide required database skills to take their overall application development skills to next level.

Key Objectives

The course is designed for the professionals to achieve these key objectives related to databases using Postgresql.

  • Ability to interpret data models.
  • Using database IDEs to interact with databases.
  • Data loading strategies to load data into database tables.
  • Write basic as well as advanced SQL queries.
  • Ability to create tables, partition tables, indexes etc.
  • Understand and use constraints effectively based up on the requirements.
  • Effective usage of functions provided by Postgresql.
  • Ability to write queries using advanced features such as Analytic Functions
  • Differences between RDBMS and Data Warehouse concepts by comparing Postgresql with Redshift.

“This course is primarily designed to gain key database skills for application developers, data engineers, testers, business analysts etc.”

Recommended Training Approach

Here are the details related to the training approach.

  • It is self paced with reference material, code snippets and videos.
  • One can use existing Postgres Database or setup their own environment using Docker (look at bonus material).
  • All sections will be available from Day 1. However, we recommend to set a target between 1 to 2 sections per week.
  • It is highly recommended to take care of the exercises at the end to ensure that you are able to meet all the key objectives for each module.
  • Support will be provided using Udemy Platform. Just send us the question and our team will respond. Please do not send personal messages in Udemy.
English
language

Content

Introduction – Resources and Tips

Must Watch – Introduction
For Udemy new comers – Interface to take the course
For Udemy new comers – Navigating through the course
Must Watch – Resources for the course
Resources – PDF

Getting Started

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

Database Operations – Overview
CRUD Operations
Creating Table
Inserting Data
Updating Data
Deleting Data
Overview of Transactions
Exercise – Database Operations

Writing Basic SQL Queries

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

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 – Pre-requisites
Exercise 1 – Adding Sequences
Exercise 2 – Adding Foreign Keys to Tables

Partitioning Tables and Indexes

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
Exercise – Partitioning Tables

Pre-defined Functions

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 – Pre-requisites
Exercise 1 – Get number of users created per year
Exercise 2 – Get the day name
Exercise 3 – Get user details added in 2019
Exercise 4 – Get user count by gender
Exercise 5 – Get last 4 digits of unique ids
Exercise 6 – Get user count by country code

Writing Advanced SQL Queries

Overview of Views
Named Queries – Using WITH Clause
Overview of Sub Queries
CTAS – Create Table As Select
Advanced DML Operations
Merging or Upserting Data
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 – Prerequisites

Bonus Material – Setup Jupyter Lab on Ubuntu VM

Signing for GCP
Overview of GCP Web Console
Overview of GCP Pricing
Provision Ubuntu VM from GCP
Setup Docker
Validating Python
Setup Jupyter Lab

Bonus Material – Setup Postgres Database on Ubuntu VM

Introduction – Setup Postgres Database
Setup Postgres using Docker
Docker Cheat Sheet
Accessing Postgres using Docker CLI
Create Database and User
Execute SQL Scripts
Setup SQL Workbench
SQL Workbench and Postgres
SQL Workbench Features
Jupyter Lab and Postgres