Section Introduction

What Is A Database?

SQL vs. MySQL

Installation: START HERE!

[FOLLOW THE PREVIOUS LECTURE NOTE INSTEAD] Installation: Windows Instructions

[FOLLOW THE PREVIOUS LECTURE NOTE INSTEAD] Installation: Mac Instructions

Section Introduction

Showing Databases

Creating Databases

Dropping and Using Databases

Introducing Tables

Data Types: The Basics

Basic Datatypes Challenge

Creating Tables

How Do We Know It Worked?

Dropping Tables

Tables Basics Activity

MySQL Comments

Section Introduction

INSERT: The Basics

A Quick Preview of SELECT

Multi-inserts

INSERT Exercise

Working With NOT NULL

Sidenote: Quotes In MySQL

Adding DEFAULT Values

Introducing Primary Keys

Working With AUTO_INCREMENT

Create Table/ Insert Exercise

Section Introduction

Introducing CRUD

Getting Our New "Dataset"

Officially Introducing SELECT

The WHERE clause

Rapid Fire Exercises

Rapid Fire Exercises SOLUTION

Aliases

Using UPDATE

A Quick Rule Of Thumb

UPDATE Exercise

UPDATE Exercise SOLUTION

Introducing DELETE

DELETE Exercise

DELETE Exercise SOLUTION

Section Introduction

Introducing the CRUD Challenge

CRUD Challenge Solution: Creating

CRUD Challenge Solution: Reading

CRUD Challenge Solution: Updating

CRUD Challenge Solution: Deleting

Section Introduction

The World Of String Functions

Loading Our Books Data

CONCAT

SUBSTRING

Combining String Functions

Sidenote: SQL Formatting

REPLACE

REVERSE

CHAR_LENGTH

UPPER & LOWER

Other String Functions

String Functions Exercise

String Functions Exerise SOLUTION

Section Introduction

Adding Some New Books

DISTINCT

ORDER BY

More On ORDER BY

LIMIT

LIKE

Escaping Wildcards

Refining Selections Exercise

Refining Selections Exercise SOLUTION

Section Introduction

Count Basics

GROUP BY

MIN and MAX Basics

Subqueries

Grouping By Multiple Columns

MIN and MAX With GROUP BY

SUM

AVG

Aggregate Functions Docs

Agg Functions Exercise

Agg Functions Exercise SOLUTION

Section Introduction

Surveying Other Data Types

CHAR vs. VARCHAR

INT, TINYINT, BIGINT, etc.

DECIMAL

FLOAT & DOUBLE

DATE and TIME

Working With Dates

CURDATE, CURTIME, & NOW

Date Functions

Time Functions

Formatting Dates

Date Math

TIMESTAMPS

DEFAULT & ON UPDATE TIMESTAMPS

Data Types Exercise

Data Types Exercise SOLUTION

Section Introduction

Not Equal

NOT LIKE

Greater Than

Less Than Or Equal To

Logical AND

Logical OR

Between

Comparing Dates

The IN Operator

CASE

IS NULL

Exercise

Exercise SOLUTION

UNIQUE Constraint

CHECK Constraints

Named Constraints

Multiple Column Constraints

ALTER TABLE: Adding Columns

ALTER TABLE: Dropping Columns

ALTER TABLE: Renaming

ALTER TABLE: Modifying Columns

ALTER TABLE: Constraints

Section Introduction

Data is Messy

Relationships Basics

One to Many Relationship

Working with FOREIGN KEY

Cross Joins

Inner Joins

Inner Joins With Group By

Left Join

Left Join With Group By

Right Join

On Delete Cascade

Joins Exercise

Joins Exercise SOLUTION

Section Introduction

Many to Many Basics

Creating Our Many To Many Tables

TV Series Challenge #1

TV Series Challenge #2

TV Series Challenge #3

TV Series Challenge #4

TV Series Challenge #5

TV Series Challenge #6

TV Series Challenge #7

Introducing Views

Updateable Views

Replacing/Altering Views

HAVING clause

WITH ROLLUP

SQL Modes Basics

STRICT_TRANS_TABLES

More Modes

Introducing Window Functions

Using OVER()

PARTITION BY

ORDER BY with Windows

RANK()

DENSE_RANK & ROW_NUMBER()

NTILE()

FIRST_VALUE

LEAD and LAG

Section Introduction

IMPORTANT NOTE ABOUT THIS SECTION!

Introducing Instagram Clone

Users Schema

Photos Schema

Comments Schema

Likes Schema

Followers Schema

3 Approaches for Hashtags

Implementing Hashtags Table

Section Introduction

Getting Lots of Instagram Data

Instagram Challenge #1

Instagram Challenge #2

Instagram Challenge #3

Instagram Challenge #4

Instagram Challenge #5

Instagram Challenge #6

Instagram Challenge #7

Introduction to Database Triggers

Writing Our First Trigger Part 1

Writing Our First Trigger Part 2

Preventing Instagram Self-Follows With Triggers

Creating Logger Triggers

Managing Triggers And A Warning