Description

Description

MySQL for Developers will be useful for anyone who wants to truly master SQL using MySQL. The course covers relational database design, implementation of a database design, and the skills necessary to use a MySQL database as a backend to an application. Along with SQL query skills, the attendee will also learn about query optimization and importing and exporting data.

Hands-on activities are included designed to reinforce the content delivered in the course. A step-by-step document is provided to guide in the creation of a Windows VM configured to support the hands-on activities.

Training Objectives

• Relational Database Design: Create a relational database schema then implement that design in MySQL.
• SQL Queries: Covers both basic and advanced SQL queries. Understanding table joins and aliases creating grouping based queries using aggregate functions.
• Data Manipulation: Work with INSERT UPDATE DELETE and TRUNCATE commands. Discover how to handle duplicate data. Understand handling transaction based commands.
• SQL Programming: Data types operators and flow control statements. Using set operators and sub-queries. Accessing MySQL using Python.
• Accessing Metadata: Get information about your schema structure and database objects using the SHOW commands and the INFORMATION_SCHEMA tables.
• MySQL Objects: Create and use Views Functions Stored Procedures and Triggers.
• Query Optimization: Covers analyzing queries indexing and understanding query plans in order to create efficient queries.
• Export and Import Data: Move data into and out of your MySQL database.

Course Outline

• Module 1: Introduction to Relational Databases<
• Why a Relational Database?<br />
• Relational Database Management Systems<br />
• MySQL Community and Workbench<br />
• Module 2: Modeling a Relational Database<
• What is the Relational Model?<br />
• The Entity-Relationship Model<br />
• Designing the Database Schema<br />
• Module 3: SQL Language<
• About SQL<br />
• Basic Query Syntax<br />
• Querying with MySQL Workbench<br />
• Module 4: Implementing Database Design<
• Reviewing the Database Design<br />
• Creating a Database<br />
• Creating Tables<br />
• Creating Indexes<br />
• Module 5: Data Modification<
• Modifying Data<br />
• Handling Duplicate Values and Rows<br />
• Handling Transactions<br />
• Module 6: Advanced Queries<
• Table Joins and Aliases<br />
• Grouping and Aggregating<br />
• Set Operations<br />
• Sub-queries and Derived Tables<br />
• Module 7: Data Types and Variables<
• Data Types<br />
• Variables<br />
• Data Type Conversion<br />
• Module 8: Flow Control and Iteration<
• Operators<br />
• Decision Making<br />
• Iteration<br />
• Module 9: Viewing Data<
• Views<br />
• Functions<br />
• Accessing Metadata<br />
• Module 10: SQL Programming<
• Stored Procedures<br />
• Triggers<br />
• MySQL and Python<br />
• Module 11: Query Optimization<
• Optimization<br />
• Optimizing SQL Queries<br />
• EXPLAIN ANALYZE<br />
• Module 12: Importing and Exporting Data<
• Importing Data<br />
• Exporting Data<br />
• MySQL Workbench Wizard