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

Welcome
1 Course Introduction
2 Sample Data Files

Introduction to PostgreSQL
3 Installing PostgreSQL on Mac
4 Installing PostgreSQL on Windows
5 Configure pgAdmin 4 client
6 Creating a Database User
7 Creating a Database
8 Running a query in pgAdmin tool
9 Install sample data files on server
10 Install Human Resources (hr) database
11 Install sample stocks market data
12 Install northwind database
13 Drop a database

Creating and Modifying Tables
14 Movie Database Structure
15 Creating the movie database and a actors table
16 Creating the directors table
17 Creating the movies table with a foreign key
18 Creating the movies revenues table
19 Creating a Junction table with movies and actors tables
20 Install sample data for ‘movies’ database
21 Create a sample Database mydata
22 Using pgAdmin – Create and modify a table
23 Using pgAdmin – View table structure, and create column
24 Using pgAdmin – Rename, delete and change the data type of a column
25 Deleting tables from a database

Modifying Data in the tables
26 Insert a data into table
27 Insert multiple records into a table
28 Insert a data that had quotes
29 Use RETURNING to get info on added rows
30 Update data in a table
31 Updating a row and returning the updated row
32 Updating all records in a table
33 Delete data from a table
34 Using UPSERT

Querying Data
35 Select all data from a table
36 Selecting specific columns from a table
37 Adding Aliases to columns in a table
38 Using SELECT statement for expressions
39 Using ORDER BY to sort records
40 Using ORDER BY with alias column name
41 Using ORDER BY to sort rows by expressions
42 Using ORDER BY with column name or column number
43 Using ORDER BY with NULL values
44 Using DISTINCT for selecting distinct values

Filtering Data
45 Comparison, Logical and Arithmetic operators
46 AND operator
47 OR operator
48 Combining AND, OR operators
49 What goes before and after WHERE clause
50 Execution order with AND, OR operators
51 Can we use column aliases with WHERE
52 Order of execution of WHERE, SELECT and ORDER BY clauses
53 Using Logical operators
54 Using LIMIT and OFFSET
55 Using FETCH
56 Using IN, NOT IN
57 Using BETWEEN and NOT BETWEEN
58 Using LIKE and ILIKE
59 Using IS NULL and IS NOT NULL keywords
60 Concatenation techniques
61 Concatenation with , CONCAT and CONCAT WS

PostgreSQL Data types
62 Boolean
63 CHAR, VARCHAR and TEXT
64 NUMERIC
65 DECIMALS
66 Selecting Numbers data types
67 Date Time data types
68 DATE
69 TIME
70 TIMESTAMP and TIMESTAMPTZ
71 UUID
72 Array
73 hstore
74 JSON
75 Network Addresses

Modifying Table Structures, Add constraints
76 Creating sample database ‘mydata’, adding columns
77 Modify Table Structures, Add Modify Columns
78 Add constraints to columns

Data type conversions
79 What is a data type conversion
80 Using CAST for data conversions
81 Implicit to Explicit conversions
82 Table data conversion

Conversion Functions
83 to char
84 to number
85 to date
86 to timestamp

User-defined Data Types
87 CREATE DOMAIN – Create a DOMAIN data type, create an address
88 CREATE DOMAIN – Create a data type for a positive number
89 CREATE DOMAIN – Create a postal code validation data type
90 CREATE DOMAIN – Create a domain data type for an email validation
91 CREATE DOMAIN – Create a Enum or Set of values domain data type
92 CREATE DOMAIN – Get the list of all DOMAIN data types
93 CREATE DOMAIN – How to drop a domain data type
94 CREATE TYPE – Create a composite address object
95 CREATE TYPE – Create a composite inventory item data type
96 CREATE TYPE – Create an ENUM data type and see how to drop a data type
97 ALTER TYPE – Alter a composite data type, change schema and more
98 ALTER TYPE – Alter an ENUM data type
99 Update an ENUM data in production server
100 An ENUM with a DEFAULT value in a table
101 Create a type if not exists using a PL pgSQL function

Explore PostgreSQL Constraints
102 Introduction to constraints
103 NOT NULL constraint
104 UNIQUE constraint
105 DEFAULT constraint
106 PRIMARY KEY Constraints
107 PRIMARY KEY Constraints on multiple columns
108 FOREIGN KEY Constraints
109 Tables without foreign key constraints
110 Creating foreign key constraints
111 Foreign keys maintains referential data integrity
112 Drop a constraint
113 Add or update foreign key constraint on existing table
114 CHECK constraint – An Introduction
115 CHECK constraint – Add to new table
116 CHECK constraint – Add, Rename, Drop on existing table

PostgreSQL Sequences
117 Create a sequence, advance a sequence, get current value, set value
118 Restart, rename a sequence, and use pgAdmin to alter a sequence
119 Create a sequence with START WITH, INCREMENT, MINVALUE and MAXVALUE
120 Create a sequence using a specific data type
121 Creating a descending sequence, and CYCLE sequence
122 Delete a sequence
123 Attach a sequence to a table column
124 List all sequences in a database
125 Share one sequence between two tables
126 Create an alphanumeric sequence

String Functions
127 UPPER, LOWER and INITCAP
128 LEFT and RIGHT
129 REVERSE
130 SPLIT PART
131 TRIM, BTRIM, LTRIM and RTRIM
132 LPAD and RPAD
133 LENGTH
134 POSITION
135 STRPOS
136 SUBSTRING
137 REPEAT
138 REPLACE

Aggregate functions
139 Counting results via COUNT function
140 Sum with SUM function
141 MIN and MAX functions
142 GREATEST AND LEAST functions
143 Average with AVG function
144 Combining Columns using Mathematical operators

Using DATE TIME functions
145 Datetimes data types
146 System Month Date settings
147 Time of day formats and inputs
148 Strings to Dates conversions
149 Using TO TIMESTAMP function
150 Formatting Dates
151 Date construction functions
152 Using MAKE INTERVAL function
153 Using MAKE TIMESTAMPTZ function
154 Date Value Extractors functions
155 Using math operators with dates
156 OVERLAPS Operator
157 Date Time Functions
158 PostgreSQL Date Time Functions
159 AGE function
160 CURRENT DATE function
161 CURRENT TIME function
162 Date accuracy with EPOCH
163 Using Date, time, timestamp in tables
164 View and set timezones
165 How to handle timezones
166 date part function
167 date trunc function

Grouping Data
168 Using GROUP BY
169 Using GROUP BY with multiple columns, ORDER BY
170 Order of execution in GROUP BY clause
171 Using HAVING
172 Order of execution in HAVING clause
173 HAVING vs WHERE
174 Handling NULL values with GROUP BY

Joining Multiple Tables
175 INNER joins
176 INNER joins with USING
177 INNER joins with filter data Part 1
178 INNER joins with filter data Part 2
179 INNER joins with filter data Part 3
180 INNER joins with different data type columns
181 LEFT joins Part 1
182 LEFT joins Part 2
183 LEFT joins Part 3
184 LEFT joins Part 4
185 RIGHT joins
186 RIGHT joins Part 2
187 FULL Joins
188 Joining multiple tables
189 Self Joins Part 1
190 Self Joins Part 2
191 CROSS Joins
192 Natural Joins Part 1
193 Natural Joins Part 2
194 Append tables with different columns
195 ON versus WHERE

Combining queries together
196 Combine results sets with UNION
197 UNION with filters and conditions
198 UNION tables with different number of columns
199 INTERSECT with tables
200 EXCEPT with tables

PostgreSQL Schemas
201 What is a Schema
202 Schema Operations (Add Alter Delete schemas)
203 Schema Hierarchy
204 Move a table to a new schema
205 Schema search path
206 Alter a schema ownership
207 Duplicate a schema along with all data
208 What is a system catalog schema
209 Compare tables and columns in two schemas
210 Schemas and Privileges

Exploring array functions
211 Constructing arrays and ranges
212 Using comparison operators
213 Using inclusion operators
214 Array constructions
215 Array metadata functions
216 Array search functions
217 Array modification functions
218 Array comparison with IN, ALL, ANY and SOME
219 Formatting and converting an array
220 Using arrays in tables
221 Insert data into arrays
222 Query array data
223 Modifying array data
224 Array Dimensions are ignored!
225 Display all array elements
226 Using Multi-dimensional arrays
227 Array vs JSONB
228 Single and Multi-dimensional arrays

JSON with PostgreSQL
229 What is JSON
230 JSON Syntax
231 JSON and JSONB data types
232 Exploring JSON objects
233 Create our first table with JSONB data type
234 Update and Delete JSON Data
235 Create JSON from tables
236 Use json agg to aggregate data
237 Build a JSON array
238 Creating a document from data
239 Null Values in JSON documents
240 JSON nulls and SQL nulls
241 Getting information from JSON documents
242 The Existence Operator
243 The Containment Operator
244 JSON search with PostgreSQL functions
245 Indexing on JSONB

Indexes and Performance Optimization
246 What is an index
247 Create an index
248 Create unique indexes
249 List all indexes
250 Size of the table index
251 List counts of all indexes
252 Drop a index
253 SQL Statement execution process
254 SQL statement execution stages
255 The query optimizer
256 Optimizer node types
257 Sequential Nodes
258 Index Nodes
259 Join Nodes
260 Index Types – B-Tree Index
261 Hash Index
262 BRIN index
263 GIN Index
264 The EXPLAIN statement
265 EXPLAIN output options
266 Using EXPLAIN ANALYZE
267 Understanding query cost model
268 Index are not free
269 Indexes for sorted output
270 Using multiple indexes on a single query
271 Execution plans depends on input values
272 Using organized vs random data
273 Try to use index only scan
274 Partial indexes
275 Expression Index
276 Adding data while indexing
277 Invalidating an index
278 Rebuilding an index

All about Views
279 Introduction to views
280 Creating a view
281 Rename a view
282 Delete a view
283 Using filters with views
284 A view with UNION of multiple tables
285 Connecting multiple tables with a single view
286 Re-arrange columns in a view
287 Delete a column in a view
288 Add a column in a view
289 Regular views are dynamic
290 What is an updatable view
291 An updatable view with CRUD operations
292 Updatable views using WITH CHECK OPTION
293 Updatable views using WITH LOCAL and CASCADED CHECK OPTION
294 What is a Materialized View
295 Creating a materialized view
296 Drop a materialized view
297 Changing materialized view data
298 How to check if a materialized view is populated or not
299 Refreshing data in materialize views
300 Why not use a table instead of materialized view
301 The downsides of using materialized views
302 Using materialized view for websites page analysis
303 List all materialized views by a SELECT statement
304 List materialized views with no unique index
305 Quick queries for materialized views

Fun with subqueries
306 Introduction to subqueries
307 Subqueries with WHERE Clause
308 Subquery with IN operator
309 Subquery with JOINs
310 Get total revenues for all ‘English’ films
311 Order entries in UNION without using ORDER BY
312 Subquery with an alias
313 A SELECT without a FROM
314 Correlated Queries
315 SELECT.. IN (Subquery)
316 Using ANY with subquery
317 Using ALL with subquery
318 Subquery using EXISTS

Common Table Expressions (CTE)
319 Introduction to CTEs
320 CTE query examples
321 Combine CTE with a table
322 Get the total revenues for each director
323 Simultaneously DELETE INSERT via CTE
324 Recursive CTEs
325 Parent-child relationship via recursive CTE

Grouping sets
326 Introduction to summarization
327 Subtotals on group sets
328 Adding subtotal with ROLLUP
329 Using GROUPING with ROLLUP

Window Functions
330 Introduction to Window Functions
331 Analyze Global Trades Data
332 Using aggregate functions
333 GROUP BY ROLLUP
334 ROLLUP – Grouping multiple columns
335 GROUP BY CUBE
336 GROUP BY GROUPING SETS
337 Query performance check
338 Using FILTER clause
339 Using Window Functions
340 Partitioning the data
341 Set data into millions format
342 Ordering inside window
343 Sliding dynamic windows
344 Understanding window frames
345 ROWS and RANGE indicators
346 Using WINDOW
347 Using WINDOW with ROWS BETWEEN
348 RANK and DENSE RANK functions
349 NTILE function
350 LEAD and LAG functions
351 FIRST VALUE, LAST VALUE and NTH value functions
352 ROW NUMBER function
353 Finding Correlations
354 ROW NUMBER() – Window with Partition datasets
355 ROW NUMBER() – Reverse fields with order by
356 ROW NUMBER() – Find Nth record
357 ROW NUMBER() – Find DISTINCT with subquery
358 ROW NUMBER() – Pagination technique
359 Using OVER() to calculate percentage
360 Calculate difference compared to average
361 Calculating cumulative totals
362 Using LEAD to compare with next values
363 Comparing with least number
364 Window Function Summary
365 Planning tips on using Window Functions
366 Difference between RANK and DENSE RANK
367 Getting RANK to compute group and global ranks
368 Using PARTITION BY for group averages
369 Using WITH clause to create your own data
370 Using WITH clause to set ORDER BY values
371 DELETE and INSERT in one query using WITH

Using regular expressions for text patterns
372 Text to structured data
373 Regular expressions notations table
374 SIMILAR TO operator
375 POSIX regular expressions
376 SUBSTRING with regular expressions
377 REGEXP MATCHES Function
378 REGEXP REPLACE Function
379 REGEXP SPLIT TO TABLE Function
380 REGEXP SPLIT TO ARRAY function

Powerful text searches in PostgreSQL
381 The Good Ol’ Text Search
382 Introducing tsvector
383 Using tsquery with operators
384 Full text search within a table
385 Setup presidents speeches data
386 Analyzing presidents speeches text data
387 Ranking and Normalizing text results

Table Partitions
388 What is a partition
389 When a partition can be used
390 Table inheritance
391 Partition types
392 Partition by Range
393 Partition by List
394 Partition by Hash
395 DEFAULT Partition
396 Multi-level partitioning
397 Partition maintenance
398 ALTERing the partition bounds
399 Partition Indexes
400 Partition pruning
401 Determining a field for partition over
402 Sizing the partition
403 Partitioning Advantages
404 Common partitioning mistakes

Server Programming
405 PostgreSQL as a development platform
406 Procedural languages
407 Keep the data on the server!
408 Functions vs stored procedures
409 User-defined functions
410 Structure of a function

Functions with SQL language
411 Creating our first SQL function
412 Introducing dollar quoting
413 Function returning no values
414 Function returning a single value
415 Function returning a single value Part 2
416 Function returning a single value Part 3
417 Function using parameters
418 Function using parameters Part 2
419 Function using parameters Part 3
420 Function using parameters Part 4
421 Function returning a composite
422 Function returning multiple rows
423 Function – order matters!
424 Function returning a table
425 Function as a table source
426 Function parameter modes
427 Function parameters with default values
428 Function based on views
429 Drop a function

Functions with PL pgSQL language
430 Introduction to PL pgSQL language
431 PL pgSQL vs SQL
432 Structure of a PL pgSQL function
433 PL pgSQL block structure
434 Declaring variables
435 Declaring variables via ALIAS FOR
436 Declaring variables in function
437 Variable initializing timing
438 Copying data types
439 Assigning variables from query
440 Using IN, OUT without RETURNS
441 Variables in block and subblock
442 How to return query results
443 Control Structures – IF statement
444 Using IF with table data
445 CASE Statement
446 Searched CASE statement
447 LOOP statement
448 FOR Loops
449 FOR Loops iterate over result set
450 CONTINUE statement
451 FOREACH loop with arrays
452 WHILE loop
453 Using RETURN QUERY
454 Returning a table
455 Using RETURN NEXT
456 Error and exception handling
457 Exception – Too many rows
458 Using SQLSTATE codes for exception handling
459 Exception with data exception errors

Exploring Stored Procedures
460 Functions vs Stored Procedures
461 Create a transactions
462 Understanding the use of stored procedures
463 Returning a value
464 Drop a procedure

PostgreSQL Triggers
465 Introduction to triggers
466 What is a trigger
467 Types of triggers
468 Trigger table
469 Pros and Cons of triggers
470 Trigger key points
471 Steps to create a trigger
472 Data auditing with a trigger
473 Bind a function to a table with trigger
474 Modify data at INSERT event
475 View triggers variables
476 Disallow DELETE on a table
477 Disallow TRUNCATE on a table
478 Creating an audit trigger
479 Creating conditional triggers
480 Disallow data change on primary key
481 Use triggers very cautiously
482 What is an event trigger
483 Event triggers usage scenarios
484 Creating event triggers
485 Event trigger events and variables
486 Creating an audit event trigger
487 Prevent schema changes
488 Dropping a trigger

Using Cursors
489 Understanding row by row operations
490 Cursors and procedural languages
491 Steps to create a cursor
492 Creating a cursor
493 Opening a cursor
494 Using a cursor
495 Updating data
496 Closing a cursor
497 Creating a PL PGSQL Cursor
498 Using a parametric cursor via function

Creating Crosstab Reports
499 What is a crosstab report
500 Installing the tablefunc extension
501 Creating a crosstab report – Student Rankings
502 Orders matters in crosstab reports
503 Pivoting with Rainfalls data
504 Pivoting rows and columns
505 Matrix report via a query
506 Aggregate over filter
507 Static to dynamic pivots
508 Dynamic pivot query via JSON
509 Dynamic pivot table columns
510 Interactive client-side pivot
511 Handling missing values

Internationalization
512 Global Character Set Support
513 Client and Server Encoding

PostgreSQL Transactions
514 What is a transaction
515 How SQL protect database during transaction
516 ACID Database
517 Transaction setup
518 Transaction analysis
519 How to fix aborted transaction
520 How to fix transactions on crash
521 Partial transaction rollback with savepoints
522 Using SAVEPOINT with transaction

Using Northwind Database
523 Using Northwind database
524 Orders shipping to USA or France
525 Total numbers of orders shipped to USA or France
526 Orders shipped to latin america
527 Show total order amount for each order
528 First the oldest and latest order date
529 Total products in each categories
530 List products that needs re-ordering
531 Freight analysis
532 Customers with no orders
533 Top customers with total orders amount
534 Orders with many lines of ordered items
535 Orders with double entry line items
536 Late shipped orders by employees
537 Countries with customers or suppliers
538 Countries with customers or suppliers – Using CTE
539 Customers with multiple orders
540 First order from each country

Using Human Resource (hr) database
541 Human Resources Database Structure
542 Quick overview of all tables
543 Quick overview of all tables Part 2
544 Get highest, lowest, total, and average salaries of employees
545 Difference b w highest and lowest salaries by job id
546 Get lowest paid salaries by each manager
547 Average salary for each department with more than 10 employees
548 Average salary for each post excluding programmer
549 Maximum salary for each post where salary is at or above $5000
550 Using an alias name with columns
551 Compute 15% of salary for all employees
552 To list all employees IDs within each job id group
553 Discard characters from employees email address
554 List all employees with first name starts with letters A , C or M
555 String manipulation with upper, lower and initcap functions
556 Using SUBSTRING to get portion of string data
557 Get unique designations in employees table
558 Select employees with particular department id only
559 Select employees not in range
560 Find Letter C in last name at 3rd or greater position
561 Update phone number with your strings
562 Get the monthly salary of each employees
563 Calculate average salary with total number of employees
564 find employees whose names contains exactly six characters

Exploring Stock Markets Data
565 Introduction To stock market data
566 Select first or last records in a table
567 Get first or last record per each group
568 How to calculate cube root in PostgreSQL

Managing PostgreSQL security
569 Security concepts and and levels
570 Instance Level Security
571 Instance Level Security – Add users to roles
572 Use pgAdmin to create roles
573 Database Level Security
574 Schema Level Security
575 Table Level Security
576 Column Level Security
577 Row Level Security
578 Using CURRENT USER with RLS
579 Row level policy for application users
580 DROP a policy
581 Inspecting permissions
582 Row Level Security Performance
583 Encrypted data in columns

PostgreSQL Utilities – psql
584 Connect to local or remote database
585 Switch connection to a new database
586 List all databases and tables
587 List all table space, schemas, and indices
588 List all sequences, roles, data types, domain data types
589 Describe a table
590 Edit SQL commands in editor
591 Display command history, run commands from a file
592 Built-in syntax reference for commands
593 Represent NULL values on psql terminal
594 Make table layout funkey!
595 Repeatedly Execute a Query
596 Represent NULL Visibly
597 Turn on query execution time

PostgreSQL Internals
598 List database users, and database sizes with and without indexes
599 List all database and schemas
600 List all tables and views
601 List all columns from a table
602 View system metadata via system information functions
603 View privileges information across tables
604 Using system Administration functions
605 Show all running queries
606 Terminate running and IDLE process
607 How to check live and dead rows in tables
608 File layout of PostgreSQL Tables
609 internal – view system administration functions

Managing Tables
610 Using SELECT INTO to create a new table with joins
611 Duplicate a table with or without data
612 Import data from CSV files
613 Export Data to CSV files
614 Deleting duplicate records
615 Backup your databases regularly
616 Test queries on a test database
617 Not documenting your process
618 Database operations and table size
619 Tracking table size
620 PostgreSQL autovacuum proces
621 Recovering unused space with VACUUM
622 Generated Columns

Extended PostgreSQL
623 Create a custom index method
624 Create a user-defined aggregate function

Thank You!
625 Your feedback is very valuable
626 Upcoming course – Advanced Time Series Analysis
627 Thank You!