English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 650 lectures (37h 32m) | 9.01 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 Course Intro
3 About Me
4 Getting The Course Code
5 First 5 Minutes of SQL
Getting Started & Installation
6 Section Introduction
7 What Is A Database
8 SQL vs. MySQL
9 Installation START HERE!
10 Installation Windows Instructions
11 Installation Mac Instructions
12 Mac Installation Commands
Creating Databases & Tables
13 Section Introduction
14 Showing Databases
15 CODE Showing Databases
16 Creating Databases
17 CODE Creating Databases
18 Dropping and Using Databases
19 CODE Dropping and Using Databases
20 Introducing Tables
21 Data Types The Basics
22 Basic Datatypes Challenge
23 CODE Basic Datatypes Challenge
24 Creating Tables
25 CODE Creating Tables
26 How Do We Know It Worked
27 CODE How Do We Know It Worked
28 Dropping Tables
29 CODE Dropping Tables
30 Tables Basics Activity
31 SOLUTION Tables Basics Activity
32 MySQL Comments
Inserting Data
33 Section Introduction
34 INSERT The Basics
35 CODE INSERT The Basics
36 A Quick Preview of SELECT
37 CODE A Quick Preview of SELECT
38 Multi-inserts
39 CODE Multi-inserts
40 INSERT Exercise
41 SOLUTION INSERT Exercise
42 Working With NOT NULL
43 CODE Working With NOT NULL
44 Sidenote Quotes In MySQL
45 Adding DEFAULT Values
46 CODE Adding DEFAULT Values
47 Introducing Primary Keys
48 CODE Introducing Primary Keys
49 Working With AUTO_INCREMENT
50 CODE Working With AUTO_INCREMENT
51 Create Table Insert Exercise
52 SOLUTION Insert Exercise
CRUD Basics
53 Section Introduction
54 Introducing CRUD
55 Getting Our New Dataset
56 CODE
57 Officially Introducing SELECT
58 CODE
59 The WHERE clause
60 CODE
61 Rapid Fire Exercises
62 Rapid Fire Exercises SOLUTION
63 SOLUTION CODE
64 Aliases
65 CODE
66 Using UPDATE
67 CODE
68 A Quick Rule Of Thumb
69 UPDATE Exercise
70 UPDATE Exercise SOLUTION
71 SOLUTION CODE
72 Introducing DELETE
73 CODE
74 DELETE Exercise
75 DELETE Exercise SOLUTION
76 SOLUTION CODE
CRUD Challenge
77 Section Introduction
78 Introducing the CRUD Challenge
79 CRUD Challenge Solution Creating
80 SOLUTION CODE
81 CRUD Challenge Solution Reading
82 SOLUTION CODE
83 CRUD Challenge Solution Updating
84 SOLUTION CODE
85 CRUD Challenge Solution Deleting
86 SOLUTION CODE
String Functions
87 Section Introduction
88 The World Of String Functions
89 Loading Our Books Data
90 CODE
91 CONCAT
92 CODE
93 SUBSTRING
94 CODE
95 Combining String Functions
96 CODE
97 Sidenote SQL Formatting
98 REPLACE
99 CODE
100 REVERSE
101 CODE
102 CHAR_LENGTH
103 CODE
104 UPPER & LOWER
105 CODE
106 Other String Functions
107 CODE
108 String Functions Exercise
109 String Functions Exerise SOLUTION
110 SOLUTION CODE
Refining Selections
111 Section Introduction
112 Adding Some New Books
113 CODE
114 DISTINCT
115 CODE
116 ORDER BY
117 CODE
118 More On ORDER BY
119 CODE
120 LIMIT
121 CODE
122 LIKE
123 CODE
124 Escaping Wildcards
125 CODE
126 Refining Selections Exercise
127 Refining Selections Exercise SOLUTION
128 SOLUTION CODE
Aggregate Functions
129 Section Introduction
130 Count Basics
131 CODE
132 GROUP BY
133 CODE
134 MIN and MAX Basics
135 CODE
136 Subqueries
137 CODE
138 Grouping By Multiple Columns
139 CODE
140 MIN and MAX With GROUP BY
141 CODE
142 SUM
143 CODE
144 AVG
145 CODE
146 Aggregate Functions Docs
147 Agg Functions Exercise
148 Agg Functions Exercise SOLUTION
149 SOLUTION CODE
Revisiting Data Types
150 Section Introduction
151 Surveying Other Data Types
152 CHAR vs. VARCHAR
153 INT, TINYINT, BIGINT, etc
154 DECIMAL
155 FLOAT & DOUBLE
156 DATE and TIME
157 Working With Dates
158 CODE
159 CURDATE, CURTIME, & NOW
160 CODE
161 Date Functions
162 CODE
163 Time Functions
164 CODE
165 Formatting Dates
166 CODE
167 Date Math
168 TIMESTAMPS
169 DEFAULT & ON UPDATE TIMESTAMPS
170 CODE
171 Data Types Exercise
172 Data Types Exercise SOLUTION
173 SOLUTION CODE
Comparison & Logical Operators
174 Section Introduction
175 Not Equal
176 CODE
177 NOT LIKE
178 CODE
179 Greater Than
180 CODE
181 Less Than Or Equal To
182 CODE
183 Logical AND
184 CODE
185 Logical OR
186 CODE
187 Between
188 CODE
189 Comparing Dates
190 CODE
191 The IN Operator
192 CODE
193 CASE
194 CODE
195 IS NULL
196 Exercise
197 Exercise SOLUTION
198 SOLUTION CODE
Constraints & ALTER TABLE
199 UNIQUE Constraint
200 CODE
201 CHECK Constraints
202 CODE
203 Named Constraints
204 CODE
205 Multiple Column Constraints
206 CODE
207 ALTER TABLE Adding Columns
208 CODE
209 ALTER TABLE Dropping Columns
210 CODE
211 ALTER TABLE Renaming
212 CODE
213 ALTER TABLE Modifying Columns
214 CODE
215 ALTER TABLE Constraints
216 CODE
One to Many & Joins
217 Section Introduction
218 Data is Messy
219 Relationships Basics
220 One to Many Relationship
221 Working with FOREIGN KEY
222 CODE
223 Cross Joins
224 CODE
225 Inner Joins
226 CODE
227 Inner Joins With Group By
228 CODE
229 Left Join
230 CODE
231 Left Join With Group By
232 CODE
233 Right Join
234 CODE
235 On Delete Cascade
236 CODE
237 Joins Exercise
238 EXERCISE STARTER DATA
239 Joins Exercise SOLUTION
240 SOLUTION CODE
Many to Many
241 Section Introduction
242 Many to Many Basics
243 Creating Our Many To Many Tables
244 CODE
245 TV Series Challenge #1
246 CODE
247 TV Series Challenge #2
248 CODE
249 TV Series Challenge #3
250 CODE
251 TV Series Challenge #4
252 CODE
253 TV Series Challenge #5
254 CODE
255 TV Series Challenge #6
256 CODE
257 TV Series Challenge #7
258 CODE
Views, Modes, & More!
259 Introducing Views
260 CODE
261 Updateable Views
262 ReplacingAltering Views
263 CODE
264 HAVING clause
265 CODE
266 WITH ROLLUP
267 CODE
268 SQL Modes Basics
269 CODE
270 STRICT_TRANS_TABLES
271 More Modes
Window Functions
272 Introducing Window Functions
273 Using OVER()
274 CODE
275 PARTITION BY
276 CODE
277 ORDER BY with Windows
278 CODE
279 RANK()
280 DENSE_RANK, & ROW_NUMBER()
281 CODE
282 NTILE()
283 CODE
284 FIRST_VALUE
285 CODE
286 LEAD and LAG
287 CODE
Instagram Database Clone
288 Section Introduction
289 IMPORTANT NOTE ABOUT THIS SECTION!
290 Introducing Instagram Clone
291 Users Schema
292 CODE
293 Photos Schema
294 CODE
295 Comments Schema
296 CODE
297 Likes Schema
298 CODE
299 Followers Schema
300 CODE
301 Note!
302 Approaches for Hashtags
303 Implementing Hashtags Table
304 CODE
305 Complete Instagram Schema
Working With Lots of IG Data
306 Section Introduction
307 Getting Lots of Instagram Data
308 CODE
309 Instagram Challenge #1
310 CODE
311 Instagram Challenge #2
312 CODE
313 Instagram Challenge #3
314 CODE
315 Instagram Challenge #4
316 CODE
317 Instagram Challenge #5
318 CODE
319 Instagram Challenge #6
320 CODE
321 Instagram Challenge #7
322 CODE
What Next
323 Additional SQL Resources
LEGACY Introduction and 5 Minutes of SQL
324 Course Introduction
325 Instructor Introduction(s)
326 Syllabus Walkthrough and Prerequisites
327 Note about SQL Editor
328 Your First 5 Minutes of SQL – Let’s Get Coding!
329 CODE Your First 5 Minutes of SQL
330 How The Course Works
LEGACY Getting Started Overview and Installation
331 Section Introduction
332 What Is a Database
333 SQL Vs. MySQL
334 Important note about the next lecture
335 Installation Overview – WATCH BEFORE INSTALLING
336 Now using GoormIDE instead of Cloud9
337 GoormIDE Registration IMPORTANT – PLEASE READ!!!
338 MySQL Tips to Make Your Life Easier
339 Mac Installation
340 CODE Mac Installation
341 Windows Installation
LEGACY Creating Databases and Tables
342 Section 3 Introduction
343 Creating Databases
344 CODE Creating Databases
345 Dropping Databases
346 CODE Dropping Databases
347 Using Databases
348 CODE Using Databases
349 Introduction to Tables
350 The Basic Datatypes
351 Note about INT max size
352 Basic Datatypes Challenge
353 Basic Datatypes Challenge – Solution
354 Creating Your Own Tables
355 CODE Creating Your Own Tables
356 How Do We Know It Worked
357 CODE How Do We Know It Worked
358 Dropping Tables
359 CODE Dropping Tables
360 Creating Your Own Tables Challenge
361 CODE Creating Your Own Tables Challenge
LEGACY Inserting Data (and a couple other things)
362 Section 4 Introduction
363 Inserting Data
364 CODE Inserting Data
365 Super Quick Intro To SELECT
366 CODE Super Quick Intro To SELECT
367 Multiple INSERT
368 CODE Multiple Insert
369 INSERT Challenges
370 Note about using quotes inside of inserted values
371 INSERT Challenges Solution
372 CODE INSERT Challenges Solution
373 NOTE MySQL Warnings
374 MySQL Warnings
375 CODE MySQL Warnings
376 NULL and NOT_NULL
377 CODE NULL and NOT NULL
378 Setting Default Values
379 CODE Setting Default Values
380 A Primer On Primary Keys
381 CODE A Primer on Primary Keys
382 Table Constraints Exercise
383 Table Constraints Exercise Solution
384 CODE Table Constraints Exercise Solution
LEGACY CRUD commmands
385 Section 5 Introduction
386 Introduction to CRUD
387 CODE Introduction to CRUD
388 Preparing Our Data
389 CODE Preparing Our Data
390 Official Introduction to SELECT
391 CODE Official Introduction to SELECT
392 Introduction to WHERE
393 CODE Introduction to WHERE
394 SELECT Challenges
395 SELECT Challenges Solution
396 CODE SELECT Challenges Solution
397 Introduction to Aliases
398 CODE Introduction to Aliases
399 The UPDATE Command
400 CODE The UPDATE Command
401 UPDATE Challenges
402 UPDATE Challenges Solution
403 CODE UPDATE Challenges Solution
404 Introduction to DELETE
405 CODE Introduction to DELETE
406 DELETE Challenges
407 DELETE Challenges Solution
408 CODE DELETE Challenges Solution
LEGACY CRUD Challenge Section
409 Section 6 Introduction
410 CRUD Exercise Overview
411 CRUD Exercise Create Solution
412 CODE CRUD Exercise Create Solution
413 CRUD Exercise Read Solution
414 CODE CRUD Exercise Read Solution
415 CRUD Exercise Update Solution
416 CODE CRUD Exercise Update Solution
417 CRUD Exercise Delete Solution
418 CODE CRUD Exercise Delete Solution
LEGACY The World Of String Functions
419 Section 7 Introduction
420 Important Note about Running SQL Files (please read)
421 Running SQL Files
422 CODE Running SQL Files
423 Loading Our Book Data
424 CODE Loading Our Book Data
425 Working with CONCAT
426 CODE Working With CONCAT
427 Introducing SUBSTRING
428 CODE Introducing SUBSTRING
429 Introducing REPLACE
430 CODE Introducing REPLACE
431 Using REVERSE
432 CODE Using REVERSE
433 Working with CHAR LENGTH
434 CODE Working with CHAR LENGTH
435 Changing Case with UPPER and LOWER
436 CODE Changing Case with UPPER and LOWER
437 Note about string functions
438 String Function Challenges
439 String Function Challenges Solution
440 CODE String Function Challenges Solution
LEGACY Refining Our Selections
441 Section 8 Introduction
442 Seed Data Adding A Couple New Books
443 CODE Seed Data Adding A Couple New Books
444 Using DISTINCT
445 CODE Using DISTINCT
446 Sorting Data with ORDER BY
447 CODE Sorting Data with ORDER BY
448 Using LIMIT
449 CODE Using LIMIT
450 Better Searches with LIKE
451 CODE Better Searches with LIKE
452 LIKE Part 2 More Wildcards
453 CODE LIKE Part 2 More Wildcards
454 Refining Selections Exercises
455 Refining Selections Exercises Solution
456 CODE Refining Selections Exercises Solution
LEGACY The Magic of Aggregate Functions
457 Section 9 Introduction
458 The Count Function
459 CODE The Count Function
460 The Joys of Group By
461 Note about SQL Mode only_full_group_by
462 CODE The Joys of Group By
463 Min and Max Basics
464 CODE MIN and MAX Basics
465 SUBQUERIES – A Problem with Min and Max
466 CODE A Problem with Min and Max
467 Using Min and Max with Group By
468 CODE Using Min and Max with Group By
469 The Sum Function
470 CODE The Sum Function
471 The Avg Function
472 CODE The Avg Function
473 Aggregate Functions Challenges
474 Aggregate Functions Challenges Solution
475 CODE Aggregate Functions Challenges Solution
LEGACY Revisiting Data Types
476 Section 10 Introduction
477 CHAR and VARCHAR
478 Note about CHAR and VARCHAR
479 CODE CHAR and VARCHAR
480 DECIMAL
481 CODE DECIMAL
482 FLOAT and DOUBLE
483 CODE FLOAT and DOUBLE
484 Note about setting the timezone in Cloud9
485 DATE, TIME, and DATETIME
486 Creating Our DATE data
487 CODE Creating Our DATE data
488 CURDATE, CURTIME, and NOW
489 Note about formatting dates
490 Formatting Dates
491 CODE Formatting Dates
492 Date Math
493 CODE Date Math
494 Working with TIMESTAMPS
495 CODE Working with TIMESTAMPS
496 Data Types Exercises
497 Data Types Exercises Solution
498 CODE Data Types Exercises Solution
LEGACY The Power of Logical Operators
499 Section 11 Introduction
500 Not Equal
501 CODE Not Equal
502 Not Like
503 CODE Not Like
504 Greater Than
505 CODE Greater Than
506 Less Than
507 CODE Less Than
508 Logical AND
509 CODE Logical AND
510 Logical OR
511 CODE Logical OR
512 Between
513 CODE Between
514 In And Not In
515 CODE In And Not In
516 Case Statements
517 CODE Case Statements
518 Logical Operators Exercises
519 Logical Operators Exercises Solution
520 CODE Logical Operators Exercises Solution
LEGACY Section 12 One To Many
521 Section 12 Introduction
522 Real World Data Is Messy
523 Types of Data Relationships
524 One To Many The Basics
525 Working With Foreign Keys
526 CODE Working With Foreign Keys
527 Cross Join
528 CODE Cross Joins
529 Inner Join
530 CODE Inner Joins
531 Left Join
532 CODE Left Joins
533 Right Joins Pt 1
534 CODE Right Joins Part 1
535 Right Joins Pt 2
536 CODE Right Joins Part 2
537 Right and Left Joins A Common Question
538 CODE Right and Left Joins FAQ
539 Our First Joins Exercise
540 Our First Joins Exercise SOLUTION
541 CODE Our First Joins Exercise
542 Our First Joins Exercise SOLUTION PT. 2
543 CODE Our First Joins Exercise SOLUTION PT. 2
LEGACY Many To Many
544 Section 13 Introduction
545 Many To Many Basics
546 Creating Our Tables
547 CODE Creating Our Tables
548 TV Joins Challenge 1
549 CODE TV Joins Challenge 1 Solution
550 TV Joins Challenge 2
551 CODE TV Joins Challenge 2 SOLUTION
552 TV Joins Challenge 3
553 CODE TV Joins Challenge 3 SOLUTION
554 TV Joins Challenge 4
555 CODE TV Joins Challenge 4 SOLUTION
556 TV Joins Challenge 5
557 CODE TV Joins Challenge 5 SOLUTION
558 TV Joins Challenge 6
559 CODE TV Joins Challenge 6 SOLUTION
560 TV Joins Challenge 7
561 CODE TV Joins Challenge 7 SOLUTION
LEGACY Instagram Database Clone
562 Section 14 Introduction
563 Introduction To Instagram Clone Schema
564 Cloning Instagram’s DB Users Schema
565 CODE IG Clone Users Schema
566 Cloning Instagram’s DB Photos Schema
567 CODE IG Clone Photos Schema
568 Cloning Instagram’s DB Comments Schema
569 CODE IG Clone Comments Schema
570 Cloning Instagram’s DB Likes Schema
571 CODE IG Clone Likes Schema
572 Cloning Instagram’s DB Followers Schema
573 CODE IG Clone Followers Schema
574 Note about Cloning Instagram’s DB Hashtags Part 1
575 Cloning Instagram’s DB Hashtags Part 1
576 Cloning Instagram’s DB Hashtags Part 2
577 CODE IG Clone Hashtags Schema
578 CODE Complete IG Clone Schema
LEGACY Working With Lots Of Instagram Data
579 Section 15 Introduction
580 Note about Loading The JUMBO dataset
581 Loading The JUMBO dataset
582 The Instagram Clone DATASET
583 Instagram Clone Challenge 1
584 Instagram Challenge 1 Solution CODE
585 Instagram Clone Challenge 2
586 Instagram Challenge 2 Solution CODE
587 Instagram Clone Challenge 3
588 Instagram Challenge 3 Solution CODE
589 Instagram Clone Challenge 4
590 Instagram Challenge 4 Solution CODE
591 Instagram Clone Challenge 5
592 Instagram Challenge 5 Solution CODE
593 Instagram Clone Challenge 6
594 Instagram Challenge 6 Solution CODE
595 Note about Instagram Clone Challenge 7
596 Instagram Clone Challenge 7
597 Instagram Challenge 7 Solution CODE
LEGACY Introducing Node
598 Note about Introducing Node
599 Section 16 Introduction
600 MySQL and Other Languages
601 BUT WHAT ABOUT PHP
602 Introduction to JOIN US app
603 Note about Setting Up Cloud9 for Node
604 Minute NodeJS Crash Course
605 CODE 5 Minute Node Crash Course
606 Note about Faker install
607 Introduction to NPM and Faker
608 CODE Introduction to NPM and Faker
609 Introducing the MySQL Package
610 Note about connecting Node to MySQL
611 Connecting Node to MySQL
612 CODE Connecting Node to MySQL
613 Creating Our Users Table
614 CODE Creating Our Users Table
615 Selecting Using Node
616 CODE Selecting Using Node
617 Inserting Using Node
618 CODE Inserting Using Node
619 Some MySQLNode Magic
620 Bulk Inserting 500 Users – Finally!
621 CODE Bulk Inserting 500 Users
622 Users Exercises
623 Users Exercises Solutions
624 CODE 500 Users Exercises Solutions
LEGACY Building Our Web App
625 Section 17 Introduction
626 Join Us Completed CODE
627 Introducing Express
628 NPM Init and package.json files
629 Note about Our First Simple Web App
630 Our First Simple Web App
631 CODE Our First Simple Web App
632 Adding Multiple Routes
633 CODE Adding Multiple Routes
634 Connecting Express and MySQL
635 CODE Connecting Express and MySQL
636 Adding EJS Templates
637 CODE Adding EJS Templates
638 Connecting The Form
639 CODE Connecting the Form
640 Styling Part 1
641 CODE HTML AND CSS FILES
642 Styling Part 2
LEGACY Database Triggers
643 Introduction to Database Triggers
644 Writing Our First Trigger Part 1
645 Writing Our First Trigger Part 2
646 Note about next lecture
647 Preventing Instagram Self-Follows With Triggers
648 Creating Logger Triggers
649 Managing Triggers And A Warning
Additional Resources
650 Links to Additional SQL Learning Resources
Resolve the captcha to access the links!