SQL 

Welcome to SQL

Why SQL?

Why MySQL?

Introduction to databases

SQL as a declarative language

Data definition language (DDL)

SQL keywords

Data manipulation language (DML)

Data control language (DCL)

Transaction control language (TCL)

Relational database essentials

Databases vs spreadsheets

Database terminology

Relational schemas - Primary key

Relational schemas - Foreign key

Relational schemas - Unique key and null values

Relationships

Installing MySQL

Additional note – Installing – Visual C

Installing MySQL on macOS and Unix systems

The Client-Server Model

Setting up a connection

Important - Please read

New Authentication Plugin - Creating a New User

Introduction to the MySQL interface

Creating a Database - Part I

SQL files

Creating a Database - Part II

Introduction to data types

String data types

Integers

Fixed and floating-point data types

Other useful data types

Creating a table

Using databases and tables

Additional notes on using tables

PRIMARY KEY constraint

FOREIGN KEY constraint - Part I

FOREIGN KEY constraint - Part II

UNIQUE Constraint

DEFAULT Constraint

NOT NULL Constraint - Part I

NOT NULL Constraint - Part II

Coding techniques and best practices - Part I

Coding techniques and best practices - Part II

Loading the 'employees' database

SELECT… FROM…

WHERE

AND

OR

Operator Precedence

IN - NOT IN

LIKE - NOT LIKE

Wildcard Characters

BETWEEN… AND…

IS NOT NULL - IS NULL

Other Comparison Operators

SELECT DISTINCT

Introduction to Aggregate Functions

ORDER BY

GROUP BY

Using Aliases (AS)

HAVING

WHERE vs HAVING - Part I

WHERE vs HAVING - Part II

LIMIT

The INSERT statement - Part I

The INSERT statement - Part II

Inserting Data INTO a New Table

TCL's COMMIT and ROLLBACK

The UPDATE Statement - Part I

The UPDATE Statement - Part II

The DELETE Statement - Part I

The DELETE Statement - Part II

DROP vs TRUNCATE vs DELETE

COUNT()

SUM()

MIN() and MAX()

AVG()

ROUND()

COALESCE() - Preamble

IFNULL() and COALESCE()

Another Example of Using COALESCE()

Introduction to JOINs

INNER JOIN - Part I

INNER JOIN - Part II

A Note on Using Joins

Duplicate Records

LEFT JOIN - Part I

LEFT JOIN - Part II

RIGHT JOIN

The new and the old join syntax

JOIN and WHERE Used Together

CROSS JOIN

Using Aggregate Functions with Joins

JOIN more than two tables in SQL

Tips and tricks for joins

UNION vs UNION ALL

SQL Subqueries with IN nested inside WHERE

SQL Subqueries with EXISTS-NOT EXISTS nested inside WHERE

SQL Subqueries nested in SELECT and FROM

SQL Self Join

Using SQL views

Introduction to Stored Routines

The MySQL syntax for stored procedures

Stored Procedures - Example - Part I

Stored Procedures - Example - Part II

Another Way to Create a Procedure in MySQL

Stored Procedures with an Input Parameter

Stored Procedures with an Output Parameter

Variables

User-defined functions in MySQL

Stored routines - conclusion

Types of MySQL Variables - Local Variables

Session Variables

Global Variables

User-Defined vs System Variables

MySQL Triggers - Preamble

MySQL Triggers

MySQL Indexes

The CASE Statement