uCertify

MySQL

(MYSQL.AJ1)/ISBN:978-1-64459-480-3

This course includes
Lessons
TestPrep
Hands-On Labs
AI Tutor (Add-on)
MySQL

The MySQL course empowers you with essential insights you need to make the most out of MySQL. This course breaks down how databases function and teaches you useful skills such as writing and managing SQL queries, designing databases, and improving their performance. By the end of this course, you'll have the practical expertise to create, manage, and optimize robust databases using MySQL. Whether you're a beginner or have some experience, this course is your gateway to mastering the art of database management.

Lessons

16+ Lessons | 133+ Exercises | 90+ Quizzes | 54+ Flashcards | 54+ Glossary of terms

TestPrep

55+ Pre Assessment Questions | 55+ Post Assessment Questions |

Hands-On Labs

35+ LiveLab | 31+ Video tutorials | 43+ Minutes

Need guidance and support? Click here to check our Instructor Led Course.

Here's what you will learn

Download Course Outline

Lessons 1: Preface

  • Who this course is for
  • What this course covers
  • To get the most out of this course

Lessons 2: Background Concepts

  • Introducing databases
  • Exploring MySQL
  • Exercise 1.01: Organizing data in a relational format
  • Exploring MySQL architecture
  • Storage engines (InnoDB and MyRocks)
  • Data modeling
  • Normalization
  • Activity 1.01: Creating an optimized table for an employee project
  • Summary

Lessons 3: Creating a Database

  • Developing databases
  • The MySQL Workbench GUI
  • Accessing MySQL through the command-line interface
  • Creating a database
  • Using Workbench to add a table
  • MySQL table indexes and foreign keys
  • Reverse engineering a database
  • Activity 2.01 – modifying the EER diagram, the model, and the database
  • Summary

Lessons 4: Using SQL to Work with a Database

  • An introduction to working with databases using SQL
  • Working with data
  • Backing up databases
  • Restoring databases
  • Working with SQL code to maintain a database
  • Creating a new database
  • Creating and modifying tables
  • SQL queries to create indexes and foreign keys
  • Activity 3.1 – creating a table with indexes and foreign keys
  • Altering table queries
  • Adding data to a table
  • Updating data in a record
  • Deleting data from tables
  • Blobs, files, and file paths
  • Activity 3.2 – adding image file paths to the database
  • Summary

Lessons 5: Selecting, Aggregating, and Applying Functions

  • An introduction to querying data
  • Querying tables in MySQL
  • Exercise 4.01 – working with simple queries
  • Filtering results
  • Exercise 4.02 – filtering results
  • Using functions on data
  • Exercise 4.03 – using functions
  • Aggregating data
  • Exercise 4.04 – aggregating data
  • Case statements
  • Exercise 4.05 – writing case statements
  • Activity 4.01 – collecting information for a travel article
  • Summary

Lessons 6: Correlating Data across Tables

  • Introduction to processing data across tables
  • Joining two tables
  • Analyzing subqueries
  • Common table expressions
  • Analyzing query performance with EXPLAIN
  • Activity 5.01: The Sakila video store
  • Activity 5.02: Generating a list of years
  • Summary

Lessons 7: Stored Procedures and Other Objects

  • Introduction to database objects
  • Exploring various database objects
  • Working with views
  • Activity 6.01 – updating the data in a view
  • Working with user-defined functions
  • Working with stored procedures
  • Working with IN, OUT, and INOUT
  • Exploring triggers
  • Using transactions
  • Summary

Lessons 8: Creating Database Clients in Node.js

  • Introduction to database management with Node.js
  • Best practices for SQL client development
  • JavaScript using Node.js
  • Connecting to MySQL
  • Activity 7.01 – building a database application with Node.js
  • Summary

Lessons 9: Working with Data Using Node.js

  • Interacting with databases
  • Inserting records in Node.js
  • Updating the records of a table
  • Activity 8.01 – multiple updates
  • Displaying data in browsers
  • ODBC connections
  • Activity 8.02 – designing a customer database
  • Summary

Lessons 10: Microsoft Access – Part 1

  • Introduction to MS Access
  • MS Access database application configurations
  • Upsizing an MS Access database to MySQL
  • Manually exporting MS Access tables
  • Adjusting field properties
  • Migrating with wizards
  • Linking to your tables and views
  • Refreshing linked MySQL tables
  • Activity 9.01 – linking the remaining MySQL tables to your MS Access database
  • Summary

Lessons 11: Microsoft Access – Part 2

  • Introduction to MS Access
  • Migrating an MS Access application to MySQL
  • Activity 10.01 – Converting gender and job statistics
  • Calling MySQL functions
  • Activity 10.02 – Creating a function and calling it
  • Calling MySQL stored procedures
  • Activity 10.03 – Creating MySQL stored procedures and using them in VBA
  • Using parameters
  • Activity 10.04 – Parameterized stored procedure (series list)
  • Activity 10.05 – Multiple parameters stored procedure (date list)
  • The Bad Bits form
  • Summary

Lessons 12: MS Excel VBA and MySQL – Part 1

  • Introduction to Excel
  • Exploring the ODBC connection
  • Exploring the Excel VBA structure
  • Learning about VBA libraries
  • Connecting to the MySQL database using VBA
  • Reading data from MySQL using VBA
  • Populating charts
  • Activity 11.01 – Creating a chart (artist track sales)
  • Summary

Lessons 13: Working With Microsoft Excel VBA – Part 2

  • An introduction to MySQL connections
  • Connecting to the MySQL database using ODBC
  • Exploring generic data read functions
  • Creating connections to MySQL in Excel
  • Inserting data using MySQL for Excel
  • Updating data using MySQL for Excel
  • Pushing data from Excel
  • Pivot tables
  • Activity 12.01 – building a MySQL-based Excel document
  • Summary

Lessons 14: Getting Data into MySQL

  • An introduction to data preparation
  • Working with the X DevAPI
  • Inserting documents
  • Loading data from a SQL file
  • Loading data from a CSV file
  • Loading data from a JSON file
  • Using the CSV storage engine to export data
  • Using the CSV storage engine to import data
  • Searching and filtering JSON documents
  • Using JSON functions and operators to query JSON columns
  • Using generated columns to query and index JSON data
  • Activity 13.01 – Exporting report data to CSV for Excel
  • Summary

Lessons 15: Manipulating User Permissions

  • Introduction to user permissions
  • Exploring users and accounts
  • Exercise 14.01 – creating users and granting permissions
  • Changing users
  • Flush privileges
  • Changing permissions
  • Exercise 14.02 – modifying users and revoking permissions
  • Using roles
  • Exercise 14.03 – using roles to manage permissions
  • Troubleshooting access problems
  • Activity 14.01 – creating users for managing the world schema
  • Summary

Lessons 16: Logical Backups

  • An introduction to backups
  • Understanding the basics of backups
  • Logical and physical backup
  • Types of restore
  • Scheduling backups
  • Using point-in-time recovery with binlog files
  • Activity 15.01 – backing up and restoring a single schema
  • Activity 15.02 – performing a point-in-time restore
  • Summary

Hands-on LAB Activities

Background Concepts

  • Creating a Relational Database
  • Creating a New Database Using MS Access
  • Normalizing Data from 2NF to 3NF
  • Normalizing Data from 1NF to 2NF

Creating a Database

  • Creating a Connection With the MySQL Workbench GUI
  • Creating a Database using MySQL Workbench
  • Creating a Table Using MySQL Workbench
  • Creating an Index
  • Creating a Foreign Key
  • Creating an EER Diagram

Using SQL to Work with a Database

  • Modifying an Existing Table
  • Adding a Record
  • Updating a Record
  • Deleting Data from a Table

Selecting, Aggregating, and Applying Functions

  • Filtering Results
  • Using Functions
  • Aggregating Data
  • Using CASE Statements

Correlating Data across Tables

  • Joining Two Tables
  • Using Common Table Expressions (CTEs)
  • Using the EXPLAIN Command

Stored Procedures and Other Objects

  • Creating a View
  • Creating a Stored Procedure
  • Using Triggers
  • Implementing a Transaction

Creating Database Clients in Node.js

  • Testing the Output of a Node.js Script
  • Writing to a Disk File

Working with Data Using Node.js

  • Creating a LAN DSN/ODBC Connection

Microsoft Access – Part 1

  • Creating an ODBC for a MySQL Database Schema
  • Upsizing a Table from Microsoft Access to MySQL

Microsoft Access – Part 2

  • Calling a MySQL Function
  • Creating a Parameterized Stored Procedure

MS Excel VBA and MySQL – Part 1

  • Activating the Developer tab and the VBA IDE
  • Creating a Code Module

Getting Data into MySQL

  • Importing a SQL File using MySQL Workbench