SQL - The Complete Guide (MySQL, PostgreSQL & more)

19h 35m 38s
English
Paid

SQL is THE most important query language you can learn! It is utilized by many popular database management systems like MySQL and PostgreSQL. Furthermore, it plays a pivotal role in data analysis and big data frameworks, such as Apache Spark. Therefore, mastering SQL opens up an endless array of opportunities and career paths. Whether working with relational databases or venturing into data science, SQL is an indispensable skill!

Course Overview

This course offers a comprehensive and detailed education in SQL from the ground up! Notably, the course includes two distinct learning paths:

  1. "Complete Course" path: Progress through the entire curriculum, step by step, learning all essential features and concepts.
  2. "Quick Summary" path: Master SQL essentials within a few hours, focusing on key concepts rapidly.

Both learning paths are integrated into this course with a single purchase, allowing flexibility to switch between them or complete both as you see fit. Begin with the free first course section to get a thorough introduction to the course structure and the learning paths.

SQL and its Variants

SQL is a standardized language, offering utility across various contexts. However, different database systems may support distinct SQL features or introduce their own language variations. This course delves into SQL by showcasing all critical features using MySQL and PostgreSQL, the two most popular database systems. All examples and commands are demonstrated in both systems, ensuring you gain confidence in any SQL environment!

Skills You'll Acquire

This course will empower you to craft your own SQL queries, designing basic or advanced database structures and handling data of varying shapes and complexities. Specifically, you will learn to:

  • Understand what SQL is and its core syntax
  • Write effective SQL commands
  • Install MySQL & PostgreSQL along with various clients
  • Create and organize database tables
  • Select appropriate data types and know when to use them
  • Perform CRUD operations: Create, Read, Update & Delete
  • Insert data into tables effectively
  • Query and filter data proficiently
  • Understand when to split data across multiple tables and how to implement it
  • Join and merge data for comprehensive result sets
  • Formulate basic to complex queries
  • Aggregate and group data effectively
  • Utilize built-in database functions for numbers, text, or dates
  • Optimize databases using indexes
  • And many more essential skills!

About the Author: Academind Pro

Academind Pro thumbnail
Academind offers the most comprehensive and up-to-date learning resources on Web Development. From the very basics up to advanced topics and real projects - we got you covered! And we'll give you course completion certificates to prove your progress to others!

Watch Online 203 lessons

This is a demo lesson (10:00 remaining)

You can watch up to 10 minutes for free. Subscribe to unlock all 203 lessons in this course and access 10,000+ hours of premium content across all courses.

View Pricing
0:00
/
#1: Welcome To The Course!
All Course Lessons (203)
#Lesson TitleDurationAccess
1
Welcome To The Course! Demo
02:25
2
What Is SQL?
06:05
3
A Closer Look At SQL & Databases
06:11
4
Course Content
03:36
5
There Are Two Ways Of Taking This Course!
02:16
6
How To Get The Most Out Of This Course
03:06
7
Module Introduction
00:49
8
Understanding The Core SQL Syntax
06:37
9
SQL in Action
03:34
10
The Core SQL Rules
03:50
11
Data Definition vs Data Manipulation
03:33
12
Module Introduction
02:43
13
Installing Different Database Management Systems
02:59
14
What We Need To Install
07:13
15
MySQL Setup Overview & macOS Setup
10:35
16
MySQL Windows Setup
08:10
17
Postgresql Setup Overview & macOS Setup
06:15
18
Postgresql Windows Setup
04:32
19
Connecting To Database Servers (MySQL & Postgresql) With Various Clients
15:30
20
Setting Up Visual Studio Code With The SQLTools Extension (Course Environment)
10:44
21
Configuring VS Code & Exploring SQLTools
11:59
22
Module Introduction
02:02
23
Key Terms When Working With Data Definition Statements & Commands
02:43
24
Introducing The Course Section Example
02:24
25
Introducing Key Data Definition Clauses
03:12
26
Creating a new Database (CREATE DATABASE)
09:57
27
The Importance Of Data Types (Value Types)
05:22
28
Introducing Key Text Value Types
06:38
29
Introducing Numeric Values, Date Types & More
05:51
30
How Do You Store Files?
03:37
31
Getting Started With The CREATE TABLE Statement & Syntax
05:17
32
Creating a First Text Column
03:50
33
Creating a Numeric Value Column
09:04
34
Working with Enums & Finishing Table Creation
07:07
35
Inserting Data Into The Created Table (INSERT INTO)
06:20
36
Inserting & Querying More Data
04:03
37
Working With Fixed Point & Floating Point Numbers
08:54
38
Adding Boolean Value Types
01:59
39
Creating a new Table with Text & Timestamp Data
05:11
40
Inserting Data Into The New Tables
07:33
41
Introducing Default Column Values
03:14
42
Deleting (Dropping) Tables & Inserting Data With Default Values
05:22
43
Updating Tables & Columns
08:58
44
Dealing With "No Data" (NULL) vs "0"
06:28
45
Exploring the NOT NULL Constraint
09:24
46
Exploring the CHECK Constraint
12:35
47
We Need Unique Values & Identifiers!
11:40
48
Working With Auto Incrementing IDs & Primary Keys
10:21
49
Constraints - Summary
02:46
50
Understanding Text Encoding & Collation
07:36
51
Temporary Tables & Tables From Other Tables
02:41
52
Creating Generated Columns
07:49
53
Module Summary
03:03
54
Time To Practice: Problem
04:00
55
Time To Practice: Solution (1/2)
13:49
56
Time To Practice: Solution (2/2)
08:56
57
Module Introduction
01:22
58
What Are CRUD Operations?
02:34
59
Inserting Data: Theory
02:35
60
Selecting Data: Theory
01:25
61
Updating Data: Theory
01:38
62
Deleting Data: Theory
01:23
63
Introducing The Section Example
03:34
64
Setting Up An Example Database & Table
07:47
65
Example: Inserting Data
07:27
66
Inserting More (Dummy) Data
01:12
67
Updating In Action
05:23
68
Deleting In Action
02:09
69
Selecting Data - The Basics
04:43
70
SELECT, Column Names & Data Expressions
05:03
71
Filtering with WHERE: Available Comparison Operators & Variations
04:24
72
Filtering In Action (WHERE In Action)
03:40
73
Combining Comparisons With AND & OR
03:37
74
Greater, Smaller & Ranges
07:16
75
Filtering Text Values
04:30
76
Working With Dates & Date Differences
05:17
77
Introducing ORDER BY & LIMIT
02:10
78
Ordering & Limiting Results In Action
07:24
79
Looking For DISTINCT Values
01:42
80
Subqueries & Views
07:06
81
Module Summary
02:34
82
Module Introduction
01:46
83
A First Look At Related Data
04:10
84
Data Normalization - First Steps
04:04
85
Splitting Data Into Tables
09:49
86
Forms Of Data Normalization
03:06
87
Our First Section Example
04:10
88
Creating Tables With Relations
10:24
89
Inserting Related Data
08:12
90
Joining Data & Introducing INNER JOIN
04:07
91
Using INNER JOIN
08:11
92
Combining Multiple JOINs
04:14
93
Data Joining & Filtering
03:49
94
Introducing LEFT JOIN
02:11
95
Using LEFT JOIN
03:58
96
Combining Multiple LEFT JOINs
02:41
97
What About RIGHT JOIN?
01:24
98
Example Time & INNER JOIN vs LEFT JOIN
12:11
99
Introducing CROSS JOIN
01:44
100
UNION & Why It's Different
08:52
101
What's Wrong With Our Foreign Keys?
05:08
102
Introducing Foreign Key Constraints
04:07
103
Diving Deeper Into Foreign Key Constraints
05:36
104
Foreign Key Constraints In Action
12:06
105
Data Relationship Types: One-to-Many, Many-to-Many, One-to-ONe
04:35
106
A Bigger Example
05:40
107
Example: Adding First Tables & Relations
16:57
108
Example: Adding More Tables & Data
11:11
109
Many-to-Many Relationships & Intermediate Tables ("Linking Tables")
07:19
110
Querying The Example Data
08:58
111
Practicing JOINs With Filtering
09:14
112
Experimenting With Referential Integrity
06:01
113
Module Introduction
01:31
114
Remember: Primary Keys Don't Have To Be Auto-Incrementing IDs!
02:52
115
Introducing Composite Primary Keys
07:53
116
Defining Composite Primary Keys (Composite Keys In Action)
02:37
117
Composite Foreign Keys
04:48
118
Composite Keys In Action
02:52
119
Self-Referencing Relationships (Self-Referential Relationships)
09:25
120
Self-Referencing Many To Many Relationships
07:59
121
Module Introduction
01:28
122
The Module Project
04:38
123
What are Aggregate Functions - Theory
04:24
124
Understanding COUNT()
10:56
125
Working with MIN() & MAX() and Adding Aliases to Aggregate Functions
07:39
126
Using SUM(), AVG() & ROUND()
06:17
127
Working with Filters & Joining Tables
11:35
128
Theory Time - Understanding GROUP BY
05:04
129
Applying GROUP BY in Practice
04:41
130
GROUP BY & Joined Queries
06:12
131
Understanding WHERE vs HAVING
10:01
132
Applying HAVING in Practice
07:30
133
Working with Nested Subqueries
11:01
134
Introducing Window Functions
08:19
135
Understanding ORDER BY and RANK()
07:01
136
Module Introduction
01:57
137
The Module Project
03:12
138
Working with Mathematical Functions & Arithmetic Operators
14:29
139
Understanding String Functions with SELECT
06:57
140
Using String Functions with INSERT
08:49
141
Understanding Date / Time Functions
04:41
142
Working with Weekdays
08:08
143
Calculating INTERVALS
09:42
144
Adding INTERVALS to Dates
07:55
145
Understanding LIKE & Pattern Matching
09:52
146
Understanding EXISTS
06:19
147
Working with Subquery Expressions and EXISTS
03:57
148
Working with Subquery Expressions and IN
08:10
149
Introducing Conditional Expressions
07:16
150
Challenge Solution
04:41
151
Database (SQL) vs Application Level
03:21
152
Module Introduction
00:53
153
Understanding Transactions
02:19
154
Applying Transactions & ROLLBACK
09:20
155
Committing Changes
08:15
156
Working with Savepoints
04:37
157
Transactions in PostgreSQL
04:53
158
Module Introduction
01:16
159
What Are Indexes? And Why Would You Need Them?
06:40
160
Don't Use Too Many Indexes!
04:33
161
Index Types: An Overview
05:07
162
Setting Up Some Example Data
03:16
163
EXPLAINing Queries & Query Planning
05:17
164
Creating & Using Single-Column Indexes
05:52
165
Unique Indexes
02:06
166
Working With Multi-Column Indexes (Composite Indexes)
09:21
167
Partial Indexes
02:37
168
Module Introduction
01:49
169
What Is SQL?
06:05
170
A Closer Look At Databases In General
06:11
171
The Core SQL Syntax
06:50
172
Course Setup (For Following Along)
05:06
173
Analyzing The Section Example Project
06:07
174
Introducing "Data Normalization"
03:36
175
Planning Tables & Relationships
10:26
176
Creating A New Database
04:13
177
Choosing Identifier Names
03:11
178
Defining Columns
03:44
179
Exploring Important Data Types
05:33
180
How To Store Files & Adding More Data Types
04:14
181
Constraints: An Introduction
05:20
182
Introducing Database Functions
02:37
183
Understanding Primary Keys & Unique IDs
06:38
184
INSERTing Data
08:13
185
Basic Data Querying (via SELECT)
02:29
186
UPDATE & DELETE In Action
05:58
187
Filtering (WHERE) & Sorting (ORDER BY)
04:37
188
Adding More Tables
08:24
189
Relations & Foreign Keys
09:11
190
Understanding ON DELETE & ON UPDATE
03:39
191
Adding More Tables & Relationships
07:15
192
Many To Many Relationships & Linking (Intermediate) Tables
08:48
193
Inserting Related Data
13:27
194
Connecting Data With INNER JOIN
11:18
195
Combining Multiple Joins
05:21
196
Introducing LEFT JOIN
10:33
197
Joining Data & Filtering
04:02
198
Filtering Text With LIKE
06:03
199
Introducing Aggregate Functions
03:57
200
Grouping Aggregate Results (GROUP BY)
05:43
201
The HAVING Clause (vs WHERE)
03:32
202
Module Summary
01:45
203
Congratulations + More Content
00:54
Unlock unlimited learning

Get instant access to all 202 lessons in this course, plus thousands of other premium courses. One subscription, unlimited knowledge.

Learn more about subscription