SQL and PostgreSQL using pgAdmin4 : Beginner to Expert

Name: SQL and PostgreSQL using pgAdmin4 : Beginner to Expert
Rating: 3.9 (8 reviews)

PgAdmin4, Load sample data in Postgres, JOINS, Subquery, Window function, CTEs, query troubleshooting and Optimisation

Created byShakti Jajpura

Last updated 10/2023

English

What you'll learn

Solving real-world data analytics use cases through Postgres
PGAdmin4 tool detailed walkthrough
Beginner commands - SELECT, LIMIT, CREATE, INSERT, WHERE, GROUP BY, ORDER BY, LIMIT, FETCH, HAVING
Joins - LEFT, RIGHT, INNER, FULL OUTER, CROSS JOIN, SELF JOIN, NATURAL JOIN
Grouping sets, CUBE and ROLLUP
UNION, UNION ALL, INTERSECT, EXCEPT
Writing sequence and Execution sequence of the codes
CASE WHEN statements
Subqueries - ALL, ANY, EXISTS commands
Window functions - NTILE, LAG, LEAD, ROW_NUMBER, FIRST_VALUE
Postgres coding best practices
Date Functions
Partitioned Tables
CTE (Common Table Expressions)
Views
Triggers
Query troubleshooting
Query optimization

Course content

6 sections • 26 lectures • 4h 41m total length

Introduction to the course Structure and learning outcomes2:29
Get an overview of what all is covered in this course.
PostgreSQL use case in Analytics - How analysts use SQL on a day-to-day basis2:25
Lecture provides details into what PostgreSQL is and what is the SQL use case for any analytics professional

SELECT, FROM and LIMIT keyword17:26
CREATE and INSERT command37:18
WHERE keyword17:30
Learn to use where keyword to filter data on the sales order table with conditions such as greater than 1000, between 1000 and 2000, not null, like, and not in.
ORDER BY, LIMIT and FETCH keyword8:33
Sort query results with order by and control rows using limit, offset, or fetch, demonstrated by sorting on gross sales and platform across the sales data.
Practice Assignment 1
GROUP BY and HAVING command9:58
Learn how to use group by and having in PostgreSQL to perform aggregations, such as total gross sales by platform, with filtered results.
Use case - Finding the granularity of any table7:44
JOINS in Postgres29:05
Explore joins in Postgres, including inner, left, right, full outer, self, and cross joins, with practical syntax and examples using pgAdmin4.
UNION, UNION ALL, INTERSECT and EXCEPT commands13:27
Writing sequence and Execution sequence5:38
Practice Assignment 2

CASE WHEN statements9:18
Explore case when statements in Postgres to implement if-else logic within a single query. Learn syntax with when-then-else, nested cases, multiple conditions, optional else, and aggregation using group by.
Use case - Careful while using WHERE with OUTER JOINS8:22
Learn to use where with left, right, or full outer joins in PostgreSQL, compute 2014 first purchases, and determine the 30-day repeat rate while preserving nulls.
Subqueries in Postgres10:27
Use case - CROSS JOINS11:46
SELF JOIN and NATURAL JOIN5:44
GROUPING SETS, CUBE and ROLLUP commands11:03
Window Functions9:57
Use case - Finding the Mean, Median and Mode4:47
Use case - Lag and Lead Window functions6:09
Practice Assignment 3

Date Functions7:42
CTE (Common Table Expressions)5:35
A Common Table Expression (CTE) is temporary result set which can be referenced within another SQL statement.
They are temporary in the sense that they only exist during the execution of the query
Partitioned Tables12:11
Partitioning a table improves the query performance.
The data gets stored into different chunks for a partitioned table.
When we query the table by filtering over partitioned field, only a small chunk of data is read instead of all the records.

Requirements

No programming experience needed

Description

This course is intended for -

Anyone willing to get a good hands-on experience with PostgreSQL (postgres is short).
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. Only the motivation to learn is enough :)

The aim to have all the concepts clarified, and at the same time, keeping the content very interactive.

The datasets considered is very practical in nature.
The assignment are designed in such a way, to give an idea of exact problems that the analytics professionals face on day-to-day basis.
Downloadable SQL files are attached with all the lessons. This will save time re-writing the codes.
The course will serve as a way to gain problem solving capabilities.
My learnings from last 7 years working in data analytics industry has been tutored in this course

After successful completion of this course -

You will be able to translate business problem statements into data driven solutions. For example, if someone asks you a use case of cross join, you can elaborate in very concise and practical way.
You will be much more confident using SQL irrespective of the database you work on.

Tools required -

PGAdmin 4,
PostgreSQL.
(There is a dedicated section in this course on setting up the environment and loading the sample dataset).

The course structure is divided into 3 sections -

Beginners,
Intermediate
Advance.

Based on the current level of experience, you can choose to start from either of these and to also jump between sections or lectures as you seem fit. There is no dependency between the lectures so you can focus on your specific learning goals.
We've included the SQL script file which is showcased in the lecture. Use these to save time re-writing the codes.

Make the most use of the available Udemy tools like Q&A, Assignment, Notes. to clarify the doubts and self evaluate.

I would also like to hear the feedback on this course to make it even better and more exhaustive with regular updates. Please provide feedbacks via ratings, reviews or direct messages.

We aim for 100% student satisfaction, you can request a 30-day refund if you are not satisfied with the course material.

Who this course is for:

Anyone willing to get a good hands-on experience with PostgreSQL.
Are working, or are interested in working in the data analytics industry or related field.
Those who wants to learn the SQL concepts by solving real-world business problems and not just hypothetical problems.

SQL and PostgreSQL using pgAdmin4 : Beginner to Expert

What you'll learn

Explore related topics

Course content

Introduction2 lectures • 5min

Setting up the environment2 lectures • 14min

Load sample datasets - Retail dataset1 lecture • 13min

Beginners9 lectures • 2hr 27min

Intermediate9 lectures • 1hr 18min

Advanced3 lectures • 25min

Requirements

Description

Who this course is for: