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

Module Introduction

Understanding The Core SQL Syntax

SQL in Action

The Core SQL Rules

Data Definition vs Data Manipulation

Module Introduction

Installing Different Database Management Systems

What We Need To Install

You Can Skip The Next Lectures!

MySQL Setup Overview & macOS Setup

MySQL Windows Setup

Postgresql Setup Overview & macOS Setup

Postgresql Windows Setup

Starting & Stopping Servers + Uninstalling Database Systems

Connecting To Database Servers (MySQL & Postgresql) With Various Clients

Setting Up Visual Studio Code With The SQLTools Extension (Course Environment)

Configuring VS Code & Exploring SQLTools

More On VS Code & SQLTools

Module Introduction

Key Terms When Working With Data Definition Statements & Commands

Introducing The Course Section Example

Introducing Key Data Definition Clauses

Creating a new Database (CREATE DATABASE)

Deleting Databases & IF NOT EXISTS

The Importance Of Data Types (Value Types)

Introducing Key Text Value Types

Introducing Numeric Values, Date Types & More

An Overview Of Numeric Value Types

How Do You Store Files?

Getting Started With The CREATE TABLE Statement & Syntax

Creating a First Text Column

Creating a Numeric Value Column

Working with Enums & Finishing Table Creation

Inserting Data Into The Created Table (INSERT INTO)

Inserting & Querying More Data

Working With Fixed Point & Floating Point Numbers

Adding Boolean Value Types

Creating a new Table with Text & Timestamp Data

Inserting Data Into The New Tables

What About Time Zones?

Introducing Default Column Values

Deleting (Dropping) Tables & Inserting Data With Default Values

Updating Tables & Columns

Dealing With "No Data" (NULL) vs "0"

NULL Values, Inserting (No) Data & Default Values

Exploring the NOT NULL Constraint

Exploring the CHECK Constraint

We Need Unique Values & Identifiers!

SERIAL in MySQL vs SERIAL in Postgresql

Working With Auto Incrementing IDs & Primary Keys

Auto-IDs, Primary Keys & Inserting Data

Constraints - Summary

Understanding Text Encoding & Collation

Temporary Tables & Tables From Other Tables

Creating Generated Columns

Module Summary

Time To Practice: Problem

Time To Practice: Solution (1/2)

Time To Practice: Solution (2/2)

Module Introduction

What Are CRUD Operations?

Inserting Data: Theory

Selecting Data: Theory

Updating Data: Theory

Deleting Data: Theory

Introducing The Section Example

Setting Up An Example Database & Table

Example: Inserting Data

Inserting More (Dummy) Data

Updating In Action

Deleting In Action

Selecting Data - The Basics

SELECT, Column Names & Data Expressions

Filtering with WHERE: Available Comparison Operators & Variations

Filtering In Action (WHERE In Action)

Combining Comparisons With AND & OR

Greater, Smaller & Ranges

Filtering Text Values

Working With Dates & Date Differences

Filtering Conditions Without Hard-Coded Values

Introducing ORDER BY & LIMIT

Ordering & Limiting Results In Action

Looking For DISTINCT Values

Subqueries & Views

Module Summary

Module Introduction

A First Look At Related Data

Data Normalization - First Steps

Splitting Data Into Tables

Forms Of Data Normalization

Our First Section Example

Creating Tables With Relations

Inserting Related Data

Joining Data & Introducing INNER JOIN

Using INNER JOIN

Combining Multiple JOINs

Data Joining & Filtering

Introducing LEFT JOIN

Using LEFT JOIN

Combining Multiple LEFT JOINs

What About RIGHT JOIN?

Example Time & INNER JOIN vs LEFT JOIN

Introducing CROSS JOIN

UNION & Why It's Different

What's Wrong With Our Foreign Keys?

Introducing Foreign Key Constraints

Diving Deeper Into Foreign Key Constraints

Updating & Removing Foreign Key Constraints

Foreign Key Constraints In Action

REFERENCES & MySQL

Data Relationship Types: One-to-Many, Many-to-Many, One-to-ONe

A Bigger Example

Example: Adding First Tables & Relations

Example: Adding More Tables & Data

Many-to-Many Relationships & Intermediate Tables ("Linking Tables")

Querying The Example Data

Practicing JOINs With Filtering

Experimenting With Referential Integrity

Module Introduction

Remember: Primary Keys Don't Have To Be Auto-Incrementing IDs!

Primary Keys: When To Use Which Column

Introducing Composite Primary Keys

Defining Composite Primary Keys (Composite Keys In Action)

Composite Foreign Keys

Composite Keys In Action

Self-Referencing Relationships (Self-Referential Relationships)

Self-Referencing Many To Many Relationships

Module Introduction

The Module Project

What are Aggregate Functions - Theory

Understanding COUNT()

Working with MIN() & MAX() and Adding Aliases to Aggregate Functions

Using SUM(), AVG() & ROUND()

Working with Filters & Joining Tables

Theory Time - Understanding GROUP BY

Applying GROUP BY in Practice

GROUP BY & Joined Queries

Understanding WHERE vs HAVING

Applying HAVING in Practice

Working with Nested Subqueries

Introducing Window Functions

Understanding ORDER BY and RANK()

Module Introduction

The Module Project

Working with Mathematical Functions & Arithmetic Operators

Understanding String Functions with SELECT

Using String Functions with INSERT

Understanding Date / Time Functions

Working with Weekdays

Calculating INTERVALS

Adding INTERVALS to Dates

Understanding LIKE & Pattern Matching

Understanding EXISTS

Working with Subquery Expressions and EXISTS

Working with Subquery Expressions and IN

Introducing Conditional Expressions

Challenge Solution

Database (SQL) vs Application Level

Module Introduction

Understanding Transactions

A Note About This Module's Project

Applying Transactions & ROLLBACK

Committing Changes

Working with Savepoints

Transactions in PostgreSQL

Module Introduction

What Are Indexes? And Why Would You Need Them?

Don't Use Too Many Indexes!

Index Types: An Overview

Setting Up Some Example Data

EXPLAINing Queries & Query Planning

Creating & Using Single-Column Indexes

More on Creating & Dropping Indexes

Unique Indexes

Working With Multi-Column Indexes (Composite Indexes)

Partial Indexes

Module Introduction

What Is SQL?

A Closer Look At Databases In General

The Core SQL Syntax

Course Setup (For Following Along)

Analyzing The Section Example Project

Introducing "Data Normalization"

Planning Tables & Relationships

Creating A New Database

Choosing Identifier Names

Defining Columns

Exploring Important Data Types

How To Store Files & Adding More Data Types

Constraints: An Introduction

Introducing Database Functions

Understanding Primary Keys & Unique IDs

INSERTing Data

Basic Data Querying (via SELECT)

UPDATE & DELETE In Action

Filtering (WHERE) & Sorting (ORDER BY)

Adding More Tables

Relations & Foreign Keys

Understanding ON DELETE & ON UPDATE

Adding More Tables & Relationships

Many To Many Relationships & Linking (Intermediate) Tables

Inserting Related Data

Connecting Data With INNER JOIN

Combining Multiple Joins

Introducing LEFT JOIN

Joining Data & Filtering

Filtering Text With LIKE

Introducing Aggregate Functions

Grouping Aggregate Results (GROUP BY)

The HAVING Clause (vs WHERE)

Module Summary

Course Roundup