SQL – The Complete Developer’s Guide (MySQL, PostgreSQL)

SQL – The Complete Developer’s Guide (MySQL, PostgreSQL)

English | MP4 | AVC 1280×720 | AAC 48KHz 2ch | 219 lectures (19h 42m) | 8.09 GB

From data definition to manipulation (CRUD, relations, JOIN): Learn SQL for MySQL, Postgresql & more from the ground up!

SQL is THE most important query language you can learn!

It’s used by many popular relational database management systems like MySQL or PostgreSQL. But it’s also used by data analysis and big data frameworks and tools like Apache Spark.

Hence knowing SQL opens up an endless amount of opportunities and jobs – no matter if you’re going to work with relational databases or if you’re becoming a data scientist, knowing SQL will be key!

This course teaches you SQL from the ground up and in extremely high detail! In addition, this course comes with two main paths that you can take:

The “Complete Course” path where you go through the entire course, step by step, learning about ALL the key features and concepts you have to know

The “Quick Summary” path where you will learn the key SQL essentials within a few hours

Both paths are included in this course (i.e. with one single purchase) and you can switch between them or take both – simply as it makes most sense for you! Just dive into the free first course section to learn all about this course and these two paths (and how to take them).

SQL is a standardized language and therefore learning SQL will help you apply it in all kinds of contexts. Nonetheless, different database systems also support different aspects of SQL or bring their own variations of the SQL language. Therefore, this course dives into SQL by exploring all key features at the example of the two most popular database systems: MySQL and PostgreSQL. All query and command examples are shown for both database systems, ensuring that you feel comfortable working with SQL in either environment!

This course will enable you to write your own (simple or complex) SQL queries, create basic or advanced databases and table structures and work with data of different shape and complexity!

In detail, you will learn:

  • What exactly SQL is and how the core syntax looks like
  • How to write SQL commands
  • How to install MySQL & PostgreSQL as well as various clients
  • How to create and structure database tables
  • Which data types you may use and when to use which data type
  • How to perform CRUD operations: Create, Read, Update & Delete Data
  • How to insert data into tables
  • How to query and filter data
  • Why you should split data across multiple tables (and how to do that)
  • How to join (merge) data into combined result sets
  • How to write basic and more complex queries
  • How to aggregate and group data
  • How to use built-in database functions to work with numbers, text or dates
  • How to optimize databases with indexes
  • And much, much more!
Table of Contents

Getting Started
1 Welcome To The Course
2 What Is SQL
3 A Closer Look At SQL & Databases
4 Join Our Learning Community
5 Course Content
6 There Are Two Ways Of Taking This Course
7 How To Get The Most Out Of This Course
8 Course Code & Resources

SQL Basics Syntax & Core Features
9 Module Introduction
10 Understanding The Core SQL Syntax
11 SQL in Action
12 The Core SQL Rules
13 Data Definition vs Data Manipulation

Course Setup Installing MySQL & Postgresql
14 Module Introduction
15 Installing Different Database Management Systems
16 What We Need To Install
17 You Can Skip The Next Lectures
18 MySQL Setup Overview & macOS Setup
19 MySQL Windows Setup
20 Postgresql Setup Overview & macOS Setup
21 Postgresql Windows Setup
22 Starting & Stopping Servers + Uninstalling Database Systems
23 Connecting To Database Servers (MySQL & Postgresql) With Various Clients
24 Setting Up Visual Studio Code With The SQLTools Extension (Course Environment)
25 Configuring VS Code & Exploring SQLTools
26 More On VS Code & SQLTools

Data Definition Managing Tables & Working With Data Types
27 Module Introduction
28 Key Terms When Working With Data Definition Statements & Commands
29 Introducing The Course Section Example
30 Introducing Key Data Definition Clauses
31 Creating a new Database (CREATE DATABASE)
32 Deleting Databases & IF NOT EXISTS
33 The Importance Of Data Types (Value Types)
34 Introducing Key Text Value Types
35 Introducing Numeric Values, Date Types & More
36 An Overview Of Numeric Value Types
37 How Do You Store Files
38 Getting Started With The CREATE TABLE Statement & Syntax
39 Creating a First Text Column
40 Creating a Numeric Value Column
41 Working with Enums & Finishing Table Creation
42 Inserting Data Into The Created Table (INSERT INTO)
43 Inserting & Querying More Data
44 Working With Fixed Point & Floating Point Numbers
45 Adding Boolean Value Types
46 Creating a new Table with Text & Timestamp Data
47 Inserting Data Into The New Tables
48 What About Time Zones
49 Introducing Default Column Values
50 Deleting (Dropping) Tables & Inserting Data With Default Values
51 Updating Tables & Columns
52 Dealing With No Data (NULL) vs 0
53 NULL Values, Inserting (No) Data & Default Values
54 Exploring the NOT NULL Constraint
55 Exploring the CHECK Constraint
56 We Need Unique Values & Identifiers
57 SERIAL in MySQL vs SERIAL in Postgresql
58 Working With Auto Incrementing IDs & Primary Keys
59 Auto-IDs, Primary Keys & Inserting Data
60 Constraints – Summary
61 Understanding Text Encoding & Collation
62 Temporary Tables & Tables From Other Tables
63 Creating Generated Columns
64 Module Summary
65 Time To Practice Problem
66 Time To Practice Solution (1 2)
67 Time To Practice Solution (2 2)

Data Manipulation & Querying CRUD Operations With SQL
68 Module Introduction
69 What Are CRUD Operations
70 Inserting Data Theory
71 Selecting Data Theory
72 Updating Data Theory
73 Deleting Data Theory
74 Introducing The Section Example
75 Setting Up An Example Database & Table
76 Example Inserting Data
77 Inserting More (Dummy) Data
78 Updating In Action
79 Deleting In Action
80 Selecting Data – The Basics
81 SELECT, Column Names & Data Expressions
82 Filtering with WHERE Available Comparison Operators & Variations
83 Filtering In Action (WHERE In Action)
84 Combining Comparisons With AND & OR
85 Greater, Smaller & Ranges
86 Filtering Text Values
87 Working With Dates & Date Differences
88 Filtering Conditions Without Hard-Coded Values
89 Introducing ORDER BY & LIMIT
90 Ordering & Limiting Results In Action
91 Looking For DISTINCT Values
92 Subqueries & Views
93 Module Summary

Data Normalization & Relationships Splitting & JOINing Data
94 Module Introduction
95 A First Look At Related Data
96 Data Normalization – First Steps
97 Splitting Data Into Tables
98 Forms Of Data Normalization
99 Our First Section Example
100 Creating Tables With Relations
101 Inserting Related Data
102 Joining Data & Introducing INNER JOIN
103 Using INNER JOIN
104 Combining Multiple JOINs
105 Data Joining & Filtering
106 Introducing LEFT JOIN
107 Using LEFT JOIN
108 Combining Multiple LEFT JOINs
109 What About RIGHT JOIN
110 Example Time & INNER JOIN vs LEFT JOIN
111 Introducing CROSS JOIN
112 UNION & Why It’s Different
113 What’s Wrong With Our Foreign Keys
114 Introducing Foreign Key Constraints
115 Diving Deeper Into Foreign Key Constraints
116 Updating & Removing Foreign Key Constraints
117 Foreign Key Constraints In Action
118 Data Relationship Types One-to-Many, Many-to-Many, One-to-ONe
119 A Bigger Example
120 Example Adding First Tables & Relations
121 Example Adding More Tables & Data
122 Many-to-Many Relationships & Intermediate Tables ( Linking Tables )
123 Querying The Example Data
124 Practicing JOINs With Filtering
125 Experimenting With Referential Integrity

More On Primary & Foreign Keys
126 Module Introduction
127 Remember Primary Keys Don’t Have To Be Auto-Incrementing IDs
128 Primary Keys When To Use Which Column
129 Introducing Composite Primary Keys
130 Defining Composite Primary Keys (Composite Keys In Action)
131 Composite Foreign Keys
132 Composite Keys In Action
133 Self-Referencing Relationships (Self-Referential Relationships)
134 Self-Referencing Many To Many Relationships

Grouping & Aggregate Functions
135 Module Introduction
136 The Module Project
137 What are Aggregate Functions – Theory
138 Understanding COUNT()
139 Working with MIN() & MAX() and Adding Aliases to Aggregate Functions
140 Using SUM(), AVG() & ROUND()
141 Working with Filters & Joining Tables
142 Theory Time – Understanding GROUP BY
143 Applying GROUP BY in Practice
144 GROUP BY & Joined Queries
145 Understanding WHERE vs HAVING
146 Applying HAVING in Practice
147 Working with Nested Subqueries
148 Introducing Window Functions
149 Understanding ORDER BY and RANK()

Built-in Query Functions & Operators
150 Module Introduction
151 The Module Project
152 Working with Mathematical Functions & Arithmetic Operators
153 Understanding String Functions with SELECT
154 Using String Functions with INSERT
155 Understanding Date Time Functions
156 Working with Weekdays
157 Calculating INTERVALS
158 Adding INTERVALS to Dates
159 Understanding LIKE & Pattern Matching
160 Understanding EXISTS
161 Working with Subquery Expressions and EXISTS
162 Working with Subquery Expressions and IN
163 Introducing Conditional Expressions
164 Challenge Solution
165 Database (SQL) vs Application Level

Database Transactions
166 Module Introduction
167 Understanding Transactions
168 A Note About This Module’s Project
169 Applying Transactions & ROLLBACK
170 Committing Changes
171 Working with Savepoints
172 Transactions in PostgreSQL

Using Indexes
173 Module Introduction
174 What Are Indexes And Why Would You Need Them
175 Don’t Use Too Many Indexes
176 Index Types An Overview
177 Setting Up Some Example Data
178 EXPLAINing Queries & Query Planning
179 Creating & Using Single-Column Indexes
180 More on Creating & Dropping Indexes
181 Unique Indexes
182 Working With Multi-Column Indexes (Composite Indexes)
183 Partial Indexes

Summary & Practice A Complete Example
184 Module Introduction
185 What Is SQL
186 A Closer Look At Databases In General
187 The Core SQL Syntax
188 Course Setup (For Following Along)
189 Analyzing The Section Example Project
190 Introducing Data Normalization
191 Planning Tables & Relationships
192 Creating A New Database
193 Choosing Identifier Names
194 Defining Columns
195 Exploring Important Data Types
196 How To Store Files & Adding More Data Types
197 Constraints An Introduction
198 Introducing Database Functions
199 Understanding Primary Keys & Unique IDs
200 INSERTing Data
201 Basic Data Querying (via SELECT)
202 UPDATE & DELETE In Action
203 Filtering (WHERE) & Sorting (ORDER BY)
204 Adding More Tables
205 Relations & Foreign Keys
206 Understanding ON DELETE & ON UPDATE
207 Adding More Tables & Relationships
208 Many To Many Relationships & Linking (Intermediate) Tables
209 Inserting Related Data
210 Connecting Data With INNER JOIN
211 Combining Multiple Joins
212 Introducing LEFT JOIN
213 Joining Data & Filtering
214 Filtering Text With LIKE
215 Introducing Aggregate Functions
216 Grouping Aggregate Results (GROUP BY)
217 The HAVING Clause (vs WHERE)
218 Module Summary

Course Roundup
219 Congratulations + Bonus Content