CF

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

67h 20m 55s
English
Paid

Unlock the full potential of PostgreSQL with this comprehensive bootcamp. From subqueries to recursive CTEs, server programming with SQL and PL/pgSQL, stored procedures, and window functions, this course covers it all. Whether you're building your next big project or enhancing your SQL skills, this course will help you master PostgreSQL from the ground up. Sign up today and take your database expertise to the next level!

Course Overview

This course is designed to take you from beginner to advanced levels step-by-step. Explore a vast array of topics through 50+ sections and 60+ hours of detailed instruction:

Core SQL Skills

  • Apply powerful SQL commands to efficiently store, update, and retrieve data.
  • Engage in a step-by-step walkthrough for simple to complex SQL queries.
  • Learn how to create a database from scratch, including table creation and modification.
  • Master the use of UPSERT to perform INSERT and UPDATE simultaneously.
  • Query data using ORDER BY, logical operators, and filters.

Data Types and Conversions

  • Gain an understanding of PostgreSQL's various data types through detailed examples.
  • Perform data type conversions using CAST and conversion functions.
  • Create your own user-defined data types.
  • Manage table and column constraints, create multiple composite constraints, and ensure data integrity.
  • Develop your own custom sequences.

Advanced Query Techniques

  • Parse data with string functions and aggregate information using aggregate functions.
  • Handle date/time data types, intervals, and timezones.
  • Utilize GROUP BY and HAVING for data grouping and conditional filtering.
  • Master joining multiple tables with various join techniques.
  • Combine queries using UNION, INTERSECT, and EXCEPT.

PostgreSQL Schemas and Security

  • Understand and create PostgreSQL Schemas, manage ownership, and mitigate security risks.
  • Construct and query arrays and ranges with conditional filters.
  • Store and index data using JSONB objects.
  • Optimize query performance with indexes, nodes, and outputs analysis.

Advanced PostgreSQL Features

  • Create and refresh regular and materialized views.
  • Develop and execute subqueries from simple to advanced levels.
  • Build Common Table Expressions (CTEs) and recursive CTEs.
  • Leverage group sets and advanced window functions.

Additional Features and Tools

  • Search using regular expressions and perform powerful text searches within PostgreSQL.
  • Explore table partitioning techniques and implement them effectively.
  • Engage in server programming with SQL and PL/pgSQL.
  • Create and manage stored procedures and triggers.
  • Utilize cursors and execute pSQL utility commands.
  • Generate crosstab reports and implement global language data storage for internationalization.
  • Run queries with PostgreSQL transactions and delve into PostgreSQL security management.
  • Uncover the inner workings of PostgreSQL internals and how data is stored.
  • Execute hundreds of queries on sample databases like HR, Northwind, Stock Markets, and World Trades.

Join now to explore all these topics in detail and become an expert in PostgreSQL!

About the Author: Udemy

Udemy thumbnail

Udemy is the largest open marketplace for online courses on the internet. Founded in 2010 by Eren Bali, Oktay Caglar, and Gagan Biyani and headquartered in San Francisco, the company went public on the Nasdaq in 2021 under the ticker UDMY. The platform hosts well over two hundred thousand courses across software development, IT and cloud, data science, design, business, marketing, and creative skills, taught by tens of thousands of independent instructors. Roughly seventy million learners use it worldwide, and the corporate arm — Udemy Business — supplies a curated subset of that catalog to enterprise customers.

Because Udemy is a marketplace rather than a single editorial publisher, the catalog is uneven by design. The strongest material lives in the long-form, project-based courses authored by working engineers — full-stack JavaScript, React, Node.js, Python data science, AWS, Docker and Kubernetes, mobile development with Flutter and React Native, and cloud certification preparation. The CourseFlix listing under this source is the slice of that catalog that has been mirrored here for offline-friendly viewing, organized by topic and updated as new releases land. Pricing on Udemy itself swings dramatically with the site's near-permanent sales, which is why the platform is best treated as a deep reference catalog: pick instructors with strong reviews and a track record of updating their material rather than buying on the headline price alone.

Watch Online 610 lessons

This is a demo lesson (10:00 remaining)

You can watch up to 10 minutes for free. Subscribe to unlock all 610 lessons in this course and access 10,000+ hours of premium content across all courses.

View Pricing
0:00
/
#1: Course Introduction
All Course Lessons (610)
#Lesson TitleDurationAccess
1
Course Introduction Demo
01:07
2
Installing PostgreSQL on Mac
02:18
3
Installing PostgreSQL on Windows
06:01
4
Configure pgAdmin 4 client
02:02
5
Creating a Database User
03:55
6
Creating a Database
02:31
7
Running a query in pgAdmin tool
02:12
8
Install sample data files on server
02:32
9
Install Human Resources (hr) database
02:22
10
Install sample stocks market data
02:05
11
Install northwind database
05:04
12
Drop a database
03:28
13
Movie Database Structure
03:39
14
Creating the movie database and a actors table
06:19
15
Creating the directors table
03:30
16
Creating the movies table with a foreign key
05:55
17
Creating the movies_revenues table
03:45
18
Creating a Junction table with movies and actors tables
04:25
19
Install sample data for 'movies' database
05:51
20
Using pgAdmin - Create and modify a table
05:37
21
Using pgAdmin - View table structure, and create column
05:29
22
Using pgAdmin - Rename, delete and change the data type of a column
06:45
23
Deleting tables from a database
02:49
24
Insert a data into table
04:47
25
Insert multiple records into a table
02:36
26
Insert a data that had quotes
02:02
27
Use RETURNING to get info on added rows
02:52
28
Update data in a table
03:26
29
Updating a row and returning the updated row
02:14
30
Updating all records in a table
03:11
31
Delete data from a table
03:08
32
Using UPSERT
10:00
33
Select all data from a table
06:32
34
Selecting specific columns from a table
03:44
35
Adding Aliases to columns in a table
09:24
36
Using SELECT statement for expressions
03:16
37
Using ORDER BY to sort records
07:55
38
Using ORDER BY with alias column name
03:15
39
Using ORDER BY to sort rows by expressions
03:09
40
Using ORDER BY with column name or column number
03:20
41
Using ORDER BY with NULL values
05:54
42
Using DISTINCT for selecting distinct values
05:28
43
Comparison, Logical and Arithmetic operators
02:20
44
AND operator
07:27
45
OR operator
03:24
46
Combining AND, OR operators
04:44
47
What goes before and after WHERE clause
02:49
48
Execution order with AND, OR operators
01:54
49
Can we use column aliases with WHERE?
02:21
50
Order of execution of WHERE, SELECT and ORDER BY clauses
01:34
51
Using Logical operators
16:16
52
Using LIMIT and OFFSET
16:52
53
Using FETCH
09:47
54
Using IN, NOT IN
10:15
55
Using BETWEEN and NOT BETWEEN
11:32
56
Using LIKE and ILIKE
17:19
57
Using IS NULL and IS NOT NULL keywords
12:37
58
Concatenation techniques
08:21
59
Concatenation with ||, CONCAT and CONCAT_WS
04:37
60
Boolean
13:15
61
CHAR, VARCHAR and TEXT
18:20
62
NUMERIC
10:15
63
DECIMALS
09:54
64
Selecting Numbers data types
05:52
65
Date/Time data types
03:44
66
DATE
06:20
67
TIME
14:58
68
TIMESTAMP and TIMESTAMPTZ
12:50
69
UUID
11:39
70
Array
07:08
71
hstore
08:01
72
JSON
12:30
73
Network Addresses
14:01
74
Creating sample database 'mydata', adding columns
04:39
75
Modify Table Structures, Add/Modify Columns
09:00
76
Add constraints to columns
10:28
77
What is a data type conversion
05:47
78
Using CAST for data conversions
11:34
79
Implicit to Explicit conversions
04:13
80
Table data conversion
07:16
81
to_char
08:07
82
to_number
05:19
83
to_date
05:34
84
to_timestamp
04:48
85
CREATE DOMAIN - Create a DOMAIN data type, create an address
06:46
86
CREATE DOMAIN - Create a data type for a positive number
02:17
87
CREATE DOMAIN - Create a postal code validation data type
03:01
88
CREATE DOMAIN - Create a domain data type for an email validation
03:46
89
CREATE DOMAIN - Create a Enum or Set of values domain data type
04:36
90
CREATE DOMAIN - Get the list of all DOMAIN data types
02:18
91
CREATE DOMAIN - How to drop a domain data type
04:45
92
CREATE TYPE - Create a composite address object
06:45
93
CREATE TYPE - Create a composite inventory_item data type
04:34
94
CREATE TYPE - Create an ENUM data type and see how to drop a data type
04:34
95
ALTER TYPE - Alter a composite data type, change schema and more..
04:18
96
ALTER TYPE - Alter an ENUM data type
03:23
97
Update an ENUM data in production server
08:14
98
An ENUM with a DEFAULT value in a table
03:40
99
Create a type if not exists using a PL/pgSQL function
02:00
100
Introduction to constraints
04:17
101
NOT NULL constraint
11:31
102
UNIQUE constraint
15:18
103
DEFAULT constraint
05:35
104
PRIMARY KEY Constraints
21:45
105
PRIMARY KEY Constraints on multiple columns
13:49
106
FOREIGN KEY Constraints
06:13
107
Tables without foreign key constraints
07:43
108
Creating foreign key constraints
07:50
109
Foreign keys maintains referential data integrity
07:45
110
Drop a constraint
02:14
111
Add or update foreign key constraint on existing table
03:06
112
CHECK constraint - An Introduction
01:33
113
CHECK constraint - Add to new table
10:13
114
CHECK constraint - Add, Rename, Drop on existing table
10:34
115
Create a sequence, advance a sequence, get current value, set value
07:21
116
Restart, rename a sequence, and use pgAdmin to alter a sequence
04:19
117
Create a sequence with START WITH, INCREMENT, MINVALUE and MAXVALUE
03:17
118
Create a sequence using a specific data type
02:43
119
Creating a descending sequence, and CYCLE sequence
04:45
120
Delete a sequence
01:29
121
Attach a sequence to a table column
08:54
122
List all sequences in a database
01:27
123
Share one sequence between two tables
04:16
124
Create an alphanumeric sequence
07:51
125
UPPER, LOWER and INITCAP
04:38
126
LEFT and RIGHT
08:08
127
REVERSE
02:09
128
SPLIT_PART
06:22
129
TRIM, BTRIM, LTRIM and RTRIM
06:39
130
LPAD and RPAD
05:54
131
LENGTH
03:53
132
POSITION
03:20
133
STRPOS
04:56
134
SUBSTRING
03:52
135
REPEAT
02:29
136
REPLACE
04:10
137
Counting results via COUNT function
07:36
138
Sum with SUM function
09:13
139
MIN and MAX functions
13:33
140
GREATEST AND LEAST functions
05:09
141
Average with AVG function
12:53
142
Combining Columns using Mathematical operators
11:07
143
Datetimes data types
09:39
144
System Month Date settings
03:08
145
Time of day formats and inputs
06:10
146
Strings to Dates conversions
11:19
147
Using TO_TIMESTAMP function
09:44
148
Formatting Dates
10:34
149
Date construction functions
06:29
150
Using MAKE_INTERVAL function
06:35
151
Using MAKE_TIMESTAMPTZ function
09:37
152
Date Value Extractors functions
05:56
153
Using math operators with dates
09:25
154
OVERLAPS Operator
04:04
155
Date / Time Functions
05:02
156
PostgreSQL Date / Time Functions
04:24
157
AGE function
03:38
158
CURRENT_DATE function
01:19
159
CURRENT_TIME function
03:52
160
Date accuracy with EPOCH
07:03
161
Using Date, time, timestamp in tables
08:15
162
View and set timezones
03:30
163
How to handle timezones
10:43
164
date_part function
10:44
165
date_trunc function
08:58
166
Using GROUP BY
11:55
167
Using GROUP BY with multiple columns, ORDER BY
14:46
168
Order of execution in GROUP BY clause
01:34
169
Using HAVING
11:38
170
Order of execution in HAVING clause
01:07
171
HAVING vs WHERE
05:40
172
Handling NULL values with GROUP BY
10:10
173
INNER joins
20:19
174
INNER joins with USING
08:56
175
INNER joins with filter data Part 1
10:19
176
INNER joins with filter data Part 2
03:59
177
INNER joins with filter data Part 3
05:29
178
INNER joins with different data type columns
05:12
179
LEFT joins Part 1
09:34
180
LEFT joins Part 2
07:28
181
LEFT joins Part 3
08:02
182
LEFT joins Part 4
05:50
183
RIGHT joins
09:44
184
RIGHT joins Part 2
03:15
185
FULL Joins
05:05
186
Joining multiple tables
09:05
187
Self Joins Part 1
08:30
188
Self Joins Part 2
02:59
189
CROSS Joins
10:35
190
Natural Joins Part 1
05:24
191
Natural Joins Part 2
02:15
192
Append tables with different columns
13:44
193
Combine results sets with UNION
11:06
194
UNION with filters and conditions
10:27
195
UNION tables with different number of columns
03:29
196
INTERSECT with tables
04:27
197
EXCEPT with tables
05:49
198
What is a Schema?
06:09
199
Schema Operations (Add/Alter/Delete schemas)
07:25
200
Schema Hierarchy
06:22
201
Move a table to a new schema
02:54
202
Schema search path
14:11
203
Alter a schema ownership
02:08
204
Duplicate a schema along with all data
06:20
205
What is a system catalog schema?
05:21
206
Compare tables and columns in two schemas
07:41
207
Schemas and Privileges
06:30
208
Constructing arrays and ranges
07:23
209
Using comparison operators
07:44
210
Using inclusion operators
04:17
211
Array constructions
05:15
212
Array metadata functions
08:42
213
Array search functions
06:33
214
Array modification functions
06:47
215
Array comparison with IN, ALL, ANY and SOME
13:03
216
Formatting and converting an array
09:04
217
Using arrays in tables
04:34
218
Insert data into arrays
05:51
219
Query array data
07:10
220
Modifying array data
03:08
221
Array Dimensions are ignored!
04:12
222
Display all array elements
03:53
223
Using Multi-dimensional arrays
10:38
224
Array vs JSONB
14:04
225
What is JSON?
04:22
226
JSON Syntax
09:07
227
JSON and JSONB data types
04:10
228
Exploring JSON objects
06:31
229
Create our first table with JSONB data type
07:49
230
Update and Delete JSON Data
12:47
231
Create JSON from tables
05:05
232
Use json_agg to aggregate data
05:24
233
Build a JSON array
04:40
234
Creating a document from data
06:17
235
Null Values in JSON documents
05:05
236
Getting information from JSON documents
06:26
237
The Existence Operator
02:55
238
The Containment Operator
04:07
239
JSON search with PostgreSQL functions
04:09
240
Indexing on JSONB
11:30
241
What is an index?
03:57
242
Create an index
15:48
243
Create unique indexes
11:11
244
List all indexes
04:18
245
Size of the table index
05:19
246
List counts of all indexes
04:35
247
Drop a index
03:35
248
SQL Statement execution process
04:36
249
SQL statement execution stages
06:36
250
The query optimizer
09:31
251
Optimizer node types
05:50
252
Sequential Nodes
06:24
253
Index Nodes
07:52
254
Join Nodes
06:47
255
Index Types - B-Tree Index
04:48
256
Hash Index
04:45
257
BRIN index
02:23
258
GIN Index
01:56
259
The EXPLAIN statement
14:32
260
EXPLAIN output options
02:56
261
Using EXPLAIN ANALYZE
09:11
262
Understanding query cost model
13:45
263
Index are not free
12:04
264
Indexes for sorted output
08:52
265
Using multiple indexes on a single query
07:52
266
Execution plans depends on input values
07:03
267
Using organized vs random data
20:11
268
Try to use index only scan
04:08
269
Partial indexes
11:08
270
Expression Index
16:26
271
Adding data while indexing
03:12
272
Invalidating an index
07:55
273
Rebuilding an index
07:10
274
Introduction to views
07:21
275
Creating a view
10:53
276
Rename a view
04:29
277
Delete a view
01:55
278
Using filters with views
07:09
279
A view with UNION of multiple tables
04:09
280
Connecting multiple tables with a single view
07:18
281
Re-arrange columns in a view
03:01
282
Delete a column in a view
01:52
283
Add a column in a view
02:19
284
Regular views are dynamic
02:37
285
What is an updatable view?
03:55
286
An updatable view with CRUD operations
05:28
287
Updatable views using WITH CHECK OPTION
07:54
288
Updatable views using WITH LOCAL and CASCADED CHECK OPTION
09:28
289
What is a Materialized View
09:30
290
Creating a materialized view
04:41
291
Drop a materialized view
03:21
292
Changing materialized view data
06:41
293
How to check if a materialized view is populated or not?
02:46
294
Refreshing data in materialize views
06:35
295
Why not use a table instead of materialized view?
04:16
296
The downsides of using materialized views
03:25
297
Using materialized view for websites page analysis
11:25
298
List all materialized views by a SELECT statement
01:34
299
List materialized views with no unique index
03:49
300
Subqueries with WHERE Clause
13:09
301
Subquery with IN operator
04:50
302
Subquery with JOINs
08:38
303
Order entries in UNION without using ORDER BY
04:13
304
Subquery with an alias
01:24
305
A SELECT without a FROM
02:42
306
Correlated Queries
11:59
307
SELECT.. IN (Subquery)
07:04
308
Using ANY with subquery
07:45
309
Using ALL with subquery
08:16
310
Subquery using EXISTS
04:52
311
Introduction to CTEs
05:45
312
CTE query examples
15:45
313
Combine CTE with a table
07:00
314
Simultaneously DELETE INSERT via CTE
08:18
315
Recursive CTEs
04:46
316
Parent-child relationship via recursive CTE
19:48
317
Introduction to summarization
03:32
318
Subtotals on group sets
12:00
319
Adding subtotal with ROLLUP
07:34
320
Using GROUPING with ROLLUP
13:55
321
Introduction to Window Functions
03:36
322
Analyze Global Trades Data
08:23
323
Using aggregate functions
09:01
324
GROUP BY ROLLUP
05:25
325
ROLLUP - Grouping multiple columns
04:10
326
GROUP BY CUBE
05:06
327
GROUP BY GROUPING SETS
03:37
328
Query performance check
05:08
329
Using FILTER clause
05:27
330
Using Window Functions
04:44
331
Partitioning the data
04:38
332
Set data into millions format
02:45
333
Ordering inside window
08:19
334
Sliding dynamic windows
08:06
335
Understanding window frames
21:41
336
ROWS and RANGE indicators
11:00
337
Using WINDOW
08:43
338
Using WINDOW with ROWS BETWEEN
03:22
339
RANK and DENSE_RANK functions
05:01
340
NTILE function
09:01
341
LEAD and LAG functions
14:25
342
FIRST_VALUE, LAST_VALUE and NTH_value functions
14:28
343
ROW_NUMBER function
09:57
344
Finding Correlations
05:52
345
ROW_NUMBER() - Window with Partition datasets
09:46
346
ROW_NUMBER() - Reverse fields with order by
02:24
347
ROW_NUMBER() - Find Nth record
03:17
348
ROW_NUMBER() - Find DISTINCT with subquery
05:01
349
ROW_NUMBER() - Pagination technique
06:29
350
Using OVER() to calculate percentage
06:01
351
Calculate difference compared to average
05:52
352
Calculating cumulative totals
05:09
353
Using LEAD to compare with next values
05:37
354
Comparing with least number
09:34
355
Window Function Summary
04:24
356
Planning tips on using Window Functions
06:52
357
Difference between RANK and DENSE_RANK
03:01
358
Getting RANK to compute group and global ranks
04:59
359
Using PARTITION BY for group averages
06:22
360
Using WITH clause to create your own data
04:16
361
Using WITH clause to set ORDER BY values
06:40
362
DELETE and INSERT in one query using WITH
05:23
363
Text to structured data
06:19
364
Regular expressions notations table
09:01
365
SIMILAR TO operator
06:43
366
POSIX regular expressions
04:57
367
SUBSTRING with regular expressions
17:54
368
REGEXP_MATCHES Function
09:53
369
REGEXP_REPLACE Function
08:01
370
REGEXP_SPLIT_TO_TABLE Function
01:30
371
REGEXP_SPLIT_TO_ARRAY function
02:45
372
The Good Ol' Text Search
09:08
373
Introducing tsvector
11:38
374
Using tsquery with operators
15:46
375
Full text search within a table
14:39
376
Setup presidents speeches data
07:41
377
Analyzing presidents speeches text data
07:45
378
Ranking and Normalizing text results
05:33
379
What is a partition
05:50
380
When a partition can be used?
18:25
381
Table inheritance
16:12
382
Partition types
02:41
383
Partition by Range
19:23
384
Partition by List
23:32
385
Partition by Hash
13:58
386
DEFAULT Partition
06:46
387
Multi-level partitioning
13:57
388
Partition maintenance
05:07
389
ALTERing the partition bounds
09:23
390
Partition Indexes
07:12
391
Partition pruning
03:59
392
Determining a field for partition over
07:43
393
Sizing the partition
05:57
394
Partitioning Advantages
04:55
395
Common partitioning mistakes
04:43
396
PostgreSQL as a development platform?
09:48
397
Procedural languages
05:20
398
Keep the data on the server!
05:48
399
Functions vs stored procedures
03:53
400
User-defined functions
03:31
401
Structure of a function
02:36
402
Creating our first SQL function
10:02
403
Introducing dollar quoting
03:47
404
Function returning no values
05:07
405
Function returning a single value
12:24
406
Function returning a single value Part 2
05:38
407
Function returning a single value Part 3
03:43
408
Function using parameters
14:04
409
Function using parameters Part 2
05:21
410
Function using parameters Part 3
14:43
411
Function using parameters Part 4
13:19
412
Function returning a composite
13:10
413
Function returning multiple rows
10:23
414
Function - order matters!
03:10
415
Function returning a table
02:55
416
Function as a table source
10:38
417
Function parameter modes
03:10
418
Function parameters with default values
11:20
419
Function based on views
06:57
420
Drop a function
04:19
421
Introduction to PL/pgSQL language
02:10
422
PL/pgSQL vs SQL
02:41
423
Structure of a PL/pgSQL function
05:16
424
PL/pgSQL block structure
02:19
425
Declaring variables
06:03
426
Declaring variables via ALIAS FOR
01:41
427
Declaring variables in function
05:40
428
Variable initializing timing
02:28
429
Copying data types
03:08
430
Assigning variables from query
10:03
431
Using IN, OUT without RETURNS
04:36
432
Variables in block and subblock
04:51
433
How to return query results
05:49
434
Control Structures - IF statement
09:07
435
Using IF with table data
06:06
436
CASE Statement
12:38
437
Searched CASE statement
12:12
438
LOOP statement
06:20
439
FOR Loops
05:50
440
FOR Loops iterate over result set
04:28
441
CONTINUE statement
05:20
442
FOREACH loop with arrays
04:08
443
WHILE loop
13:39
444
Using RETURN QUERY
11:37
445
Returning a table
04:38
446
Using RETURN NEXT
14:49
447
Error and exception handling
06:29
448
Exception - Too many rows
05:40
449
Using SQLSTATE codes for exception handling
04:20
450
Exception with data exception errors
07:22
451
Functions vs Stored Procedures
05:37
452
Create a transactions
08:16
453
Understanding the use of stored procedures
05:09
454
Returning a value
04:11
455
Drop a procedure
01:55
456
What is a trigger
10:41
457
Types of triggers
02:35
458
Trigger table
02:55
459
Pros and Cons of triggers
10:44
460
Trigger key points
02:46
461
Steps to create a trigger
05:29
462
Data auditing with a trigger
11:41
463
Bind a function to a table with trigger
08:39
464
Modify data at INSERT event
11:57
465
View triggers variables
04:54
466
Disallow DELETE on a table
10:08
467
Disallow TRUNCATE on a table
05:18
468
Creating an audit trigger
20:43
469
Creating conditional triggers
15:06
470
Disallow data change on primary key
05:37
471
Use triggers very cautiously
03:31
472
What is an event trigger
01:51
473
Event triggers usage scenarios
03:22
474
Creating event triggers
04:37
475
Event trigger events and variables
04:26
476
Creating an audit event trigger
14:58
477
Prevent schema changes
08:53
478
Dropping a trigger
00:58
479
Understanding row by row operations
06:41
480
Cursors and procedural languages
04:31
481
Steps to create a cursor
02:47
482
Creating a cursor
08:39
483
Opening a cursor
09:40
484
Using a cursor
06:34
485
Updating data
01:43
486
Closing a cursor
01:23
487
Creating a PL/PGSQL Cursor
10:32
488
Using a parametric cursor via function
14:04
489
What is a crosstab report
04:03
490
Installing the tablefunc extension
01:54
491
Creating a crosstab report - Student Rankings
14:39
492
Orders matters in crosstab reports
03:03
493
Pivoting with Rainfalls data
11:03
494
Pivoting rows and columns
04:03
495
Matrix report via a query
09:57
496
Aggregate over filter
05:49
497
Static to dynamic pivots
06:51
498
Dynamic pivot query via JSON
06:59
499
Dynamic pivot table columns
17:17
500
Interactive client-side pivot
06:06
501
Handling missing values
10:32
502
Global Character Set Support
10:15
503
Client and Server Encoding
11:55
504
What is a transaction?
08:36
505
How SQL protect database during transaction
05:21
506
ACID Database
08:41
507
Transaction setup
02:22
508
Transaction analysis
13:45
509
How to fix aborted transaction
03:45
510
How to fix transactions on crash
05:33
511
Partial transaction rollback with savepoints
03:37
512
Using SAVEPOINT with transaction
05:41
513
Using Northwind database
01:12
514
Orders shipping to USA or France
02:09
515
Total numbers of orders shipped to USA or France
01:49
516
Orders shipped to latin america
03:22
517
Show total order amount for each order
04:02
518
First the oldest and latest order date
02:27
519
Total products in each categories
03:56
520
List products that needs re-ordering
03:42
521
Freight analysis
10:01
522
Customers with no orders
05:53
523
Top customers with total orders amount
04:59
524
Orders with many lines of ordered items
02:50
525
Orders with double entry line items
10:17
526
Late shipped orders by employees
10:53
527
Countries with customers or suppliers
02:51
528
Countries with customers or suppliers - Using CTE
06:16
529
Customers with multiple orders
07:23
530
First order from each country
07:49
531
Human Resources Database Structure
03:15
532
Quick overview of all tables
05:32
533
Quick overview of all tables Part 2
03:21
534
Get highest, lowest, total, and average salaries of employees
03:50
535
Difference b/w highest and lowest salaries by job_id
01:26
536
Get lowest paid salaries by each manager
02:16
537
Average salary for each department with more than 10 employees.
02:37
538
Average salary for each post excluding programmer
02:00
539
Maximum salary for each post where salary is at or above $5000
02:29
540
Using an alias name with columns
01:41
541
Compute 15% of salary for all employees
02:53
542
To list all employees IDs within each job_id group
01:23
543
Discard characters from employees email address
05:43
544
List all employees with first_name starts with letters "A", "C" or "M"
03:13
545
String manipulation with upper, lower and initcap functions
02:06
546
Using SUBSTRING to get portion of string data
00:53
547
Get unique designations in employees table
02:10
548
Select employees with particular department id only
02:16
549
Select employees not in range
03:22
550
Find Letter "C" in last_name at 3rd or greater position
02:37
551
Update phone_number with your strings
02:58
552
Get the monthly salary of each employees
02:12
553
Calculate average salary with total number of employees
01:52
554
find employees whose names contains exactly six characters
02:30
555
Select first or last records in a table
03:50
556
Get first or last record per each group
04:17
557
How to calculate cube root in PostgreSQL?
01:56
558
Security concepts and and levels
04:19
559
Instance Level Security
05:38
560
Instance Level Security - Add users to roles
04:31
561
Use pgAdmin to create roles
04:08
562
Database Level Security
05:34
563
Schema Level Security
11:14
564
Table Level Security
11:06
565
Column Level Security
08:33
566
Row Level Security
14:54
567
Using CURRENT_USER with RLS
06:15
568
Row level policy for application users
05:04
569
DROP a policy
02:22
570
Inspecting permissions
02:51
571
Row Level Security Performance
04:17
572
Encrypted data in columns
07:20
573
Connect to local or remote database
04:08
574
Switch connection to a new database
01:46
575
List all databases and tables
03:25
576
List all table space, schemas, and indices
03:33
577
List all sequences, roles, data types, domain data types
07:12
578
Describe a table
01:02
579
Edit SQL commands in editor
01:18
580
Display command history, run commands from a file
03:06
581
Built-in syntax reference for commands
01:06
582
Represent NULL values on psql terminal
03:51
583
Make table layout funkey!
02:11
584
Repeatedly Execute a Query
01:42
585
Represent NULL Visibly
03:51
586
Turn on query execution time
02:45
587
List database users, and database sizes with and without indexes
04:11
588
List all database and schemas
04:21
589
List all tables and views
08:25
590
List all columns from a table
02:47
591
View system metadata via system information functions
02:34
592
View privileges information across tables
08:16
593
Using system Administration functions
05:25
594
Show all running queries
04:17
595
Terminate running and IDLE process
02:55
596
How to check live and dead rows in tables
01:50
597
File layout of PostgreSQL Tables
09:22
598
Using SELECT INTO to create a new table with joins
12:07
599
Duplicate a table with or without data
03:09
600
Import data from CSV files
07:00
601
Export Data to CSV files
02:43
602
Deleting duplicate records
07:27
603
Database operations and table size
05:58
604
Tracking table size
07:27
605
PostgreSQL autovacuum proces
10:29
606
Recovering unused space with VACUUM
09:56
607
Generated Columns
10:46
608
Create a custom index method
22:37
609
Create a user-defined aggregate function
10:57
610
Thank You!
00:49
Unlock unlimited learning

Get instant access to all 609 lessons in this course, plus thousands of other premium courses. One subscription, unlimited knowledge.

Learn more about subscription

Related courses

Frequently asked questions

What prerequisites are needed before enrolling in this PostgreSQL course?
This PostgreSQL bootcamp is designed for beginners, so no prior experience with PostgreSQL is necessary. However, a basic understanding of general programming concepts and familiarity with any SQL dialect might be helpful. The course covers everything from installing PostgreSQL on different operating systems to advanced query techniques.
What types of projects will I be able to build after completing this course?
Upon completing the course, you will be able to create and manage complex databases using PostgreSQL. Some of the projects include building and handling databases like the movie database, human resources database, and stock market data. You'll also learn to create junction tables and manage data integrity through constraints and custom sequences.
Who is the target audience for this PostgreSQL bootcamp?
The course is aimed at individuals ranging from absolute beginners to those with intermediate SQL skills who want to deepen their understanding of PostgreSQL. It is ideal for software developers, data analysts, and database administrators looking to master PostgreSQL from the ground up.
How does the content depth of this course compare to other PostgreSQL courses?
This course offers a comprehensive overview with 60+ hours of content spread across 50+ sections, making it suitable for both beginners and those seeking to advance their skills. It covers core SQL skills, advanced query techniques like recursive CTEs and window functions, and server programming, which may not be covered as extensively in shorter courses.
What specific tools and platforms will I learn to use during this course?
You will learn to use PostgreSQL, pgAdmin 4, and command-line tools throughout the course. Lessons include installing PostgreSQL on Mac and Windows, configuring the pgAdmin client, and running queries using these platforms. You will also explore data manipulation using PostgreSQL-specific commands and functions.
What topics are not covered in this PostgreSQL bootcamp?
While the course is extensive, it does not cover non-PostgreSQL database management systems. It focuses solely on PostgreSQL-specific features and techniques, so cross-platform database management or specific cloud-based PostgreSQL services are not included. Additionally, it does not delve into database security or performance optimization in great detail.
How much time should I expect to commit to this course?
The course consists of over 60 hours of video instruction, broken down into more than 610 lessons. Depending on your pace and the time you dedicate each week, completing the course can vary. It is designed to be self-paced, allowing you to progress through the content as your schedule permits.