Danh mục
Duocmmo Tedu Tin học văn phòng Laravel Cyberlearn Khoá học miễn phí Tất cả khoá học
Get link khoá học
Trang chủ
Development
Database Design & Development
PostgreSQL
PostgreSQL Bootcamp : Go From Beginner to Advanced, 60+hours

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

Loại khoá học: Database Design & Development

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

image
50.000 VND
110 VND
Đầy Đủ Bài Giảng
Học Online Tiện Lợi
Kích Hoạt Nhanh 2-5 Phút
Thanh toán tự động
Được phép tải xuống

Tổng quan

Nội dung khoá học

Đánh giá

Mô tả

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!

Bạn sẽ học được gì

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

Yêu cầu

  • 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

Nội dung khoá học

49 sections

Welcome

2 lectures
Course Introduction
01:06
Sample Data Files
00:12

Introduction to PostgreSQL

11 lectures
Installing PostgreSQL on Mac
02:17
Installing PostgreSQL on Windows
06:00
Configure pgAdmin 4 client
02:01
Creating a Database User
03:54
Creating a Database
02:30
Running a query in pgAdmin tool
02:11
Install sample data files on server
02:31
Install Human Resources (hr) database
02:21
Install sample stocks market data
02:04
Install northwind database
05:03
Drop a database
03:27

Creating and Modifying Tables

12 lectures
Movie Database Structure
03:38
Creating the movie database and a actors table
06:18
Creating the directors table
03:29
Creating the movies table with a foreign key
05:54
Creating the movies_revenues table
03:43
Creating a Junction table with movies and actors tables
04:24
Install sample data for 'movies' database
05:50
Create a sample Database "mydata"
00:32
Using pgAdmin - Create and modify a table
05:36
Using pgAdmin - View table structure, and create column
05:28
Using pgAdmin - Rename, delete and change the data type of a column
06:44
Deleting tables from a database
02:48

Modifying Data in the tables

9 lectures
Insert a data into table
04:46
Insert multiple records into a table
02:35
Insert a data that had quotes
02:00
Use RETURNING to get info on added rows
02:51
Update data in a table
03:25
Updating a row and returning the updated row
02:13
Updating all records in a table
03:10
Delete data from a table
03:07
Using UPSERT
09:59

Querying Data

10 lectures
Select all data from a table
06:31
Selecting specific columns from a table
03:43
Adding Aliases to columns in a table
09:23
Using SELECT statement for expressions
03:15
Using ORDER BY to sort records
07:54
Using ORDER BY with alias column name
03:14
Using ORDER BY to sort rows by expressions
03:08
Using ORDER BY with column name or column number
03:19
Using ORDER BY with NULL values
05:53
Using DISTINCT for selecting distinct values
05:27

Filtering Data

17 lectures
Comparison, Logical and Arithmetic operators
02:18
AND operator
07:26
OR operator
03:23
Combining AND, OR operators
04:43
What goes before and after WHERE clause
02:48
Execution order with AND, OR operators
01:53
Can we use column aliases with WHERE?
02:20
Order of execution of WHERE, SELECT and ORDER BY clauses
01:33
Using Logical operators
16:15
Using LIMIT and OFFSET
16:51
Using FETCH
09:46
Using IN, NOT IN
10:14
Using BETWEEN and NOT BETWEEN
11:31
Using LIKE and ILIKE
17:18
Using IS NULL and IS NOT NULL keywords
12:36
Concatenation techniques
08:20
Concatenation with ||, CONCAT and CONCAT_WS
04:35

PostgreSQL Data types

14 lectures
Boolean
13:14
CHAR, VARCHAR and TEXT
18:19
NUMERIC
10:14
DECIMALS
09:53
Selecting Numbers data types
05:51
Date/Time data types
03:42
DATE
06:19
TIME
14:57
TIMESTAMP and TIMESTAMPTZ
12:49
UUID
11:38
Array
07:07
hstore
08:00
JSON
12:29
Network Addresses
13:59

Modifying Table Structures, Add constraints

3 lectures
Creating sample database 'mydata', adding columns
04:38
Modify Table Structures, Add/Modify Columns
08:59
Add constraints to columns
10:27

Data type conversions

4 lectures
What is a data type conversion
05:46
Using CAST for data conversions
11:33
Implicit to Explicit conversions
04:12
Table data conversion
07:15

Conversion Functions

4 lectures
to_char
08:06
to_number
05:18
to_date
05:33
to_timestamp
04:47

User-defined Data Types

15 lectures
CREATE DOMAIN - Create a DOMAIN data type, create an address
06:45
CREATE DOMAIN - Create a data type for a positive number
02:16
CREATE DOMAIN - Create a postal code validation data type
02:59
CREATE DOMAIN - Create a domain data type for an email validation
03:45
CREATE DOMAIN - Create a Enum or Set of values domain data type
04:35
CREATE DOMAIN - Get the list of all DOMAIN data types
02:17
CREATE DOMAIN - How to drop a domain data type
04:44
CREATE TYPE - Create a composite address object
06:44
CREATE TYPE - Create a composite inventory_item data type
04:33
CREATE TYPE - Create an ENUM data type and see how to drop a data type
04:33
ALTER TYPE - Alter a composite data type, change schema and more..
04:17
ALTER TYPE - Alter an ENUM data type
03:22
Update an ENUM data in production server
08:13
An ENUM with a DEFAULT value in a table
03:39
Create a type if not exists using a PL/pgSQL function
01:59

Explore PostgreSQL Constraints

15 lectures
Introduction to constraints
04:16
NOT NULL constraint
11:29
UNIQUE constraint
15:17
DEFAULT constraint
05:33
PRIMARY KEY Constraints
21:44
PRIMARY KEY Constraints on multiple columns
13:48
FOREIGN KEY Constraints
06:12
Tables without foreign key constraints
07:42
Creating foreign key constraints
07:49
Foreign keys maintains referential data integrity
07:43
Drop a constraint
02:13
Add or update foreign key constraint on existing table
03:05
CHECK constraint - An Introduction
01:32
CHECK constraint - Add to new table
10:12
CHECK constraint - Add, Rename, Drop on existing table
10:33

PostgreSQL Sequences

10 lectures
Create a sequence, advance a sequence, get current value, set value
07:20
Restart, rename a sequence, and use pgAdmin to alter a sequence
04:17
Create a sequence with START WITH, INCREMENT, MINVALUE and MAXVALUE
03:16
Create a sequence using a specific data type
02:42
Creating a descending sequence, and CYCLE sequence
04:43
Delete a sequence
01:28
Attach a sequence to a table column
08:53
List all sequences in a database
01:26
Share one sequence between two tables
04:15
Create an alphanumeric sequence
07:50

String Functions

12 lectures
UPPER, LOWER and INITCAP
04:37
LEFT and RIGHT
08:07
REVERSE
02:07
SPLIT_PART
06:21
TRIM, BTRIM, LTRIM and RTRIM
06:38
LPAD and RPAD
05:53
LENGTH
03:52
POSITION
03:19
STRPOS
04:55
SUBSTRING
03:51
REPEAT
02:28
REPLACE
04:09

Aggregate functions

8 lectures
Counting results via COUNT function
07:34
COUNT(), COUNT(*) and COUNT(1)
00:36
Sum with SUM function
09:12
MIN and MAX functions
13:32
GREATEST AND LEAST functions
05:08
GREATEST vs. MAX()
00:56
Average with AVG function
12:52
Combining Columns using Mathematical operators
11:06

Using DATE/TIME functions

23 lectures
Datetimes data types
09:38
System Month Date settings
03:07
Time of day formats and inputs
06:09
Strings to Dates conversions
11:18
Using TO_TIMESTAMP function
09:43
Formatting Dates
10:33
Date construction functions
06:27
Using MAKE_INTERVAL function
06:34
Using MAKE_TIMESTAMPTZ function
09:36
Date Value Extractors functions
05:55
Using math operators with dates
09:24
OVERLAPS Operator
04:02
Date / Time Functions
05:01
PostgreSQL Date / Time Functions
04:23
AGE function
03:37
CURRENT_DATE function
01:17
CURRENT_TIME function
03:51
Date accuracy with EPOCH
07:02
Using Date, time, timestamp in tables
08:14
View and set timezones
03:29
How to handle timezones
10:42
date_part function
10:43
date_trunc function
08:57

Grouping Data

7 lectures
Using GROUP BY
11:54
Using GROUP BY with multiple columns, ORDER BY
14:45
Order of execution in GROUP BY clause
01:33
Using HAVING
11:37
Order of execution in HAVING clause
01:06
HAVING vs WHERE
05:39
Handling NULL values with GROUP BY
10:09

Joining Multiple Tables

21 lectures
INNER joins
20:18
INNER joins with USING
08:54
INNER joins with filter data Part 1
10:18
INNER joins with filter data Part 2
03:58
INNER joins with filter data Part 3
05:28
INNER joins with different data type columns
05:11
LEFT joins Part 1
09:33
LEFT joins Part 2
07:27
LEFT joins Part 3
08:01
LEFT joins Part 4
05:49
RIGHT joins
09:43
RIGHT joins Part 2
03:13
FULL Joins
05:03
Joining multiple tables
09:04
Self Joins Part 1
08:29
Self Joins Part 2
02:58
CROSS Joins
10:34
Natural Joins Part 1
05:23
Natural Joins Part 2
02:14
Append tables with different columns
13:42
ON versus WHERE
00:40

Combining queries together

5 lectures
Combine results sets with UNION
11:05
UNION with filters and conditions
10:26
UNION tables with different number of columns
03:28
INTERSECT with tables
04:26
EXCEPT with tables
05:48

PostgreSQL Schemas

10 lectures
What is a Schema?
06:08
Schema Operations (Add/Alter/Delete schemas)
07:24
Schema Hierarchy
06:21
Move a table to a new schema
02:53
Schema search path
14:10
Alter a schema ownership
02:07
Duplicate a schema along with all data
06:19
What is a system catalog schema?
05:20
Compare tables and columns in two schemas
07:40
Schemas and Privileges
06:29

Exploring array functions

18 lectures
Constructing arrays and ranges
07:21
Using comparison operators
07:43
Using inclusion operators
04:16
Array constructions
05:14
Array metadata functions
08:41
Array search functions
06:32
Array modification functions
06:46
Array comparison with IN, ALL, ANY and SOME
13:02
Formatting and converting an array
09:03
Using arrays in tables
04:33
Insert data into arrays
05:50
Query array data
07:09
Modifying array data
03:07
Array Dimensions are ignored!
04:11
Display all array elements
03:52
Using Multi-dimensional arrays
10:37
Array vs JSONB
14:03
Single and Multi-dimensional arrays
01:36

JSON with PostgreSQL

17 lectures
What is JSON?
04:21
JSON Syntax
09:06
JSON and JSONB data types
04:09
Exploring JSON objects
06:30
Create our first table with JSONB data type
07:48
Update and Delete JSON Data
12:46
Create JSON from tables
05:04
Use json_agg to aggregate data
05:23
Build a JSON array
04:39
Creating a document from data
06:16
Null Values in JSON documents
05:04
JSON nulls and SQL nulls
00:22
Getting information from JSON documents
06:25
The Existence Operator
02:54
The Containment Operator
04:06
JSON search with PostgreSQL functions
04:08
Indexing on JSONB
11:28

Indexes and Performance Optimization

33 lectures
What is an index?
03:56
Create an index
15:47
Create unique indexes
11:10
List all indexes
04:17
Size of the table index
05:18
List counts of all indexes
04:33
Drop a index
03:34
SQL Statement execution process
04:34
SQL statement execution stages
06:35
The query optimizer
09:30
Optimizer node types
05:49
Sequential Nodes
06:23
Index Nodes
07:51
Join Nodes
06:46
Index Types - B-Tree Index
04:47
Hash Index
04:44
BRIN index
02:22
GIN Index
01:55
The EXPLAIN statement
14:31
EXPLAIN output options
02:55
Using EXPLAIN ANALYZE
09:10
Understanding query cost model
13:44
Index are not free
12:03
Indexes for sorted output
08:51
Using multiple indexes on a single query
07:51
Execution plans depends on input values
07:02
Using organized vs random data
20:10
Try to use index only scan
04:07
Partial indexes
11:07
Expression Index
16:25
Adding data while indexing
03:10
Invalidating an index
07:54
Rebuilding an index
07:09

All about Views

27 lectures
Introduction to views
07:20
Creating a view
10:52
Rename a view
04:28
Delete a view
01:54
Using filters with views
07:08
A view with UNION of multiple tables
04:08
Connecting multiple tables with a single view
07:17
Re-arrange columns in a view
03:00
Delete a column in a view
01:51
Add a column in a view
02:18
Regular views are dynamic
02:36
What is an updatable view?
03:54
An updatable view with CRUD operations
05:27
Updatable views using WITH CHECK OPTION
07:53
Updatable views using WITH LOCAL and CASCADED CHECK OPTION
09:27
What is a Materialized View
09:29
Creating a materialized view
04:40
Drop a materialized view
03:19
Changing materialized view data
06:40
How to check if a materialized view is populated or not?
02:45
Refreshing data in materialize views
06:34
Why not use a table instead of materialized view?
04:15
The downsides of using materialized views
03:24
Using materialized view for websites page analysis
11:24
List all materialized views by a SELECT statement
01:33
List materialized views with no unique index
03:48
Quick queries for materialized views
00:11

Fun with subqueries

13 lectures
Introduction to subqueries
01:08
Subqueries with WHERE Clause
13:08
Subquery with IN operator
04:49
Subquery with JOINs
08:37
Get total revenues for all 'English' films.
00:25
Order entries in UNION without using ORDER BY
04:12
Subquery with an alias
01:23
A SELECT without a FROM
02:41
Correlated Queries
11:58
SELECT.. IN (Subquery)
07:03
Using ANY with subquery
07:44
Using ALL with subquery
08:15
Subquery using EXISTS
04:51

Common Table Expressions (CTE)

7 lectures
Introduction to CTEs
05:44
CTE query examples
15:44
Combine CTE with a table
06:59
Get the total revenues for each director
00:18
Simultaneously DELETE INSERT via CTE
08:17
Recursive CTEs
04:45
Parent-child relationship via recursive CTE
19:47

Grouping sets

4 lectures
Introduction to summarization
03:31
Subtotals on group sets
11:59
Adding subtotal with ROLLUP
07:33
Using GROUPING with ROLLUP
13:54

Window Functions

42 lectures
Introduction to Window Functions
03:35
Analyze Global Trades Data
08:21
Using aggregate functions
09:00
GROUP BY ROLLUP
05:24
ROLLUP - Grouping multiple columns
04:09
GROUP BY CUBE
05:05
GROUP BY GROUPING SETS
03:36
Query performance check
05:07
Using FILTER clause
05:26
Using Window Functions
04:43
Partitioning the data
04:37
Set data into millions format
02:44
Ordering inside window
08:18
Sliding dynamic windows
08:05
Understanding window frames
21:39
ROWS and RANGE indicators
10:59
Using WINDOW
08:42
Using WINDOW with ROWS BETWEEN
03:21
RANK and DENSE_RANK functions
05:00
NTILE function
09:00
LEAD and LAG functions
14:23
FIRST_VALUE, LAST_VALUE and NTH_value functions
14:27
ROW_NUMBER function
09:56
Finding Correlations
05:51
ROW_NUMBER() - Window with Partition datasets
09:45
ROW_NUMBER() - Reverse fields with order by
02:23
ROW_NUMBER() - Find Nth record
03:16
ROW_NUMBER() - Find DISTINCT with subquery
05:00
ROW_NUMBER() - Pagination technique
06:28
Using OVER() to calculate percentage
06:00
Calculate difference compared to average
05:50
Calculating cumulative totals
05:08
Using LEAD to compare with next values
05:36
Comparing with least number
09:33
Window Function Summary
04:23
Planning tips on using Window Functions
06:51
Difference between RANK and DENSE_RANK
03:00
Getting RANK to compute group and global ranks
04:58
Using PARTITION BY for group averages
06:21
Using WITH clause to create your own data
04:15
Using WITH clause to set ORDER BY values
06:39
DELETE and INSERT in one query using WITH
05:21

Using regular expressions for text patterns

9 lectures
Text to structured data
06:18
Regular expressions notations table
09:00
SIMILAR TO operator
06:42
POSIX regular expressions
04:56
SUBSTRING with regular expressions
17:53
REGEXP_MATCHES Function
09:52
REGEXP_REPLACE Function
08:00
REGEXP_SPLIT_TO_TABLE Function
01:29
REGEXP_SPLIT_TO_ARRAY function
02:44

Powerful text searches in PostgreSQL

7 lectures
The Good Ol' Text Search
09:07
Introducing tsvector
11:37
Using tsquery with operators
15:45
Full text search within a table
14:38
Setup presidents speeches data
07:40
Analyzing presidents speeches text data
07:43
Ranking and Normalizing text results
05:32

Table Partitions

17 lectures
What is a partition
05:48
When a partition can be used?
18:24
Table inheritance
16:11
Partition types
02:40
Partition by Range
19:21
Partition by List
23:31
Partition by Hash
13:57
DEFAULT Partition
06:45
Multi-level partitioning
13:55
Partition maintenance
05:06
ALTERing the partition bounds
09:22
Partition Indexes
07:11
Partition pruning
03:58
Determining a field for partition over
07:42
Sizing the partition
05:56
Partitioning Advantages
04:54
Common partitioning mistakes
04:42

Server Programming

6 lectures
PostgreSQL as a development platform?
09:47
Procedural languages
05:19
Keep the data on the server!
05:47
Functions vs stored procedures
03:52
User-defined functions
03:30
Structure of a function
02:35

Functions with SQL language

19 lectures
Creating our first SQL function
10:01
Introducing dollar quoting
03:45
Function returning no values
05:06
Function returning a single value
12:23
Function returning a single value Part 2
05:37
Function returning a single value Part 3
03:42
Function using parameters
14:03
Function using parameters Part 2
05:20
Function using parameters Part 3
14:42
Function using parameters Part 4
13:18
Function returning a composite
13:09
Function returning multiple rows
10:21
Function returning a table
02:54
Function - order matters!
03:09
Function as a table source
10:37
Function parameter modes
03:09
Function parameters with default values
11:19
Function based on views
06:56
Drop a function
04:18

Functions with PL/pgSQL language

30 lectures
Introduction to PL/pgSQL language
02:09
PL/pgSQL vs SQL
02:40
Structure of a PL/pgSQL function
05:14
PL/pgSQL block structure
02:18
Declaring variables
06:02
Declaring variables via ALIAS FOR
01:40
Declaring variables in function
05:39
Variable initializing timing
02:27
Copying data types
03:07
Assigning variables from query
10:02
Using IN, OUT without RETURNS
04:35
Variables in block and subblock
04:50
How to return query results
05:48
Control Structures - IF statement
09:06
Using IF with table data
06:05
CASE Statement
12:37
Searched CASE statement
12:11
LOOP statement
06:19
FOR Loops
05:49
FOR Loops iterate over result set
04:27
CONTINUE statement
05:19
FOREACH loop with arrays
04:07
WHILE loop
13:38
Using RETURN QUERY
11:36
Returning a table
04:37
Using RETURN NEXT
14:48
Error and exception handling
06:28
Exception - Too many rows
05:39
Using SQLSTATE codes for exception handling
04:19
Exception with data exception errors
07:21

Exploring Stored Procedures

5 lectures
Functions vs Stored Procedures
05:36
Create a transactions
08:15
Understanding the use of stored procedures
05:07
Returning a value
04:10
Drop a procedure
01:54

PostgreSQL Triggers

24 lectures
Introduction to triggers
00:40
What is a trigger
10:40
Types of triggers
02:34
Trigger table
02:54
Pros and Cons of triggers
10:43
Trigger key points
02:45
Steps to create a trigger
05:28
Data auditing with a trigger
11:40
Bind a function to a table with trigger
08:38
Modify data at INSERT event
11:56
View triggers variables
04:53
Disallow DELETE on a table
10:07
Disallow TRUNCATE on a table
05:16
Creating an audit trigger
20:42
Creating conditional triggers
15:05
Disallow data change on primary key
05:36
Use triggers very cautiously
03:30
What is an event trigger
01:50
Event triggers usage scenarios
03:21
Creating event triggers
04:36
Event trigger events and variables
04:25
Creating an audit event trigger
14:57
Prevent schema changes
08:52
Dropping a trigger
00:57

Using Cursors

10 lectures
Understanding row by row operations
06:40
Cursors and procedural languages
04:30
Steps to create a cursor
02:46
Creating a cursor
08:38
Opening a cursor
09:39
Using a cursor
06:33
Updating data
01:42
Closing a cursor
01:22
Creating a PL/PGSQL Cursor
10:31
Using a parametric cursor via function
14:03

Creating Crosstab Reports

13 lectures
What is a crosstab report
04:02
Installing the tablefunc extension
01:52
Creating a crosstab report - Student Rankings
14:37
Orders matters in crosstab reports
03:02
Pivoting with Rainfalls data
11:02
Pivoting rows and columns
04:02
Matrix report via a query
09:55
Aggregate over filter
05:48
Static to dynamic pivots
06:50
Dynamic pivot query via JSON
06:58
Dynamic pivot table columns
17:16
Interactive client-side pivot
06:05
Handling missing values
10:31

Internationalization

2 lectures
Global Character Set Support
10:14
Client and Server Encoding
11:54

PostgreSQL Transactions

9 lectures
What is a transaction?
08:35
How SQL protect database during transaction
05:20
ACID Database
08:40
Transaction setup
02:21
Transaction analysis
13:44
How to fix aborted transaction
03:44
How to fix transactions on crash
05:32
Partial transaction rollback with savepoints
03:36
Using SAVEPOINT with transaction
05:40

Using Northwind Database

18 lectures
Using Northwind database
01:11
Orders shipping to USA or France
02:08
Total numbers of orders shipped to USA or France
01:48
Orders shipped to latin america
03:21
Show total order amount for each order
04:01
First the oldest and latest order date
02:26
Total products in each categories
03:55
List products that needs re-ordering
03:41
Freight analysis
09:59
Customers with no orders
05:52
Top customers with total orders amount
04:57
Orders with many lines of ordered items
02:49
Orders with double entry line items
10:16
Late shipped orders by employees
10:52
Countries with customers or suppliers
02:50
Countries with customers or suppliers - Using CTE
06:15
Customers with multiple orders
07:21
First order from each country
07:48

Using Human Resource (hr) database

24 lectures
Human Resources Database Structure
03:14
Quick overview of all tables
05:31
Quick overview of all tables Part 2
03:20
Get highest, lowest, total, and average salaries of employees
03:49
Difference b/w highest and lowest salaries by job_id
01:25
Get lowest paid salaries by each manager
02:15
Average salary for each department with more than 10 employees.
02:36
Average salary for each post excluding programmer
01:59
Maximum salary for each post where salary is at or above $5000
02:28
Using an alias name with columns
01:40
Compute 15% of salary for all employees
02:52
To list all employees IDs within each job_id group
01:22
Discard characters from employees email address
05:42
List all employees with first_name starts with letters "A", "C" or "M"
03:12
String manipulation with upper, lower and initcap functions
02:05
Using SUBSTRING to get portion of string data
00:52
Get unique designations in employees table
02:09
Select employees with particular department id only
02:15
Select employees not in range
03:21
Find Letter "C" in last_name at 3rd or greater position
02:36
Update phone_number with your strings
02:57
Get the monthly salary of each employees
02:11
Calculate average salary with total number of employees
01:51
find employees whose names contains exactly six characters
02:29

Exploring Stock Markets Data

4 lectures
Introduction To stock market data
00:15
Select first or last records in a table
03:49
Get first or last record per each group
04:16
How to calculate cube root in PostgreSQL?
01:55

Managing PostgreSQL security

15 lectures
Security concepts and and levels
04:18
Instance Level Security
05:37
Instance Level Security - Add users to roles
04:30
Use pgAdmin to create roles
04:06
Database Level Security
05:33
Schema Level Security
11:13
Table Level Security
11:04
Column Level Security
08:32
Row Level Security
14:52
Using CURRENT_USER with RLS
06:14
Row level policy for application users
05:03
DROP a policy
02:21
Inspecting permissions
02:50
Row Level Security Performance
04:16
Encrypted data in columns
07:19

PostgreSQL Utilities - psql

14 lectures
Connect to local or remote database
04:06
Switch connection to a new database
01:45
List all databases and tables
03:24
List all table space, schemas, and indices
03:32
List all sequences, roles, data types, domain data types
07:11
Describe a table
01:01
Edit SQL commands in editor
01:17
Display command history, run commands from a file
03:05
Built-in syntax reference for commands
01:05
Represent NULL values on psql terminal
03:50
Make table layout funkey!
02:10
Repeatedly Execute a Query
01:41
Represent NULL Visibly
03:50
Turn on query execution time
02:44

PostgreSQL Internals

11 lectures
List database users, and database sizes with and without indexes
04:10
List all database and schemas
04:20
List all tables and views
08:24
List all columns from a table
02:45
View system metadata via system information functions
02:33
View privileges information across tables
08:15
Using system Administration functions
05:24
Show all running queries
04:16
Terminate running and IDLE process
02:54
How to check live and dead rows in tables
01:49
File layout of PostgreSQL Tables
09:21

Managing Tables

13 lectures
Using SELECT INTO to create a new table with joins
12:06
Duplicate a table with or without data
03:08
Import data from CSV files
06:59
Export Data to CSV files
02:42
Deleting duplicate records
07:26
Backup your databases regularly
00:32
Test queries on a test database
00:29
Not documenting your process?
00:25
Database operations and table size
05:57
Tracking table size
07:26
PostgreSQL autovacuum proces
10:28
Recovering unused space with VACUUM
09:55
Generated Columns
10:44

Extended PostgreSQL

2 lectures
Create a custom index method
22:36
Create a user-defined aggregate function
10:56

Thank You!

4 lectures
Your feedback is very valuable
00:27
Upcoming course - Advanced Time Series Analysis
00:11
Bonus Courses
00:15
Thank You!
00:48

Đánh giá của học viên

Chưa có đánh giá
Course Rating
5
0%
4
0%
3
0%
2
0%
1
0%

Bình luận khách hàng

Viết Bình Luận

Bạn đánh giá khoá học này thế nào?

Khoá học liên quan

How To Create 2D Defender Game With Unity & C#

How To Create 2D Defender Game With Unity & C#

749.000 VND
50.000 VND
The Complete UML Course (2024): Learn to Design UML Diagrams

The Complete UML Course (2024): Learn to Design UML Diagrams

25 VND
50.000 VND
Master Network Automation with Python for Network Engineers

Master Network Automation with Python for Network Engineers

1.699.000 VND
50.000 VND
Unity Mobile Game Development - Exterminator

Unity Mobile Game Development - Exterminator

1.499.000 VND
50.000 VND
Mastering CSS Grid 2023 - With 3 cool projects

Mastering CSS Grid 2023 - With 3 cool projects

99 VND
50.000 VND
Django 3 - Full Stack Websites with Python Web Development

Django 3 - Full Stack Websites with Python Web Development

1.699.000 VND
50.000 VND
Appium -Mobile Testing (Android/IOS) from Scratch+Frameworks

Appium -Mobile Testing (Android/IOS) from Scratch+Frameworks

95 VND
50.000 VND
Appium - Mobile Testing with Latest 2.0 and Live Projects

Appium - Mobile Testing with Latest 2.0 and Live Projects

70 VND
50.000 VND
Deep Learning for Object Detection with Python and PyTorch

Deep Learning for Object Detection with Python and PyTorch

899.000 VND
50.000 VND
Full Android Development Masterclass | 14 Real Apps-46 Hours

Full Android Development Masterclass | 14 Real Apps-46 Hours

1.699.000 VND
50.000 VND
Go Java Full Stack with Spring Boot and Angular

Go Java Full Stack with Spring Boot and Angular

90 VND
50.000 VND
Machine Learning Practical Workout | 8 Real-World Projects

Machine Learning Practical Workout | 8 Real-World Projects

79 VND
50.000 VND
WordPress 2024: The Complete WordPress Website Course

WordPress 2024: The Complete WordPress Website Course

90 VND
50.000 VND
Android App Development Master Course with Java | Android

Android App Development Master Course with Java | Android

99 VND
50.000 VND
Unreal Engine 5: Soulslike Melee Combat System

Unreal Engine 5: Soulslike Melee Combat System

1.399.000 VND
50.000 VND
ASP.NET Core Web Application Using Razor Pages

ASP.NET Core Web Application Using Razor Pages

1.699.000 VND
50.000 VND
image

Đăng ký get khoá học Udemy - Unica - Gitiho giá chỉ 50k!

Get khoá học giá rẻ ngay trước khi bị fix.

Get khoá học cho tôi