The Comprehensive SQL Course (MySQL)

Section Overview

The SELECT Statement

USE SCHEMA

Statements vs Queries

SELECT STAR (*)

Aliasing Column Names

Aliasing Table Names (and Qualifying Columns)

Adding Comments to SQL Code

Selecting Distinct Rows

Ordering Rows

Limiting Rows

Order of Execution (So Far)

Syntax Differences Across Databases

Selecting Columns

Aliasing Columns

Top 5 Salaries

Section Overview

Numeric Data Types

String Data Types

Date and Time Data Types

The Describe Statement

Data Type Differences Across Databases

Section Overview

Creating and Dropping Tables

Creating and Dropping Schemas

Inserting Rows

Inserting Multiple Rows

Create Table As (CTAS)

Truncating Tables

Alter Table

Updating and Deleting Rows

Constraints in SQL

Not Null Constraint

Unique Constraint

Default Constraint

Check Constraint

Dropping the Not Null and Default Constraints

Dropping the Unique and Check Constraints

Section Overview

Normalization

Types of Relationships

Primary and Foreign Keys

Primary and Foreign Keys (Demo)

Entity Relationship Diagrams (ERD)

Tidying Up Our Schemas

Syntax Differences Across Databases

HR Schema

Customer Orders Schema

Assignment 1

Assignment 2

Assignment 3

Section Overview

Introduction to the Where Clause

Comparison Operators and the Where Clause

Null Values

Arithmetic Operators, Expressions and Order of SQL Execution

Logical Operators

Pattern Matching with the LIKE Operator

Recapping DDL and DML Statements

Syntax Differences Across Databases

Assignment 1

Assignment 2

Assignment 3

Internal and Metadata Schemas in MySQL

Information Schema Demo

Section Overview

String Functions - Concat

String Functions - Upper and Lower

SQL Literals

String Functions - Length

String Functions - Right and Left

String Functions - Substring and Mid

String Functions - Locate

Numerical Functions - Round and Truncate

Numerical Functions - Abs

Date and Time Functions - Current Time, Date and Timestamp

Date and Time Functions - Date Format

Date and Time Functions - String to Date

Date and Time Functions - Add Date and Date Diff

Conditional Functions - Case Expression

Coalesce

Using Expressions in Clauses

Differences Across Databases

Assignment 1

Assignment 2

Assignment 3

Assignment 4

Section Overview

Aggregate Functions

Overview of the Group By Clause

Group By Examples

Group By Index Position

Group By Expression

Filtering Aggregated Data with the Having Clause

Assignment 1

Assignment 2

Assignment 3

Assignment 4

Assignment 5

Section Overview

Joining Tables Overview

Joining Tables Demo

Join Syntax with other Clauses

Joining Tables Demo 2

Union and Union All

Intersect and Except

Cross Join

Emulating Full Outer Joins in MySQL

Differences Across Databases

Order of Execution

Aliasing Columns and SQL Execution Order

Section Overview

Subqueries

Subqueries Demo

Self Join

Views Overview

Views Demo

Assignment 1

Assignment 2

Assignment 3

Assignment 4

Assignment 5

Section Overview

Creating a New User Account

Grant and Revoke Demo

Differences Across Databases

Section Overview

Commit

Rollback

Savepoint

Differences Across Databases

Bonus Lecture: Congratulations & Next Steps