English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 383 lectures (21h 45m) | 8.15 GB
JUST COMPLETELY REDONE! Master SQL, Work With Complex Databases, Build Reports, and More!
This course was just completely redone and rebuilt from the ground up, with over 325 brand new videos recorded. The course now uses MySQL 8.x and covers new topics including: Window Functions, Views, and SQL modes.
If you want to learn how to gain insights from data but are too intimidated by databases to know where to start, then this course is for you. This course is a gentle but comprehensive introduction to MySQL, one of the most highly in-demand skills in the business sector today.
Whether you work in sales or marketing, you run your own company, or you want to build your own apps, mastering MySQL is crucial to answering complex business problems and questions using insights from data. The Ultimate MySQL Bootcamp introduces you to a solid foundation in databases in a way that’s both informative and engaging. Yes, that’s right, it’s possible to make an engaging course on databases.
In this course, you will:
- Learn the ins and outs of SQL syntax
- Generate reports using sales and user data
- Analyze data using Aggregate Functions
- Run complex queries using MySQL logical operators and string functions
- Write all the common SQL joins
- Work with large datasets containing thousands of entries
- Design and implement complex database schemas
- Learn to navigate the treacherous world of storing dates and times
- Clone the database structure of a photo sharing social network
- Work with MySQL 8.x Window Functions: RANK, LEAD, LAG, NTILE, etc.
- Create MySQL database views and virtual tables
This course is also chock full of exercises, challenges, projects, and opportunities for you to practice what you’re learning. Apply what you’re learning to real-world challenges such as finding a website’s power users, calculating your top students, identifying bots on a site, or determining which hashtags generate the most traction on a site. Along the way, you’ll also get to meet my pet cat, Blue! (She’s really cute.)
Why Learn MySQL
Consistently ranked the most in-demand skill in recent employer surveys, SQL is a fantastic way to increase your income and boost your professional development. So many companies today use MySQL, including Twitter, Uber, Airbnb, Dropbox, Github, Kickstarter, Udemy, Slack, and many others. Unsure about the difference between SQL and MySQL? MySQL is the most popular open source SQL database out there, so it’s a great choice to begin your learning journey. We’ll talk a lot more about the difference between SQL and MySQL in the course, but 95% of what you learn about MySQL in this course will apply to other databases such as Postgres, Oracle, and Microsoft SQL Server.
Why This Course Is Different
This isn’t going to be a course where you watch me code for 20 hours; it’s a course with ample opportunity for you to get your hands dirty writing code (even within the first 7 minutes of the course).
My teaching experience is rooted in the classroom. I’ve spent years teaching programming, web development, and databases to my in-person bootcamp students. This course combines the best of my offline courses with the convenience of learning online, in a setting that’s best for you.
Upon completing this course, you’ll be able to interact with and query any SQL database out there. You’ll also be able to generate reports with ease, answer company performance questions using data, and integrate MySQL into complete applications.
So let’s do this! Enroll today and start learning SQL!
What you’ll learn
- Create your own database or interact with existing databases
- Write complex SQL join queries across multiple tables
- Master MySQL 8 Window Functions (LEAD, LAG, RANK, etc.)
- Model real-world data and generate reports using SQL
- Answer company performance or sales questions using data
Table of Contents
Introduction & 5 Minutes of SQL
1 This Course Was Just Updated!
2 Join The Community!
3 Course Intro
4 About Me
5 Getting The Course Code
6 First 5 Minutes of SQL
Getting Started & Installation
7 Section Introduction
8 What Is A Database
9 SQL vs. MySQL
10 Installation START HERE!
11 VERY IMPORTANT, PLEASE READ! — New MySQL Windows Installation Instructions
12 Installation Windows Instructions (FIRST FOLLOW THE PREVIOUS WRITTEN LECTURE)
13 VERY IMPORTANT, PLEASE READ! — New MySQL Mac Installation Instructions
14 Installation Mac Instructions (FIRST FOLLOW THE PREVIOUS WRITTEN LECTURE)
Creating Databases & Tables
15 Section Introduction
16 Showing Databases
17 CODE Showing Databases
18 Creating Databases
19 CODE Creating Databases
20 Dropping and Using Databases
21 CODE Dropping and Using Databases
22 Introducing Tables
23 Data Types The Basics
24 Basic Datatypes Challenge
25 CODE Basic Datatypes Challenge
26 Creating Tables
27 CODE Creating Tables
28 How Do We Know It Worked
29 CODE How Do We Know It Worked
30 Dropping Tables
31 CODE Dropping Tables
32 Tables Basics Activity
33 SOLUTION Tables Basics Activity
34 MySQL Comments
Inserting Data
35 Section Introduction
36 INSERT The Basics
37 CODE INSERT The Basics
38 A Quick Preview of SELECT
39 CODE A Quick Preview of SELECT
40 Multi-inserts
41 CODE Multi-inserts
42 INSERT Exercise
43 SOLUTION INSERT Exercise
44 Working With NOT NULL
45 CODE Working With NOT NULL
46 Sidenote Quotes In MySQL
47 Adding DEFAULT Values
48 CODE Adding DEFAULT Values
49 Introducing Primary Keys
50 CODE Introducing Primary Keys
51 Working With AUTO_INCREMENT
52 CODE Working With AUTO_INCREMENT
53 Create Table Insert Exercise
54 SOLUTION Insert Exercise
CRUD Basics
55 Section Introduction
56 Introducing CRUD
57 Getting Our New Dataset
58 CODE
59 Officially Introducing SELECT
60 CODE
61 The WHERE clause
62 CODE
63 Rapid Fire Exercises
64 Rapid Fire Exercises SOLUTION
65 SOLUTION CODE
66 Aliases
67 CODE
68 Using UPDATE
69 CODE
70 A Quick Rule Of Thumb
71 UPDATE Exercise
72 UPDATE Exercise SOLUTION
73 SOLUTION CODE
74 Introducing DELETE
75 CODE
76 DELETE Exercise
77 DELETE Exercise SOLUTION
78 SOLUTION CODE
CRUD Challenge
79 Section Introduction
80 Introducing the CRUD Challenge
81 CRUD Challenge Solution Creating
82 SOLUTION CODE
83 CRUD Challenge Solution Reading
84 SOLUTION CODE
85 CRUD Challenge Solution Updating
86 SOLUTION CODE
87 CRUD Challenge Solution Deleting
88 SOLUTION CODE
String Functions
89 Section Introduction
90 The World Of String Functions
91 Loading Our Books Data
92 CODE
93 CONCAT
94 CODE
95 SUBSTRING
96 CODE
97 Combining String Functions
98 CODE
99 Sidenote SQL Formatting
100 REPLACE
101 CODE
102 REVERSE
103 CODE
104 CHAR_LENGTH
105 CODE
106 UPPER & LOWER
107 CODE
108 Other String Functions
109 CODE
110 String Functions Exercise
111 String Functions Exerise SOLUTION
112 SOLUTION CODE
Refining Selections
113 Section Introduction
114 Adding Some New Books
115 CODE
116 DISTINCT
117 CODE
118 ORDER BY
119 CODE
120 More On ORDER BY
121 CODE
122 LIMIT
123 CODE
124 LIKE
125 CODE
126 Escaping Wildcards
127 CODE
128 Refining Selections Exercise
129 Refining Selections Exercise SOLUTION
130 SOLUTION CODE
Aggregate Functions
131 Section Introduction
132 Count Basics
133 CODE
134 GROUP BY
135 CODE
136 MIN and MAX Basics
137 CODE
138 Subqueries
139 CODE
140 Grouping By Multiple Columns
141 CODE
142 MIN and MAX With GROUP BY
143 CODE
144 SUM
145 CODE
146 AVG
147 CODE
148 Aggregate Functions Docs
149 Agg Functions Exercise
150 Agg Functions Exercise SOLUTION
151 SOLUTION CODE
Revisiting Data Types
152 Section Introduction
153 Surveying Other Data Types
154 CHAR vs. VARCHAR
155 INT, TINYINT, BIGINT, etc
156 DECIMAL
157 FLOAT & DOUBLE
158 DATE and TIME
159 Working With Dates
160 CODE
161 CURDATE, CURTIME, & NOW
162 CODE
163 Date Functions
164 CODE
165 Time Functions
166 CODE
167 Formatting Dates
168 CODE
169 Date Math
170 TIMESTAMPS
171 DEFAULT & ON UPDATE TIMESTAMPS
172 CODE
173 Data Types Exercise
174 Data Types Exercise SOLUTION
175 SOLUTION CODE
Comparison & Logical Operators
176 Section Introduction
177 Not Equal
178 CODE
179 NOT LIKE
180 CODE
181 Greater Than
182 CODE
183 Less Than Or Equal To
184 CODE
185 Logical AND
186 CODE
187 Logical OR
188 CODE
189 Between
190 CODE
191 Comparing Dates
192 CODE
193 The IN Operator
194 CODE
195 CASE
196 CODE
197 IS NULL
198 Exercise
199 Exercise SOLUTION
200 SOLUTION CODE
Constraints & ALTER TABLE
201 UNIQUE Constraint
202 CODE
203 CHECK Constraints
204 CODE
205 Named Constraints
206 CODE
207 Multiple Column Constraints
208 CODE
209 ALTER TABLE Adding Columns
210 CODE
211 ALTER TABLE Dropping Columns
212 CODE
213 ALTER TABLE Renaming
214 CODE
215 ALTER TABLE Modifying Columns
216 CODE
217 ALTER TABLE Constraints
218 CODE
One to Many & Joins
219 Section Introduction
220 Data is Messy
221 Relationships Basics
222 One to Many Relationship
223 Working with FOREIGN KEY
224 CODE
225 Cross Joins
226 CODE
227 Inner Joins
228 CODE
229 Inner Joins With Group By
230 CODE
231 Left Join
232 CODE
233 Left Join With Group By
234 CODE
235 Right Join
236 CODE
237 On Delete Cascade
238 CODE
239 Joins Exercise
240 EXERCISE STARTER DATA
241 Joins Exercise SOLUTION
242 SOLUTION CODE
Many to Many
243 Section Introduction
244 Many to Many Basics
245 Creating Our Many To Many Tables
246 CODE
247 TV Series Challenge #1
248 CODE
249 TV Series Challenge #2
250 CODE
251 TV Series Challenge #3
252 CODE
253 TV Series Challenge #4
254 CODE
255 TV Series Challenge #5
256 CODE
257 TV Series Challenge #6
258 CODE
259 TV Series Challenge #7
260 CODE
Views, Modes, & More!
261 Introducing Views
262 CODE
263 Updateable Views
264 ReplacingAltering Views
265 CODE
266 HAVING clause
267 CODE
268 WITH ROLLUP
269 CODE
270 SQL Modes Basics
271 CODE
272 STRICT_TRANS_TABLES
273 More Modes
Window Functions
274 Important Note (please read)
275 Introducing Window Functions
276 Using OVER()
277 CODE
278 PARTITION BY
279 CODE
280 ORDER BY with Windows
281 CODE
282 RANK()
283 DENSE_RANK & ROW_NUMBER()
284 CODE
285 NTILE()
286 CODE
287 FIRST_VALUE
288 CODE
289 LEAD and LAG
290 CODE
Instagram Database Clone
291 Section Introduction
292 IMPORTANT NOTE ABOUT THIS SECTION!
293 Introducing Instagram Clone
294 Users Schema
295 CODE
296 Photos Schema
297 CODE
298 Comments Schema
299 CODE
300 Likes Schema
301 CODE
302 Followers Schema
303 CODE
304 Note!
305 Approaches for Hashtags
306 Implementing Hashtags Table
307 CODE
308 Complete Instagram Schema
Working With Lots of IG Data
309 Section Introduction
310 Getting Lots of Instagram Data
311 CODE
312 Instagram Challenge #1
313 CODE
314 Instagram Challenge #2
315 CODE
316 Instagram Challenge #3
317 CODE
318 Instagram Challenge #4
319 CODE
320 Instagram Challenge #5
321 CODE
322 Instagram Challenge #6
323 CODE
324 Instagram Challenge #7
325 CODE
What Next
326 Additional SQL Resources
OPTIONAL LEGACY SECTION Introducing Node
327 IMPORTANT NOTE Using a Local Coding Environment Instead of Cloud9
328 IMPORTANT NOTE Downloading Source Code
329 Section Introduction
330 MySQL and Other Languages
331 BUT WHAT ABOUT PHP
332 Introduction to JOIN US app
333 REMINDER NodeJS Installation
334 Minute NodeJS Crash Course
335 CODE 5 Minute Node Crash Course
336 IMPORTANT NOTE About the Faker Package
337 Introduction to NPM and Faker
338 UPDATED CODE Introduction to NPM and Faker
339 Introducing the MySQL Package
340 IMPORTANT NOTE About the MySQL Package and Connecting Node to MySQL
341 REMINDER How to Start the MySQL Command Line Locally (instead of Cloud9)
342 Connecting Node to MySQL
343 CODE Connecting Node to MySQL
344 Creating Our Users Table
345 CODE Creating Our Users Table
346 Selecting Using Node
347 CODE Selecting Using Node
348 Inserting Using Node
349 CODE Inserting Using Node
350 Some MySQLNode Magic
351 Bulk Inserting 500 Users – Finally!
352 CODE Bulk Inserting 500 Users
353 Users Exercises
354 Users Exercises Solutions
355 CODE 500 Users Exercises Solutions
OPTIONAL LEGACY SECTION Building Our Web App
356 REMINDER Set Up A Local Coding Environment Instead of Cloud9
357 Section Introduction
358 Join Us Completed CODE
359 Introducing Express
360 NPM Init and package.json files
361 IMPORTANT NOTE About Previewing Our Node Web App in the Browser
362 Our First Simple Web App
363 CODE Our First Simple Web App
364 Adding Multiple Routes
365 CODE Adding Multiple Routes
366 REMINDER Use the Updated Connection Code
367 Connecting Express and MySQL
368 CODE Connecting Express and MySQL
369 Adding EJS Templates
370 CODE Adding EJS Templates
371 Connecting The Form
372 CODE Connecting the Form
373 Styling Part 1
374 CODE HTML AND CSS FILES
375 Styling Part 2
OPTIONAL LEGACY SECTION Database Triggers
376 NOTE Use MySQL Locally Instead of Cloud9
377 Introduction to Database Triggers
378 Writing Our First Trigger Part 1
379 Writing Our First Trigger Part 2
380 NOTE About the Next Lecture
381 Preventing Instagram Self-Follows With Triggers
382 Creating Logger Triggers
383 Managing Triggers And A Warning
Resolve the captcha to access the links!