Sql And Postgresql
Tags: SQL
Become an expert with SQL and PostgreSQL! Store and fetch data, tune queries, and design efficient database structures!
Last updated 2022-01-10 | 4.7
- Learn and apply multiple database design patterns- Optimize queries for superb read and write efficiency
- Understand how PostgreSQL stores information at the hardware level
What you'll learn
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
Practice all of these skills using a huge number of built-in exercises
* Requirements
* A Mac* PC
* or Linux-based Computer
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!
Who this course is for:
- Any developers looking to understand databases
- Application engineers seeking to expand their backend skillset
Course content
37 sections • 334 lectures
Join Our Community! Preview 00:07
Course Resources Preview 00:38
What is PostgreSQL All About? Preview 04:12
Database Design Preview 05:11
Database Terminology
Creating Tables Preview 04:42
Analyzing CREATE TABLE Preview 03:30
Inserting Data Into a Table Preview 04:49
Retrieving Data with Select Preview 02:07
Create, Insert, and Select!
Calculated Columns Preview 04:37
Calculating Phone Revenue Preview 00:58
Using Calculated Columns
Exercise Solution Preview 01:03
String Operators and Functions Preview 06:17
Filtering Rows with "Where" Preview 03:49
More on the "Where" Keyword Preview 02:30
Compound "Where" Clauses Preview 05:24
A "Where" Exercise Overview Preview 00:37
Practicing Where Statements
A "Where" Solution Preview 00:56
"Where" With Lists Preview 00:37
A More Challenging 'Where'
A "Where" With Lists Solution Preview 01:42
Calculations in "Where" Clauses Preview 02:05
Trying Calculations in Where Clauses
Solving Calculations Preview 01:51
Updating Rows Preview 04:16
Deleting Rows Preview 03:16
Try Updating Records In a Table!
A Solution for Updating Rows Preview 01:29
Practice Deleting Records
Solution for Deleting Rows Preview 01:17
The Plan Moving Forward Preview 01:43
Approaching Database Design Preview 07:28
Let's Design Some Schema
One-to-Many and Many-to-One Relationships Preview 06:33
A 'Has One' or 'Has Many'?
One-to-One and Many-to-Many Relationships Preview 05:41
Identifying One-to-One and Many-to-Many Relationships
Primary Keys and Foreign Keys Preview 05:29
Understanding Foreign Keys Preview 08:19
Foreign Keys; How Do They Work?
Auto-Generated ID's Preview 05:47
Creating Foreign Key Columns Preview 04:38
Running Queries on Associated Data Preview 06:48
Exercise Overview Preview 02:07
Creating and Using Foreign Keys
Foreign Key Creation Solution Preview 02:09
Foreign Key Constraints Around Insertion Preview 05:35
Constraints Around Deletion Preview 05:00
Commands You’ll Need for the Next Video Preview 00:22
Testing Deletion Constraints Preview 04:37
Setting Foreign Keys to Null on Delete Preview 02:58
What Happens On Delete?
Adding Some Complexity Preview 02:56
Adding Some Data Preview 03:56
Queries with Joins and Aggregations Preview 03:23
Joining Data from Different Tables Preview 07:38
Another Quick Join Preview 02:22
Exercise Overview Preview 01:13
Practice Joining Data
A Joinful Solution Preview 01:42
Alternate Forms of Syntax Preview 05:51
Missing Data in Joins Preview 06:16
Why Wasn't It Included Preview 02:22
Four Kinds of Joins Preview 09:25
Each Join in Practice Preview 04:14
Does Order Matter? Preview 04:11
Test Your Joining Knowledge
Exercise Overview Preview 00:55
Joins, Joins, Join!
Two Possible Solutions Preview 02:59
Where with Join Preview 06:44
Three Way Joins Preview 08:29
A Bit of Practice Preview 04:58
Three Way Exercise
Exercise Solution Preview 02:52
Aggregating and Grouping Preview 02:10
Picturing Group By Preview 05:29
Selecting Columns After Grouping
Aggregate Functions Preview 02:59
Combining Group By and Aggregates Preview 04:47
A Gotcha with Count Preview 02:35
Visualizing More Grouping Preview 03:10
Exercise Overview Preview 00:39
Practice For Grouping and Aggregating
Grouping Solution Preview 01:20
Adding a Layer of Difficulty Preview 00:42
Grouping With a Join!
Solution Preview 01:27
Filtering Groups with Having Preview 04:43
Having In Action Preview 02:35
More on Having! Preview 06:47
A Having Exercise Overview Preview 03:36
Practice Yourself Some Having
A Quick Solution Preview 02:32
A New Dataset Preview 10:22
Investigating This Dataset Preview 02:34
Some Group By Practice Preview 01:20
Group By Review
Group By Review Solution Preview 02:25
Remember Joins? Preview 02:13
Inner Join Review
Of Course You Remember! Preview 01:25
The Basics of Sorting Preview 02:14
Two Variations on Sorting Preview 02:40
Offset and Limit Preview 06:38
Exercise Overview Preview 00:25
Sorting, Offsetting, and Limiting
Exercise Solution Preview 01:20
Handling Sets with Union Preview 05:43
A Few Notes on Union Preview 03:45
Commonalities with Intersect Preview 03:23
Removing Commonalities with Except Preview 05:49
Union Exercise Overview Preview 01:26
Merging Results with Union
Exercise Solution Preview 01:27
What's a Subquery? Preview 05:48
Thinking About the Structure of Data Preview 04:46
What's the Data Look Like?
Subqueries in a Select Preview 05:41
Exercise Overview Preview 01:05
Embedding in Select
Select Solution Preview 01:18
Subqueries in a From Preview 07:58
From Subqueries that Return a Value Preview 02:45
Example of a Subquery in a From Preview 08:56
Exercise Overview Preview 01:15
Subquery From's
Exercise Solution Preview 01:59
Subqueries in a Join Clause Preview 04:52
More Useful - Subqueries with Where Preview 08:11
Data Structure with Where Subqueries Preview 05:35
Exercise Overview Preview 00:57
Subquery Where's
Exercise Solution Preview 02:05
The Not In Operator with a List Preview 04:09
A New Where Operator Preview 06:20
Finally Some! Preview 05:21
Is It A Valid Subquery?
Exercise Overview Preview 00:27
Practice Your Subqueries!
A Quick Solution Preview 01:33
Probably Too Much About Correlated Subqueries Preview 16:39
More on Correlated Subqueries Preview 08:53
A Select Without a From? Preview 03:28
Exercise Overview Preview 00:45
From-less Selects
Exercise Solution Preview 01:19
Selecting Distinct Values Preview 04:14
Exercise Overview Preview 00:29
Some Practice with Distinct
A Distinct Solution Preview 00:40
The Greatest Value in a List Preview 03:20
And the Least Value in a List! Preview 02:01
The Case Keyword Preview 04:13
PostgreSQL Installation on macOS Preview 05:47
PGAdmin Setup on macOS Preview 04:56
Postgres installation on Windows Preview 03:55
What'd We Just Do? Preview 04:02
Data Types Preview 04:09
Fast Rules on Numeric Data Types Preview 02:01
More on Number Data Types Preview 09:19
Reminder on Character Types Preview 04:42
Boolean Data Types Preview 02:28
Times, Dates, and Timestamps Preview 05:30
Really Awesome Intervals Preview 05:44
Thinking About Validation Preview 04:12
Creating and Viewing Tables in PGAdmin Preview 07:19
Applying a Null Constraint Preview 06:24
Solving a Gotcha with Null Constraints Preview 04:09
Creating NULL Constraints
Default Column Values Preview 03:13
Applying a Unique Constraint to One column Preview 05:35
Multi-Column Uniqueness Preview 03:40
Is It Unique?
Adding a Validation Check Preview 03:58
Checks Over Multiple Columns Preview 05:04
Does It Pass a Check?
So Where Are We Applying Validation? Preview 05:46
Approaching More Complicated Designs Preview 03:13
Using a SQL Design Tool Preview 06:06
A Config-based Schema Designer Preview 07:02
Here's the Plan Preview 02:15
Rebuilding Some Schema Preview 06:54
Requirements of a Like System Preview 04:35
How Not to Design a Like System Preview 02:58
Designing a Like System Preview 05:04
Building a Similar System
Making a Reaction System Instead Preview 01:34
Polymorphic Associations Preview 05:58
Polymorphic Association Alternative Implementation Preview 06:56
The Simplest Alternative Preview 03:39
Polymorphic Associations
So Which Approach? Preview 04:21
Additional Features Around Posts Preview 06:10
Adding Captions and Locations Preview 01:39
Photo Mentions vs Caption Mentions Preview 07:27
Considerations on Photo Tags vs Caption Tags Preview 07:08
Update For Tags Preview 05:01
Designing a Hashtag System Preview 07:33
Tables for Hashtags Preview 03:36
Including the Hashtag Table Preview 04:06
A Few More User Columns Preview 06:01
Why No Number of Followers or Posts? Preview 03:44
Designing a Follower System Preview 05:50
Back to Postgres Preview 01:45
Creating Tables with Checks Preview 13:00
Posts Creation Preview 08:31
Comments Creation Preview 02:16
Likes Creation Preview 06:52
Photo Tags and Caption Tags Preview 05:28
Creating Hashtags, Hashtag Posts, and Followers Preview 06:27
Quick Note About Adding Some Data Preview 00:10
Adding Some Data Preview 04:06
Restoring from Scratch Preview 04:24
Highest User ID's Exercise Preview 01:41
Solution for User ID's Preview 01:09
Posts by a Particular User Preview 01:05
Solving for Posts by User Preview 01:56
Likes Per User Preview 00:50
Solution for Likes Per User Preview 01:41
Thinking About Performance Preview 02:02
Where Does Postgres Store Data? Preview 05:51
Heaps, Blocks, and Tuples Preview 03:36
Terminology Check
Block Data Layout Preview 04:20
Heap File Layout Preview 31:52
Full Table Scans Preview 04:08
What's an Index Preview 02:23
How an Index Works Preview 07:58
Creating an Index Preview 03:58
Benchmarking Queries Preview 05:27
Downsides of Indexes Preview 05:09
Index Types Preview 01:35
Automatically Generated Indexes Preview 03:37
Using Automatically Created Indexes
Behind the Scenes of Indexes Preview 31:55
The Query Processing Pipeline Preview 04:57
Explain and Explain Analyze Preview 05:25
Explain Vs Explain Analyze
Solving an Explain Mystery Preview 08:58
Developing an Intuitive Understanding of Cost Preview 11:57
Calculating Cost by Hand Preview 06:20
A Touch More on Costs Preview 07:17
Calculating Costs
Startup vs Total Costs Preview 05:34
Costs Flow Up Preview 01:48
Use My Index! Preview 07:55
Common Table Expressions Preview 01:52
A Quick Solution Preview 03:52
So What's a CTE? Preview 03:49
Recursive CTE's Preview 03:18
Recursive CTE's Step by Step Preview 10:21
Why Use Recursive CTE's? Preview 04:33
Writing the Query Preview 08:02
Walking Through Recursion Preview 09:47
Most Popular Users Preview 06:16
A Possible Solution for Merging Tables Preview 03:51
Creating a View Preview 05:36
When to Use a View? Preview 03:19
Deleting and Changing Views Preview 02:26
Materialized Views Preview 02:18
Grouping by Week Preview 04:08
Reminder on Left Joins Preview 04:29
Writing a Slow Query Preview 09:18
Creating and Refreshing Materialized Views Preview 06:37
Views vs Materialized Views
What are Transactions Used For? Preview 04:03
Some Sample Data Preview 01:59
Opening and Closing Transactions Preview 09:44
Transaction Cleanup on Crash Preview 04:03
Closing Aborted Transactions Preview 01:45
A Story on Migrations Preview 17:13
Migration Files Preview 05:00
Issues Solved by Migrations Preview 02:51
A Few Notes on Migrations Libraries Preview 04:38
A Note About Node Installation Preview 00:06
Project Creation Preview 02:52
Generating and Writing Migrations Preview 07:11
Applying and Reverting Migrations Preview 07:15
Generating and Applying a Second Migration Preview 03:37
Schema vs Data Migrations Preview 04:08
Dangers Around Data Migrations Preview 09:06
Properly Running Data and Schema Migrations Preview 05:28
Creating a Posts Table Preview 04:39
A Small Web Server Preview 14:52
Web Server Setup Instructions Preview 00:38
Adding the Loc Column Preview 04:10
Writing Values to Both Columns Preview 04:12
Transaction Locks Preview 13:39
Updating Values Preview 04:19
Migrations Setup Instructions Preview 00:29
Updating the App Server Preview 04:13
Dropping the Last Columns Preview 03:01
Section Goal Preview 00:52
Initial Setup Preview 01:34
One Fast Migration Preview 05:01
Building the Users Router Preview 04:53
Understanding Connection Pools Preview 06:31
Validating Connection Credentials Preview 06:56
Query and Close Preview 01:57
The Repository Pattern Preview 03:38
Creating a Repository Preview 08:20
Accessing the API Preview 03:51
Casing Issues Preview 04:21
Fixing Casing Preview 06:33
Finding Particular Users Preview 05:26
SQL Injection Exploits Preview 06:40
Handling SQL Injection with Prepared Statements Preview 07:48
Preventing SQL Injection Preview 07:32
Reminder on Post Requests Preview 02:51
Inserting Users Preview 04:13
Handling Updates Preview 04:36
And, Finally, Delete Preview 04:05
A Note on Testing Preview 02:47
Assertions Around User Count Preview 07:04
Connecting to a DB For Tests Preview 05:01
Disconnecting After Tests Preview 02:45
Multi-DB Setup Preview 04:40
Assumptions Around Content Preview 04:44
Issues with Parallel Tests Preview 04:48
Isolation with Schemas Preview 04:09
Creating and Accessing Schemas Preview 05:21
Controlling Schema Access with Search Paths Preview 05:47
Routing Schema Access Preview 04:02
Strategy for Isolation Preview 04:42
Programmatic Schema Creation Preview 11:44
Escaping Identifiers Preview 04:20
Test Helpers Preview 05:18
Cleaning up Schemas and Roles Preview 05:56
Finally... Parallel Tests! Preview 03:32
Bonus! Preview 00:22
This course includes:
- 22 hours on-demand video
- Access on mobile and TV
- Certificate of completion
Related Courses
Recent Posts
What Is Training Data? How it’s used in Machine Learning
What is Machine learning? – This is one of the most frequently asked questions. It is a kind of Artificial Intelligence (AI) by which software ...Read more
Udemy vs Coursera: which platform is best for learning tech skills
Technology is both the present and the future of the world. People in every corner of the world appreciate and welcome professional-level tech skills. It ...Read more
Intriguing Online Courses For Sport Trainees
Due to the Covid pandemic, we have been strictly restricted from outdoor activities to comply with the isolation regulation. Luckily, with the existence of countless ...Read more
What are the possible paralegal careers?
Taking on a career in Paralegal can be a tough decision, especially for students, who are looking for entry level Paralegal jobs. No one wants ...Read more
Restaurant Managers: Job description, education requirements, and salary
If you like to run a place and it’s time for you to make a career choice, you should consider becoming a restaurant manager. Restaurant ...Read more
