SQL and PostgreSQL: The Complete Developer's Guide

22h 24m 31s
English
Paid
May 18, 2023

SQL and PostgreSQL: The Complete developer’s Guide is a comprehensive SQL language training course and PostgreSQL database management system . Nowadays, all applications, from web applications and vmvyabel to a simple HTML page, need to store information in the database . Despite the numerous databases available, PostgreSQL has become the number one choice for developers due to its high efficiency, uninterrupted functionality and flexible design . 

More

During this period, even if you do not have any knowledge of the database, you will become its expert !  The course With provide an overview of the SQL language, that to interact with PostgreSQL is needed started, and then whatever for storage and get the data, the design of the structure, and tables for effective and optimized jQuery is necessary education is given .

In this course, you will be familiar with different types of data and how to deal with them and build a proportional database for each of them . Numerous examples and tests that are included throughout this course are very effective in understanding and learning its subjects . Among the topics this course can be used to learn the template design of the database, apply the commands of the powerful, SQL for the storage, update and get information. making connections between records, etc. familiarity with different type of data PostgreSQL and the application of them. receive reports from the trade data, etc. split the database into schema for access and maintenance easier, they and Data Validation using the rules noted . In addition to the aforementioned functional topics, you will also be familiar with how PostgreSQL works and how raw data is processed by it .

What things to learn

Learn and apply multiple database design patterns
Optimization of queries
Familiarity with the internal function of PostgreSQL
Connecting PostgreSQL to front-end applications using the API
Managing complex concurrency problems
Progress of database design using schema (schema )
And …

Watch Online SQL and PostgreSQL: The Complete Developer's Guide

Join premium to watch
Go to premium
# Title Duration
1 What is PostgreSQL All About? 04:13
2 Database Design 05:12
3 Creating Tables 04:43
4 Analyzing CREATE TABLE 03:31
5 Inserting Data Into a Table 04:50
6 Retrieving Data with Select 02:08
7 Calculated Columns 04:38
8 Calculating Phone Revenue 07:00
9 Exercise Solution 05:27
10 String Operators and Functions 09:54
11 Filtering Rows with "Where" 03:50
12 More on the "Where" Keyword 02:31
13 Compound "Where" Clauses 05:25
14 A "Where" Exercise Overview 00:38
15 A "Where" Solution 00:57
16 "Where" With Lists 07:18
17 A "Where" With Lists Solution 01:43
18 Calculations in "Where" Clauses 02:06
19 Solving Calculations 01:52
20 Updating Rows 06:02
21 Deleting Rows 03:17
22 A Solution for Updating Rows 01:30
23 Solution for Deleting Rows 01:18
24 The Plan Moving Forward 01:44
25 Approaching Database Design 08:35
26 One-to-Many and Many-to-One Relationships 06:34
27 One-to-One and Many-to-Many Relationships 05:42
28 Primary Keys and Foreign Keys 05:30
29 Understanding Foreign Keys 08:20
30 Auto-Generated ID's 05:48
31 Creating Foreign Key Columns 04:39
32 Running Queries on Associated Data 06:49
33 Exercise Overview 02:08
34 Foreign Key Creation Solution 02:10
35 Foreign Key Constraints Around Insertion 05:36
36 Constraints Around Deletion 05:01
37 Testing Deletion Constraints 04:38
38 Setting Foreign Keys to Null on Delete 02:59
39 Adding Some Complexity 02:57
40 Queries with Joins and Aggregations 03:24
41 Joining Data from Different Tables 07:40
42 Another Quick Join 02:23
43 Exercise Overview 01:14
44 A Joinful Solution 01:44
45 Alternate Forms of Syntax 05:52
46 Missing Data in Joins 06:17
47 Why Wasn't It Included 02:23
48 Four Kinds of Joins 09:26
49 Each Join in Practice 04:15
50 Does Order Matter? 04:12
51 Exercise Overview 00:56
52 Two Possible Solutions 03:00
53 Where with Join 06:46
54 Three Way Joins 08:30
55 A Bit of Practice 04:59
56 Exercise Solution 02:53
57 Aggregating and Grouping 02:11
58 Picturing Group By 05:30
59 Aggregate Functions 03:00
60 Combining Group By and Aggregates 04:48
61 A Gotcha with Count 02:37
62 Visualizing More Grouping 03:11
63 Exercise Overview 00:40
64 Grouping Solution 01:22
65 Adding a Layer of Difficulty 00:43
66 Solution 01:28
67 Filtering Groups with Having 04:44
68 Having In Action 02:37
69 More on Having! 06:48
70 A Having Exercise Overview 03:37
71 A Quick Solution 02:33
72 Investigating This Dataset 02:35
73 Some Group By Practice 01:21
74 Group By Review Solution 02:26
75 Remember Joins? 02:14
76 Of Course You Remember! 01:26
77 The Basics of Sorting 02:15
78 Two Variations on Sorting 02:41
79 Offset and Limit 06:39
80 Exercise Overview 00:26
81 Exercise Solution 01:21
82 Handling Sets with Union 05:44
83 A Few Notes on Union 03:46
84 Commonalities with Intersect 03:24
85 Removing Commonalities with Except 05:50
86 Union Exercise Overview 01:27
87 Exercise Solution 01:28
88 What's a Subquery? 05:49
89 Thinking About the Structure of Data 04:47
90 Subqueries in a Select 05:42
91 Exercise Overview 01:06
92 Select Solution 01:19
93 Subqueries in a From 07:59
94 From Subqueries that Return a Value 02:46
95 Example of a Subquery in a From 08:57
96 Exercise Overview 01:16
97 Exercise Solution 02:00
98 Subqueries in a Join Clause 04:53
99 More Useful - Subqueries with Where 08:13
100 Data Structure with Where Subqueries 05:36
101 Exercise Overview 00:58
102 Exercise Solution 02:06
103 The Not In Operator with a List 04:10
104 A New Where Operator 06:21
105 Finally Some! 05:22
106 Exercise Overview 00:28
107 A Quick Solution 01:34
108 Probably Too Much About Correlated Subqueries 16:40
109 More on Correlated Subqueries 08:54
110 A Select Without a From? 03:29
111 Exercise Overview 00:46
112 Exercise Solution 01:20
113 Selecting Distinct Values 04:15
114 Exercise Overview 00:30
115 A Distinct Solution 00:41
116 The Greatest Value in a List 03:21
117 And the Least Value in a List! 02:02
118 The Case Keyword 04:14
119 PostgreSQL Installation on macOS 05:48
120 PGAdmin Setup on macOS 04:57
121 Postgres installation on Windows 03:56
122 What'd We Just Do? 04:03
123 Data Types 04:10
124 Fast Rules on Numeric Data Types 02:02
125 More on Number Data Types 09:20
126 Reminder on Character Types 04:43
127 Boolean Data Types 02:29
128 Times, Dates, and Timestamps 05:31
129 Really Awesome Intervals 05:45
130 Thinking About Validation 04:13
131 Creating and Viewing Tables in PGAdmin 07:20
132 Applying a Null Constraint 06:26
133 Solving a Gotcha with Null Constraints 04:10
134 Default Column Values 03:14
135 Applying a Unique Constraint to One column 05:36
136 Multi-Column Uniqueness 03:41
137 Adding a Validation Check 04:00
138 Checks Over Multiple Columns 05:05
139 So Where Are We Applying Validation? 05:47
140 Approaching More Complicated Designs 03:14
141 Using a SQL Design Tool 06:07
142 A Config-based Schema Designer 07:03
143 Here's the Plan 02:16
144 Rebuilding Some Schema 06:55
145 Requirements of a Like System 04:36
146 How Not to Design a Like System 02:59
147 Designing a Like System 05:05
148 Making a Reaction System Instead 01:36
149 Polymorphic Associations 05:59
150 Polymorphic Association Alternative Implementation 06:58
151 The Simplest Alternative 03:40
152 So Which Approach? 04:22
153 Additional Features Around Posts 06:11
154 Adding Captions and Locations 01:40
155 Photo Mentions vs Caption Mentions 07:28
156 Considerations on Photo Tags vs Caption Tags 07:09
157 Update For Tags 05:02
158 Designing a Hashtag System 07:34
159 Tables for Hashtags 03:37
160 Including the Hashtag Table 04:07
161 A Few More User Columns 06:02
162 Why No Number of Followers or Posts? 03:45
163 Designing a Follower System 05:51
164 Back to Postgres 01:46
165 Creating Tables with Checks 13:01
166 Posts Creation 08:33
167 Comments Creation 02:17
168 Likes Creation 06:53
169 Photo Tags and Caption Tags 05:29
170 Creating Hashtags, Hashtag Posts, and Followers 06:28
171 Adding Some Data 04:07
172 Restoring from Scratch 04:25
173 Highest User ID's Exercise 01:42
174 Solution for User ID's 01:10
175 Posts by a Particular User 01:06
176 Solving for Posts by User 01:57
177 Likes Per User 00:51
178 Solution for Likes Per User 01:42
179 Thinking About Performance 02:04
180 Where Does Postgres Store Data? 05:52
181 Heaps, Blocks, and Tuples 03:37
182 Block Data Layout 04:21
183 Heap File Layout 31:53
184 Full Table Scans 04:09
185 What's an Index 02:24
186 How an Index Works 07:59
187 Creating an Index 03:59
188 Benchmarking Queries 05:28
189 Downsides of Indexes 05:10
190 Index Types 01:36
191 Automatically Generated Indexes 03:38
192 Behind the Scenes of Indexes 31:56
193 The Query Processing Pipeline 04:58
194 Explain and Explain Analyze 05:26
195 Solving an Explain Mystery 09:00
196 Developing an Intuitive Understanding of Cost 11:58
197 Calculating Cost by Hand 06:22
198 A Touch More on Costs 07:18
199 Startup vs Total Costs 05:35
200 Costs Flow Up 01:49
201 Use My Index! 07:56
202 Common Table Expressions 01:53
203 A Quick Solution 03:53
204 So What's a CTE? 03:51
205 Recursive CTE's 03:19
206 Recursive CTE's Step by Step 10:22
207 Why Use Recursive CTE's? 04:34
208 Writing the Query 08:03
209 Walking Through Recursion 09:48
210 Most Popular Users 06:17
211 A Possible Solution for Merging Tables 03:52
212 Creating a View 05:37
213 When to Use a View? 03:20
214 Deleting and Changing Views 02:27
215 Materialized Views 02:19
216 Grouping by Week 04:09
217 Reminder on Left Joins 04:31
218 Writing a Slow Query 09:19
219 Creating and Refreshing Materialized Views 06:38
220 What are Transactions Used For? 04:04
221 Some Sample Data 02:00
222 Opening and Closing Transactions 09:45
223 Transaction Cleanup on Crash 04:04
224 Closing Aborted Transactions 01:46
225 A Story on Migrations 17:14
226 Migration Files 05:01
227 Issues Solved by Migrations 02:52
228 A Few Notes on Migrations Libraries 04:39
229 Project Creation 02:53
230 Generating and Writing Migrations 07:12
231 Applying and Reverting Migrations 07:16
232 Generating and Applying a Second Migration 03:39
233 Schema vs Data Migrations 04:09
234 Dangers Around Data Migrations 09:07
235 Properly Running Data and Schema Migrations 05:29
236 Creating a Posts Table 04:40
237 A Small Web Server 14:53
238 Adding the Loc Column 04:11
239 Writing Values to Both Columns 04:13
240 Transaction Locks 13:40
241 Updating Values 04:20
242 Updating the App Server 04:14
243 Dropping the Last Columns 03:02
244 Section Goal 00:53
245 Initial Setup 01:35
246 One Fast Migration 05:02
247 Building the Users Router 04:54
248 Understanding Connection Pools 06:32
249 Validating Connection Credentials 06:57
250 Query and Close 01:58
251 The Repository Pattern 03:39
252 Creating a Repository 08:21
253 Accessing the API 03:52
254 Casing Issues 04:22
255 Fixing Casing 06:34
256 Finding Particular Users 05:27
257 SQL Injection Exploits 06:41
258 Handling SQL Injection with Prepared Statements 07:49
259 Preventing SQL Injection 07:33
260 Reminder on Post Requests 02:52
261 Inserting Users 04:14
262 Handling Updates 04:37
263 And, Finally, Delete 04:06
264 A Note on Testing 02:48
265 Assertions Around User Count 07:05
266 Connecting to a DB For Tests 05:02
267 Disconnecting After Tests 02:46
268 Multi-DB Setup 04:41
269 Assumptions Around Content 04:45
270 Issues with Parallel Tests 04:49
271 Isolation with Schemas 04:10
272 Creating and Accessing Schemas 05:22
273 Controlling Schema Access with Search Paths 05:48
274 Routing Schema Access 04:03
275 Strategy for Isolation 04:44
276 Programmatic Schema Creation 11:45
277 Escaping Identifiers 04:21
278 Test Helpers 05:19
279 Cleaning up Schemas and Roles 05:57
280 Finally... Parallel Tests! (Final Lecture) 03:33

Similar courses to SQL and PostgreSQL: The Complete Developer's Guide

SQL - The Complete Guide (MySQL, PostgreSQL & more)

SQL - The Complete Guide (MySQL, PostgreSQL & more)

Duration 19 hours 35 minutes 38 seconds
PostgreSQL Bootcamp : Go From Beginner to Advanced, 60+hours

PostgreSQL Bootcamp : Go From Beginner to Advanced, 60+hours

Duration 67 hours 20 minutes 55 seconds
Mastery with SQL

Mastery with SQL

Duration 9 hours 15 minutes 2 seconds
100 Days Of Code: The Complete Web Development Bootcamp 2022

100 Days Of Code: The Complete Web Development Bootcamp 2022

Duration 78 hours 51 minutes 55 seconds
Learn Database Design with MySQL

Learn Database Design with MySQL

Duration 6 hours 14 minutes 51 seconds
Complete SQL Mastery

Complete SQL Mastery

Duration 10 hours 47 minutes 7 seconds
Database Design & Implementation

Database Design & Implementation

Duration 2 hours 7 minutes 49 seconds
Make a Google search engine clone: JavaScript PHP and MySQL
Zero To Shipped

Zero To Shipped

Duration 24 hours 38 minutes 44 seconds