MySQL Data Analysis

Course details

MySQL is a powerful tool for managing and analyzing relational databases, and a skill that every analyst and BI professional should have in the arsenal. In this course, you'll play the role of a business owner who just acquired Maven Movies, a brick-and-mortar DVD rental shop. Using only a MySQL database, your job is to learn everything you can about your new business, including inventory, staff, and customer behavior.

The course starts with a quick intro to the SQL language, addresses some key database fundamentals, and uses MySQL to select, filter, sort, and group data from individual tables. From there, shift into more complex use cases, and blend data across multiple tables with JOIN queries.

Learning objectives
Identify key components of the broader SQL landscape, including history, common tools, common "flavors," and benefits.
Describe the functionality of the "Big 6" SQL statements and clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY.
Interpret the results of SQL statements, including database queries and error messages.
Identify aggregate functions used to analyze groups of rows in SQL, including COUNT, SUM, MAX, AVG, and COUNT DISTINCT.
Learn MySQL pro tips and their common use cases, including commenting, aliases, pattern matching, and the IN operator.
Identify the proper syntax for the CASE statement, and interpret CASE statement processing and results.
Learn key principles of database design, including normalization, cardinality, and primary to foreign key mapping.

Concepts

0. Introduction

01 - Course structure and outline
02 - Introducing the course project
03 - Setting expectations

1. MySQL Intro and Setup

04 - Introduction
05 - Why learn SQL
06 - Brief history and SQL flavors
07 - MySQL installation overview
08 - Mac download - Community Server
09 - Mac download - MySQL Workbench
10 - PC download - Community Server plus Workbench
11 - Connecting Workbench to the server
12 - MySQL Workbench interface
13 - Preparing Workbench for the course
14 - Creating the maven movies database

2. Single Table Analysis - Part 1

15 - Introduction
16 - Getting to know the database
17 - The Big 6
18 - The SELECT statement
19 - The FROM clause
20 - SELECT FROM
21 - The USE statement
22 - Selecting specific columns
23 - Challenge - SELECT and FROM
24 - Solution - SELECT and FROM
25 - SELECT DISTINCT
26 - Challenge - SELECT DISTINCT
27 - Solution - SELECT DISTINCT
28 - The WHERE clause
29 - Common WHERE operators
30 - Challenge - The WHERE clause
31 - Solution - The WHERE clause
32 - Combining WHERE and AND
33 - Challenge - WHERE and AND
34 - Solution - WHERE and AND
35 - Combining WHERE and OR
36 - Challenge - Combining WHERE and OR
37 - Solution - Combining WHERE and OR
38 - Combining WHERE and IN
39 - The LIKE operator
40 - LIKE wildcard examples
41 - Challenge - The LIKE operator
42 - Solution - The LIKE operator
43 - The GROUP BY clause
44 - GROUP BY example
45 - Pro Tip - Using comments and aliases
46 - Challenge - GROUP BY
47 - Solution - GROUP BY
48 - Multiple dimension GROUP BY clauses
49 - Aggregate functions
50 - Challenge - Aggregate functions
51 - Solution - Aggregate functions
52 - The HAVING clause
53 - HAVING example
54 - Challenge - The HAVING clause
55 - Solution - The HAVING clause
56 - The ORDER BY clause
57 - ORDER BY example
58 - Challenge - The ORDER BY clause
59 - Solution - The ORDER BY clause
60 - Recap - The Big 6

3. Single Table Analysis - Part 2

61 - The CASE statement
62 - CASE example
63 - Common CASE operators
64 - Challenge - The CASE statement
65 - Solution - The CASE statement
66 - Pro tip - Pivoting with COUNT and CASE
67 - COUNT and CASE demo
68 - Challenge - COUNT and CASE
69 - Solution - COUNT and CASE

4. Mid-Course Project

70 - Challenge - Mid-course project
71 - Solution - Mid-course project

5. Analyzing Multiple Tables with Joins

72 - Introduction
73 - Normalization and Cardinality
74 - Relationship diagrams
75 - Multi-table querying
76 - Reviewing the maven movies database
77 - Common JOIN types
78 - INNER JOIN
79 - INNER JOIN example
80 - Challenge - INNER JOIN
81 - Solution - INNER JOIN
82 - LEFT JOIN
83 - LEFT JOIN example
84 - Challenge - LEFT JOIN
85 - Solution - LEFT JOIN
86 - RIGHT JOIN
87 - LEFT vs. INNER vs. RIGHT JOIN
88 - FULL OUTER JOIN
89 - Pro tip - Bridging unrelated tables
90 - Challenge - Bridging tables
91 - Solution - Bridging tables
92 - Multi-condition joins
93 - Challenge - Multi-condition joins
94 - Solution - Multi-condition joins
95 - The UNION operator
96 - UNION example
97 - Challenge - The UNION operator
98 - Solution - The UNION operator

6. Final Project

99 - Challenge - Final project
100 - Solution - Final project

MySQL Data Analysis

Authors

Maven Analytics

John Pauler