MySQL Training with Certification

Curriculum

Getting Started with MySQL
Learning Objectives:
This module introduces you to the world of MySQL, what it is, and about its benefits. Your hands-on introduction to MySQL will start with installing MySQL workbench and then creating a MySQL database that will be used throughout the course. You will then progress to understanding various data types that can be used for storing our data in MySQL. Then you will learn how to set up schema tables by using the CREATE data definition language statement. Learn the distinction between Primary Key and Foreign Key, use Primary Key to the model unique ID of your tables. Next, move to DML queries and learn about INSERT, UPDATE, and DELETE for manipulating data in the DB. Also, learn about data migration by understanding the DROP, ALTER commands. Lastly, learn about CHECK, NOT NULL, and UNIQUE constraints that could be used to add validation to the tables and columns.

Topics:

• MySQL and its Use Cases
• Setup MySQL database
• Discuss data types that can be used
• Leverage CREATE TABLE DDL for setting up a schema of tables
• Using Primary Key and Foreign Key constraints
• Insert, Update and Delete for DML operations
• Alter and Drop existing tables
• Adding and examining Constraints on tables

Hands-On:

• Leverage CREATE Table DDL for setting up the schema of tables, after learning its basics
• Insert, Update and Delete for DML operations, after learning about how to use Primary Key and Foreign Key constraints

2. Deep Dive into SQL Statements
Learning Objectives:
This module helps you investigate the more detailed aspects of MySQL. First, you will learn to use SELECT to retrieve data by primary key and use SELECT DISTINCT for removing duplicates. You will then learn to filter retrieved data by using the WHERE statement and combine WHERE with multiple conditions. Next, you will learn about using COUNT for retrieving information about the cardinality of data set in the table and leveraging LIMIT for narrowing the resulting data set to first occurrences. You will then learn how to order returned data in ascending or descending order by using ORDER BY ASC or DESC. Finally, you will learn how to use subqueries with IN to specify more complex limits and leverage BETWEEN for two conditions.

Topics:

• Select and Select Distinct for removing duplicates
• Leverage Where statement to narrow resulting data set
• Count and Limit for diagnosing and controlling resulting data set
• Specify ordering of retrieved data by using ORDER BY
• Leverage sub-queries with IN and BETWEEN

Hands-On:

• Count and Limit for diagnosing and controlling resulting data set, after learning about Count and Limit for diagnosing and controlling resulting data set
• Leverage sub-queries with IN and BETWEEN, after learning their basics

3. Analytical SQL queries
Learning Objectives:
Start by learning the concepts of MIN, MAX, and AVG for getting statistics about data. You will then learn how to use the GROUP BY statement and use it in the context of aggregating data in a customized manner. Finally, you will learn to filter grouped data by using the HAVING clause with the GROUP BY statement.

Topics:

• Getting statistics about data by using MIN MAX and AVG
• leverage GROUP BY to aggregate data in a custom way
• Conditional GROUP BY statements with HAVING clause

Hands-On:

• Learn how to get statistics about data by using MIN MAX and AVG, after learning the concepts
• Create conditional GROUP BY statements with HAVING clause, post a conceptual analysis

4. Joining Data from Multiple Data Sets
Learning Objectives:
This module explains the various SQL statements that are used to join data multiple datasets such as CREATE, JOINs, and more. You will start with the CREATE data definition and reference the primary key from another table to create relationships. Next, you will use INNER JOIN to fetch data from two tables that have a relation. You will use it to only fetch data that is present in both data sets. Next, you will use LEFT and RIGHT join to fetch data from two tables where data is not present in one of those data sources. And finally, you will see the use of FULL OUTER JOIN for joining data when it is not present in both datasets and leverage UNION for finding data sets.

Topics:

• Creating tables using primary key references
• INNER JOIN for full joins
• LEFT and RIGHT joins for joining data
• Full Outer Join and Union

Hands-On:

• Execute INNER JOIN for full joins
• Implement Full Outer Join and Union real-time

5. Advanced SQL Language Features
Learning Objectives:
Learn about advanced SQL features including mathematical functions such as ABS, MOD, FLOOR that are used to calculate data dynamically when retrieving it from tables. You will then learn how to work with text data and how to retrieve it and then move on to using Regular Expressions within SQL. Finally, you will earn how to solve complex SQL problems by using SELF JOIN and use it for multiple lookups on the same table.

Topics:

• Perform advanced calculations with mathematical functions
• Operations on String and Text columns
• Leverage Self-Join when needed to join the table with itself

Hands-On:

• Perform advanced calculations with mathematical functions once you’re familiar with the concepts
• Practical leveraging Self-Join when needed to join the table with itself

6. Using MySQL with Web Applications
Learning Objectives:
In this module, you will learn how to use MySQL with different popularly used web applications. You will work with Node.js (Express) application, Python (Django) application, and PHP (Laravel) application and learn how they interact with MySQL.

Topics:

• MySQL Application with Node.js (Express)
• MySQL Application with Python (Django)
• MySQL Application with PHP (Laravel)

7. Optimize Performance and Security of MySQL Queries
Learning Objectives:
This final module covers what it takes to optimize performance on your database and keep it secure. So, you will start by making your MySQL instance secure, create secure users, and enable encryption. You will learn to use EXPLAIN to understand queries, track performance problems, and learn how to alleviate them. You will understand how to create indexes on the most useful fields, learn how to pick the proper fields for indexing, and use indexing with queries. You will also learn how to use prepared statements both for performance and security reasons and use these prepared statements from code.
Next, you will learn how to make your data safe with backup policies, using backup for recovery, and understanding which log is needed to restart the MySQL DB. Finally, you will understand the performance of complex and huge queries and learn how to decompose them into smaller ones.

Topics:

• Securing MySQL
• Track Performance Problems with EXPLAIN
• Use Indexing to improve the performance of search queries
• Prepared Statements as a best MySQL query pattern
• Performing Backups and using Logs
• Decomposition of complex queries into simpler ones