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

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

English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 24 Hours | 14.7 GB
eLearning | Skill level: All Levels


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 The Community
4 The Big Data Breach At Keiko Corp

History And Story Of Data
5 What Is a Database
6 I Didn’t Learn Anything, Try Again…
7 Database Management System (DBMS)
8 Exercise Building Amazon
9 Exercise Building Amazon 2
10 Types Of Databases
11 Exercise What Is A Database
12 Section Review

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

Environment Setup
38 Why PostgreSQL
39 Environment Tooling
40 SQL Tooling Alternatives
41 Command Line 101
42 Getting Help With The Setup
43 WINDOWS Setup
44 Optional Setting Up Windows For Command Line
45 MAC Setup
46 MAC Commandline tools
47 LINUX Setup
48 Importing Data On WINDOWS
49 Importing Data On MAC
50 Importing Data On LINUX

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

Advanced SQL
103 GROUP BY
104 HAVING Keyword
105 Ordering Grouped Data
106 Group By Mental Model
107 Grouping Sets
108 Rollup
109 Group By Exercises
110 Window What
111 Looking Through The Window
112 PARTITION BY
113 Order By Acting Strange
114 Using Framing In Window Function
115 Solving For Current Salary
116 FIRST VALUE
117 LAST VALUE
118 SUM
119 ROW NUMBER
120 Window Function Exercises
121 Conditional Statements
122 NULLIF
123 Views…What Are They Good For
124 View Syntax
125 Using Views
126 Indexes
127 Index Types
128 Index Algorithms
129 What Are Subqueries
130 Subqueries vs Joins
131 Subquery Guidelines As Types
132 Using Subqueries
133 Getting The Latest Salaries
134 Subquery Operators
135 Subquery Exercises

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

Solving The Mystery
173 Clue #1 and #2
174 Getting ready to solve the Mystery!
175 Clue #3
176 Exercise Solving The First Clues
177 Solution Solving The First Clues
178 Clue #4
179 Exercise Clue #4
180 Solution Clue #4
181 Clue #5 and #6
182 Exercise Clue #5 and #6
183 Solution Clue #5 and #6
184 Solving The Mystery

Database Design
185 System Design And SDLC
186 SDLC Phases
187 System Design Deep Dive
188 Top-Down vs Bottom-Up
189 DRIVEME Academy
190 Top Down Design
191 ER Model
192 Step 1 Determining Entities
193 Tooling For Diagramming
194 DRIVEME Academy Entities
195 Step 2 Attributes
196 Relational Model Extended
197 Relational Schema And Instance
198 Super Key and Candidate Key
199 Primary Key and Foreign Key
200 Compound Composite And Surrogate Key
201 DRIVEME Attributes
202 Step 3 Relationships
203 DRIVEME Relationships
204 Step 4 Solving Many To Many
205 Step 5 Subject Areas
206 DRIVEME Subject Areas
207 Exercise Painting Reservations
208 Exercise Movie Theatre
209 Bottom Up Design
210 Anomalies
211 Normalization
212 Functional Dependencies
213 Functional Dependencies 2
214 The Normal Forms
215 Going from 0NF to 1NF
216 Going from 1NF to 2NF
217 Going from 2NF to 3NF
218 Boyce-Codd Normal Form
219 Why 4NF And 5NF Are Not Useful
220 Exercise Database Design Quiz

Database Landscape, Performance and Security
221 Bruno’s Request
222 Scalability
223 Sharding
224 Replication
225 Backups
226 Distributed vs Centralized Databases
227 Database Security
228 Exercise SQL Injection
229 Optional All About Injections Attacks
230 Optional Storing Passwords
231 Optional How To Store Passwords
232 Relational vs NoSQL, PostgreSQL vs MongoDB Databases
233 Future Of Relational Databases
234 Elasticsearch
235 S3 Object Storage
236 Top Databases To Use

Where To Go From Here
237 Thank You
238 LinkedIn Endorsements
239 Become An Alumni
240 Coding Challenges

Bonus Data Engineering (And the role of Machine Learning)
241 Big Data + Analytics
242 Quick Note Upcoming Videos
243 Reviewing How We Got Here
244 Data Engineering Introduction
245 What Is Data
246 What Is A Data Engineer
247 What Is A Data Engineer 2
248 What Is A Data Engineer 3
249 What Is A Data Engineer 4
250 Types Of Databases
251 Hadoop, HDFS and MapReduce
252 Apache Spark and Apache Flink
253 Kafka and Stream Processing

Bonus Redis
254 Quick Note Upcoming Videos
255 What Is Caching
256 Introducing Redis
257 Installing Redis
258 Redis Commands
259 Redis Data Types
260 Redis Hashes
261 Redis Lists
262 Redis Sets and Sorted Sets
263 Section Summary

Extra Bits Databases In A Web App (with Node.js)
264 Quick Note Upcoming Videos
265 Setting Up Your Database
266 Connecting To The Database
267 Registering A User Part 1

Extras
268 Bonus Special Thank You Gift!