SQL and PostgreSQL: The Complete Developer's Guide

Become an expert with SQL and PostgreSQL! Store and fetch data, tune queries, and design efficient database structures!

4.69 (8911 reviews)
Udemy
platform
English
language
Databases
category
instructor
59,629
students
22 hours
content
Mar 2024
last update
$119.99
regular price

What you will learn

Master the fundamentals of relational databases

Acquire the skills necessary to obtain a database administrator (DBA) or database developer job

Design fast and efficient databases using the latest technologies

Practice your skills with many quizzes, projects, and built-in exercises

Learn and apply multiple database design patterns

Optimize queries for superb read and write efficiency

Understand how PostgreSQL stores information at the hardware level

Connect PostgreSQL to front-end apps using an API

Build common app features, such as a 'like' and 'mention' systems

Handle complex concurrency issues and race conditions

Advance your database designs using schema migrations

See how to use PostgreSQL to speed up automated tests

Description

Database structure design?  It's here.  Query tuning and optimization? You'll master it.  Complex queries? Yes indeed!

This is the only course online that will teach you how to design a database, store complex data, optimize your queries, everything that is needed for operating a production, scalable database!

Every app in the world, whether it is a mobile app built with Flutter, a web app constructed with React, or a plain HTML page, needs to store information in a database.  Although there are many different databases you can use, PostgreSQL has been a number-one pick for decades, offering scalable performance, rock-solid uptime, and flexible design systems.  This course will teach you everything you need to know about PostgreSQL to use it on your next big project!

Don't know anything about databases at all? No problem. We begin with an overview of SQL, the language used to interact with PostgreSQL. Through an incredible number of exercises, you'll get practical, hands on experience working with realistic datasets.  You will understand how to store, fetch, and update information with a series of powerful commands. 

After gaining experience with elementary operations, you will move on to understanding database design patterns, offering multiple ways to structure your database for efficient data modeling. You'll see many different scenarios with different types of data, and understand the pros and cons to several approaches.

This course is packed with practice exercises and quizzes. You'll get immediate practice on all of the different topics and features that you learn!  In addition, you'll understand exactly where to use each of these features in real and practical projects.

Besides getting practical hands-on experience, you'll also get a behind-the-scenes look at how PostgreSQL works internally. We'll dive into raw data files, investigating how PostgreSQL stores information on your hard drive bit by bit.  This knowledge is invaluable when it comes time to start tuning your queries for performance.  By having a natural instinct of how PostgreSQL works, you can plan on being able to get every last bit of performance out of your database.

Here is a partial list of some of the topics that are covered in this course:

  • Apply powerful SQL commands to store, update, and retrieve information

  • Build relationships between records using foreign keys between tables

  • Understand PostgreSQL's numerous data types, and when to use each

  • Assemble reports of business data by using aggregation pipelines

  • Work with millions of records to simulate real production queries

  • Exercise your query logic skills through the use of sets and sorting operators

  • Compose queries out of smaller reusable units using subqueries

  • Use different design patterns to efficiently model your data

  • See how to divide database objects into different schemas to maintain them more easily

  • Validate your data using simple 'check' rules

  • Construct perfect designs for common features such as 'like', 'follow', and 'tag' systems

  • Speed up your queries with indexes - you'll see exactly how they work behind the scenes!

  • Dive into some of the most complex queries around with recursive common table expressions

  • Deal with concurrency issues easily by applying transactions

I made this course to be the perfect resource for engineers just getting started with their first database.  Master the database itself, and see how to apply it with real designs.  Sign up today and master PostgreSQL!

Content

Simple - But Powerful - SQL Statements

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 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

Agg

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!

Screenshots

SQL and PostgreSQL: The Complete Developer's Guide - Screenshot_01SQL and PostgreSQL: The Complete Developer's Guide - Screenshot_02SQL and PostgreSQL: The Complete Developer's Guide - Screenshot_03SQL and PostgreSQL: The Complete Developer's Guide - Screenshot_04

Reviews

Rohith
November 19, 2023
By default I would rate Stephen's all courses as 5 star, at least for me he is the perfect teacher that I wish for ?, Glad that I found his courses on Udemy .... Thanks a ton Stephie G ☺
Adam
November 18, 2023
I'm an experienced web developer and I found the first 3/4's of the course to be extremely useful. The SQL and Postgres concepts were explained clearly and I've come away with a lot of useful information. Personally I did not need the last 1/4 of the course as I am quite familiar with querying and using a DB in a production application and while testing. If you are new to the software industry the last 1/4 of the course will be useful to you as well
Jeff
November 1, 2023
Clear explanation on how to design DB. Great for beginner to understand Postgresql. I'm glad I took this course
Emmanuel
October 24, 2023
This course was a GREAT experience. The structure, the level of details and attention, the instructor's locution, everything was off the charts. It kept me interested and motivated from start to finish. It is a MUST have course for all people interested in SQL and PostgreSQL. Many thanks Stephen, it was a delight.
Djamel
October 23, 2023
I expected more advanced sql queries because the title said "Become an expert with SQL", the course is good for beginners.
Tiago
October 23, 2023
Very interactive course, exceeded my expectations. Has all the essecials to learn SQL and PostgresSQL
Dy
September 6, 2023
His explanation is crystal clear. I love it even sometimes it is a bit long. But his desire is helping us to understand clearly! Thanks!
Matt
July 1, 2023
The fact that there are exercises constantly is 100% reinforcing SQL for me already. It takes more effort for the instructor to do this but it's definitely a better teaching method! Looking forward to completing the course.
Shahzad
June 27, 2023
By far one of the best hands-on courses I have taken. Worth the money and really gives you insights into the world of Postgres
Joseph
June 26, 2023
Clarity. I think where a lot of these courses fail is when there is not a simplified way of explaining examples and concepts. This is well thought out so I understand it and well paced, so that I can follow along and not feel I'm falling behind on the concepts and feeling overwhelmed.
Ivan
June 23, 2023
This course is a great learning resource. Also having a section about writing automated tests proves that we are talking about a pro course here.
Bishesh
June 20, 2023
This course is the best one yet and I would recommend it to everyone who wants to deep dive into the sql world
Evgheni
June 20, 2023
The fact the Stephen found time to go over some low level details is admirable. Great content as always. Love the fact the prioritize explaining how things work, instead of simply feeding us some information and examples
Jonathan
June 15, 2023
Great course. I love how it started simple then built up on concepts. Use of diagrams was really useful. I really liked putting it all together at the end of the course with the use of creating an express app. I will be using this course for years to come, I am sure.
Mohamed
June 13, 2023
The course is very good discussing and applying SQL. Some topics, like subqueries, need more real examples. Overall, great work as expected from Stephen.

Coupons

DateDiscountStatus
10/21/202095% OFF
expired
11/19/202095% OFF
expired
2/12/202194% OFF
expired
7/11/202192% OFF
expired
3/31/202292% OFF
expired
11/23/202284% OFF
expired

Charts

Price

SQL and PostgreSQL: The Complete Developer's Guide - Price chart

Rating

SQL and PostgreSQL: The Complete Developer's Guide - Ratings chart

Enrollment distribution

SQL and PostgreSQL: The Complete Developer's Guide - Distribution chart

Related Topics

3350536
udemy ID
7/20/2020
course created date
10/20/2020
course indexed date
Bot
course submited by