SQL and PostgreSQL: The Complete Developer's Guide

Udemy

Learn the concepts involved with SQL and PostgreSQL to become a professional SQL developer.

Brochure

Enquire

Online

₹ 599 4099

particular

details

                                    Medium of instructions
                                    English

                                    Mode of learning
                                    Self study

                                    Mode of Delivery
                                    Video and Text Based

Simple - But Powerful - SQL Statements

Join Our Community!
Course Resources
What is PostgreSQL All About?
Database Design
Database Terminology
Creating Tables
Analyzing CREATE TABLE
Inserting Data Into a Table
Retrieving Data with Select
Create, Insert, and Select!
Calculated Columns
Calculating Phone Revenue
Using Calculated Columns
Exercise Solution
String Operators and Functions

Filtering Records

Filtering Rows with "Where"
More on the "Where" Keyword
Compound "Where" Clauses
A "Where" Exercise Overview
Practicing Where Statements
A "Where" Solution
"Where" With Lists
A More Challenging 'Where'
A "Where" With Lists Solution
Calculations in "Where" Clauses
Trying Calculations in Where Clauses
Solving Calculations
Updating Rows
Deleting Rows
Try Updating Records In a Table!
A Solution for Updating Rows
Practice Deleting Records
Solution for Deleting Rows

Working with Tables

The Plan Moving Forward
Approaching Database Design
Let's Design Some Schema
One-to-Many and Many-to-One Relationships
A 'Has One' or 'Has Many'?
One-to-One and Many-to-Many Relationships
Identifying One-to-One and Many-to-Many Relationships
Primary Keys and Foreign Keys
Understanding Foreign Keys
Foreign Keys; How Do They Work?
Auto-Generated ID's
Creating Foreign Key Columns
Running Queries on Associated Data
Exercise Overview
Creating and Using Foreign Keys
Foreign Key Creation Solution
Foreign Key Constraints Around Insertion
Constraints Around Deletion
Commands You’ll Need for the Next Video
Testing Deletion Constraints
Setting Foreign Keys to Null on Delete
What Happens On Delete?
Adding Some Complexity

Relating Records with Joins

Adding Some Data
Queries with Joins and Aggregations
Joining Data from Different Tables
Another Quick Join
Exercise Overview
Practice Joining Data
A Joinful Solution
Alternate Forms of Syntax
Missing Data in Joins
Why Wasn't It Included
Four Kinds of Joins
Each Join in Practice
Does Order Matter?
Test Your Joining Knowledge
Exercise Overview
Joins, Joins, Join!
Two Possible Solutions
Where with Join
Three Way Joins
A Bit of Practice
Three Way Exercise
Exercise Solution

Aggregation of Records

Aggregating and Grouping
Picturing Group By
Selecting Columns After Grouping
Aggregate Functions
Combining Group By and Aggregates
A Gotcha with Count
Visualizing More Grouping
Exercise Overview
Practice For Grouping and Aggregating
Grouping Solution
Adding a Layer of Difficulty
Grouping With a Join!
Solution
Filtering Groups with Having
Having In Action
More on Having!
A Having Exercise Overview
Practice Yourself Some Having
A Quick Solution

Working with Large Datasets

A New Dataset
Investigating This Dataset
Some Group By Practice
Group By Review
Group By Review Solution
Remember Joins?
Inner Join Review
Of Course You Remember!

Sorting Records

The Basics of Sorting
Two Variations on Sorting
Offset and Limit
Exercise Overview
Sorting, Offsetting, and Limiting
Exercise Solution

Unions and Intersections with Sets

Handling Sets with Union
A Few Notes on Union
Commonalities with Intersect
Removing Commonalities with Except
Union Exercise Overview
Merging Results with Union
Exercise Solution

Assembling Queries with SubQueries

What's a Subquery?
Thinking About the Structure of Data
What's the Data Look Like?
Subqueries in a Select
Exercise Overview
Embedding in Select
Select Solution
Subqueries in a From
From Subqueries that Return a Value
Example of a Subquery in a From
Exercise Overview
Subquery From's
Exercise Solution
Subqueries in a Join Clause
More Useful - Subqueries with Where
Data Structure with Where Subqueries
Exercise Overview
Subquery Where's
Exercise Solution
The Not In Operator with a List
A New Where Operator
Finally Some!
Is It A Valid Subquery?
Exercise Overview
Practice Your Subqueries!
A Quick Solution
Probably Too Much About Correlated Subqueries
More on Correlated Subqueries
A Select Without a From?
Exercise Overview
From-less Selects
Exercise Solution

Selecting Distinct Records

Selecting Distinct Values
Exercise Overview
Some Practice with Distinct
A Distinct Solution

Utility Operators, Keywords, and Functions

The Greatest Value in a List
And the Least Value in a List!
The Case Keyword

Local PostgreSQL Installation

PostgreSQL Installation on macOS
PGAdmin Setup on macOS
Postgres installation on Windows

PostgreSQL Complex Datatypes

What'd We Just Do?
Data Types
Fast Rules on Numeric Data Types
More on Number Data Types
Reminder on Character Types
Boolean Data Types
Times, Dates, and Timestamps
Really Awesome Intervals

Database-Side Validation and Constraints

Thinking About Validation
Creating and Viewing Tables in PGAdmin
Applying a Null Constraint
Solving a Gotcha with Null Constraints
Creating NULL Constraints
Default Column Values
Applying a Unique Constraint to One column
Multi-Column Uniqueness
Is It Unique?
Adding a Validation Check
Checks Over Multiple Columns
Does It Pass a Check?
So Where Are We Applying Validation?

Database Structure Design Patterns

Approaching More Complicated Designs
Using a SQL Design Tool
A Config-based Schema Designer
Here's the Plan
Rebuilding Some Schema

How to Build a 'Like' System

Requirements of a Like System
How Not to Design a Like System
Designing a Like System
Building a Similar System
Making a Reaction System Instead
Polymorphic Associations
Polymorphic Association Alternative Implementation
The Simplest Alternative
Polymorphic Associations
So Which Approach?

How to Build a 'Mention' System

Additional Features Around Posts
Adding Captions and Locations
Photo Mentions vs Caption Mentions
Considerations on Photo Tags vs Caption Tags
Update For Tags

How to Build a 'Hashtag' System

Designing a Hashtag System
Tables for Hashtags
Including the Hashtag Table
A Few More User Columns
Why No Number of Followers or Posts?

How to Design a 'Follower' System

Designing a Follower System

Implementing Database Design Patterns

Back to Postgres
Creating Tables with Checks
Posts Creation
Comments Creation
Likes Creation
Photo Tags and Caption Tags
Creating Hashtags, Hashtag Posts, and Followers

Approaching and Writing Complex Queries

Quick Note About Adding Some Data
Adding Some Data
Restoring from Scratch
Highest User ID's Exercise
Solution for User ID's
Posts by a Particular User
Solving for Posts by User
Likes Per User
Solution for Likes Per User

Understanding the Internals of PostgreSQL

Thinking About Performance
Where Does Postgres Store Data?
Heaps, Blocks, and Tuples
Terminology Check
Block Data Layout
Heap File Layout

A Look at Indexes for Performance

Full Table Scans
What's an Index
How an Index Works
Creating an Index
Benchmarking Queries
Downsides of Indexes
Index Types
Automatically Generated Indexes
Using Automatically Created Indexes
Behind the Scenes of Indexes

Basic Query Tuning

The Query Processing Pipeline
Explain and Explain Analyze
Explain Vs Explain Analyze
Solving an Explain Mystery

Advanced Query Tuning

Developing an Intuitive Understanding of Cost
Calculating Cost by Hand
A Touch More on Costs
Calculating Costs
Startup vs Total Costs
Costs Flow Up
Use My Index!

Simple Common Table Expressions

Common Table Expressions
A Quick Solution
So What's a CTE?

Recursive Common Table Expressions

Recursive CTE's
Recursive CTE's Step by Step
Why Use Recursive CTE's?
Writing the Query
Walking Through Recursion

Simplifying Queries with Views

Most Popular Users
A Possible Solution for Merging Tables
Creating a View
When to Use a View?
Deleting and Changing Views

Optimizing Queries with Materialized Views

Materialized Views
Grouping by Week
Reminder on Left Joins
Writing a Slow Query
Creating and Refreshing Materialized Views
Views vs Materialized Views

Handling Concurrency and Reversibility with Transactions

What are Transactions Used For?
Some Sample Data
Opening and Closing Transactions
Transaction Cleanup on Crash
Closing Aborted Transactions

Managing Database Design with Schema Migrations

A Story on Migrations
Migration Files
Issues Solved by Migrations
A Few Notes on Migrations Libraries
A Note About Node Installation
Project Creation
Generating and Writing Migrations
Applying and Reverting Migrations
Generating and Applying a Second Migration

Schema vs Data Migrations

Schema vs Data Migrations
Dangers Around Data Migrations
Properly Running Data and Schema Migrations
Creating a Posts Table
A Small Web Server
Web Server Setup Instructions
Adding the Loc Column
Writing Values to Both Columns
Transaction Locks
Updating Values
Migrations Setup Instructions
Updating the App Server
Dropping the Last Columns

Accessing PostgreSQL From API's

Section Goal
Initial Setup
One Fast Migration
Building the Users Router
Understanding Connection Pools
Validating Connection Credentials
Query and Close

Data Access Pattern - Repositories

The Repository Pattern
Creating a Repository
Accessing the API
Casing Issues
Fixing Casing
Finding Particular Users

Security Around PostgreSQL

SQL Injection Exploits
Handling SQL Injection with Prepared Statements
Preventing SQL Injection
Reminder on Post Requests
Inserting Users
Handling Updates
And, Finally, Delete

Fast Parallel Testing

A Note on Testing
Assertions Around User Count
Connecting to a DB For Tests
Disconnecting After Tests
Multi-DB Setup
Assumptions Around Content
Issues with Parallel Tests
Isolation with Schemas
Creating and Accessing Schemas
Controlling Schema Access with Search Paths
Routing Schema Access
Strategy for Isolation
Programmatic Schema Creation
Escaping Identifiers
Test Helpers
Cleaning up Schemas and Roles
Finally... Parallel Tests!

Bonus!

Bonus!

Popular Courses

Popular Platforms

Popular Searches

SQL and PostgreSQL: The Complete Developer's Guide

Online

₹ 599 4099

Quick Facts

Course overview

The highlights

Program offerings

Course and certificate fees

Fees information

certificate availability

certificate providing authority

Who it is for

What you will learn

The syllabus