SQL and PostgreSQL: The Complete Developer's Guide

Name: SQL and PostgreSQL: The Complete Developer's Guide
Price: 9 USD
Availability: InStock

22h 1m

English

Paid

July 20, 2024

Course description

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 .

Watch Online

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

0:00

#1: What is PostgreSQL All About?

All Course Lessons (280)

#	Lesson Title	Duration
1	What is PostgreSQL All About? Demo	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	00:59
9	Exercise Solution	01:04
10	String Operators and Functions	06:18
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	00:38
17	A "Where" With Lists Solution	01:43
18	Calculations in "Where" Clauses	02:06
19	Solving Calculations	01:52
20	Updating Rows	04:17
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	07:29
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

Unlock unlimited learning

Get instant access to all 279 lessons in this course, plus thousands of other premium courses. One subscription, unlimited knowledge.

Learn more about subscription

Comments

0 comments

Want to join the conversation?

Similar courses

SQL - MySQL for Data Analytics and Business Intelligence

Sources: udemy

Well, when you can work with SQL, it means you don’t have to rely on others sending you data and executing queries for you. You can do that on your own. This al

9 hours 22 minutes 55 seconds

100 Days Of Code: The Complete Web Development Bootcamp 2024

Sources: Academind Pro

Learn web development from A to Z in 100 days (or at your own pace) - from "basic" to "advanced", it's all included!

78 hours 51 minutes 55 seconds

Database Mastery: SQL to Prisma

Sources: jsmastery.pro, Adrian Hajdin

Master key technologies with a practical approach! You will gain applied knowledge, clear explanations, and all the necessary tools to confidently master...

16 minutes 3 seconds

SQL for Data Engineers

Sources: Andreas Kretz

SQL is the foundation for working with relational databases. And if you are planning to work in the field of Data Engineering, knowing SQL is not just a...

1 hour 51 minutes

SQL Injections Unlocked - SQLi Web Attacks