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

Name: PostgreSQL Bootcamp : Go From Beginner to Advanced, 60+hours
Rating: 4.6 (5339 reviews)

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

Bestseller

Created byAdnan Waheed

Last updated 10/2023

English

What you'll 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

Course content

49 sections • 629 lectures • 67h 20m total length

Course Introduction1:06
Welcome to the course!
Sample Data Files0:12
I've created this lecture to contains;
Important sample data files

All files are attached to the lecture

Installing PostgreSQL on Mac2:17
In this video, we will install the postgreSQL server on a Mac.
Installing PostgreSQL on Windows6:00
How to install PostgreSQL on Windows
Configure pgAdmin 4 client2:01
We will configure the pgAdmin 4 client interface to connect to our PostgreSQL 12 server installed earlier.
Creating a Database User3:54
We will be creating a new user login to access our PostgreSQL database, and creating training tables.
Creating a Database2:30
We will be creating our learning database on our new server.
Running a query in pgAdmin tool2:11
A quick introduction on how we will be running queries within the pgAdmin tool
Install sample data files on server2:31
We will be working on uploading and installing the sample data files to our PostgreSQL server.
Install Human Resources (hr) database2:21
Install Human Resources (hr) database
Install sample stocks market data2:04
In this video, we will be installing some sample stock market data to our tables.
Install northwind database5:03
Create a Northwind database in PostgreSQL, download the Northwind.sql file, and run it to populate 14 tables for hands-on query exercises.
Drop a database3:27
How to drop a database

Movie Database Structure3:38
Movie Database Structure
Creating the movie database and a actors table6:18
We will create an actor table which will contains actor related information.
Creating the directors table3:29
We will create an directors table which will contains director related information.
Creating the movies table with a foreign key5:54
We will create a movies table and store various movies related information like movie name, release_date and more.
Creating the movies_revenues table3:43
In this video, we will show you how to create a movies_revenues table which will contains movies revenues.
Creating a Junction table with movies and actors tables4:24
We will show you how to create a junction table which will connect movies and actors table together by means of foreign keys.
Install sample data for 'movies' database5:50
Install sample data for 'movies' database
Create a sample Database "mydata"0:32
We will create a sample Database called "mydata" and then we will create tables and data inside as per later videos.
Using pgAdmin - Create and modify a table5:36
We will use the pgAdmin Graphical User Interface (GUI) tool to create and modify a table.
Using pgAdmin - View table structure, and create column5:28
We will use the pgAdmin Graphical User Interface (GUI) tool to view existing table structure like columns, constraints etc., and create a column to an existing table.
Using pgAdmin - Rename, delete and change the data type of a column6:44
We will use the pgAdmin Graphical User Interface (GUI) tool to view a column properties, rename it, and change its data type.
Deleting tables from a database2:48
How to use pgAdmin or DROP TABLE command to delete a table from a database.

Insert a data into table4:46
How to insert data into a table.
Insert multiple records into a table2:35
How to insert multiple records in one single INSERT statement.
Insert a data that had quotes2:00
How to insert a data if this contains a quote e.g. we want to insert 'users's log' as text in our table.
Use RETURNING to get info on added rows2:51
How to use RETURNING clause in INSERT statement to get information of added row.
Update data in a table3:25
How to update data in a table
Updating a row and returning the updated row2:13
How to updating a row and returning the updated row
Updating all records in a table3:10
How to update all records in a table.
Delete data from a table3:07
How to delete a row based on a condition or delete all data from a table
Using UPSERT9:59

Select all data from a table6:31
How to select all data from a table
Selecting specific columns from a table3:43
How to use SELECT statement to get selected columns from a table.
Adding Aliases to columns in a table9:23
How we can use column aliases to make our sql commands results output more presentable and readable for all users.
Using SELECT statement for expressions3:15
We will demonstrate how to use SELECT statement with expressions. i.e. use SELECT but not on a table?
Using ORDER BY to sort records7:54
How to use ORDER BY to list records in a table
Using ORDER BY with alias column name3:14
How to use ORDER BY with alias column name
Using ORDER BY to sort rows by expressions3:08
How to use ORDER BY to sort rows by expressions
Using ORDER BY with column name or column number3:19
How to use either column name or column number for ORDER BY clause.
Using ORDER BY with NULL values5:53
How to use ORDER BY when you have NULL data in a table
Using DISTINCT for selecting distinct values5:27
How to use DISTINCT function to get unique or distinct value of a table.

Comparison, Logical and Arithmetic operators2:18
We will look into comparison, Logical, and Arithmetic operators
AND operator7:26
How to use WITH with AND operator
OR operator3:23
How to use WITH along with OR operator
Combining AND, OR operators4:43
How to use AND, OR operators together, and how with/without parenthesis will change the query result output.
What goes before and after WHERE clause2:48
What is allowed before and after WHERE clause
Execution order with AND, OR operators1:53
How SQL process AND, OR operators in terms of executions, and what role parenthesis play in query output.
Can we use column aliases with WHERE?2:20
Can we use column aliases with WHERE?
Order of execution of WHERE, SELECT and ORDER BY clauses1:33
How SQL does the order of execution of WHERE, SELECT and ORDER BY clause
Using Logical operators16:15
Explore PostgreSQL logical operators, including =, >, <, >=, <=, !=, and text and date handling. See practical examples with movie length, release date, and language, plus ordering results.
Using LIMIT and OFFSET16:51
How to use LIMIT and OFFSET to slice and dice to limit output records
Using FETCH9:46
Using IN, NOT IN10:14
How to use IN and NOT IN with WHERE clause to filter data and more.
Using BETWEEN and NOT BETWEEN11:31
How to use BETWEEN and NOT BETWEEN clause with WHERE statement
Using LIKE and ILIKE17:18
Learn how to use like and ilike for pattern matching in PostgreSQL, with % and _ wildcards, case sensitivity differences, and practical examples in where clauses.
Using IS NULL and IS NOT NULL keywords12:36
How to use IS NULL and IS NOT NULL to filter missing values and more.
Concatenation techniques8:20
Concatenation techniques
Concatenation with ||, CONCAT and CONCAT_WS4:35
How to use ||, CONCAT and CONCAT_WS to concatenate strings and table columns

Boolean13:14
Boolean data type
CHAR, VARCHAR and TEXT18:19
Characters data types
NUMERIC10:14
Numeric data types
DECIMALS9:53
Decimals data types
Selecting Numbers data types5:51
Selecting Numbers data types
Date/Time data types3:42
Explore date and time data types in PostgreSQL, including date, time, timestamp, timestamp with time zone, and interval, and understand how they store values for time series and global data.
DATE6:19
DATE
TIME14:57
TIME
TIMESTAMP and TIMESTAMPTZ12:49
TIMESTAMP and TIMESTAMPTZ
UUID11:38
UUID data type
Array7:07
hstore8:00
hstore data type
JSON12:29
JSON data type
Network Addresses13:59
Network Address data types

to_char8:06
to_number5:18
Explore the Postgres to_number function to convert strings into numeric values using flexible format patterns, including decimal points, grouping, currency symbols, and locale-based formatting.
to_date5:33
Use to_date to convert a string to a date in PostgreSQL by specifying a format. It maps year, month, and day tokens and outputs a date, not a timestamp.
to_timestamp4:47
Learn to_timestamp in PostgreSQL to convert strings to timestamp with time zone using a format string; cover tokens for year, month, day, hour, minute, second, and timezone, plus error handling.

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

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!

Who this course is for:

Complete beginners to database world
Anyone wanting to learn SQL or PostgreSQL
Anyone wanting to learn advanced query expressions
Application engineers seeking to expand their backend skillset
Anyone wants to learn from basic to advanced functionality of PostgreSQL

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

What you'll learn

Explore related topics

Course content

Welcome2 lectures • 1min

Introduction to PostgreSQL11 lectures • 34min

Creating and Modifying Tables12 lectures • 54min

Modifying Data in the tables9 lectures • 34min

Querying Data10 lectures • 52min

Filtering Data17 lectures • 2hr 14min

PostgreSQL Data types14 lectures • 2hr 29min

Modifying Table Structures, Add constraints3 lectures • 24min

Data type conversions4 lectures • 29min

Conversion Functions4 lectures • 24min

Requirements

Description

Who this course is for: