English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 282 lectures (24h 26m ) | 16.09 GB
Master SQL, Database Management & Design and learn to work with databases like PostgreSQL, MySQL + more. With Exercises!
Using the latest best practices in SQL, Database Management and Database Design, this course is focused on efficiency. Never spend time on confusing, out of date, incomplete tutorials anymore. Graduates of Andrei’s courses are now working at Google, Tesla, Amazon, Apple, IBM, JP Morgan, Facebook, + other top tech companies.
We guarantee you this is the most comprehensive online resource on Databases like PostgreSQL and MySQL. This project and exercise based course will introduce you to all of the modern toolchain of an SQL developer or anyone using a database in the workplace (Product Manager, Business Analyst/Intelligence, Data Analyst, Data Scientists, Machine Learning Engineer, Web Developer, Mobile Developer + any role requiring insights from data). Along the way, we will learn practical and real world skills that will get you hired.
The curriculum is going to be very hands on as we walk you from start to finish of working with databases and SQL, all the way into learning how to scale databases, how to manage them, and even bonus material on working with Big Data, Caching using Redis, and connecting PostgreSQL to a Node.js server. We even talk about pros and cons of choosing an SQL Database vs NoSQL like MongoDB. We will start from the very beginning by teaching you SQL and Database Fundamentals and then going into advanced topics so you can make good decisions and work with any data that your company has no matter how complex!
The topics covered are:
- NoSQL (MongoDB) vs PostgreSQL, MySQL vs NewSQL
- SQL Theory And Concepts
- The Relational Model
- SQL Basics
- SQL Functions
- Data Modification Language / DML
- Data Query Language / DQL
- Subqueries
- Indexes
- SQL Filtering / WHERE Statement
- 3 Valued Logic
- SQL JOINS
- Window Functions
- Date Filtering and Timestamps
- SQL Aggregate Functions
- SQL Operator Precedense
- SQL ORDER BY
- SQL GROUP BY
- SQL Top Down Design
- SQL Bottom Up Design
- SQL Entity Relationship Diagram
- SQL Normalization
- Database Types
- The role of a DBMS
- Multi Table SELECT
- The Software Development Lifecycle / SDLC
- POSTGRES Role Management
- POSTGRES Permission Management
- POSTGRES Backup Strategies
- POSTGRES Transaction Management/ SQL Transactions
- POSTGRES/SQL Data Types
- SQL Views
- Redis Database
- Elasticsearch
- Connecting A Database To A Server/Web App (Node.js)
- Data Engineering (Kafka, Hadoop, etc…)
- Sharding
- Replication
- Backups
- Vertical + Horizontal Scaling
- Distributed vs Centralized Databases
- Big Data + Analytics
- Database Security (SQL Injections, Access Control, etc…)
- + more
With SQL you will be able to work with all databases like: PostgreSQL, MySQL, Oracle SQL, Microsoft SQL Server, IBM DB2, SQLite, MariaDB, Amazon Redshift, Presto, Apache Hive with Hadoop, and many many more because SQL is everywhere!
You see, data is everywhere and it is the most valuable asset in the world. All the top companies need people that can work with data. That is where this course comes in. Unlike most tutorials out there, this course encompasses many fields working with many databases. Whether you want to get into the tech industry, you’re a mobile or web developer, a data scientist, a machine learning engineer, a business analyst, even sales and marketing or you have your own company. Any role that requires you to work with data will need to know this valuable skill that is SQL (how to interact with databases, analyze, and use data).
Here is the thing though. There are many courses on this topic.
Let me tell you 3 reasons why this course is different from any other SQL/PostgreSQL/MySQL/Database tutorial online:
1. In this course you will learn to work with not just 1 but many Databases like MySQL, PostgreSQL, Microsoft Server, Redis, and so much more. No prior programming or technical experience is necessary. We take you from absolute zero, all the way to mastery. We will go above and beyond to not just teach you SQL commands but to teach you advanced techniques, best practices, database design and how to think about performance, security, and scalability.
2. This course is taught by actual professionals who have experience and have worked with databases for some of the largest companies in the world. Mo is a super star when it comes SQL. He has built software for the European Union, launched products for 5 Fortune 500 companies, and has consulted at Google. Andrei has worked on enterprise level apps for large tech firms in Silicon Valley as well as Toronto and has also taught others tech skills that got them into big companies like Google. By having both Andrei and Mo teach, you get to see different perspective and learn from 2 engineers as if you are working at a company together.
3. We are going to have fun here. The course starts off with you getting hired at Keiko Corp to investigate their recent Database breach. Because we believe in learning by doing, you will be doing tons of real life assignments and exercises along the way, and eventually get to the point where you can help Keiko Corp solve their mystery by looking at their databases and analyzing hidden information. Our goals is that everyone has fun and is successful after completing the course
This course is not about making you just watch along without understanding the principles so that when you are done with the course you don’t know what to do other than watch another tutorial. No! This course will push you and challenge you to go from an absolute beginner in SQL and Databases to someone that is in the top 10% of SQL and Database experts!
Table of Contents
Introduction
1 Course Outline
2 Join Our Online Classroom!
3 Exercise Meet Your Classmates & Instructor
4 The Big Data Breach At Keiko Corp
5 ZTM Resources
6 Monthly Coding Challenges, Free Resources and Guides
History And Story Of Data
7 What Is a Database
8 I Didn’t Learn Anything, Try Again
9 Database Management System (DBMS)
10 Exercise Building Amazon
11 Exercise Building Amazon 2
12 Types Of Databases
13 Exercise What Is A Database
14 Section Review
Databases + SQL Fundamentals
15 SQL Playground
16 What Is SQL
17 What Is A Query
18 Exercise Setting Up Your First Database
19 Imperative vs Declarative
20 History of SQL
21 Exercises The Select Statement
22 Optional History of SQL Deep Dive
23 SQL Standards
24 What Is A Database Revisited
25 Database Oriented Approach
26 Exercise SQL Starter Quiz
27 Database Models
28 Hierarchical And Networking Model
29 Relational Model
30 DBMS Revisited
31 Relational Model Revisited
32 Tables
33 Columns
34 Rows
35 Primary And Foreign Keys
36 OLTP vs OLAP
37 Exercise OLTP vs OLAP
38 Endorsements On LinkedIN
39 Exercise Relational Model Quiz
Environment Setup
40 Why PostgreSQL
41 Environment Tooling
42 Having Trouble Registering A Serial Key For ValentinaDB
43 SQL Tooling Alternatives
44 Command Line 101
45 Getting Help With The Setup
46 WINDOWS Setup
47 Optional Setting Up Windows For Command Line
48 MAC Setup
49 MAC Commandline tools
50 LINUX Setup
51 Importing The Databases
52 Exercise Imposter Syndrome
SQL Deep Dive
53 Query Along
54 Starting With Query
55 Exercise Simple Queries
56 Changing Column Names in a SELECT Query
57 Concat Function
58 What Is A Function In SQL
59 Aggregate Functions
60 Exercise Aggregate Functions
61 Commenting Your Queries
62 Common SELECT Mistakes
63 Filtering Data
64 AND and OR
65 Exercise Filtering Data
66 The NOT Keyword
67 Exercise The Where Clause
68 Comparison Operators
69 Exercise Comparison Operators
70 Logical Operators
71 Operator Precedence
72 Operator Precedence 2
73 Exercise Operator Precedence
74 Operator Precedence Extra
75 Checking For NULL Values
76 IS Keyword
77 NULL Coalescing
78 Valued Logic
79 Exercise Null Value Coalescing
80 Exercise 3 Valued Logic
81 BETWEEN + AND
82 Exercise BETWEEN + AND
83 IN Keyword
84 Exercise IN Keyword
85 LIKE
86 Exercise Like Keyword
87 Dates And Timezones
88 Setting Up Timezones
89 How Do We Format Date And Time
90 Timestamps
91 Date Functions
92 Date Difference And Casting
93 Age Calculation
94 Extracting Information
95 Intervals
96 Exercise Date and Timestamp
97 DISTINCT
98 Exercise Distinct Keyword
99 Sorting Data
100 Exercise Sorting Data
101 Multi Table SELECT
102 Inner Join
103 Self Join
104 Outer Join
105 Less Common Joins
106 Inner-Join Exercises
107 USING Keyword
Advanced SQL
108 GROUP BY
109 Group By Exercises
110 HAVING Keyword
111 Having Exercises
112 Ordering Grouped Data
113 Group By Mental Model
114 Grouping Sets
115 Rollup
116 Window What
117 Looking Through The Window
118 PARTITION BY
119 Order By Acting Strange
120 Using Framing In Window Function
121 Solving For Current Salary
122 FIRST_VALUE
123 LAST_VALUE
124 SUM
125 ROW_NUMBER
126 Window Function Exercises
127 Conditional Statements
128 Conditional Statement Exercise
129 NULLIF
130 NULLIF Exercise
131 Views…What Are They Good For
132 View Syntax
133 Using Views
134 Views Exercises
135 Indexes
136 Index Types
137 Index Algorithms
138 Quick Note On Index Algorithms
139 What Are Subqueries
140 Subqueries vs Joins
141 Subquery Guidelines As Types
142 Using Subqueries
143 Quick Note Titles For Employees
144 Getting The Latest Salaries
145 Subquery Operators
146 Subquery Exercises
Database Management
147 Before We Get Started
148 Time To Create Some Stuff!
149 Types Of Databases In A RDBMS
150 Default PostgreSQL Database
151 Template Databases
152 Creating A Database
153 Database Organization
154 Roles In Postgres
155 Role Attributes And Creation
156 Creating Users And Configuring Login
157 Privileges
158 Granting Privileges and Role Management
159 Best Practices For Role Management
160 Data Types & Boolean Type
161 Storing Text
162 Storing Numbers
163 Storing Arrays
164 Data Models And Naming Conventions
165 CREATE TABLE
166 Extra information on CREATE TABLE
167 Column Constraints
168 Table Constraints
169 Regexes!
170 UUID Explained
171 Custom Data Types And Domains
172 Creating The Tables For ZTM
173 Extra information on ALTER TABLE
174 Adding Students And Teachers
175 Creating A Course
176 Adding Feedback To A Course
177 A Tale Of 2 Feedbacks
178 SQL Exercises
179 SQL Quiz
180 Backups And Why They Are Important
181 Backing Up In Postgres
182 Restoring A Database
183 Transactions
Solving The Mystery
184 Clue #1 and #2
185 Getting ready to solve the Mystery!
186 Clue #3
187 Exercise Solving The First Clues
188 Solution Solving The First Clues
189 Clue #4
190 Exercise Clue #4
191 Solution Clue #4
192 Clue #5 and #6
193 Exercise Clue #5 and #6
194 Solution Clue #5 and #6
195 Solving The Mystery
Database Design
196 System Design And SDLC
197 SDLC Phases
198 System Design Deep Dive
199 Top-Down vs Bottom-Up
200 DRIVEME Academy
201 Top Down Design
202 ER Model
203 Step 1 Determining Entities
204 Tooling For Diagramming
205 DRIVEME Academy Entities
206 Step 2 Attributes
207 Relational Model Extended
208 Relational Schema And Instance
209 Super Key and Candidate Key
210 Primary Key and Foreign Key
211 Compound Composite And Surrogate Key
212 DRIVEME Attributes
213 Step 3 Relationships
214 DRIVEME Relationships
215 Step 4 Solving Many To Many
216 Step 5 Subject Areas
217 DRIVEME Subject Areas
218 Exercise Painting Reservations
219 Exercise Movie Theatre
220 Bottom Up Design
221 Anomalies
222 Normalization
223 Functional Dependencies
224 Functional Dependencies 2
225 The Normal Forms
226 Going from 0NF to 1NF
227 Going from 1NF to 2NF
228 Going from 2NF to 3NF
229 Boyce-Codd Normal Form
230 Why 4NF And 5NF Are Not Useful
231 Exercise Database Design Quiz
Database Landscape, Performance and Security
232 Bruno’s Request
233 Scalability
234 Sharding
235 Replication
236 Backups
237 Distributed vs Centralized Databases
238 Database Security
239 Exercise SQL Injection
240 Optional All About Injections Attacks
241 Optional Storing Passwords
242 Optional How To Store Passwords
243 Relational vs NoSQL, PostgreSQL vs MongoDB Databases
244 Future Of Relational Databases
245 Elasticsearch
246 S3 Object Storage
247 Top Databases To Use
Where To Go From Here
248 Thank You
249 Become An Alumni
250 Coding Challenges
251 Learning Guideline
252 LinkedIn Endorsements
Extras Data Engineering (And the role of Machine Learning)
253 Big Data + Analytics
254 Quick Note Upcoming Videos
255 Reviewing How We Got Here
256 Data Engineering Introduction
257 What Is Data
258 What Is A Data Engineer
259 What Is A Data Engineer 2
260 What Is A Data Engineer 3
261 What Is A Data Engineer 4
262 Types Of Databases
263 Hadoop, HDFS and MapReduce
264 Apache Spark and Apache Flink
265 Kafka and Stream Processing
Extras Redis
266 Quick Note Upcoming Videos
267 What Is Caching
268 Introducing Redis
269 Redis Version Updates and Playground
270 Installing Redis
271 Resources Installing Redis
272 Redis Commands
273 Redis Data Types
274 Redis Hashes
275 Redis Lists
276 Redis Sets and Sorted Sets
277 Section Summary
Extra Bits Databases In A Web App (with Node.js)
278 Quick Note Upcoming Videos
279 Setting Up Your Database
280 Connecting To The Database
281 Registering A User Part 1
BONUS SECTION
282 Special Bonus Lecture
Resolve the captcha to access the links!