SQL - MySQL for Data Analytics and Business Intelligence

Introduction to databases, SQL, and MySQL

What does the course cover?

Why SQL?

Why SQL?

Why MySQL?

Why MySQL?

Introduction to databases

Introduction to databases

SQL theory

SQL as a declarative language

SQL as a declarative language

Data definition language (DDL)

Data definition language (DDL)

SQL keywords

SQL keywords

Data manipulation language (DML)

Data manipulation language (DML)

Data control language (DCL)

Data control language (DCL)

Transaction control language (TCL)

Transaction control language (TCL)

Basic database terminology

Relational database essentials

Relational database essentials

Databases vs spreadsheets

Databases vs spreadsheets

Database terminology

Database terminology

Relational schemas - Primary key

Relational schemas - Primary key

Relational schemas - Foreign key

Relational schemas - Foreign key

Relational schemas - Unique key and null values

Relational Schemas - Unique key

Relationships

Relational Schemas – Relationships

Installing MySQL and getting acquainted with the interface

Installing MySQL

Additional note – Installing – Visual C

Installing MySQL on macOS and Unix systems

The Client-Server Model

Setting up a connection

Read me!!!

New Authentication Plugin - Creating a New User

Introduction to the MySQL interface

First steps in SQL

Creating a Database - Part I

SQL files

Creating a Database - Part I - exercise

Creating a Database - Part I - solution

Creating a Database - Part II

Creating a Database - Part II - exercise

Creating a Database - Part II - solution

Introduction to data types

Introduction to data types

String data types

String data types

Integers

Integers

Fixed and floating-point data types

Fixed and floating-point data types

Other useful data types

Other useful data types

Creating a table

Creating a table - exercise

Creating a table - solution

Using databases and tables

Using databases and tables - exercise

Using databases and tables - solution

Additional notes on using tables

Additional notes on using tables

Additional notes on using tables - exercise

MySQL constraints

PRIMARY KEY constraint

PRIMARY KEY Constraint

PRIMARY KEY constraint - exercise

PRIMARY KEY constraint - solution

FOREIGN KEY constraint - Part I

FOREIGN KEY constraint - Part II

FOREIGN KEY constraint - Part II - exercise

FOREIGN KEY constraint - Part II - solution

UNIQUE Constraint

UNIQUE Constraint - exercise

DEFAULT Constraint

DEFAULT Constraint - exercise

DEFAULT Constraint - solution

NOT NULL Constraint - Part I

NOT NULL Constraint - Part I - exercise

NOT NULL Constraint - Part I - solution

NOT NULL Constraint - Part II

SQL best practices

Coding techniques and best practices - Part I

Coding techniques and best practices – Part I

Coding techniques and best practices - Part II

Coding techniques and best practices – Part II

Loading the 'employees' database

Loading the 'employees' database

Loading the 'employees' database

SQL SELECT statement

SELECT - FROM

SELECT - FROM - exercise

SELECT - FROM - solution

WHERE

WHERE - exercise

WHERE - solution

AND

AND - exercise

AND - solution

OR

OR - exercise

OR - solution

Operator precedence

Operator precedence - exercise

Operator precedence - solution

IN - NOT IN

IN - NOT IN - exercise 1

IN - NOT IN - solution 1

IN - NOT IN - exercise 2

IN - NOT IN - solution 2

LIKE - NOT LIKE

LIKE - NOT LIKE - exercise

LIKE - NOT LIKE - solution

Wildcard characters

Wildcard characters - exercise

Wildcard characters - solution

BETWEEN - AND

BETWEEN - AND - exercise

BETWEEN - AND- solution

IS NOT NULL - IS NULL

IS NOT NULL - IS NULL - exercise

IS NOT NULL - IS NULL - solution

Other comparison operators

Other comparison operators - exercise

Other comparison operators - solution

SELECT DISTINCT

SELECT DISTINCT - exercise

SELECT DISTINCT - solution

Introduction to aggregate functions

Introduction to aggregate functions - exercise

Introduction to aggregate functions - solution

ORDER BY

ORDER BY - exercise

ORDER BY - solution

GROUP BY

Using Aliases (AS)

Using Aliases (AS) - exercise

Using Aliases (AS) - solution

HAVING

HAVING - exercise

HAVING - solution

WHERE vs HAVING- Part I

WHERE vs HAVING- Part II

WHERE vs HAVING - Part II - exercise

WHERE vs HAVING - Part II - solution

LIMIT

LIMIT - exercise

LIMIT - solution

SQL INSERT statement

The INSERT statement - Part I

The INSERT statement - Part II

The INSERT statement - exercise 1

The INSERT statement - solution 1

The INSERT statement - exercise 2

The INSERT statement - solution 2

Inserting data INTO a new table

Inserting Data INTO a New Table - exercise

Inserting Data INTO a New Table - solution

SQL UPDATE Statement

TCL's COMMIT and ROLLBACK

The UPDATE Statement - Part I

ROLLBACK; ARTICLE

The UPDATE Statement - Part II

The UPDATE Statement – Part II - exercise

The UPDATE Statement – Part II - solution

SQL DELETE Statement

The DELETE Statement - Part I

The DELETE Statement - Part II

The DELETE Statement – Part II - exercise

The DELETE Statement – Part II - solution

DROP vs TRUNCATE vs DELETE

DROP vs TRUNCATE vs DELETE

MySQL - Aggregate functions

COUNT()

COUNT() - exercise

COUNT() - solution

SUM()

SUM() - exercise

SUM() - solution

MIN() and MAX()

MIN() and MAX() - exercise

MIN() and MAX() - solution

AVG()

AVG() - exercise

AVG() - solution

ROUND()

ROUND() - exercise

ROUND() - solution

COALESCE() - Preamble

IFNULL() and COALESCE()

Another Example of Using COALESCE()

Another example of using COALESCE() - exercise 1

Another example of using COALESCE() - solution 1

Another example of using COALESCE() - exercise 2

Another example of using COALESCE() - solution 2

Another example of using COALESCE()

SQL Joins

Introduction to JOINs

Intro to JOINs - exercise 1

Intro to JOINs - solution 1

Intro to JOINs - exercise 2

INNER JOIN - Part I

INNER JOIN - Part II

INNER JOIN - Part II - exercise

INNER JOIN - Part II - solution

A Note on Using Joins

Duplicate Records

LEFT JOIN - Part I

LEFT JOIN - Part II

LEFT JOIN - Part II - exercise

LEFT JOIN - Part II - solution

RIGHT JOIN

The new and the old join syntax

The new and the old join syntax - exercise

The new and the old join syntax - solution

JOIN and WHERE Used Together

Important – Prevent Error Code: 1055!

JOIN and WHERE Used Together - exercise

JOIN and WHERE Used Together - solution

CROSS JOIN

CROSS JOIN - exercise 1

CROSS JOIN - solution 1

CROSS JOIN - exercise 2

CROSS JOIN - solution 2

Using Aggregate Functions with Joins

JOIN more than two tables in SQL

Join more than two tables in SQL - exercise

Join more than two tables in SQL - solution

Tips and tricks for joins

Tips and tricks for joins - exercise

Tips and tricks for joins - solution

UNION vs UNION ALL

UNION vs UNION ALL - exercise

UNION vs UNION ALL - solution

SQL Subqueries

SQL Subqueries with IN nested inside WHERE

SQL Subqueries with IN nested inside WHERE - exercise

SQL Subqueries with IN nested inside WHERE - solution

SQL Subqueries with EXISTS-NOT EXISTS nested inside WHERE

SQL Subqueries with EXISTS-NOT EXISTS nested inside WHERE - exercise

SQL Subqueries with EXISTS-NOT EXISTS nested inside WHERE - solution

SQL Subqueries nested in SELECT and FROM

SQL Subqueries nested in SELECT and FROM - exercise 1

SQL Subqueries nested in SELECT and FROM - solution 1

SQL Subqueries nested in SELECT and FROM - exercise 2

SQL Subqueries nested in SELECT and FROM - solution 2

SQL Self Join

Self Join

Self join

SQL Views

Views

Views - exercise

Views - solution

Stored routines

Introduction to stored routines

Introduction to Stored Routines

The MySQL syntax for stored procedures

Stored procedures - Example - Part I

Stored procedures - Example - Part II

Stored procedures - Example - Part II - exercise

Stored procedures - Example - Part II - solution

Another way to create a procedure

Stored procedures with an input parameter

Stored procedures with an output parameter

Stored procedures with an output parameter - exercise

Stored procedures with an output parameter - solution

Variables

Variables - exercise

Variables - solution

User-defined functions in MySQL

Error Code: 1418.

User-defined functions in MySQL - exercise

User-defined functions in MySQL - solution

Stored routines - conclusion

Stored routines - conclusion

Advanced SQL Topics

Types of MySQL Variables - Local Variables

Types of MySQL Variables - Local Variables

Session Variables

Session Variables

Global Variables

Global Variables

User-Defined vs System Variables

User-Defined vs System Variables

MySQL Triggers

MySQL Triggers - exercise

MySQL Triggers - solution

MySQL Indexes

MySQL Indexes - exercise 1

MySQL Indexes - solution 1

MySQL Indexes - exercise 2

MySQL Indexes - solution 2

The CASE Statement

The CASE Statement - exercise 1

The CASE Statement - solution 1

The CASE Statement - exercise 2

The CASE Statement - solution 2

The CASE Statement - exercise 3

The CASE Statement - solution 3

Combining SQL and Tableau - Introduction

The Advantages of Software Integration

Why Combining SQL and Tableau is useful

Installing Tableau Public

Loading the 'employees_mod' Database

Loading the 'employees_mod' Database

Combining SQL and Tableau - Task 1

Task 1

Task 1 - Text

Important clarification!

Task 1: SQL Solution

Task 1: SQL Solution - Code

Transferring a Dataset from SQL to Tableau

Chart 1: Visualizing and Analyzing the Solution in Tableau - Part I

Chart 1: Visualizing and Analyzing the Solution in Tableau - Part II

Combining SQL and Tableau - Task 2

Task 2

Task 2 - Text

Task 2: SQL Solution

Task 2: SQL Solution - Code

Chart 2: Visualizing and Analyzing the Solution in Tableau

Combining SQL and Tableau - Task 3

Task 3

Task 3 - Text

Task 3: SQL Solution

Task 3: SQL Solution - Code

Chart 3: Visualizing and Analyzing the Solution in Tableau

Combining SQL and Tableau - Task 4

Task 4

Task 4 - Text

Task 4: Solution in SQL

Task 4: Solution in SQL - Code

Chart 4: Visualizing and Analyzing the Solution in Tableau

Combining SQL and Tableau - Task 5

Task 5: Organizing Charts 1-4 into a Beautiful Dashboard using Tableau

Practice SQL – 10 Final Query Questions

Practice SQL – 10 Final Query Questions

BONUS LECTURE

Bonus Lecture: Next Steps