Learn about Subqueries, CTEs, , Window Functions on real-world practical datasets.

What you'll learn

Solve 200+ SQL hands-on practical query exercises

Work with real-world, practical datasets and problems

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

Learn simple to complex queries

Subqueries

Common Table Expressions (CTEs)

Advanced Window Functions

Requirements

No pre-knowledge is required: I'll teach you absolutely everything you need to know!

A computer is required, but any will do. A desktop or a laptop running Mac, Windows, or Linux

All the software used in the course is free, and I'll personally walk you through setting it all up

Description

Are you ready to embark on an exciting journey through the dynamic world of PostgreSQL? Look no further! Our comprehensive PostgreSQL course has been meticulously designed to transform you from a novice into a PostgreSQL wizard, armed with the skills to conquer real-world data challenges!By the end of this course, you will run 200+ SQL queries to:Apply Powerful SQL Commands: Gain the expertise to effectively store, update, and retrieve information using PostgreSQL's powerful SQL commands.Master Complex Joins: Navigate the intricate web of data by seamlessly joining multiple tables with various techniques.Harness the Magic of Subqueries: Move from the basics to advanced subqueries with ease, enabling you to extract precise information from complex datasets.Unlock the Secrets of Aggregate Functions: Learn how to aggregate data using functions that turn raw data into meaningful insights.Time Travel with Date/Time: Handle date and time data types, intervals, and timezones like a pro.Data Grouping: Organize your data with GROUP BY and apply conditional filters using HAVING, allowing for more structured analysis.Combining Queries: Combine query results using UNION, INTERSECT, and EXCEPT, creating a powerful toolbox for data manipulation.Windows Functions: Delve into the world of window functions, from the basics to advanced techniques for advanced data analysis.Real-world Practice: Work on practical datasets, including Employee Management System, International Debt Data Analysis, Stock Market Analysis, World Trades databases, and much more to preparing you for real-world scenarios.Ready to Take the Leap? Join Us Today!Don't miss this opportunity to master PostgreSQL, one of the most popular and powerful relational database systems in the world. Sign up now, and you'll be well on your way to becoming a PostgreSQL pro! Your next big project is waiting – seize it with PostgreSQL expertise!Your journey begins with a single click. Enroll now and revolutionize your data handling skills with PostgreSQL!

Overview

Section 1: Introduction

Lecture 1 Introduction

Lecture 2 Install PostgreSQL 13

Lecture 3 Install pgAdmin 4 v5.5

Lecture 4 Run PostgreSQL on the cloud

Section 2: *** Employees Management System ***

Lecture 5 Understand the database model

Lecture 6 Create tables and populate data

Lecture 7 Get filtered data via WHERE clause

Lecture 8 Referencing an alias and concatenate columns

Lecture 9 Using conditional logic with CASE expression

Lecture 10 Using ORDER BY, LIMIT and RANDOM

Lecture 11 Find NULL values and transform into real values

Lecture 12 Searching for patterns

Lecture 13 Sorting on data dependent key

Lecture 14 Staking tables together

Section 3: Hierarchical Queries

Lecture 15 Expressing a Parent-Child Relationship

Lecture 16 Expressing a Child-Parent-Grandparent Relationship

Lecture 17 Creating a hierarchical view of a table

Lecture 18 Finding all child rows for a given parent row

Lecture 19 Which rows are leaf, branch, or root nodes

Section 4: Working with numbers

Lecture 20 Computing an average, min/max values

Lecture 21 Sum, and count

Lecture 22 Generating a running total

Lecture 23 Calculating 3-point moving averages

Lecture 24 Calculate a mode

Lecture 25 Calculate a median, averages without highs and lows

Section 5: Date Arithmetic

Lecture 26 Add, subtract dates, and numbers of days

Lecture 27 Compute running date difference

Lecture 28 Comparing records using dates

Section 6: Advances Searching

Lecture 29 Find differences between rows within same group or partition

Lecture 30 Pagination

Lecture 31 Skipping n rows

Lecture 32 Selecting the Top n Records

Section 7: Window Functions

Lecture 33 Turn aggregate function to window function

Lecture 34 PARTITION BY clause

Lecture 35 Handling NULLs

Lecture 36 The ORDER BY clause matters

Section 8: Metadata Queries

Lecture 37 Listing tables, columns and indexes

Lecture 38 Listing constraints on a table

Lecture 39 Using SQL to Generate SQL

Section 9: International Debt Analysis

Lecture 40 Preparing the data

Lecture 41 Analyzing International debt - Part 1

Lecture 42 Analyzing International debt - Part 2

Section 10: Stocks Markets Analysis

Lecture 43 Introduction to Stock Markets Data

Lecture 44 CODE: Table Creation - Sectors, Symbols, Prices

Lecture 45 Import sample datasets

Lecture 46 CODE: Import sample datasets

Lecture 47 Important - SQL Query is in CODE: Articles

Lecture 48 List data, sectors in ascending and descending orders

Lecture 49 CODE: List data, sectors in ascending and descending orders

Lecture 50 Using COUNT, WHERE, IN and NOT IN with sectors

Lecture 51 CODE: Using COUNT, WHERE, IN and NOT IN with sectors

Lecture 52 CODE: Find total stocks per each sectors

Section 11: Congratulations and Thank You!

Lecture 53 Your feedback is very valuable!

Lecture 54 Updates to the course

Lecture 55 Bonus Lectures

Complete beginners to database world,Anyone wanting to learn SQL or PostgreSQL,Application engineers seeking to expand their backend skillset,Anyone who wants to apply SQL knowledge on practical datasets,Anyone wants to learn from basic to advanced functionality of PostgreSQL