Complete SQL + Databases Bootcamp: Zero to Mastery [2023]

Complete SQL + Databases Bootcamp: Zero to Mastery [2023]

English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 281 lectures (24h 22m) | 15.1 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
  • Window Functions
  • Date Filtering and Timestamps
  • SQL Aggregate Functions
  • SQL Operator Precedense
  • 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

1 Course Outline
2 Join Our Online Classroom
3 Exercise Meet Your Classmates and Instructor
4 The Big Data Breach At Keiko Corp
5 Monthly Coding Challenges Free Resources and Guides

History And Story Of Data
6 What Is a Database
7 I Didnt Learn Anything Try Again
8 Database Management System DBMS
9 Exercise Building Amazon
10 Exercise Building Amazon 2
11 Types Of Databases
12 Exercise What Is A Database
13 Section Review

Databases SQL Fundamentals
14 SQL Playground
15 What Is SQL
16 What Is A Query
17 Exercise Setting Up Your First Database
18 Imperative vs Declarative
19 History of SQL
20 Optional History of SQL Deep Dive
21 Exercises The Select Statement
22 SQL Standards
23 What Is A Database Revisited
24 Database Oriented Approach
25 Exercise SQL Starter Quiz
26 Database Models
27 Hierarchical And Networking Model
28 Relational Model
29 DBMS Revisited
30 Relational Model Revisited
31 Tables
32 Columns
33 Rows
34 Primary And Foreign Keys
36 Exercise OLTP vs OLAP
37 Exercise Relational Model Quiz
38 Endorsements On LinkedIN

Environment Setup
39 Why PostgreSQL
40 Environment Tooling
41 Having Trouble Registering A Serial Key For ValentinaDB
42 SQL Tooling Alternatives
43 Command Line 101
44 Getting Help With The Setup
45 WINDOWS Setup
46 Optional Setting Up Windows For Command Line
47 MAC Setup
48 MAC Commandline tools
49 LINUX Setup
50 Importing The Databases
51 Exercise Imposter Syndrome

SQL Deep Dive
52 Query Along
53 Starting With Query
54 Exercise Simple Queries
55 Changing Column Names in a SELECT Query
56 Concat Function
57 What Is A Function In SQL
58 Aggregate Functions
59 Exercise Aggregate Functions
60 Commenting Your Queries
61 Common SELECT Mistakes
62 Filtering Data
63 AND and OR
64 Exercise Filtering Data
65 The NOT Keyword
66 Exercise The Where Clause
67 Comparison Operators
68 Exercise Comparison Operators
69 Logical Operators
70 Operator Precedence
71 Operator Precedence 2
72 Operator Precedence Extra
73 Exercise Operator Precedence
74 Checking For NULL Values
75 IS Keyword
76 NULL Coalescing
77 Exercise Null Value Coalescing
78 Valued Logic
79 Exercise 3 Valued Logic
81 Exercise BETWEEN AND
82 IN Keyword
83 Exercise IN Keyword
85 Exercise Like Keyword
86 Dates And Timezones
87 Setting Up Timezones
88 How Do We Format Date And Time
89 Timestamps
90 Date Functions
91 Date Difference And Casting
92 Age Calculation
93 Extracting Information
94 Intervals
95 Exercise Date and Timestamp
97 Exercise Distinct Keyword
98 Sorting Data
99 Exercise Sorting Data
100 Multi Table SELECT
101 Inner Join
102 Self Join
103 Outer Join
104 Less Common Joins
105 InnerJoin Exercises
106 USING Keyword

Advanced SQL
108 Group By Exercises
109 HAVING Keyword
110 Having Exercises
111 Ordering Grouped Data
112 Group By Mental Model
113 Grouping Sets
114 Rollup
115 Window What
116 Looking Through The Window
118 Order By Acting Strange
119 Using Framing In Window Function
120 Solving For Current Salary
123 SUM
125 Window Function Exercises
126 Conditional Statements
127 Conditional Statement Exercise
129 NULLIF Exercise
130 ViewsWhat Are They Good For
131 View Syntax
132 Using Views
133 Views Exercises
134 Indexes
135 Index Types
136 Index Algorithms
137 Quick Note On Index Algorithms
138 What Are Subqueries
139 Subqueries vs Joins
140 Subquery Guidelines As Types
141 Using Subqueries
142 Quick Note Titles For Employees
143 Getting The Latest Salaries
144 Subquery Operators
145 Subquery Exercises

Database Management
146 Before We Get Started
147 Time To Create Some Stuff
148 Types Of Databases In A RDBMS
149 Default PostgreSQL Database
150 Template Databases
151 Creating A Database
152 Database Organization
153 Roles In Postgres
154 Role Attributes And Creation
155 Creating Users And Configuring Login
156 Privileges
157 Granting Privileges and Role Management
158 Best Practices For Role Management
159 Data Types & Boolean Type
160 Storing Text
161 Storing Numbers
162 Storing Arrays
163 Data Models And Naming Conventions
165 Extra information on CREATE TABLE
166 Column Constraints
167 Table Constraints
168 Regexes
169 UUID Explained
170 Custom Data Types And Domains
171 Creating The Tables For ZTM
172 Extra information on ALTER TABLE
173 Adding Students And Teachers
174 Creating A Course
175 Adding Feedback To A Course
176 A Tale Of 2 Feedbacks
177 SQL Exercises
178 SQL Quiz
179 Backups And Why They Are Important
180 Backing Up In Postgres
181 Restoring A Database
182 Transactions

Solving The Mystery
183 Clue 1 and 2
184 Getting ready to solve the Mystery
185 Clue 3
186 Exercise Solving The First Clues
187 Solution Solving The First Clues
188 Clue 4
189 Exercise Clue 4
190 Solution Clue 4
191 Clue 5 and 6
192 Exercise Clue 5 and 6
193 Solution Clue 5 and 6
194 Solving The Mystery

Database Design
195 System Design And SDLC
196 SDLC Phases
197 System Design Deep Dive
198 TopDown vs BottomUp
199 DRIVEME Academy
200 Top Down Design
201 ER Model
202 Step 1 Determining Entities
203 Tooling For Diagramming
204 DRIVEME Academy Entities
205 Step 2 Attributes
206 Relational Model Extended
207 Relational Schema And Instance
208 Super Key and Candidate Key
209 Primary Key and Foreign Key
210 Compound Composite And Surrogate Key
211 DRIVEME Attributes
212 Step 3 Relationships
213 DRIVEME Relationships
214 Step 4 Solving Many To Many
215 Step 5 Subject Areas
216 DRIVEME Subject Areas
217 Exercise Painting Reservations
218 Exercise Movie Theatre
219 Bottom Up Design
220 Anomalies
221 Normalization
222 Functional Dependencies
223 Functional Dependencies 2
224 The Normal Forms
225 Going from 0NF to 1NF
226 Going from 1NF to 2NF
227 Going from 2NF to 3NF
228 BoyceCodd Normal Form
229 Why 4NF And 5NF Are Not Useful
230 Exercise Database Design Quiz

Database Landscape Performance and Security
231 Brunos Request
232 Scalability
233 Sharding
234 Replication
235 Backups
236 Distributed vs Centralized Databases
237 Database Security
238 Exercise SQL Injection
239 Optional All About Injections Attacks
240 Optional Storing Passwords
241 Optional How To Store Passwords
242 Relational vs NoSQL PostgreSQL vs MongoDB Databases
243 Future Of Relational Databases
244 Elasticsearch
245 S3 Object Storage
246 Top Databases To Use

Where To Go From Here
247 Thank You
248 LinkedIn Endorsements
249 Become An Alumni
250 Learning Guideline
251 Coding Challenges

Extras Data Engineering And the role of Machine Learning
252 Big Data Analytics
253 Quick Note Upcoming Videos
254 Reviewing How We Got Here
255 Data Engineering Introduction
256 What Is Data
257 What Is A Data Engineer
258 What Is A Data Engineer 2
259 What Is A Data Engineer 3
260 What Is A Data Engineer 4
261 Types Of Databases
262 Hadoop HDFS and MapReduce
263 Apache Spark and Apache Flink
264 Kafka and Stream Processing

Extras Redis
265 Quick Note Upcoming Videos
266 What Is Caching
267 Introducing Redis
268 Redis Version Updates and Playground
269 Installing Redis
270 Resources Installing Redis
271 Redis Commands
272 Redis Data Types
273 Redis Hashes
274 Redis Lists
275 Redis Sets and Sorted Sets
276 Section Summary

Extra Bits Databases In A Web App with Nodejs
277 Quick Note Upcoming Videos
278 Setting Up Your Database
279 Connecting To The Database
280 Registering A User Part 1

281 Special Bonus Lecture