SQL and PostgreSQL: The Complete Developer’s Guide

SQL and PostgreSQL: The Complete Developer’s Guide

English | MP4 | AVC 1920×1080 | AAC 44KHz 2ch | 290 lectures (22h 13m) | 8.09 GB

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

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!

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
Table of Contents

Simple But Powerful SQL Statements
1 Join Our Community
2 Course Resources
3 What is PostgreSQL All About
4 Database Design
5 Creating Tables
6 Analyzing CREATE TABLE
7 Inserting Data Into a Table
8 Retrieving Data with Select
9 Calculated Columns
10 Calculating Phone Revenue
11 Exercise Solution
12 String Operators and Functions

Filtering Records
13 Filtering Rows with Where
14 More on the Where Keyword
15 Compound Where Clauses
16 A Where Exercise Overview
17 A Where Solution
18 Where With Lists
19 A Where With Lists Solution
20 Calculations in Where Clauses
21 Solving Calculations
22 Updating Rows
23 Deleting Rows
24 A Solution for Updating Rows
25 Solution for Deleting Rows

Working with Tables
26 The Plan Moving Forward
27 Approaching Database Design
28 OnetoMany and ManytoOne Relationships
29 OnetoOne and ManytoMany Relationships
30 Primary Keys and Foreign Keys
31 Understanding Foreign Keys
32 AutoGenerated IDs
33 Creating Foreign Key Columns
34 Running Queries on Associated Data
35 Exercise Overview
36 Foreign Key Creation Solution
37 Foreign Key Constraints Around Insertion
38 Constraints Around Deletion
39 Commands Youll Need for the Next Video
40 Testing Deletion Constraints
41 Setting Foreign Keys to Null on Delete
42 Adding Some Complexity

Relating Records with Joins
43 Adding Some Data
44 Queries with Joins and Aggregations
45 Joining Data from Different Tables
46 Another Quick Join
47 Exercise Overview
48 A Joinful Solution
49 Alternate Forms of Syntax
50 Missing Data in Joins
51 Why Wasnt It Included
52 Four Kinds of Joins
53 Each Join in Practice
54 Does Order Matter
55 Exercise Overview
56 Two Possible Solutions
57 Where with Join
58 Three Way Joins
59 A Bit of Practice
60 Exercise Solution

Aggregation of Records
61 Aggregating and Grouping
62 Picturing Group By
63 Aggregate Functions
64 Combining Group By and Aggregates
65 A Gotcha with Count
66 Visualizing More Grouping
67 Exercise Overview
68 Grouping Solution
69 Adding a Layer of Difficulty
70 Solution
71 Filtering Groups with Having
72 Having In Action
73 More on Having
74 A Having Exercise Overview
75 A Quick Solution

Working with Large Datasets
76 A New Dataset
77 Investigating This Dataset
78 Some Group By Practice
79 Group By Review Solution
80 Remember Joins
81 Of Course You Remember

Sorting Records
82 The Basics of Sorting
83 Two Variations on Sorting
84 Offset and Limit
85 Exercise Overview
86 Exercise Solution

Unions and Intersections with Sets
87 Handling Sets with Union
88 A Few Notes on Union
89 Commonalities with Intersect
90 Removing Commonalities with Except
91 Union Exercise Overview
92 Exercise Solution

Assembling Queries with SubQueries
93 Whats a Subquery
94 Thinking About the Structure of Data
95 Subqueries in a Select
96 Exercise Overview
97 Select Solution
98 Subqueries in a From
99 From Subqueries that Return a Value
100 Example of a Subquery in a From
101 Exercise Overview
102 Exercise Solution
103 Subqueries in a Join Clause
104 More Useful Subqueries with Where
105 Data Structure with Where Subqueries
106 Exercise Overview
107 Exercise Solution
108 The Not In Operator with a List
109 A New Where Operator
110 Finally Some
111 Exercise Overview
112 A Quick Solution
113 Probably Too Much About Correlated Subqueries
114 More on Correlated Subqueries
115 A Select Without a From
116 Exercise Overview
117 Exercise Solution

Selecting Distinct Records
118 Selecting Distinct Values
119 Exercise Overview
120 A Distinct Solution

Utility Operators Keywords and Functions
121 The Greatest Value in a List
122 And the Least Value in a List
123 The Case Keyword

Local PostgreSQL Installation
124 PostgreSQL Installation on macOS
125 PGAdmin Setup on macOS
126 Postgres installation on Windows

PostgreSQL Complex Datatypes
127 Whatd We Just Do
128 Data Types
129 Fast Rules on Numeric Data Types
130 More on Number Data Types
131 Reminder on Character Types
132 Boolean Data Types
133 Times Dates and Timestamps
134 Really Awesome Intervals

DatabaseSide Validation and Constraints
135 Thinking About Validation
136 Creating and Viewing Tables in PGAdmin
137 Applying a Null Constraint
138 Solving a Gotcha with Null Constraints
139 Default Column Values
140 Applying a Unique Constraint to One column
141 MultiColumn Uniqueness
142 Adding a Validation Check
143 Checks Over Multiple Columns
144 So Where Are We Applying Validation

Database Structure Design Patterns
145 Approaching More Complicated Designs
146 Using a SQL Design Tool
147 A Configbased Schema Designer
148 Heres the Plan
149 Rebuilding Some Schema

How to Build a Like System
150 Requirements of a Like System
151 How Not to Design a Like System
152 Designing a Like System
153 Making a Reaction System Instead
154 Polymorphic Associations
155 Polymorphic Association Alternative Implementation
156 The Simplest Alternative
157 So Which Approach

How to Build a Mention System
158 Additional Features Around Posts
159 Adding Captions and Locations
160 Photo Mentions vs Caption Mentions
161 Considerations on Photo Tags vs Caption Tags
162 Update For Tags

How to Build a Hashtag System
163 Designing a Hashtag System
164 Tables for Hashtags
165 Including the Hashtag Table
166 A Few More User Columns
167 Why No Number of Followers or Posts

How to Design a Follower System
168 Designing a Follower System

Implementing Database Design Patterns
169 Back to Postgres
170 Creating Tables with Checks
171 Posts Creation
172 Comments Creation
173 Likes Creation
174 Photo Tags and Caption Tags
175 Creating Hashtags Hashtag Posts and Followers

Approaching and Writing Complex Queries
176 Quick Note About Adding Some Data
177 Adding Some Data
178 Restoring from Scratch
179 Highest User IDs Exercise
180 Solution for User IDs
181 Posts by a Particular User
182 Solving for Posts by User
183 Likes Per User
184 Solution for Likes Per User

Understanding the Internals of PostgreSQL
185 Thinking About Performance
186 Where Does Postgres Store Data
187 Heaps Blocks and Tuples
188 Block Data Layout
189 Heap File Layout

A Look at Indexes for Performance
190 Full Table Scans
191 Whats an Index
192 How an Index Works
193 Creating an Index
194 Benchmarking Queries
195 Downsides of Indexes
196 Index Types
197 Automatically Generated Indexes
198 Behind the Scenes of Indexes

Basic Query Tuning
199 The Query Processing Pipeline
200 Explain and Explain Analyze
201 Solving an Explain Mystery

Advanced Query Tuning
202 Developing an Intuitive Understanding of Cost
203 Calculating Cost by Hand
204 A Touch More on Costs
205 Startup vs Total Costs
206 Costs Flow Up
207 Use My Index

Simple Common Table Expressions
208 Common Table Expressions
209 A Quick Solution
210 So Whats a CTE

Recursive Common Table Expressions
211 Recursive CTEs
212 Recursive CTEs Step by Step
213 Why Use Recursive CTEs
214 Writing the Query
215 Walking Through Recursion

Simplifying Queries with Views
216 Most Popular Users
217 A Possible Solution for Merging Tables
218 Creating a View
219 When to Use a View
220 Deleting and Changing Views

Optimizing Queries with Materialized Views
221 Materialized Views
222 Grouping by Week
223 Reminder on Left Joins
224 Writing a Slow Query
225 Creating and Refreshing Materialized Views

Handling Concurrency and Reversibility with Transactions
226 What are Transactions Used For
227 Some Sample Data
228 Opening and Closing Transactions
229 Transaction Cleanup on Crash
230 Closing Aborted Transactions

Managing Database Design with Schema Migrations
231 A Story on Migrations
232 Migration Files
233 Issues Solved by Migrations
234 A Few Notes on Migrations Libraries
235 A Note About Node Installation
236 Project Creation
237 Generating and Writing Migrations
238 Applying and Reverting Migrations
239 Generating and Applying a Second Migration

Schema vs Data Migrations
240 Schema vs Data Migrations
241 Dangers Around Data Migrations
242 Properly Running Data and Schema Migrations
243 Creating a Posts Table
244 A Small Web Server
245 Web Server Setup Instructions
246 Adding the Loc Column
247 Writing Values to Both Columns
248 Transaction Locks
249 Updating Values
250 Migrations Setup Instructions
251 Updating the App Server
252 Dropping the Last Columns

Accessing PostgreSQL From APIs
253 Section Goal
254 Initial Setup
255 One Fast Migration
256 Building the Users Router
257 Understanding Connection Pools
258 Validating Connection Credentials
259 Query and Close

Data Access Pattern Repositories
260 The Repository Pattern
261 Creating a Repository
262 Accessing the API
263 Casing Issues
264 Fixing Casing
265 Finding Particular Users

Security Around PostgreSQL
266 SQL Injection Exploits
267 Handling SQL Injection with Prepared Statements
268 Preventing SQL Injection
269 Reminder on Post Requests
270 Inserting Users
271 Handling Updates
272 And Finally Delete

Fast Parallel Testing
273 A Note on Testing
274 Assertions Around User Count
275 Connecting to a DB For Tests
276 Disconnecting After Tests
277 MultiDB Setup
278 Assumptions Around Content
279 Issues with Parallel Tests
280 Isolation with Schemas
281 Creating and Accessing Schemas
282 Controlling Schema Access with Search Paths
283 Routing Schema Access
284 Strategy for Isolation
285 Programmatic Schema Creation
286 Escaping Identifiers
287 Test Helpers
288 Cleaning up Schemas and Roles
289 Finally Parallel Tests Final Lecture

290 Bonus