Mastering SQL using Postgresql

Mastering SQL using Postgresql, available at $74.99, has an average rating of 4.4, with 105 lectures, based on 244 reviews, and has 28998 subscribers.

You will learn about 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 This course is ideal for individuals who are College students and entry level professionals to get hands on expertise with respect to SQL to be prepared for the interviews. or Experienced application developers to understand key aspects of Databases to improve their productivity. or Data Engineers and Data Warehouse Developers to understand the relevance of SQL and other key concepts. or Testers to improve their query writing abilities to validate data in the tables as part of running their test cases. or Business Analysts to write ad-hoc queries to understand data better or troubleshoot data quality issues. or Any other hands on IT Professional who want to improve their query writing and tuning capabilities. It is particularly useful for College students and entry level professionals to get hands on expertise with respect to SQL to be prepared for the interviews. or Experienced application developers to understand key aspects of Databases to improve their productivity. or Data Engineers and Data Warehouse Developers to understand the relevance of SQL and other key concepts. or Testers to improve their query writing abilities to validate data in the tables as part of running their test cases. or Business Analysts to write ad-hoc queries to understand data better or troubleshoot data quality issues. or Any other hands on IT Professional who want to improve their query writing and tuning capabilities.

Enroll now: Mastering SQL using Postgresql

Summary

Title: Mastering SQL using Postgresql

Price: $74.99

Average Rating: 4.4

Number of Lectures: 105

Number of Published Lectures: 105

Number of Curriculum Items: 105

Number of Published Curriculum Objects: 105

Original Price: $19.99

Quality Status: approved

Status: Live

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

Who Should Attend

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.

Target Audiences

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.

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.

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.

Course Curriculum

Chapter 1: Introduction – Resources and Tips

Lecture 1: Must Watch – Introduction

Lecture 2: For Udemy new comers – Interface to take the course

Lecture 3: For Udemy new comers – Navigating through the course

Lecture 4: Must Watch – Resources for the course

Lecture 5: Resources – PDF

Lecture 6: Must Watch – How to get support for this course using Q&A?

Chapter 2: Getting Started

Lecture 1: Connecting to Database

Lecture 2: Using psql

Lecture 3: Setup Postgres using Docker

Lecture 4: Setup SQL Workbench

Lecture 5: SQL Workbench and Postgres

Lecture 6: SQL Workbench Features

Lecture 7: Data Loading Utilities

Lecture 8: Loading Data – Docker

Lecture 9: Exercise – Loading Data

Chapter 3: DML or CRUD Operations

Lecture 1: Database Operations – Overview

Lecture 2: CRUD Operations

Lecture 3: Creating Table

Lecture 4: Inserting Data

Lecture 5: Updating Data

Lecture 6: Deleting Data

Lecture 7: Overview of Transactions

Lecture 8: Exercise – Database Operations

Chapter 4: Writing Basic SQL Queries

Lecture 1: Standard Transformations

Lecture 2: Overview of Data Model

Lecture 3: Define Problem Statement – Daily Product Revenue

Lecture 4: Preparing Tables

Lecture 5: Selecting or Projecting Data

Lecture 6: Filtering Data

Lecture 7: Joining Tables – Inner

Lecture 8: Joining Tables – Outer

Lecture 9: Performing Aggregations

Lecture 10: Sorting Data

Lecture 11: Solution – Daily Product Revenue

Lecture 12: Exercises – Basic SQL Queries

Chapter 5: Creating Tables and Indexes

Lecture 1: DDL – Data Definition Language

Lecture 2: Overview of Data Types

Lecture 3: Adding or Modifying Columns

Lecture 4: Different Types of Constraints

Lecture 5: Managing Constraints

Lecture 6: Indexes on Tables

Lecture 7: Indexes for Constraints

Lecture 8: Overview of Sequences

Lecture 9: Truncating Tables

Lecture 10: Dropping Tables

Lecture 11: Exercises – Managing Database Objects – Pre-requisites

Lecture 12: Exercise 1 – Adding Sequences

Lecture 13: Exercise 2 – Adding Foreign Keys to Tables

Chapter 6: Partitioning Tables and Indexes

Lecture 1: Overview of Partitioning

Lecture 2: List Partitioning

Lecture 3: Managing Partitions – List

Lecture 4: Manipulating Data

Lecture 5: Range Partitioning

Lecture 6: Managing Partitions – Range

Lecture 7: Repartitioning – Range

Lecture 8: Hash Partitioning

Lecture 9: Managing Partitions – Hash

Lecture 10: Usage Scenarios

Lecture 11: Sub Partitioning

Lecture 12: Exercise – Partitioning Tables

Chapter 7: Pre-defined Functions

Lecture 1: Overview of Pre-Defined Functions

Lecture 2: String Manipulation Functions

Lecture 3: Date Manipulation Functions

Lecture 4: Overview of Numeric Functions

Lecture 5: Data Type Conversion

Lecture 6: Handling Null Values

Lecture 7: Using CASE and WHEN

Lecture 8: Exercises – Pre-Defined Functions – Pre-requisites

Lecture 9: Exercise 1 – Get number of users created per year

Lecture 10: Exercise 2 – Get the day name

Lecture 11: Exercise 3 – Get user details added in 2019

Lecture 12: Exercise 4 – Get user count by gender

Lecture 13: Exercise 5 – Get last 4 digits of unique ids

Lecture 14: Exercise 6 – Get user count by country code

Chapter 8: Writing Advanced SQL Queries

Lecture 1: Overview of Views

Lecture 2: Named Queries – Using WITH Clause

Lecture 3: Overview of Sub Queries

Lecture 4: CTAS – Create Table As Select

Lecture 5: Advanced DML Operations

Lecture 6: Merging or Upserting Data

Lecture 7: Overview of Analytic Functions

Lecture 8: Analytic Functions – Aggregations

Lecture 9: Cumulative or Moving Aggregations

Lecture 10: Analytic Functions – Windowing

Lecture 11: Analytic Functions – Ranking

Lecture 12: Analytic Functions – Filtering

Lecture 13: Ranking and Filtering – Recap

Lecture 14: Exercises – Analytics Functions – Prerequisites

Chapter 9: Bonus Material – Setup Jupyter Lab on Ubuntu VM

Lecture 1: Signing for GCP

Lecture 2: Overview of GCP Web Console

Lecture 3: Overview of GCP Pricing

Instructors

Durga Viswanatha Raju Gadiraju
CEO at ITVersity and CTO at Analytiqs, Inc

Kavitha Penmetsa

Sathvika Dandu

Pratik Kumar

Madhuri Gadiraju

Sai Varma

Phani Bhushan Bozzam

Rating Distribution

1 stars: 4 votes

2 stars: 9 votes

3 stars: 31 votes

4 stars: 74 votes

5 stars: 126 votes

Frequently Asked Questions

How long do I have access to the course materials?

You can view and review the lecture materials indefinitely, like an on-demand channel.

Can I take my courses with me wherever I go?

Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don’t have an internet connection, some instructors also let their students download course lectures. That’s up to the instructor though, so make sure you get on their good side!