Udemy

Platform

English

Language

Databases

Category

PostgreSQL Bootcamp : Go From Beginner to Advanced, 60+hours

Learn about Subqueries, CTEs, Indexes, PL/SQL, PL/pgSQL, Triggers, Cursors, Partitions, Views, JSON, Window Functions...

4.47 (330 reviews)

Students

67.5 hours

Content

Aug 2021

Last Update
Regular Price


What you will learn

Learn with 60+ hours of hands-on practical exercises

Step-by-step walkthrough to perform simple to complex SQL queries

Subqueries, CTEs, Indexes, PL/SQL, PL/pgSQL, Triggers, Cursors, Partitions, Views, JSON, Window Functions, and much more

How to create a database from scratch

Creating and using various Data types

Creating User-defined data types

Table and Column constraints

Understanding, Using, and creating custom Sequences

String Functions

Aggregate Functions

Date/Time Functions with query analysis

Grouping data with group sets

Schemas creations and privileges

Table Partitioning techniques

Exploring Array Functions

In-depth JSON data types and queries

Subqueries

Common Table Expressions (CTEs)

Recursive Common Table Expressions

Advanced Window Functions

Server Programming

Functions with SQL Language

Functions with PL/pgSQL Language

Exploring Stored Procedures

Indexes and queries performance optimizations

Multiple Data Views types and Security

Using Regular Expressions for Text Patterns

Power Text Searches with special data types and indexes

Crosstab Reports with crosstab extensions

Making our database for global languages - Internationalization

psql Utility Commands

Understanding PostgreSQL Internals

Triggers

Using Cursors

Data integrity with Transactions and Savepoints

Managing PostgreSQL security

Extended PostgreSQL echosystem

Analyze sample HR, Stocks, Northwind, Global Trades databases

Practice with huge number of queries

Use SQL to perform data analysis

Confident putting SQL and PostgreSQL on your resume


Description

Subqueries? Yes, Recursive CTEs? Yes, Server programming with SQL and PL/pgSQL, Stored Procedures? Yes, Window Functions? Yes indeed and much more...

I've design this course from step-by-step move from basic to advanced topics. Here is a partial list of some of the topics that are covered in 50+ sections and 60+ hours of this course:

  • Apply powerful SQL commands to store, update and retrieve information

  • Step-by-step walkthrough to perform simple to complex SQL queries

  • How to create a database from the scratch, Creating and modifying tables

  • Using UPSERT for INSERT and UPDATE at the same time

  • Query data with ORDER BY, Logical operators, and filters

  • Understand PostgreSQL various data types with examples

  • Data type conversions with CAST, and conversion functions

  • Create your own user-defined data types

  • Explore table and column constraints, create multiple composite constraints, see data integrity operations

  • Create your own custom sequences

  • Parsing data with queries using string functions

  • Aggregate data with aggregate functions

  • Date/Time data types, Intervals, and handling timezones

  • Grouping Data with GROUP BY, conditional filter with HAVING

  • Joining Multiple Tables with various joins techniques

  • Combining Queries together with UNION, INTERSECT, and EXCEPT

  • Understand and create PostgreSQL Schemas, ownership, security privileges

  • Constructing arrays and ranges, query array data with conditional filters

  • Storing data with JSONB objects and Indexing

  • Query performance optimization with indexes, understanding indexes nodes and outputs

  • Create and update regular and materialize views

  • Simple to advances step-by-step Subqueries

  • Creating Common Table Expressions (CTEs), and recursive CTEs queries

  • Making data into group sets

  • Simple to advanced Windows Functions

  • Using regular expressions for text patterns

  • Powerful text searches in PostgreSQL

  • Create and exploring table partitioning techniques

  • Server programming with SQL and PL/pqSQL

  • Creating Stores Procedures

  • Extending PostgreSQL echosystem with custom indexes, and aggregate functions

  • Creating, and executing Triggers

  • Using Cursors

  • pqsql Utility Commands

  • Creating Crosstab reports

  • Making our database for storing global language data - Internationalization

  • Running queries with PostgreSQL transactions

  • Crating, and Managing PostgreSQL Security

  • All about PostgreSQL internals on how data gets stored and much more

  • Running hundreds of queries on sample databases like HR, Northwind, Stock Markets, Word Trades database

This comprehensive course will teach you everything about PostgreSQL to use in your next big project!

Master the PostgreSQL database, and see how to apply it with real world database and queries. Sign up today and master PostgreSQL!


Screenshots

PostgreSQL Bootcamp : Go From Beginner to Advanced, 60+hours
PostgreSQL Bootcamp : Go From Beginner to Advanced, 60+hours
PostgreSQL Bootcamp : Go From Beginner to Advanced, 60+hours
PostgreSQL Bootcamp : Go From Beginner to Advanced, 60+hours

Content

Welcome

Course Introduction

Sample Data Files

Introduction to PostgreSQL

Installing PostgreSQL on Mac

Installing PostgreSQL on Windows

Configure pgAdmin 4 client

Creating a Database User

Creating a Database

Running a query in pgAdmin tool

Install sample data files on server

Install Human Resources (hr) database

Install sample stocks market data

Install northwind database

Creating and Modifying Tables

Movie Database Structure

Creating the movie database and a actors table

Creating the directors table

Creating the movies table with a foreign key

Creating the movies_revenues table

Creating a Junction table with movies and actors tables

Using pgAdmin - Create and modify a table

Using pgAdmin - View table structure, and create column

Using pgAdmin - Rename, delete and change the data type of a column

Deleting tables from a database

Modifying Data in the tables

Insert a data into table

Insert multiple records into a table

Insert a data that had quotes

Use RETURNING to get info on added rows

Update data in a table

Updating a row and returning the updated row

Updating all records in a table

Delete data from a table

Using UPSERT

Querying Data

Select all data from a table

Selecting specific columns from a table

Adding Aliases to columns in a table

Using SELECT statement for expressions

Using ORDER BY to sort records

Using ORDER BY with alias column name

Using ORDER BY to sort rows by expressions

Using ORDER BY with column name or column number

Using ORDER BY with NULL values

Using DISTINCT for selecting distinct values

Filtering Data

Comparison, Logical and Arithmetic operators

AND operator

OR operator

Combining AND, OR operators

What goes before and after WHERE clause

Execution order with AND, OR operators

Can we use column aliases with WHERE?

Order of execution of WHERE, SELECT and ORDER BY clauses

Using Logical operators

Using LIMIT and OFFSET

Using FETCH

Using IN, NOT IN

Using BETWEEN and NOT BETWEEN

Using LIKE and ILIKE

Using IS NULL and IS NOT NULL keywords

Concatenation techniques

Concatenation with ||, CONCAT and CONCAT_WS

PostgreSQL Data types

Boolean

CHAR, VARCHAR and TEXT

NUMERIC

DECIMALS

Selecting Numbers data types

Date/Time data types

DATE

TIME

TIMESTAMP and TIMESTAMPTZ

UUID

Array

hstore

JSON

Network Addresses

Data type conversions

What is a data type conversion

Using CAST for data conversions

Implicit to Explicit conversions

Table data conversion

Conversion Functions

to_char

to_number

to_date

to_timestamp

User-defined Data Types

CREATE DOMAIN - Create a DOMAIN data type, create an address

CREATE DOMAIN - Create a data type for a positive number

CREATE DOMAIN - Create a postal code validation data type

CREATE DOMAIN - Create a domain data type for an email validation

CREATE DOMAIN - Create a Enum or Set of values domain data type

CREATE DOMAIN - Get the list of all DOMAIN data types

CREATE DOMAIN - How to drop a domain data type

CREATE TYPE - Create a composite address object

CREATE TYPE - Create a composite inventory_item data type

CREATE TYPE - Create an ENUM data type and see how to drop a data type

ALTER TYPE - Alter a composite data type, change schema and more..

ALTER TYPE - Alter an ENUM data type

Update an ENUM data in production server

An ENUM with a DEFAULT value in a table

Create a type if not exists using a PL/pgSQL function

Explore PostgreSQL Constraints

Introduction to constraints

NOT NULL constraint

UNIQUE constraint

DEFAULT constraint

PRIMARY KEY Constraints

PRIMARY KEY Constraints on multiple columns

FOREIGN KEY Constraints

Tables without foreign key constraints

Creating foreign key constraints

Foreign keys maintains referential data integrity

Drop a constraint

Add or update foreign key constraint on existing table

CHECK constraint - An Introduction

CHECK constraint - Add to new table

CHECK constraint - Add, Rename, Drop on existing table

PostgreSQL Sequences

Create a sequence, advance a sequence, get current value, set value

Restart, rename a sequence, and use pgAdmin to alter a sequence

Create a sequence with START WITH, INCREMENT, MINVALUE and MAXVALUE

Create a sequence using a specific data type

Creating a descending sequence, and CYCLE sequence

Delete a sequence

Attach a sequence to a table column

List all sequences in a database

Share one sequence between two tables

Create an alphanumeric sequence

String Functions

UPPER, LOWER and INITCAP

LEFT and RIGHT

REVERSE

SPLIT_PART

TRIM, BTRIM, LTRIM and RTRIM

LPAD and RPAD

LENGTH

POSITION

STRPOS

SUBSTRING

REPEAT

REPLACE

Aggregate functions

Counting results via COUNT function

Sum with SUM function

MIN and MAX functions

GREATEST AND LEAST functions

Average with AVG function

Combining Columns using Mathematical operators

Using DATE/TIME functions

Datetimes data types

System Month Date settings

Time of day formats and inputs

Strings to Dates conversions

Using TO_TIMESTAMP function

Formatting Dates

Date construction functions

Using MAKE_INTERVAL function

Using MAKE_TIMESTAMPTZ function

Date Value Extractors functions

Using math operators with dates

OVERLAPS Operator

Date / Time Functions

PostgreSQL Date / Time Functions

AGE function

CURRENT_TIME function

Date accuracy with EPOCH

Using Date, time, timestamp in tables

View and set timezones

How to handle timezones

date_part function

date_trunc function

Grouping Data

Using GROUP BY

Using GROUP BY with multiple columns, ORDER BY

Order of execution in GROUP BY clause

Using HAVING

Order of execution in HAVING clause

HAVING vs WHERE

Handling NULL values with GROUP BY

Joining Multiple Tables

INNER joins

INNER joins with USING

INNER joins with filter data Part 1

INNER joins with filter data Part 2

INNER joins with filter data Part 3

INNER joins with different data type columns

LEFT joins Part 1

LEFT joins Part 2

LEFT joins Part 3

LEFT joins Part 4

RIGHT joins

RIGHT joins Part 2

FULL Joins

Joining multiple tables

Self Joins Part 1

Self Joins Part 2

CROSS Joins

Natural Joins Part 1

Natural Joins Part 2

Append tables with different columns

ON versus WHERE

Combining queries together

Combine results sets with UNION

UNION with filters and conditions

UNION tables with different number of columns

INTERSECT with tables

EXCEPT with tables

PostgreSQL Schemas

What is a Schema?

Schema Operations (Add/Alter/Delete schemas)

Schema Hierarchy

Move a table to a new schema

Schema search path

Alter a schema ownership

Duplicate a schema along with all data

What is a system catalog schema?

Compare tables and columns in two schemas

Schemas and Privileges

Exploring array functions

Constructing arrays and ranges

Using comparison operators

Using inclusion operators

Array constructions

Array metadata functions

Array search functions

Array modification functions

Array comparison with IN, ALL, ANY and SOME

Formatting and converting an array

Using arrays in tables

Insert data into arrays

Query array data

Modifying array data

Array Dimensions are ignored!

Display all array elements

Using Multi-dimensional arrays

Array vs JSONB

JSON with PostgreSQL

What is JSON?

JSON Syntax

JSON and JSONB data types

Exploring JSON objects

Create our first table with JSONB data type

Update and Delete JSON Data

Create JSON from tables

Use json_agg to aggregate data

Build a JSON array

Creating a document from data

Null Values in JSON documents

JSON nulls and SQL nulls

Getting information from JSON documents

The Existence Operator

The Containment Operator

JSON search with PostgreSQL functions

Indexing on JSONB

Indexes and Performance Optimization

What is an index?

Create an index

Create unique indexes

List all indexes

Size of the table index

List counts of all indexes

Drop a index

SQL Statement execution process

SQL statement execution stages

The query optimizer

Optimizer node types

Sequential Nodes

Index Nodes

Join Nodes

Index Types - B-Tree Index

Hash Index

BRIN index

GIN Index

The EXPLAIN statement

EXPLAIN output options

Using EXPLAIN ANALYZE

Understanding query cost model

Index are not free

Indexes for sorted output

Using multiple indexes on a single query

Execution plans depends on input values

Using organized vs random data

Try to use index only scan

Partial indexes

Expression Index

Adding data while indexing

Invalidating an index

Rebuilding an index

All about Views

Introduction to views

Creating a view

Rename a view

Delete a view

Using filters with views

A view with UNION of multiple tables

Connecting multiple tables with a single view

Re-arrange columns in a view

Delete a column in a view

Add a column in a view

Regular views are dynamic

What is an updatable view?

An updatable view with CRUD operations

Updatable views using WITH CHECK OPTION

Updatable views using WITH LOCAL and CASCADED CHECK OPTION

What is a Materialized View

Creating a materialized view

Drop a materialized view

Changing materialized view data

How to check if a materialized view is populated or not?

Refreshing data in materialize views

Why not use a table instead of materialized view?

The downsides of using materialized views

Using materialized view for websites page analysis

List all materialized views by a SELECT statement

List materialized views with no unique index

Quick queries for materialized views

Fun with subqueries

Introduction to subqueries

Subqueries with WHERE Clause

Subquery with IN operator

Subquery with JOINs

Order entries in UNION without using ORDER BY

Subquery with an alias

A SELECT without a FROM

SELECT.. IN (Subquery)

Using ANY with subquery

Using ALL with subquery

Subquery using EXISTS

Common Table Expressions (CTE)

Introduction to CTEs

CTE query examples

Combine CTE with a table

Simultaneously DELETE INSERT via CTE

Recursive CTEs

Parent-child relationship via recursive CTE

Grouping sets

Introduction to summarization

Subtotals on group sets

Adding subtotal with ROLLUP

Using GROUPING with ROLLUP

Window Functions

Introduction to Window Functions

Analyze Global Trades Data

Using aggregate functions

GROUP BY ROLLUP

ROLLUP - Grouping multiple columns

GROUP BY CUBE

GROUP BY GROUPING SETS

Query performance check

Using FILTER clause

Using Window Functions

Partitioning the data

Set data into millions format

Ordering inside window

Sliding dynamic windows

Understanding window frames

ROWS and RANGE indicators

Using WINDOW

Using WINDOW with ROWS BETWEEN

RANK and DENSE_RANK functions

NTILE function

LEAD and LAG functions

FIRST_VALUE, LAST_VALUE and NTH_value functions

ROW_NUMBER function

Finding Correlations

ROW_NUMBER() - Window with Partition datasets

ROW_NUMBER() - Reverse fields with order by

ROW_NUMBER() - Find Nth record

ROW_NUMBER() - Find DISTINCT with subquery

ROW_NUMBER() - Pagination technique

Using regular expressions for text patterns

Text to structured data

Regular expressions notations table

SIMILAR TO operator

POSIX regular expressions

SUBSTRING with regular expressions

REGEXP_MATCHES Function

REGEXP_REPLACE Function

REGEXP_SPLIT_TO_TABLE Function

REGEXP_SPLIT_TO_ARRAY function

Powerful text searches in PostgreSQL

The Good Ol' Text Search

Introducing tsvector

Using tsquery with operators

Full text search within a table

Setup presidents speeches data

Analyzing presidents speeches text data

Ranking and Normalizing text results

Table Partitions

What is a partition

When a partition can be used?

Table inheritance

Partition types

Partition by Range

Partition by List

Partition by Hash

DEFAULT Partition

Multi-level partitioning

Partition maintenance

ALTERing the partition bounds

Partition Indexes

Partition pruning

Determining a field for partition over

Sizing the partition

Partitioning Advantages

Common partitioning mistakes

Server Programming

PostgreSQL as a development platform?

Procedural languages

Keep the data on the server!

Functions vs stored procedures

User-defined functions

Structure of a function

Functions with SQL language

Creating our first SQL function

Introducing dollar quoting

Function returning no values

Function returning a single value

Function returning a single value Part 2

Function returning a single value Part 3

Function using parameters

Function using parameters Part 2

Function using parameters Part 3

Function using parameters Part 4

Function returning a composite

Function returning multiple rows

Function - order matters!

Function returning a table

Function as a table source

Function parameter modes

Function parameters with default values

Function based on views

Drop a function

Functions with PL/pgSQL language

Introduction to PL/pgSQL language

PL/pgSQL vs SQL

Structure of a PL/pgSQL function

PL/pgSQL block structure

Declaring variables

Declaring variables via ALIAS FOR

Declaring variables in function

Variable initializing timing

Copying data types

Assigning variables from query

Using IN, OUT without RETURNS

Variables in block and subblock

How to return query results

Control Structures - IF statement

Using IF with table data

CASE Statement

Searched CASE statement

LOOP statement

FOR Loops

FOR Loops iterate over result set

CONTINUE statement

FOREACH loop with arrays

WHILE loop

Using RETURN QUERY

Returning a table

Using RETURN NEXT

Error and exception handling

Exception - Too many rows

Using SQLSTATE codes for exception handling

Exception with data exception errors

Exploring Stored Procedures

Functions vs Stored Procedures

Create a transactions

Understanding the use of stored procedures

Returning a value

Drop a procedure

PostgreSQL Triggers

Introduction to triggers

What is a trigger

Types of triggers

Trigger table

Pros and Cons of triggers

Trigger key points

Steps to create a trigger

Data auditing with a trigger

Bind a function to a table with trigger

Modify data at INSERT event

View triggers variables

Disallow DELETE on a table

Disallow TRUNCATE on a table

Creating an audit trigger

Creating conditional triggers

Disallow data change on primary key

Use triggers very cautiously

What is an event trigger

Event triggers usage scenarios

Creating event triggers

Event trigger events and variables

Creating an audit event trigger

Prevent schema changes

Dropping a trigger

Using Cursors

Understanding row by row operations

Cursors and procedural languages

Steps to create a cursor

Creating a cursor

Opening a cursor

Using a cursor

Updating data

Closing a cursor

Creating a PL/PGSQL Cursor

Using a parametric cursor via function

Creating Crosstab Reports

What is a crosstab report

Installing the tablefunc extension

Creating a crosstab report - Student Rankings

Orders matters in crosstab reports

Pivoting with Rainfalls data

Pivoting rows and columns

Matrix report via a query

Aggregate over filter

Static to dynamic pivots

Dynamic pivot query via JSON

Dynamic pivot table columns

Interactive client-side pivot

Handling missing values

Internationalization

Global Character Set Support

Client and Server Encoding

PostgreSQL Transactions

What is a transaction?

How SQL protect database during transaction

ACID Database

Transaction setup

Transaction analysis

How to fix aborted transaction

How to fix transactions on crash

Partial transaction rollback with savepoints

Using SAVEPOINT with transaction

Using Northwind Database

Using Northwind database

Orders shipping to USA or France

Total numbers of orders shipped to USA or France

Orders shipped to latin america

Show total order amount for each order

First the oldest and latest order date

Total products in each categories

List products that needs re-ordering

Freight analysis

Customers with no orders

Top customers with total orders amount

Orders with many lines of ordered items

Orders with double entry line items

Late shipped orders by employees

Countries with customers or suppliers

Countries with customers or suppliers - Using CTE

Customers with multiple orders

First order from each country

Using Human Resource (hr) database

Human Resources Database Structure

Quick overview of all tables

Quick overview of all tables Part 2

Difference b/w highest and lowest salaries by job_id

Get lowest paid salaries by each manager

Average salary for each department with more than 10 employees.

Maximum salary for each post where salary is at or above $5000

To list all employees IDs within each job_id group

Discard characters from employees email address

List all employees with first_name starts with letters "A", "C" or "M"

Find Letter "C" in last_name at 3rd or greater position

Update phone_number with your strings

Exploring Stock Markets Data

Introduction To stock market data

Select first or last records in a table

Get first or last record per each group

How to calculate cube root in PostgreSQL?

Managing PostgreSQL security

Security concepts and and levels

Instance Level Security

Instance Level Security - Add users to roles

Use pgAdmin to create roles

Database Level Security

Schema Level Security

Table Level Security

Column Level Security

Row Level Security

Using CURRENT_USER with RLS

Row level policy for application users

DROP a policy

Inspecting permissions

Row Level Security Performance

PostgreSQL Utilities - psql

Connect to local or remote database

Switch connection to a new database

List all databases and tables

List all table space, schemas, and indices

List all sequences, roles, data types, domain data types

Describe a table

Edit SQL commands in editor

Display command history, run commands from a file

Built-in syntax reference for commands

Represent NULL values on psql terminal

Make table layout funkey!

Repeatedly Execute a Query

Represent NULL Visibly

Turn on query execution time

PostgreSQL Internals

List database users, and database sizes with and without indexes

List all database and schemas

List all tables and views

List all columns from a table

View system metadata via system information functions

View privileges information across tables

Using system Administration functions

Show all running queries

Terminate running and IDLE process

How to check live and dead rows in tables

File layout of PostgreSQL Tables

Managing Tables

Using SELECT INTO to create a new table with joins

Duplicate a table with or without data

Import data from CSV files

Export Data to CSV files

Deleting duplicate records

Backup your databases regularly

Test queries on a test database

Not documenting your process?

Database operations and table size

Tracking table size

PostgreSQL autovacuum proces

Recovering unused space with VACUUM

Extended PostgreSQL

Create a custom index method

Create a user-defined aggregate function

Thank You!

Your feedback is very valuable

Upcoming course - Advanced Time Series Analysis

Thank You!


Reviews

B
Benjamin1 August 2021

I'm glad to enroll in this course, it teachs and give a solid fundation in PostgreSQL. Thx for this great job. Now i can master PostgreSQL.

R
Randy24 July 2021

the course is created on basic topics mostly from postgresql document to generate passive income only without further responsibility!speed up videos and come up with 30 hours and eliminate BS, you come up with 5 hours lectures with possibly basic useful materials. Really shameful !

T
Tim16 June 2021

The instructor drives each point with examples building up understanding necessary for the following sections. So far very methodical which is probably appropriate for learning about databases.

S
Shai4 June 2021

Super comprehensive, excellent for people that know the basics. would've been perfect if we had all scripts to follow along or self-exercises (within the course or even good references)

I
Isaac18 May 2021

It would be even better if the instructor can upload all the resources especially the notes and the query in a zip file for reference purpose

E
Elaine3 May 2021

The pace of the training is pretty fast, hence your suggestion to pause the video frequently and attempt the exercises on your own. A definite down-side is you do not provide the exercises for download in any form. We are expected to do them as we follow along with the video. I requested access to download the exercises and was informed that they are only for the instructor’s personal use. This makes it more difficult to take notes or make yourself any type of reference materials. My personal memory bank does not have enough storage to remember the extensive range of topics covered in this course. I spent the better part of two weeks watching the videos and trying to follow along. I know I won’t remember everything. A copy of the exercises to take notes on and refer to later would have been really helpful.

C
Christian28 April 2021

Excelente, aunque no domino muy bien el idioma inglés, pude entender todo lo que dice a la perfección. Excelent, even when I'm not good with English I can understand everything he says perfectly.

D
Dr.25 April 2021

I like the detailed explanation and hands on. He is masteing you not only the postgres but also the tool you are using, which is very important.

V
Vladimir21 April 2021

I think such a course is the only way to be familiar with a huge number of great PostgreSQL features. It is absolutely impossible to read all of that and try. Thanks a lot for such a course!

B
Bader13 April 2021

I bought two course, both rate very high, that is the better. Therefore I added max rate. Great course, thanks!

K
Kevin1 April 2021

Extremely in depth SQL course (by far the most in depth I have seen on udemy). Extremely good work from the creator. Well worth the money for the knowledge.

D
Dimitrij18 March 2021

So far, the course covers every topic in depth, which is highly appreciated. It is obvious that the instructor is very knowledgeable. The only thing which could be better is monotonal voice of the instructor. Overall I would recommend this course for its extensive content.

E
Emmanuel7 February 2021

This is the most comprehensive course that I've ever come across on Postgres. Adnan is exceptionally knowledgeable on the subject, and he is also a very good teacher. The course is very detailed, and it worth way more than what I paid for. Thanks a lot.


3499600

Udemy ID

9/14/2020

Course created date

1/18/2021

Course Indexed date
Bot
Course Submitted by