Mastering Postgresql: Learning By Real Examples

[nieriorefasow63]

Mastering Postgresql: Learning By Real Examples

Published 6/2023
MP4 | Video: h264, 1280x720 | Audio: AAC, 44.1 KHz
Language: English | Size: 1.28 GB | Duration: 3h 22m


A Comprehensive Guide to Practical PostgreSQL

What you'll learn
Learn SQL Concepts with PostgreSQL.
Write SQL queries to retrieve data.
Gain a comprehensive understanding of PostgreSQL, including topics such as window functions, aggregates, and joins.
Understand how to perform date-related calculations and manipulations in PostgreSQL
Learn how to work with Arrays and JSON in PostgreSQL
Learn how to create databases, tables, columns and data using PostgreSQL

Requirements
SQL experience is not needed.
You need a PC, Mac, or a Desktop Computer
Make sure you have PostgreSQL Installed locally in your computer or in a docker image.
Make sure to install tools such as pgAdmin, DBeaver, or Datagrip to help you write queries.

Description
This is a course intended for software engineers and data analysts that want to learn more about Relational Databases and SQL It covers both basic and advanced SQL concepts and theories. The course is intended for both Junior and Senior Level Engineers and is purely focused on SQL and writing queries.PostgreSQL will be used in this course. But the theories you learn can be applied in any Relational Database such as SQL Server or MySqlAll the sections are explained by one or more examples. I used a sample database that had students, courses, registrations and instructors records. Learning by doing examples is a much better technique than just studying a theory.This course contains quizzes and exercises. You'll get immediate practice on all of the different topics and features that you learn!You will learn about:Selecting DataAggregate FunctionsBuilt in FunctionsJoinsDatesSchema and DataCRUD OperationsTransactionsArrays in PostgresJSONs in PostgresAnd More!Data is the core of an application. Any application that you see on your phone or on your website uses some storage somewhere. Having a good database design and queries is very crucial for any application. This course focuses mainly on the querying part. At the end of the course, you should be able to:Master basic query syntax and functionality: Students will learn the syntax of basic SQL queries, including SELECT, FROM, WHERE, GROUP BY, and ORDER BY. They will also gain an understanding of how to filter and sort data, and how to use basic functions and operators.Develop proficiency in advanced querying techniques: Students will learn how to write more complex queries, including subqueries, inner and outer joins, and aggregations. They will also learn how to use advanced functions and operators such as CASE statements, window functions, and regular expressions.Optimize queries for performance: Students will learn how to optimize queries for faster execution, including indexing strategies, query planning and execution, and common performance issues to avoid.Apply queries to real-world scenarios: Students will learn how to apply their query skills to solve real-world problems. They will also explore best practices for data modeling and query design to ensure their queries are accurate and efficient.

Overview
Section 1: Introduction

Lecture 1 Prerequisites

Lecture 2 Working Data

Section 2: Querying

Lecture 3 Select

Lecture 4 Select Multiple

Lecture 5 Select Constants

Lecture 6 Order By

Lecture 7 Limit and Offset

Lecture 8 Where Statement

Lecture 9 IS NULL

Lecture 10 Or/And

Lecture 11 Not

Lecture 12 Between

Lecture 13 In

Lecture 14 Any/Sum

Lecture 15 Alias

Lecture 16 Exists

Lecture 17 Distinct

Lecture 18 Like

Lecture 19 Case When

Section 3: Querying Exercises

Section 4: String Operators

Lecture 20 Lower - Upper

Lecture 21 Initcap

Lecture 22 Concat

Lecture 23 Left

Lecture 24 Right

Lecture 25 Substring

Lecture 26 Length

Lecture 27 Replace

Lecture 28 Cast

Lecture 29 Trim

Lecture 30 Split_Part

Section 5: Function Exercises

Section 6: Aggregates

Lecture 31 Introduction

Lecture 32 Min

Lecture 33 Max

Lecture 34 Sum

Lecture 35 Avg

Lecture 36 Count

Lecture 37 Filter

Lecture 38 Group By

Lecture 39 Group By Multiple Columns

Lecture 40 Having

Lecture 41 Over

Section 7: Aggregates Exercises

Section 8: Joins

Lecture 42 Union

Lecture 43 Union All

Lecture 44 Left Join

Lecture 45 Inner Join

Section 9: Joins Exercises

Section 10: Dates

Lecture 46 Current Datetime

Lecture 47 AT TIME ZONE

Lecture 48 EXTRACT

Lecture 49 DATE_PART

Lecture 50 DATE_TRUNC

Lecture 51 Date Differences

Lecture 52 AGE

Lecture 53 TO_CHAR

Lecture 54 TO_DATE

Lecture 55 INTERVAL

Lecture 56 make_interval

Section 11: Working with JSON

Lecture 57 Inserting JSON as a Value

Lecture 58 Selecting a JSON Property

Lecture 59 Selecting a Sub Property

Lecture 60 Json Aggregate

Lecture 61 Json Build Object

Section 12: Working with Arrays

Lecture 62 Introduction

Lecture 63 UNNEST

Lecture 64 SLICING

Lecture 65 ARRAY_APPEND

Lecture 66 ARRAY_LENGTH

Lecture 67 Searching an Array

Lecture 68 ARRAY_AGG

Lecture 69 STRING_TO_ARRAY

Section 13: Window Functions

Lecture 70 Introduction

Lecture 71 ROW_NUMBER

Lecture 72 PARTITION BY

Lecture 73 NTILE

Section 14: Schema and Data

Lecture 74 Introduction

Lecture 75 Database

Lecture 76 Tables

Lecture 77 Columns

Lecture 78 Columns Attributes - DEFAULT

Lecture 79 Columns Attributes - NOT NULL

Lecture 80 Columns Attributes- UNIQUE

Lecture 81 Columns Attributes - CHECK

Lecture 82 Primary Keys

Lecture 83 Primary Keys - Multiple

Lecture 84 Foreign Keys

Lecture 85 Foreign Keys - On Delete

Lecture 86 Indexes

Lecture 87 IF NOT EXISTS

Lecture 88 Rows

Lecture 89 Rows: Insert Multiple

Lecture 90 Rows: Handle onConflict

Lecture 91 Transactions

Section 15: Extras

Lecture 92 Greatest/Least

Lecture 93 Nested Queries

Lecture 94 Coalesce

Lecture 95 Except

Lecture 96 CTE

Lecture 97 Generate Series

Section 16: Conclusion

Lecture 98 Where To Go Next

Section 17: Easy Exercises

Section 18: Medium Exercises

Section 19: Hard Exercises

Backend Engineers,Data Scientists,Data Analysts,Software Engineers,Full Stack Engineers

HOMEPAGE

[To see links please register or login]

DOWNLOAD

[To see links please register or login]