SQL and PostgreSQL: The Complete Developer’s Guide

SQL and PostgreSQL: The Complete Developer’s Guide
SQL and PostgreSQL: The Complete Developer’s Guide
English | MP4 | AVC 1920×1080 | AAC 44KHz 2ch | 22 Hours | 7.97 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 What is PostgreSQL All About?
2 Database Design
3 Creating Tables
4 Analyzing CREATE TABLE
5 Inserting Data Into a Table
6 Retrieving Data with Select
7 Calculated Columns
8 Calculating Phone Revenue
9 Exercise Solution
10 String Operators and Functions

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

Working with Tables
24 The Plan Moving Forward
25 Approaching Database Design
26 One-to-Many and Many-to-One Relationships
27 One-to-One and Many-to-Many Relationships
28 Primary Keys and Foreign Keys
29 Understanding Foreign Keys
30 Auto-Generated ID’s
31 Creating Foreign Key Columns
32 Running Queries on Associated Data
33 Exercise Overview
34 Foreign Key Creation Solution
35 Foreign Key Constraints Around Insertion
36 Constraints Around Deletion
37 Commands You’ll Need for the Next Video
38 Testing Deletion Constraints
39 Setting Foreign Keys to Null on Delete
40 Adding Some Complexity

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

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

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

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

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

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

Selecting Distinct Records
116 Selecting Distinct Values
117 Exercise Overview
118 A Distinct Solution

Utility Operators, Keywords, and Functions
119 The Greatest Value in a List
120 And the Least Value in a List!
121 The Case Keyword

Local PostgreSQL Installation
122 PostgreSQL Installation on macOS
123 PGAdmin Setup on macOS
124 Postgres installation on Windows

PostgreSQL Complex Datatypes
125 What’d We Just Do?
126 Data Types
127 Fast Rules on Numeric Data Types
128 More on Number Data Types
129 Reminder on Character Types
130 Boolean Data Types
131 Times, Dates, and Timestamps
132 Really Awesome Intervals

Database-Side Validation and Constraints
133 Thinking About Validation
134 Creating and Viewing Tables in PGAdmin
135 Applying a Null Constraint
136 Solving a Gotcha with Null Constraints
137 Default Column Values
138 Applying a Unique Constraint to One column
139 Multi-Column Uniqueness
140 Adding a Validation Check
141 Checks Over Multiple Columns
142 So Where Are We Applying Validation?

Database Structure Design Patterns
143 Approaching More Complicated Designs
144 Using a SQL Design Tool
145 A Config-based Schema Designer
146 Here’s the Plan
147 Rebuilding Some Schema

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

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

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

How to Design a ‘Follower’ System
166 Designing a Follower System

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

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

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

A Look at Indexes for Performance
188 Full Table Scans
189 What’s an Index
190 How an Index Works
191 Creating an Index
192 Benchmarking Queries
193 Downsides of Indexes
194 Index Types
195 Automatically Generated Indexes
196 Behind the Scenes of Indexes

Basic Query Tuning
197 The Query Processing Pipeline
198 Explain and Explain Analyze
199 Solving an Explain Mystery

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

Simple Common Table Expressions
206 Common Table Expressions
207 A Quick Solution
208 So What’s a CTE?

Recursive Common Table Expressions
209 Recursive CTE’s
210 Recursive CTE’s Step by Step
211 Why Use Recursive CTE’s?
212 Writing the Query
213 Walking Through Recursion

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

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

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

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

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

Accessing PostgreSQL From API’s
251 Section Goal
252 Initial Setup
253 One Fast Migration
254 Building the Users Router
255 Understanding Connection Pools
256 Validating Connection Credentials
257 Query and Close

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

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

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