A Closer Look At SQL & Databases | Academind

Autoplay
Autocomplete

Previous Lesson Complete and Continue

SQL - The Complete Developer's Guide (MySQL, PostgreSQL)

Getting Started

Welcome To The Course! (2:24)
What Is SQL? (6:04)
A Closer Look At SQL & Databases (6:10)
Course Content (3:35)
There Are Two Ways Of Taking This Course! (2:15)
How To Get The Most Out Of This Course (3:05)
Course Code & Resources
The Academind Pro Referral Program

SQL Basics: Syntax & Core Features

Module Introduction (0:48)
Understanding The Core SQL Syntax (6:36)
SQL in Action (3:33)
The Core SQL Rules (3:49)
Data Definition vs Data Manipulation (3:32)

Course Setup: Installing MySQL & Postgresql

Module Introduction (2:42)
Installing Different Database Management Systems (2:58)
What We Need To Install (7:12)
You Can Skip The Next Lectures!
MySQL Setup Overview & macOS Setup (10:34)
MySQL Windows Setup (8:09)
Postgresql Setup Overview & macOS Setup (6:14)
Postgresql Windows Setup (4:31)
Starting & Stopping Servers + Uninstalling Database Systems
Connecting To Database Servers (MySQL & Postgresql) With Various Clients (15:29)
Setting Up Visual Studio Code With The SQLTools Extension (Course Environment) (10:43)
Configuring VS Code & Exploring SQLTools (11:58)
More On VS Code & SQLTools

Data Definition: Managing Tables & Working With Data Types

Module Introduction (2:01)
Key Terms When Working With Data Definition Statements & Commands (2:42)
Introducing The Course Section Example (2:23)
Introducing Key Data Definition Clauses (3:11)
Creating a new Database (CREATE DATABASE) (9:56)
Deleting Databases & IF NOT EXISTS
The Importance Of Data Types (Value Types) (5:21)
Introducing Key Text Value Types (6:37)
Introducing Numeric Values, Date Types & More (5:50)
An Overview Of Numeric Value Types
How Do You Store Files? (3:36)
Getting Started With The CREATE TABLE Statement & Syntax (5:16)
Creating a First Text Column (3:49)
Creating a Numeric Value Column (9:03)
Working with Enums & Finishing Table Creation (7:06)
Inserting Data Into The Created Table (INSERT INTO) (6:19)
Inserting & Querying More Data (4:02)
Working With Fixed Point & Floating Point Numbers (8:53)
Adding Boolean Value Types (1:58)
Creating a new Table with Text & Timestamp Data (5:10)
Inserting Data Into The New Tables (7:32)
What About Time Zones?
Introducing Default Column Values (3:13)
Deleting (Dropping) Tables & Inserting Data With Default Values (5:21)
Updating Tables & Columns (8:57)
Dealing With "No Data" (NULL) vs "0" (6:27)
NULL Values, Inserting (No) Data & Default Values
Exploring the NOT NULL Constraint (9:23)
Exploring the CHECK Constraint (12:34)
We Need Unique Values & Identifiers! (11:39)
SERIAL in MySQL vs SERIAL in Postgresql
Working With Auto Incrementing IDs & Primary Keys (10:20)
Auto-IDs, Primary Keys & Inserting Data
Constraints - Summary (2:45)
Understanding Text Encoding & Collation (7:35)
Temporary Tables & Tables From Other Tables (2:40)
Creating Generated Columns (7:48)
Module Summary (3:02)
Time To Practice: Problem (3:59)
Time To Practice: Solution (1/2) (13:48)
Time To Practice: Solution (2/2) (8:55)

Data Manipulation & Querying: CRUD Operations With SQL

Module Introduction (1:21)
What Are CRUD Operations? (2:33)
Inserting Data: Theory (2:34)
Selecting Data: Theory (1:24)
Updating Data: Theory (1:37)
Deleting Data: Theory (1:22)
Introducing The Section Example (3:33)
Setting Up An Example Database & Table (7:46)
Example: Inserting Data (7:26)
Inserting More (Dummy) Data (1:11)
Updating In Action (5:22)
Deleting In Action (2:08)
Selecting Data - The Basics (4:42)
SELECT, Column Names & Data Expressions (5:02)
Filtering with WHERE: Available Comparison Operators & Variations (4:23)
Filtering In Action (WHERE In Action) (3:39)
Combining Comparisons With AND & OR (3:36)
Greater, Smaller & Ranges (7:15)
Filtering Text Values (4:29)
Working With Dates & Date Differences (5:16)
Filtering Conditions Without Hard-Coded Values
Introducing ORDER BY & LIMIT (2:09)
Ordering & Limiting Results In Action (7:23)
Looking For DISTINCT Values (1:41)
Subqueries & Views (7:05)
Module Summary (2:33)

Data Normalization & Relationships: Splitting & JOINing Data

Module Introduction (1:45)
A First Look At Related Data (4:09)
Data Normalization - First Steps (4:03)
Splitting Data Into Tables (9:48)
Forms Of Data Normalization (3:05)
Our First Section Example (4:09)
Creating Tables With Relations (10:23)
Inserting Related Data (8:11)
Joining Data & Introducing INNER JOIN (4:06)
Using INNER JOIN (8:10)
Combining Multiple JOINs (4:13)
Data Joining & Filtering (3:48)
Introducing LEFT JOIN (2:10)
Using LEFT JOIN (3:57)
Combining Multiple LEFT JOINs (2:40)
What About RIGHT JOIN? (1:23)
Example Time & INNER JOIN vs LEFT JOIN (12:10)
Introducing CROSS JOIN (1:43)
UNION & Why It's Different (8:51)
What's Wrong With Our Foreign Keys? (5:07)
Introducing Foreign Key Constraints (4:06)
Diving Deeper Into Foreign Key Constraints (5:35)
Updating & Removing Foreign Key Constraints
Foreign Key Constraints In Action (12:05)
Data Relationship Types: One-to-Many, Many-to-Many, One-to-ONe (4:34)
A Bigger Example (5:39)
Example: Adding First Tables & Relations (16:56)
Example: Adding More Tables & Data (11:10)
Many-to-Many Relationships & Intermediate Tables ("Linking Tables") (7:18)
Querying The Example Data (8:57)
Practicing JOINs With Filtering (9:13)
Experimenting With Referential Integrity (6:00)

More On Primary & Foreign Keys

Module Introduction (1:30)
Remember: Primary Keys Don't Have To Be Auto-Incrementing IDs! (2:51)
Primary Keys: When To Use Which Column
Introducing Composite Primary Keys (7:52)
Defining Composite Primary Keys (Composite Keys In Action) (2:36)
Composite Foreign Keys (4:47)
Composite Keys In Action (2:51)
Self-Referencing Relationships (Self-Referential Relationships) (9:24)
Self-Referencing Many To Many Relationships (7:58)

Grouping & Aggregate Functions

Module Introduction (1:27)
The Module Project (4:37)
What are Aggregate Functions - Theory (4:23)
Understanding COUNT() (10:55)
Working with MIN() & MAX() and Adding Aliases to Aggregate Functions (7:38)
Using SUM(), AVG() & ROUND() (6:16)
Working with Filters & Joining Tables (11:34)
Theory Time - Understanding GROUP BY (5:03)
Applying GROUP BY in Practice (4:40)
GROUP BY & Joined Queries (6:11)
Understanding WHERE vs HAVING (10:00)
Applying HAVING in Practice (7:29)
Working with Nested Subqueries (11:00)
Introducing Window Functions (8:18)
Understanding ORDER BY and RANK() (7:00)

Built-in Query Functions & Operators

Module Introduction (1:56)
The Module Project (3:11)
Working with Mathematical Functions & Arithmetic Operators (14:28)
Understanding String Functions with SELECT (6:56)
Using String Functions with INSERT (8:48)
Understanding Date / Time Functions (4:40)
Working with Weekdays (8:07)
Calculating INTERVALS (9:41)
Adding INTERVALS to Dates (7:54)
Understanding LIKE & Pattern Matching (9:51)
Understanding EXISTS (6:18)
Working with Subquery Expressions and EXISTS (3:56)
Working with Subquery Expressions and IN (8:09)
Introducing Conditional Expressions (7:15)
Challenge Solution (4:40)
Database (SQL) vs Application Level (3:20)

Database Transactions

Module Introduction (0:52)
Understanding Transactions (2:18)
Applying Transactions & ROLLBACK (9:19)
Committing Changes (8:14)
Working with Savepoints (4:36)
Transactions in PostgreSQL (4:52)

Using Indexes

Module Introduction (1:15)
What Are Indexes? And Why Would You Need Them? (6:39)
Don't Use Too Many Indexes! (4:32)
Index Types: An Overview (5:06)
Setting Up Some Example Data (3:15)
EXPLAINing Queries & Query Planning (5:16)
Creating & Using Single-Column Indexes (5:51)
Unique Indexes (2:05)
Working With Multi-Column Indexes (Composite Indexes) (9:20)
Partial Indexes (2:36)
More on Creating & Dropping Indexes

Summary & Practice: A Complete Example

Module Introduction (1:48)
What Is SQL? (6:04)
A Closer Look At Databases In General (6:10)
The Core SQL Syntax (6:49)
Course Setup (For Following Along) (5:05)
Analyzing The Section Example Project (6:06)
Introducing "Data Normalization" (3:35)
Planning Tables & Relationships (10:25)
Creating A New Database (4:12)
Choosing Identifier Names (3:10)
Defining Columns (3:43)
Exploring Important Data Types (5:32)
How To Store Files & Adding More Data Types (4:13)
Constraints: An Introduction (5:19)
Introducing Database Functions (2:36)
Understanding Primary Keys & Unique IDs (6:37)
INSERTing Data (8:12)
Basic Data Querying (via SELECT) (2:28)
UPDATE & DELETE In Action (5:57)
Filtering (WHERE) & Sorting (ORDER BY) (4:36)
Adding More Tables (8:23)
Relations & Foreign Keys (9:10)
Understanding ON DELETE & ON UPDATE (3:38)
Adding More Tables & Relationships (7:14)
Many To Many Relationships & Linking (Intermediate) Tables (8:47)
Inserting Related Data (13:26)
Connecting Data With INNER JOIN (11:17)
Combining Multiple Joins (5:20)
Introducing LEFT JOIN (10:32)
Joining Data & Filtering (4:01)
Filtering Text With LIKE (6:02)
Introducing Aggregate Functions (3:56)
Grouping Aggregate Results (GROUP BY) (5:42)
The HAVING Clause (vs WHERE) (3:31)
Module Summary (1:44)

Course Roundup

Congratulations + More Content (0:53)

A Closer Look At SQL & Databases

Complete and Continue