Enrolment options
Udemy - PostgreSQL Bootcamp Go From Beginner to Advanced, 60+hours 2022-3
Course modified date: 9 Feb 2024
- Enrolled students: There are no students enrolled in this course.
Guests cannot access this course. Please log in.
Udemy - PostgreSQL Bootcamp Go From Beginner to Advanced, 60+hours 2022-3
Course content
Sections:
48
•
Activities:
0
•
Resources:
593
- 001 Course Introduction
- 001 Installing PostgreSQL on Mac
- 002 Installing PostgreSQL on Windows
- 003 Configure pgAdmin 4 client
- 004 Creating a Database User
- 005 Creating a Database
- 006 Running a query in pgAdmin tool
- 007 Install sample data files on server
- 008 Install Human Resources (hr) database
- 009 Install sample stocks market data
- 010 Install northwind database
- 011 Drop a database
- 001 Movie Database Structure
- 002 Creating the movie database and a actors table
- 003 Creating the directors table
- 004 Creating the movies table with a foreign key
- 005 Creating the movies_revenues table
- 006 Creating a Junction table with movies and actors tables
- 007 Install sample data for 'movies' database
- 009 Using pgAdmin - Create and modify a table
- 010 Using pgAdmin - View table structure, and create column
- 011 Using pgAdmin - Rename, delete and change the data type of a column
- 012 Deleting tables from a database
- 001 Insert a data into table
- 002 Insert multiple records into a table
- 003 Insert a data that had quotes
- 004 Use RETURNING to get info on added row
- 005 Update data in a table
- 006 Updating a row and returning the updated row
- 007 Updating all records in a table
- 008 Delete data from a table
- 009 Using UPSERT
- 001 Select all data from a table
- 002 Selecting specific columns from a table
- 003 Adding Aliases to columns in a table
- 004 Using SELECT statement for expressions
- 005 Using ORDER BY to sort records
- 006 Using ORDER BY with alias column name
- 007 Using ORDER BY to sort rows by expressions
- 008 Using ORDER BY with column name or column number
- 009 Using ORDER BY with NULL values
- 010 Using DISTINCT for selecting distinct values
- 001 Comparison, Logical and Arithmetic operators
- 002 AND operator
- 003 OR operator
- 004 Combining AND, OR operators
- 005 What goes before and after WHERE clause
- 006 Execution order with AND, OR operators
- 007 Can we use column aliases with WHERE
- 008 Order of execution of WHERE, SELECT and ORDER BY clauses
- 009 Using Logical operators
- 010 Using LIMIT and OFFSET
- 011 Using FETCH
- 012 Using IN, NOT IN
- 013 Using BETWEEN and NOT BETWEEN
- 014 Using LIKE and ILIKE
- 015 Using IS NULL and IS NOT NULL keywords.m
- 016 Concatenation techniques
- 017 Concatenation with , CONCAT and CONCAT_WS
- 001 Boolean
- 002 CHAR, VARCHAR and TEXT
- 003 NUMERIC
- 004 DECIMALS
- 005 Selecting Numbers data types
- 006 DateTime data types
- 007 DATE
- 008 TIME
- 009 TIMESTAMP and TIMESTAMPTZ
- 010 UUID
- 011 Array
- 012 hstore
- 013 JSON.
- 014 Network Addresses
- 001 Creating sample database 'mydata', adding columns
- 002 Modify Table Structures, AddModify Columns
- 003 Add constraints to columns
- 001 What is a data type conversion
- 002 Using CAST for data conversions
- 003 Implicit to Explicit conversions
- 004 Table data conversion
- 001 to_char
- 002 to_number
- 003 to_date
- 004 to_timestamp
- 001 Introduction to constraints
- 002 NOT NULL constraint
- 003 UNIQUE constraint
- 004 DEFAULT constraint
- 005 PRIMARY KEY Constraints
- 006 PRIMARY KEY Constraints on multiple columns
- 007 FOREIGN KEY Constraints
- 008 Tables without foreign key constraints
- 009 Creating foreign key constraints
- 010 Foreign keys maintains referential data integrity
- 011 Drop a constraint
- 012 Add or update foreign key constraint on existing table
- 013 CHECK constraint - An Introduction
- 014 CHECK constraint - Add to new table
- 015 CHECK constraint - Add, Rename, Drop on existing table
- 001 Create a sequence, advance a sequence, get current value, set value
- 002 Restart, rename a sequence, and use pgAdmin to alter a sequence
- 003 Create a sequence with START WITH, INCREMENT, MINVALUE and MAXVALUE
- 004 Create a sequence using a specific data type
- 005 Creating a descending sequence, and CYCLE sequence
- 006 Delete a sequence
- 007 Attach a sequence to a table column
- 008 List all sequences in a database
- 009 Share one sequence between two tables.
- 010 Create an alphanumeric sequence
- 001 UPPER, LOWER and INITCAP
- 002 LEFT and RIGHT
- 003 REVERSE
- 004 SPLIT_PART
- 005 TRIM, BTRIM, LTRIM and RTRIM
- 006 LPAD and RPAD
- 007 LENGTH
- 008 POSITION
- 009 STRPOS
- 010 SUBSTRING
- 011 REPEAT
- 012 REPLACE
- 001 Counting results via COUNT function
- 002 Sum with SUM function
- 003 MIN and MAX functions
- 004 GREATEST AND LEAST functions
- 005 Average with AVG function
- 006 Combining Columns using Mathematical operators
- 001 Datetimes data types
- 002 System Month Date settings
- 003 Time of day formats and inputs
- 004 Strings to Dates conversions
- 005 Using TO_TIMESTAMP function
- 006 Formatting Dates
- 007 Date construction functions
- 008 Using MAKE_INTERVAL function
- 009 Using MAKE_TIMESTAMPTZ function
- 010 Date Value Extractors functions
- 011 Using math operators with dates
- 012 OVERLAPS Operator.
- 013 Date Time Functions
- 014 PostgreSQL Date Time Function
- 015 AGE function
- 016 CURRENT_DATE function
- 017 CURRENT_TIME function
- 018 Date accuracy with EPOCH
- 019 Using Date, time, timestamp in tables
- 020 View and set timezones
- 021 How to handle timezones
- 022 date_part function
- 023 date_trunc function
- 001 Using GROUP BY
- 002 Using GROUP BY with multiple columns, ORDER BY
- 003 Order of execution in GROUP BY clause
- 004 Using HAVING
- 005 Order of execution in HAVING clause
- 006 HAVING vs WHERE
- 007 Handling NULL values with GROUP BY
- 001 INNER joins
- 002 INNER joins with USING
- 003 INNER joins with filter data Part 1
- 004 INNER joins with filter data Part 2
- 005 INNER joins with filter data Part 3
- 006 INNER joins with different data type column
- 007 LEFT joins Part 1
- 008 LEFT joins Part 2
- 009 LEFT joins Part 3
- 010 LEFT joins Part 4
- 011 RIGHT joins
- 012 RIGHT joins Part 2
- 013 FULL Joins
- 014 Joining multiple table
- 015 Self Joins Part 1
- 016 Self Joins Part 2
- 017 CROSS Join
- 018 Natural Joins Part 1
- 019 Natural Joins Part 2
- 020 Append tables with different column
- 001 Combine results sets with UNION
- 002 UNION with filters and conditions
- 003 UNION tables with different number of columns
- 004 INTERSECT with tables
- 005 EXCEPT with tables.mp4
- 001 What is a Schema
- 002 Schema Operations (AddAlterDelete schemas)
- 003 Schema Hierarchy
- 004 Move a table to a new schema
- 005 Schema search path
- 006 Alter a schema ownership.
- 007 Duplicate a schema along with all data
- 008 What is a system catalog schema
- 009 Compare tables and columns in two schemas
- 010 Schemas and Privileges
- 001 Constructing arrays and ranges
- 002 Using comparison operator
- 003 Using inclusion operator
- 004 Array constructions
- 005 Array metadata functions
- 006 Array search functions
- 007 Array modification functions
- 008 Array comparison with IN, ALL, ANY and SOME
- 009 Formatting and converting an array
- 010 Using arrays in tables
- 011 Insert data into arrays
- 012 Query array data
- 013 Modifying array data
- 014 Array Dimensions are ignored!
- 015 Display all array elements
- 016 Using Multi-dimensional arrays
- 017 Array vs JSONB
- 001 What is JSON
- 002 JSON Syntax
- 003 JSON and JSONB data types
- 004 Exploring JSON objects
- 005 Create our first table with JSONB data type
- 006 Update and Delete JSON Data
- 007 Create JSON from tables
- 008 Use json_agg to aggregate data
- 009 Build a JSON array
- 010 Creating a document from data
- 011 Null Values in JSON documents
- 013 Getting information from JSON documents
- 014 The Existence Operator
- 015 The Containment Operator
- 016 JSON search with PostgreSQL function
- 017 Indexing on JSONB
- 23 - Indexes and Performance Optimization/001 What is an index
- 23 - Indexes and Performance Optimization/002 Create an index
- 003 Create unique indexes
- 23 - Indexes and Performance Optimization/004 List all indexes
- 005 Size of the table index
- 006 List counts of all indexes
- 007 Drop a index
- 008 SQL Statement execution process
- 009 SQL statement execution stages
- 010 The query optimizer
- 011 Optimizer node types
- 012 Sequential Nodes
- 013 Index Nodes
- 014 Join Nodes
- 015 Index Types - B-Tree Index
- 016 Hash Index
- 017 BRIN index
- 018 GIN Index
- 019 The EXPLAIN statement
- 020 EXPLAIN output options
- 021 Using EXPLAIN ANALYZE
- 022 Understanding query cost model
- 023 Index are not free
- 024 Indexes for sorted output
- 025 Using multiple indexes on a single query
- 026 Execution plans depends on input values
- 027 Using organized vs random data
- 028 Try to use index only scan
- 029 Partial indexes
- 030 Expression Index
- 031 Adding data while indexing
- 032 Invalidating an index
- 033 Rebuilding an index
- 001 Introduction to views
- 002 Creating a view
- 003 Rename a view
- 004 Delete a view
- 005 Using filters with views
- 006 A view with UNION of multiple tables.
- 007 Connecting multiple tables with a single view
- 008 Re-arrange columns in a view
- 009 Delete a column in a view
- 010 Add a column in a view.
- 011 Regular views are dynamic
- 012 What is an updatable view
- 013 An updatable view with CRUD operations
- 014 Updatable views using WITH CHECK OPTION
- 015 Updatable views using WITH LOCAL and CASCADED CHECK OPTION
- 016 What is a Materialized View
- 017 Creating a materialized view.
- 018 Drop a materialized view.
- 019 Changing materialized view data.
- 020 How to check if a materialized view is populated or not.
- 021 Refreshing data in materialize views
- 022 Why not use a table instead of materialized view
- 023 The downsides of using materialized views
- 024 Using materialized view for websites page analysis
- 025 List all materialized views by a SELECT statement
- 026 List materialized views with no unique index
- 002 Subqueries with WHERE Clause
- 003 Subquery with IN operator
- 004 Subquery with JOINs
- 006 Order entries in UNION without using ORDER BY
- 007 Subquery with an alias
- 008 A SELECT without a FROM.
- 009 Correlated Queries
- 010 SELECT.. IN (Subquery)
- 011 Using ANY with subquery
- 012 Using ALL with subquery
- 013 Subquery using EXISTS.
- 001 Introduction to CTEs
- 002 CTE query examples
- 003 Combine CTE with a table
- 005 Simultaneously DELETE INSERT via CTE
- 006 Recursive CTEs
- 007 Parent-child relationship via recursive CTE
- 001 Introduction to summarization
- 002 Subtotals on group sets
- 003 Adding subtotal with ROLLUP
- 004 Using GROUPING with ROLLUP
- 001 Introduction to Window Functions
- 002 Analyze Global Trades Data
- 003 Using aggregate functions
- 004 GROUP BY ROLLUP
- 005 ROLLUP - Grouping multiple columns
- 006 GROUP BY CUBE.
- 007 GROUP BY GROUPING SETS
- 008 Query performance check
- 009 Using FILTER clause
- 010 Using Window Functions
- 011 Partitioning the data
- 012 Set data into millions format
- 013 Ordering inside window.
- 014 Sliding dynamic windows
- 015 Understanding window frames
- 016 ROWS and RANGE indicators
- 017 Using WINDOW
- 018 Using WINDOW with ROWS BETWEEN
- 019 RANK and DENSE_RANK functions
- 020 NTILE function
- 021 LEAD and LAG functions
- 022 FIRST_VALUE, LAST_VALUE and NTH_value functions.
- 023 ROW_NUMBER function
- 024 Finding Correlations.
- 025 ROW_NUMBER() - Window with Partition datasets
- 026 ROW_NUMBER() - Reverse fields with order by
- 027 ROW_NUMBER() - Find Nth record.
- 028 ROW_NUMBER() - Find DISTINCT with subquery.
- 029 ROW_NUMBER() - Pagination technique
- 030 Using OVER() to calculate percentage
- 031 Calculate difference compared to average
- 032 Calculating cumulative totals
- 033 Using LEAD to compare with next values
- 034 Comparing with least number
- 035 Window Function Summary
- 036 Planning tips on using Window Functions
- 037 Difference between RANK and DENSE_RANK
- 038 Getting RANK to compute group and global ranks
- 039 Using PARTITION BY for group averages
- 040 Using WITH clause to create your own data
- 041 Using WITH clause to set ORDER BY values
- 042 DELETE and INSERT in one query using WITH
- 001 Text to structured data
- 002 Regular expressions notations table
- 003 SIMILAR TO operator
- 004 POSIX regular expressions
- 005 SUBSTRING with regular expressions
- 006 REGEXP_MATCHES Function
- 007 REGEXP_REPLACE Function
- 008 REGEXP_SPLIT_TO_TABLE Function
- 009 REGEXP_SPLIT_TO_ARRAY function
- 001 The Good Ol' Text Search
- 002 Introducing tsvector
- 003 Using tsquery with operators
- 004 Full text search within a table
- 005 Setup presidents speeches data
- 006 Analyzing presidents speeches text data
- 007 Ranking and Normalizing text results
- 001 What is a partition
- 002 When a partition can be used
- 003 Table inheritance
- 004 Partition types
- 005 Partition by Range
- 006 Partition by List
- 007 Partition by Hash
- 008 DEFAULT Partition
- 009 Multi-level partitioning
- 010 Partition maintenance
- 011 ALTERing the partition bounds
- 012 Partition Indexes
- 013 Partition pruning
- 014 Determining a field for partition over
- 015 Sizing the partition
- 016 Partitioning Advantages
- 017 Common partitioning mistakes
- 001 PostgreSQL as a development platform
- 002 Procedural languages
- 003 Keep the data on the server!
- 004 Functions vs stored procedures
- 005 User-defined functions
- 006 Structure of a function
- 001 Creating our first SQL function
- 002 Introducing dollar quoting
- 003 Function returning no values
- 004 Function returning a single value
- 005 Function returning a single value Part 2
- 006 Function returning a single value Part 3
- 007 Function using parameters
- 008 Function using parameters Part 2
- 009 Function using parameters Part 3
- 010 Function using parameters Part 4
- 011 Function returning a composite
- 012 Function returning multiple rows
- 013 Function returning a table
- 014 Function - order matters!
- 015 Function as a table source
- 016 Function parameter modes
- 017 Function parameters with default values
- 018 Function based on views
- 019 Drop a function
- 001 Introduction to PLpgSQL language
- 002 PLpgSQL vs SQL
- 003 Structure of a PLpgSQL function
- 004 PLpgSQL block structure
- 005 Declaring variables
- 006 Declaring variables via ALIAS FOR
- 007 Declaring variables in function
- 008 Variable initializing timing
- 009 Copying data types
- 010 Assigning variables from query
- 011 Using IN, OUT without RETURNS
- 012 Variables in block and subblock
- 013 How to return query results
- 014 Control Structures - IF statement
- 015 Using IF with table data
- 016 CASE Statement
- 017 Searched CASE statement
- 018 LOOP statement
- 019 FOR Loops
- 020 FOR Loops iterate over result set
- 021 CONTINUE statement
- 022 FOREACH loop with arrays
- 023 WHILE loop
- 024 Using RETURN QUERY
- 025 Returning a table
- 026 Using RETURN NEXT
- 027 Error and exception handling
- 028 Exception - Too many rows
- 029 Using SQLSTATE codes for exception handling
- 030 Exception with data exception errors
- 001 Functions vs Stored Procedures
- 002 Create a transactions
- 003 Understanding the use of stored procedures
- 004 Returning a value
- 005 Drop a procedure
- 002 What is a trigger
- 003 Types of triggers
- 004 Trigger table
- 005 Pros and Cons of triggers
- 006 Trigger key points
- 007 Steps to create a trigger
- 009 Bind a function to a table with trigger
- 010 Modify data at INSERT event
- 011 View triggers variables
- 012 Disallow DELETE on a table
- 013 Disallow TRUNCATE on a table
- 014 Creating an audit trigger
- 015 Creating conditional triggers
- 016 Disallow data change on primary key
- 017 Use triggers very cautiously
- 018 What is an event trigger
- 019 Event triggers usage scenarios
- 020 Creating event triggers
- 021 Event trigger events and variables
- 022 Creating an audit event trigger
- 023 Prevent schema changes
- 024 Dropping a trigger
- 001 Understanding row by row operations
- 002 Cursors and procedural languages
- 003 Steps to create a cursor
- 004 Creating a cursor
- 005 Opening a cursor
- 006 Using a cursor
- 007 Updating data
- 008 Closing a cursor
- 009 Creating a PLPGSQL Cursor
- 010 Using a parametric cursor via function
- 001 What is a crosstab report
- 002 Installing the tablefunc extension.
- 003 Creating a crosstab report - Student Rankings
- 004 Orders matters in crosstab reports
- 005 Pivoting with Rainfalls data
- 006 Pivoting rows and columns
- 007 Matrix report via a query
- 008 Aggregate over filter
- 009 Static to dynamic pivots
- 010 Dynamic pivot query via JSON
- 011 Dynamic pivot table columns
- 012 Interactive client-side pivot
- 013 Handling missing values
- 001 Global Character Set Support
- 002 Client and Server Encoding
- 001 What is a transaction
- 002 How SQL protect database during transaction
- 003 ACID Database
- 004 Transaction setup
- 005 Transaction analysis
- 006 How to fix aborted transaction
- 007 How to fix transactions on crash
- 008 Partial transaction rollback with savepoints
- 009 Using SAVEPOINT with transaction
- 001 Using Northwind database
- 002 Orders shipping to USA or France
- 003 Total numbers of orders shipped to USA or France
- 004 Orders shipped to latin america
- 005 Show total order amount for each order
- 006 First the oldest and latest order date
- 007 Total products in each categories
- 008 List products that needs re-ordering
- 009 Freight analysis.
- 010 Customers with no orders
- 011 Top customers with total orders amount
- 012 Orders with many lines of ordered items
- 013 Orders with double entry line items
- 014 Late shipped orders by employees
- 015 Countries with customers or suppliers
- 016 Countries with customers or suppliers - Using CTE
- 017 Customers with multiple orders
- 018 First order from each country
- 001 Human Resources Database Structure
- 002 Quick overview of all tables
- 003 Quick overview of all tables Part 2
- 004 Get highest, lowest, total, and average salaries of employees
- 006 Get lowest paid salaries by each manager
- 007 Average salary for each department with more than 10 employees
- 008 Average salary for each post excluding programmer
- 010 Using an alias name with columns
- 011 Compute 15% of salary for all employees
- 012 To list all employees IDs within each job_id group
- 013 Discard characters from employees email address
- 014 List all employees with first_name starts with letters A, C or M
- 015 String manipulation with upper, lower and initcap functions
- 016 Using SUBSTRING to get portion of string data
- 017 Get unique designations in employees table
- 018 Select employees with particular department id only
- 019 Select employees not in range
- 020 Find Letter C in last_name at 3rd or greater position
- 021 Update phone_number with your strings
- 022 Get the monthly salary of each employees
- 023 Calculate average salary with total number of employees
- 024 find employees whose names contains exactly six characters
- 002 Select first or last records in a table
- 003 Get first or last record per each group
- 004 How to calculate cube root in PostgreSQL
- 001 Security concepts and and levels
- 002 Instance Level Security
- 003 Instance Level Security - Add users to roles
- 004 Use pgAdmin to create roles
- 005 Database Level Security
- 006 Schema Level Security
- 007 Table Level Security
- 008 Column Level Security
- 009 Row Level Security
- 010 Using CURRENT_USER with RLS
- 011 Row level policy for application users
- 012 DROP a policy
- 013 Inspecting permissions
- 014 Row Level Security Performance
- 015 Encrypted data in columns
- 001 Connect to local or remote database
- 002 Switch connection to a new database
- 003 List all databases and tables
- 004 List all table space, schemas, and indices
- 005 List all sequences, roles, data types, domain data types
- 006 Describe a table
- 007 Edit SQL commands in editor
- 008 Display command history, run commands from a file
- 009 Built-in syntax reference for commands
- 010 Represent NULL values on psql terminal
- 011 Make table layout funkey!
- 012 Repeatedly Execute a Query
- 013 Represent NULL Visibly
- 014 Turn on query execution time
- 001 List database users, and database sizes with and without indexes
- 002 List all database and schemas
- 003 List all tables and views
- 004 List all columns from a table
- 005 View system metadata via system information functions
- 006 View privileges information across tables
- 007 internal-view-system-administration-functions
- 007 Using system Administration functions
- 008 Show all running queries
- 009 Terminate running and IDLE process
- 010 How to check live and dead rows in tables
- 011 File layout of PostgreSQL Tables
- 001 Using SELECT INTO to create a new table with joins
- 002 Duplicate a table with or without data
- 003 Import data from CSV files
- 004 Export Data to CSV files
- 005 Deleting duplicate records
- 009 Database operations and table size
- 010 Tracking table size
- 011 PostgreSQL autovacuum proces
- 012 Recovering unused space with VACUUM
- 013 Generated Columns
- 001 Create a custom index method
- 002 Create a user-defined aggregate function
- 004 Thank You!